oracle dba常用腳本2

11、表空間實有、現有、使用情況查詢對比
SELECT TABLESPACE_NAME 表空間,TO_CHAR(ROUND(BYTES / 1024, 2), '99990.00') || '' 實有,TO_CHAR(ROUND(FREE / 1024, 2), '99990.00') || 'G' 現有,TO_CHAR(ROUND((BYTES - FREE) / 1024, 2), '99990.00') || 'G' 使用,TO_CHAR(ROUND(10000 * USED / BYTES) / 100, '99990.00') || '%' 比例FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,FLOOR(A.BYTES / (1024 * 1024)) BYTES,FLOOR(B.FREE / (1024 * 1024)) FREE,FLOOR((A.BYTES - B.FREE) / (1024 * 1024)) USEDFROM (SELECT TABLESPACE_NAME TABLESPACE_NAME, SUM(BYTES) BYTESFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME TABLESPACE_NAME, SUM(BYTES) FREEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) BWHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)ORDER BY FLOOR(10000 * USED / BYTES) DESC;

在這里插入圖片描述

12、數據文件自動擴展
ALTER DATABASE DATAFILE '/oradata/zghx/TS_XMGL_DATA1.dbf' AUTOEXTEND ON NEXT  100M MAXSIZE  30000M;

13、查看回滾
select t.STATE,t.UNDOBLOCKSDONE 已回滾,t.UNDOBLOCKSTOTAL - t.UNDOBLOCKSDONE 剩余,t.UNDOBLOCKSTOTAL 回滾總量from v$fast_start_transactions twhere state = 'RECOVERING';

14、查看session 回滾大小
select a.SID,a.USERNAME,b.XIDUSN,b.USED_UREC,b.USED_UBLK,r.RSSIZE / 1024 / 1024from v$transaction b, v$session a, v$rollstat rwhere a.SADDR = b.SES_ADDRand b.XIDUSN = r.USNand a.sid = 'xxx'

15、用戶操作權限
SELECT *FROM dba_TAB_PRIVS twhere t.owner not in ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'PERFSTAT','MGMT_VIEW', 'CTXSYS', 'MDSYS', 'DMSYS', 'OLAPSYS','ORDSYS', 'XDB', 'WMSYS', 'SYSMAN', 'ORDPLUGINS')order by t.owner;

在這里插入圖片描述 在這里插入代碼片

16、檢查一下系統當前的鎖
SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCKFROM V$LOCKWHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME IS NOT NULL);

在這里插入圖片描述

17、查找前十條磁盤讀多(性能差)的sql
SELECT SID, TYPE, ID1, ID2, LMODE, CTIMEFROM V$LOCKWHERE ADDR IN(SELECT ADDR FROM V$TRANSACTION WHERE START_DATE < TRUNC(SYSDATE));

18、查看占io較大的正在運行的sessionSELECT se.sid,se.serial#,se.sql_address,se.username,se.status,se.terminal,se.program,se.MODULE,pr.SPID,st.event,st.p1text,si.physical_reads,si.block_changesFROM v$session se, v$session_wait st, v$sess_io si, v$process prWHERE st.sid = se.sidAND st.sid = si.sidAND se.PADDR = pr.ADDRAND se.sid > 6AND st.wait_time = 0AND st.event NOT LIKE 'SQL%'ORDER BY physical_reads DESC;

在這里插入圖片描述

19、查看當前有哪些用戶正在使用數據
select a.osuser,a.username,a.machine,b.cpu_time / b.executions / 1000000 || 's',b.sql_text 　　from v$session a, v$sqlarea b 　　where a.sql_address = b.address 　　order by cpu_time / executions desc;

在這里插入圖片描述

20、查看鎖
col object_name for a35;
col machine     for a30;
select object_name,object_type,machine,s.sid,s.serial#,s.username,s.program,s.module,s.statusfrom v$locked_object l, dba_objects o, v$session swhere l.object_id = o.object_id(+)and l.session_id = s.sid;

