oracle数据库日常管理语句汇总.docx

1、oracle数据库日常管理语句汇总一、查看临时表空间使用情况Selectf.tablespace_name,sum(f.bytes_free + f.bytes_used)/1024/1024/1024 total GB,sum(f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)/1024/1024/1024 Free GB,sum(nvl(p.bytes_used, 0)/1024/1024/1024 Used GBfrom sys.v_$temp_space_header f, dba_temp_files d, sys.v_$temp_

2、extent_pool pwhere f.tablespace_name(+) = d.tablespace_nameand f.file_id(+) = d.file_idand p.file_id(+) = d.file_idgroup byf.tablespace_name二、查看使用临时表空间的SQLSelect se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value) as Space,tablespace,segtype,sql_textfrom v$sort_usage su, v$parameter p

3、, v$session se, v$sql swhere p.name = db_block_sizeand su.session_addr = se.saddrand s.hash_value = su.sqlhashand s.address = su.sqladdrorder by se.username, se.sid三、收缩临时表空间alter tablespace temp shrink space;alter tablespace temp shrink tempfile 四、重建索引alter index PK_CROSSRELATION rebuild;五、查看表空间使用情况

4、SELECT UPPER(F.TABLESPACE_NAME) 表空间名,D.TOT_GROOTTE_MB 表空间大小(M),D.TOT_GROOTTE_MB - F.TOTAL_BYTES 已使用空间(M),TO_CHAR(ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),990.99) | % 使用比,F.TOTAL_BYTES 空闲空间(M),F.MAX_BYTES 最大块(M)FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024),

5、2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY

6、 1;六、查询表空间的总容量select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name;七、查询表空间使用率select total.tablespace_name,round(total.MB, 2) as Total_MB,考试大论坛round(total.MB - free.MB, 2) as Used_MB,round(1 - free.MB / total.MB) * 100, 2) | % as Used_Pctfrom (select table

7、space_name, sum(bytes) / 1024 / 1024 as MBfrom dba_free_spacegroup by tablespace_name) free,(select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name) totalwhere free.tablespace_name = total.tablespace_name;八、查找当前表级锁select sess.sid,sess.serial#,lo.oracle_user

8、name,lo.os_user_name,ao.object_name,lo.locked_modefrom v$locked_object lo,dba_objects ao,v$session sesswhere ao.object_id = lo.object_id and lo.session_id = sess.sid;杀掉锁表进程：alter system kill session 436,35123;九、监控当前数据库谁在运行什么SQL语句select osuser, username, sql_textfrom v$session a, v$sqltext bwhere a.s

9、ql_address =b.address order by address, piece;十、找使用CPU多的用户sessionselect a.sid,spid,status,substr(a.program,1,40) prog, a.terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere c.statistic#=12 andc.sid=a.sid anda.paddr=b.addrorder by value desc;十一、查看死锁信息SELECT (SELECT usernam

10、eFROM v$sessionWHERE SID = a.SID) blocker, a.SID, is blocking,(SELECT usernameFROM v$sessionWHERE SID = b.SID) blockee, b.SIDFROM v$lock a, v$lock bWHERE a.BLOCK = 1 AND b.request 0 AND a.id1 = b.id1 AND a.id2 = b.id2;十二、具有最高等待的对象SELECT o.OWNER,o.object_name, o.object_type, a.event,SUM (a.wait_time

11、+ a.time_waited) total_wait_timeFROM v$active_session_history a, dba_objects oWHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATEAND a.current_obj# = o.object_idGROUP BY o.OWNER,o.object_name, o.object_type, a.eventORDER BY total_wait_time DESC;十三、查看具有最高等待的对象SELECT a.session_id, s.osuser, s.

12、machine, s.program, o.owner, o.object_name,o.object_type, a.event,SUM (a.wait_time + a.time_waited) total_wait_timeFROM v$active_session_history a, dba_objects o, v$session sWHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATEAND a.current_obj# = o.object_idAND a.session_id = s.SIDGROUP BY o.

13、owner,o.object_name,o.object_type,a.event,a.session_id,s.program,s.machine,s.osuserORDER BY total_wait_time DESC;十四、查看等待最多的SQLSELECT a.program, a.session_id, a.user_id, d.username, s.sql_text,SUM (a.wait_time + a.time_waited) total_wait_timeFROM v$active_session_history a, v$sqlarea s, dba_users dWH

