1、from sys.v_$temp_space_header f, dba_temp_files d, sys.v_$temp_extent_pool pwhere f.tablespace_name(+) = d.tablespace_nameand f.file_id(+) = d.file_idand p.file_id(+) = d.file_idgroup by二、查看使用临时表空间的SQLSelect se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value) as Space,tablespace,segty
2、pe,sql_textfrom v$sort_usage su, v$parameter p, 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 四、重建索引al
3、ter index PK_CROSSRELATION rebuild;五、查看表空间使用情况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 TABLESPA
4、CE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 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.T
5、ABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 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) *
6、 100, 2) | as Used_Pctfrom (select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_free_spacegroup by tablespace_name) free,(select tablespace_name, sum(bytes) / 1024 / 1024 as MBgroup by tablespace_name) totalwhere free.tablespace_name = total.tablespace_name;八、查找当前表级锁select sess.sid,sess.s
7、erial#,lo.oracle_username,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,
8、 v$sqltext bwhere a.sql_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;十一、查看死锁信息S
9、ELECT (SELECT usernameFROM v$sessionWHERE SID = a.SID) blocker, a.SID, is blocking(SELECT usernameWHERE 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
10、_time + 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.osus
11、er, s.machine, s.program, o.owner, o.object_name,o.object_type, a.event,FROM v$active_session_history a, dba_objects o, v$session sAND a.session_id = s.SIDGROUP BY o.owner,o.object_name,o.object_type,a.event,a.session_id,s.program,s.machine,s.osuser十四、查看等待最多的SQLSELECT a.program, a.session_id, a.user
12、_id, d.username, s.sql_text,FROM v$active_session_history a, v$sqlarea s, dba_users dAND 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全部文本*/ SE.SID, /*会话的唯一标识,通常要对某个会话进行分析前,首先就需要获得该会话的SID。*/ -SE.SERIAL#, /*会
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1