21、查各session的邏輯讀及物理讀
col machine for a15
col module for a25
select s.sid,s.serial#,s.process,s.machine,s.module,si.block_gets,si.physical_readsfrom v$session s,v$session_wait sw,v$sess_io si 
where s.status='ACTIVE'and s.sid=sw.sid(+) and s.sid=si.sid(+)and s.module is not null;

在這里插入圖片描述

22、查長事務
SELECT SID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,to_char(start_time, 'yyyymmddhh24miss') strtime,ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"FROM V$SESSION_LONGOPSWHERE TOTALWORK != 0AND SOFAR <> TOTALWORK;

23、查長事務相關的SQL
select s.sid, s.serial#, q.sql_textfrom v$session s, v$sql qwhere s.sid in (SELECT SIDFROM V$SESSION_LONGOPSWHERE TOTALWORK != 0AND SOFAR <> TOTALWORK)and s.sql_address = q.address;

24、查大量的Enqueue Wait時查哪些會話閑著	
select sysdate day,sid,serial#,machine,nvl(sql_hash_value, prev_hash_value) hash_value,last_call_etfrom v$sessionwhere sid in (select sidfrom v$lockwhere block = 1UNION ALLselect sidfrom v$lockwhere type = 'TX'and lmode > 1)and last_call_et > 60and type = 'USER'and status <> 'ACTIVE';

25、查語句使用的臨時空間
SELECT S.sid || ',' || S.serial# sid_serial,S.username,S.program,T.blocks * TBS.block_size / 1024 / 1024 mb_used,T.tablespace,T.sqladdr address,Q.hash_value,Q.sql_text 　　FROM v$sort_usage T, v$session S, v$sql Q, dba_tablespaces TBS 　　WHERE T.session_addr = S.saddr 　　AND T.sqladdr = Q.address(+) 　　AND T.tablespace = TBS.tablespace_name 　　ORDER BY S.sid;

26、查看每個會話的UGA內存使用
select username, value || 'bytes' "Current UGA memory"from v$session sess, v$sesstat stat, v$statname namewhere sess.sid = stat.sidand stat.statistic# = name.statistic#and name.name = 'session uga memory';

27、查各session消耗CPU的問題
select ss.sid,se.machine,ss.value CPU,se.username,se.program,se.process,se.modulefrom v$sesstat ss, v$session sewhere ss.statistic# in(select statistic#from v$statnamewhere name = 'CPU used by this session')and se.sid = ss.sidand ss.sid > 6and se.module is not nulland se.status = 'ACTIVE'order by ss.value;

28、查非并行等待語句
select s.sid, s.serial#, q.sql_textfrom v$session s, v$sql qwhere s.sid in (select sidfrom v$session_waitwhere event not like 'PX%'and state = 'WAITING')and s.sql_address = q.address;

29、查看各session的buffer cache hit ratio
select substr(a.username, 1, 12) "User",a.sid "sid",a.SERIAL#,a.PROGRAM,b.consistent_gets "ConsGets",b.block_gets "BlockGets",b.physical_reads "PhysReads",100 * round((b.consistent_gets + b.block_gets - b.physical_reads) /(b.consistent_gets + b.block_gets),3) HitRatiofrom v$session a, v$sess_io bwhere a.sid = b.sidand (b.consistent_gets + b.block_gets) > 0and a.username is not nullorder by HitRatio asc;

30、查詢最近1個小時內的跑得時間最長的SQL
SELECT a.sql_id,a.parsing_schema_name || '.' uname,round(a.buffer_gets / decode(a.executions, 0, 1, a.executions)) per_bu,TRUNC(((a.ELAPSED_TIME / decode(a.executions, 0, 1, a.executions)) /1000000),2) etime,round(buffer_gets / decode(a.ROWS_PROCESSED, 0, 1, a.ROWS_PROCESSED)) buf_row,a.executions,a.sql_fulltext,a.sql_text,a.moduleFROM v$sqlarea a,(SELECT DISTINCT sql_idFROM v$active_session_history bWHERE b.SAMPLE_TIME BETWEEN SYSDATE - 120 / 1440 AND SYSDATE) bWHERE a.sql_id = b.sql_idAND a.MODULE NOT IN ('PL/SQL Developer', 'plsqldev.exe', 'ORACLE.EXE')AND TRUNC(((a.ELAPSED_TIME / decode(a.executions, 0, 1, a.executions)) /1000000),2) > 100ORDER BY 3 DESC;

31、Oracle一個大事務的SQL往往不知道運行到了哪里,可以使用如下SQL查看執行進度。
SELECT se.sid,opname,TRUNC(sofar / totalwork * 100, 2) pct_work,elapsed_seconds elapsed,ROUND(elapsed_seconds * (totalwork - sofar) / sofar) remain_time,sql_textFROM v$session_longops sl, v$sqlarea sa, v$session seWHERE sl.sql_hash_value = sa.hash_valueAND sl.sid = se.sidAND sofar != totalworkORDER BY start_time;

32、查詢表空間的剩余量
SELECT D.TABLESPACE_NAME "表空間名",T.EXTENT_MANAGEMENT,T.SEGMENT_SPACE_MANAGEMENT,D.TOT_GROOTTE_MB "表空間大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",ROUND(F.TOTAL_BYTES / 1024) "空閑大小G",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比"FROM (SELECT TABLESPACE_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) D,DBA_TABLESPACES TWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)AND D.TABLESPACE_NAME = T.TABLESPACE_NAME
--and D.TABLESPACE_NAME not in('UNDOTBS1','UNDOTBS2','UNDOTBS3','UNDOTBS4')ORDER BY 7 DESC;

33、臨時表空間
SELECT A.TABLESPACE_NAME TABLESPACE,D.MB_TOTAL,SUM(A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_USED,D.MB_TOTAL - SUM(A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_FREEFROM GV$SORT_SEGMENT A,(SELECT B.NAME, C.BLOCK_SIZE, SUM(C.BYTES) / 1024 / 1024 MB_TOTALFROM GV$TABLESPACE B, GV$TEMPFILE CWHERE B.TS# = C.TS#GROUP BY B.NAME, C.BLOCK_SIZE) DWHERE A.TABLESPACE_NAME = D.NAMEGROUP BY A.TABLESPACE_NAME, D.MB_TOTAL;

34、查詢表空間的剩余量
SELECT D.TABLESPACE_NAME "表空間名",T.EXTENT_MANAGEMENT,T.SEGMENT_SPACE_MANAGEMENT,D.TOT_GROOTTE_MB "表空間大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",ROUND(F.TOTAL_BYTES / 1024) "空閑大小G",TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比"FROM (SELECT TABLESPACE_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) D,DBA_TABLESPACES TWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)AND D.TABLESPACE_NAME = T.TABLESPACE_NAMEand D.TABLESPACE_NAME not in('UNDOTBS1', 'UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4')ORDER BY 7 DESC;

在這里插入圖片描述

35、失效索引檢查
SELECT OWNER INDEX_OWNER,INDEX_NAME,INDEX_TYPE,'N/A' PARTITION_NAME,STATUS,TABLE_NAME,TABLESPACE_NAMEFROM DBA_INDEXESWHERE STATUS = 'UNUSABLE'
UNION ALL
SELECT A.INDEX_OWNER,A.INDEX_NAME,B.INDEX_TYPE,A.PARTITION_NAME,A.STATUS,B.TABLE_NAME,A.TABLESPACE_NAMEFROM DBA_IND_PARTITIONS A, DBA_INDEXES BWHERE A.INDEX_NAME = B.INDEX_NAMEAND A.INDEX_OWNER = B.OWNERAND A.STATUS = 'UNUSABLE';

36、表行數，使用的塊數，空的塊數，塊的使用率，行遷移和鏈接的數量，pctfree，pctused的數據，行的平均大小
select t.table_name,t.last_analyzed,blocks, --表中數據所占的數據塊數empty_blocks, --表中的空塊數avg_space, --數據塊中平均的使用空間chain_cnt, --表中行連接和行遷移的數量avg_row_len, --每條記錄的平均長度t.num_rows --行數from dba_tables twhere t.table_name = upper('emp')and t.owner = upper('scott');