14、ERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATEAND a.sql_id = s.sql_idAND a.user_id = d.user_idGROUP BY a.program, a.session_id, a.user_id, s.sql_text, d.username;十五、显示正在等待锁的所有会话SELECT * FROM DBA_WAITERS;十七、查数据库中正在执行的SQL SELECT SE.INST_ID, -实例 SQ.SQL_TEXT, /*SQL文本*/ SQ.SQL_FULLTEXT, /*SQL全

15、部文本*/ SE.SID, /*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/ -SE.SERIAL#, /*会话的序号*/ SQ.OPTIMIZER_COST AS COST_, /* COST 值*/ SE.LAST_CALL_ET CONTINUE_TIME, /*执行时间 可能是单个sql也可能是整个功能*/ SE.PREV_EXEC_START, /*SQL execution start of the last executed SQL statement*/ SE.EVENT, /*等待事件*/ SE.LOCKWAIT, /*是否等待LOCK(SE

16、，P)*/ SE.MACHINE, /*客户端的机器名。(WORKGROUPPC-201211082055)*/ SQ.SQL_ID, /*SQL_ID*/ SE.USERNAME, /*创建该会话的用户名*/ SE.LOGON_TIME /*登陆时间*/ -SE.TERMINAL, /*客户端运行的终端名。(PC-201211082055)*/ -,SQ.HASH_VALUE, /*一个SQL 产生的HASH 值*/ -SQ.PLAN_HASH_VALUE /*执行SQL的HASH值(解析后HASH值)，与SQL_ADDRESS关联查询其他SQL相关视图后即可查询会话当前正在执行的SQL语句

17、*/ FROM GV$SESSION SE, /*会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句*/ /*GV$SQLAREA 多节点 */ GV$SQLAREA SQ /*跟踪所有SHARED POOL中的共享CURSOR信息，包括 执行次数，逻辑读，物理读等*/ WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE AND SE.STATUS = ACTIVE AND SE.SQL_ID = SQ.SQL_ID AND SQ.INST_ID = SE.INST_

18、ID AND SE.USERNAME is not null; -过滤条件 -AND SE.USERNAME = FWSB -用户名 -AND SQ.COMMAND_TYPE IN (2, 3, 5, 6, 189) -AND SE.SID != USERENV (SID)/*rac集群环境误用*/ -AND MACHINE != WORKGROUPMHQ-PC ;十八、每天执行慢的SQL 十九、查看非绑定变量的SQLSELECT S.SQL_TEXT, S.SQL_FULLTEXT, S.SQL_ID, ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (E

19、XECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END), 2) 执行时间S, S.EXECUTIONS 执行次数, S.OPTIMIZER_COST COST, S.SORTS, S.MODULE, -连接模式（JDBC THIN CLIENT：程序） - S.LOCKED_TOTAL, S.PHYSICAL_READ_BYTES 物理读, - S.PHYSICAL_READ_REQUESTS 物理读请求, S.PHYSICAL_WRITE_REQUESTS 物理写, - S.PHYSICAL_WRITE_BY

20、TES 物理写请求, S.ROWS_PROCESSED 返回行数, S.DISK_READS 磁盘读, S.DIRECT_WRITES 直接路径写, S.PARSING_SCHEMA_NAME, S.LAST_ACTIVE_TIME FROM GV$SQLAREA S WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END), 2) 5 -100 0000微秒=1S - AND S.PARSING_SCHEMA

21、_NAME = USER AND TO_CHAR(S.LAST_LOAD_TIME, YYYY-MM-DD ) = TO_CHAR( SYSDATE, YYYY-MM-DD ) AND S.COMMAND_TYPE IN (2, 3, 5 , 6, 189) /*值对应类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询V$SQLCOMMAND*/ AND MODULE = JDBC Thin Client ORDER BY 执行时间S DESC; SELECT V.SQL_ID, V.SQL_FULLTEXT, V.PARSING_SCHEM

