1.查看正在执行的存储过程 :通过视图v$db_object_cache
select owner,name from v$db_object_cache where type like '%PROCE%' and locks >0 and pins >0;
2.查看存储过程的内容:
SELECT TEXT FROM user_source WHERE NAME = '存储过程名' ORDER BY line;
3. 我想要查看之前执行过的sql语句或者存储过程执行的时间,要怎么办呢?
——可以通过oracle数据字典的一个视图 v$sql。这个可以看到执行过的sql...
但是这里有比较特别的地方,就是同一个语句或者存储过程,如果执行多次,不是多条记录,而是一条记录,只是executions在增加,LAST_ACTIVE_TIME 也是在更新,所以要求得执行时间使用ELAPSED_TIME/executions/1000
——总的时间/次数/1000000(微秒转为秒)
SELECT SQL_TEXT, LAST_ACTIVE_TIME,SQL_FULLTEXT ,LAST_LOAD_TIME,executions,
ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/executions
FROM V$SQL WHERE INSTR(SQL_TEXT,'过程名')>0 ORDER BY LAST_ACTIVE_TIME DESC
(V$SQL里面有很多的字段,比较难分清楚,ELAPSED_TIME是执行时间,有误差是因为执行多次取平均)(另外,Java程序等调用的存储过程,这里是没有记录的,O(∩_∩)O)
下面链接这里有V$SQL具体字段的解释(*^▽^*),然后发现,比较麻烦的是很多时间用的是NUMBER类型,比如:所以上面的语句用的是ELAPSED_TIME/1000000/executions得到的秒
ELAPSED_TIME | NUMBER | 该游标用于解析/执行/抓取的已用时间(以微秒为单位) |
4.刷新一个表的统计信息,通过调用DBMS_STATS包
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => '数据库用户名',
tabname => '表名',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
/
5.查看表的索引状态
select status,T.* from user_indexes T where table_name='表名'
索引空间 ,表空间 ,剩余空间
select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX'
and segment_name like '%表名%' group by segment_name;
select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments
where segment_type='TABLE' --and segment_name like '%表名%'
group by segment_name;
select tablespace_name ,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;
6.查看被锁/占用的对象(表...)
SELECT L.SESSION_ID SID,
S.SERIAL#,
L.LOCKED_MODE 锁模式,
L.ORACLE_USERNAME 登录用户,
L.OS_USER_NAME 登录机器用户名,
S.MACHINE 机器名,
S.TERMINAL 终端用户名,
O.OBJECT_NAME 被锁对象名,
S.LOGON_TIME 登录数据库时间
FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
ORDER BY SID, S.SERIAL#;
杀死某个占用的进程
alter system kill session '589,15771';--(其中24,111分别是上面查询出的sid,serial#
7.删除一个表重复的数据
前提:一个表里面,存在重复的数据,记录id相同,但是jgbs不同
操作:(多个重复字段同样的操作,这里单字段多字段都可以,多字段如:id和name)
--1.根据id分组,查出重复的id
select id from 表名 group by id having count(id)>1
--2.根据id分组,查出重复的id,得到每个重复id记录中rowid最小(或最大)的一个
select min(rowid) from 表名 group by id having count(id)>1
--3.删除重复id中除了rowid最小的其他的记录
delete from 表名 where
id in (select id from 表名 group by id having count(id)>1 )
and rowid not in (select min(rowid) from 表名 group by id having count(id)>1 )
另外的方法,删除重复的列中不是最小rowid的列,保留一条记录
--方法一: 分组,每个组里面最小的一条记录,但是执行慢
delete from 表名 y where y.rowid not in (select min(n.rowid) from 表名 n group by n.wzbs )
--方法二: [更快]
delete from 表名 y where y.rowid > (select min(n.rowid) from 表名 n where n.wzbs = y.wzbs)
--遍历Y表,找到N表中相同的记录,找出最小的一条,其他的删除[Y表N表是同一个表]
8.查看一个表的创建时间
select created from dba_objects where object_name = '表名' and owner = '用户名'
9.查看一个表有无对应的外键 B是有外键的表 ,A的某个字段作为B的外键
SELECT A.OWNER,
A.TABLE_NAME PRIMARY_TABLE_NAME,
A.CONSTRAINT_NAME PRIMARY_TABLE_KEY_NAME,
B.OWNER,
B.TABLE_NAME FOREIGN_TABLE,
B.CONSTRAINT_NAME FOREIGN_TABLE_FOREIGN_KEY_NAME,
B.STATUS FOREIGN_TABLE_FOREIGN_KEY_STAT
FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
AND A.TABLE_NAME = '表名'
AND A.OWNER = '用户名'
AND B.OWNER = '用户名'
ORDER BY 1, 2, 3, 4;
10. 备份表;从一个数据库用户复制表的结构和数据;
复制之后数据和表字段都有,但是,字段备注和主键,索引等不在。
create table 备份表名 as select * from 表名;
只复制表结构:
create table table_name_new as select * from table_name_old where 1=2;
--或者:
create table table_name_new like table_name_old;
11.扩展表空间【a.为表空间添加数据文件 b.设置表空间自动增长】
---/***执行插入的时候:报错:ORA-01653: 表 A 无法通过 128 (在表空间... 中) 扩展***/
--1.查询表空间信息
select * from DBA_TABLESPACES where tablespace_name = '表空间名'
--2.查询剩余表空间 bytes转换成M
SELECT tablespace_name,sum(bytes)/1024/1024 FROM DBA_FREE_SPACE where tablespace_name = '表空间名' group by tablespace_name
--3.表空间的 数据文件信息[一个表空间有多个表空间文件]
select * from DBA_DATA_FILES where tablespace_name = '表空间名'
--4.添加一个数据文件[表空间数据文件 10M好像太小] 数据文件路径名称,如:/data/oracle/DATA01
alter tablespace 表空间名 add datafile '数据文件路径名称' size 10m autoextend on next 5M maxsize 1000M;
--修改表空间的大小[不要改太小了] 20000M
alter database datafile '数据文件路径名称' resize 4000m
--5.或者设置表空间自动增长?扩大数据文件的大小?
--查询当前数据库中表空间是否为自动扩展
select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = '表空间名';
12. 分区
---删除分区数据【只删数据不删除分区】
ALTER TABLE 表名 TRUNCATE PARTITION 分区名;
---删除分区【包括数据和分区】
ALTER TABLE tableName DROP PARTITION partionName;
13.约束
14.不同数据库通过链接复制表结构、表数据
--第一步:在要建表的数据库用户下,建立链接
CREATE database link_name --//链接名称
CONNECT to username --//被连接的数据库的用户名
IDENTIFIED by password --//被连接的数据库的密码
using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 地址)(PORT = 端口号))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SID或服务名)))';--//被连接的数据库配置
--第二步:创建表包括复制数据,通过 @链接名
CREATE TABLE TABLE_NEW_A AS SELECT * FROM TABLE_A@link_name WHERE 1=1;
-- 复制表数据
INSERT INTO TABLE_NEW_A SELECT * FROM TABLE_A@link_name T ;
--第三步:查询然后删除链接
select * from dba_db_links;
drop database link link_name;