22、A_NAME, FM.EXECUTIONS_COUNT, FM.ELAPSED_TIME FROM (SELECT L.FORCE_MATCHING_SIGNATURE MATHCES, MAX(L.SQL_ID | L.CHILD_NUMBER) MAX_SQL_CHILD, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC ) RANKING, ROUND(SUM (ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE

23、 EXECUTIONS END), 5) ELAPSED_TIME, SUM(L.EXECUTIONS) EXECUTIONS_COUNT FROM V$SQL L WHERE TO_CHAR(TO_DATE(LAST_LOAD_TIME, YYYY-MM-DD HH24:MI:SS), YYYY-MM-DD) = TO_CHAR(SYSDATE - 1, YYYY-MM-DD) - 当天 LAST_LOAD_TIME(VARCHAR类型,LOADED INTO THE LIBRARY CACHE TIME) AND L.MODULE LIKE %JDBC% -程序连接 AND L.FORCE

24、_MATCHING_SIGNATURE 0 AND L.PARSING_SCHEMA_NAME = UPPER (&USERNAME) -用户 AND L.COMMAND_TYPE IN (2, 3, 5 , 6, 189) -命令类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询V$SQLCOMMAND GROUP BY L.FORCE_MATCHING_SIGNATURE HAVING COUNT (*) 5) FM, V$SQL V WHERE FM.MAX_SQL_CHILD = (V.SQL_ID | V.CHILD_NUMBER)

25、 AND EXECUTIONS_COUNT = 50 -执行次数超过50次先筛选改写，后续慢慢在范围小 ORDER BY FM.RANKING; -V$SQL_BIND_CAPTURE -记录包含变量得表.包括 ROWNUM:1 变量二十、查看LOG切换频率 select b.SEQUENCE#, b.FIRST_TIME, a.SEQUENCE#, a.FIRST_TIME, round(a.FIRST_TIME - b.FIRST_TIME) * 24 ) * 60, 2) from v$log_history a, v$log_history b where a.SEQUENCE# =

26、b.SEQUENCE# + 1 and b.THREAD# = 1 order by a.SEQUENCE# desc;二十一、查看SQL执行进度 -显示运行时间超过6秒的数据库操作的状态 SELECT A.SID, A.SERIAL#, OPNAME, TARGET, -对象 TO_CHAR(START_TIME, YYYY-MM-DD HH24:MI:SS ) START_TIME, -开始时间 (SOFAR / TOTALWORK) * 100 PROGRESS, -进度比 TIME_REMAINING, -估算剩余时间 ELAPSED_SECONDS, -运行时间S A.SQL_ID

27、FROM V$SESSION_LONGOPS A WHERE SID = ; * 其中SID和SERIAL#是与V$SESSION中的匹配的， * OPNAME：指长时间执行的操作名.如： TABLE SCAN * TARGET：被操作的OBJECT_NAME. 如：TABLEA * TARGET_DESC：描述TARGET的内容 * SOFAR：这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。 * TOTALWORK：指目标对象一共有多少数量（预计）。如块的数量。 * START_TIME：进程的开始时间 * LAST_UPDATE_TIM：最后一次调用SET_SESSION

28、_LONGOPS的时间 * TIME_REMAINING： 估计还需要多少时间完成，单位为秒 * ELAPSED_SECONDS：指从开始操作时间到最后更新时间 * MESSAGE：对于操作的完整描述，包括进度和操作内容。 * USERNAME：与V$SESSION中的一样。 * SQL_ADDRESS：关联V$SQL * SQL_HASH_VALUE：关联V$SQL * QCSID：主要是并行查询一起使用。 二十二、查询外键字段在主键表中没有索引的 SELECT C.*, C1.r_constraint_name, c2.table_name, T.NUM_ROWS, create inde

29、x idx_ | c.table_name | _ | column_name | on | c.table_name | ( | column_name | ); FROM USER_CONS_COLUMNS C JOIN USER_CONSTRAINTS C1 ON C1.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND C1.CONSTRAINT_TYPE = R AND (C.TABLE_NAME, C.COLUMN_NAME) NOT IN ( SELECT TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS I) JOIN USER_TABLES T ON T.TABLE_NAME = C.TABLE_NAME join USER_CONSTRAINTS c2 on c1.r_constraint_name = c2.constraint_name; 博客：为什么子表外键列需要建立索引? http: