DBA常用SQL語句
檢視錶空間的名稱及大小:
SQL>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
檢視錶空間物理檔案的名稱及大小:
SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
檢視回滾段名稱及大小:
SQL>select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+) order by segment_name;
如何檢視某個回滾段裡面，跑的什麼事物或者正在執行什麼sql語句:
SQL>select d.sql_text,a.name
from v$rollname a,v$transaction b,v$session c,v$sqltext d
where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=
d.address and c.sql_hash_value=d.hash_value
and a.usn=1;
(備註：你要看哪個，就把usn=?寫成幾就行了)
檢視控制檔案:
SQL>select * from v$controlfile;
檢視日誌檔案:
SQL> col member format a50
SQL>select * from v$logfile;
如何檢視當前SQL*PLUS使用者的sid和serial#:
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');
如何檢視當前資料庫的字符集:
SQL>select userenv('language') from dual;
SQL>select userenv('lang') from dual;
怎麼判斷當前正在使用何種SQL最佳化方式:
用explain plan產生EXPLAIN PLAN，檢查PLAN_TABLE中ID=0的POSITION列的值。
SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;
如何檢視系統當前最新的SCN號：
SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;
在ORACLE中查詢TRACE檔案的指令碼:
SQL>select u_dump.value || '/' || instance.value || '_ora_' ||
v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid');
SQL>select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from (select p.spid from sys.v_$mystat m,sys.v_$session s,
sys.v_$process p where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d;
如何檢視客戶端登陸的IP地址:
SQL>select sys_context('userenv','ip_address') from dual;
如何在生產資料庫中建立一個追蹤客戶端IP地址的觸發器：
SQL>create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
REM 記錄登陸資訊的觸發器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
insert into session_history
select username,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
from v$session where audsid = userenv('sessionid');
END;
查詢當前日期:
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
檢視所有表空間對應的資料檔名：
SQL>select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
檢視錶空間的使用情況:
SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space group by tablespace_name;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
column tablespace_name format a18;
column Sum_M format a12;
column Used_M format a12;
column Free_M format a12;
column pto_M format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M' Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M' Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,
sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100 FreePercentRatio from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by b.file_id,b.tablespace_name,b.bytes
order by b.tablespace_name) s group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc;
檢視資料檔案的hwm（可以resize的最小空間）和檔案頭大小:
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 "USED_SPACE(HWM)",
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
資料檔案大小及頭大小:
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 Used_space,
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
(執行以上查詢，我們可以如下資訊：
Totle_pace:該資料檔案的總大小，位元組為單位
Free_space:該資料檔案的剩於大小，位元組為單位
Used_space:該資料檔案的已用空間，位元組為單位
Data_space:該資料檔案中段資料佔用空間，也就是資料空間，位元組為單位
File_Head:該資料檔案頭部佔用空間，位元組為單位)
資料庫各個表空間增長情況的檢查：
SQL>select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
From (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name;
SQL>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), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
檢視各個表空間佔用磁碟情況:
SQL>col tablespace_name format a20;
SQL>select b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
資料庫物件下一擴充套件與表空間的free擴充套件值的檢查：
SQL>select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk
union select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;
Disk Read最高的SQL語句的獲取：
SQL>select sql_text from (select * from v$sqlarea order by disk_reads)
where rownum<=5;
查詢前十條效能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
sql_text FROM v$sqlarea ORDER BY disk_reads DESC)
WHERE ROWNUM<10 ;
等待時間最多的5個系統等待事件的獲取：
SQL>select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5;
檢視當前等待事件的會話:
col username format a10
set line 120
col EVENT format a30
select SE.Sid,s.Username,SE.Event,se.Total_Waits,SE.Time_Waited,SE.Average_Wait
from v$session S,v$session_event SE where S.Username is not null and SE.Sid=S.Sid
and S.Status='ACTIVE' and SE.Event not like '%SQL*Net%';
select sid, event, p1, p2, p3, wait_time, seconds_in_wait, state from v$session_wait where event not like '%message%' and event not like 'SQL*Net%' and event not like '%timer%' and event != 'wakeup time manager';
找到與所連線的會話有關的當前等待事件：
select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;
Oracle所有回滾段狀態的檢查：
SQL>select segment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.status from dba_rollback_segs,v$datafile where file_id=file#;
Oracle回滾段擴充套件資訊的檢查:
col name format a10
set linesize 140
select substr(name,1,40) name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsize
from v$rollname rn,v$rollstat rs where (rn.usn=rs.usn);
extents:回滾段中的盤區數量。
Rssize:以位元組為單位的回滾段的尺寸。
optsize：為optimal引數設定的值。
Aveactive:從回滾段中刪除盤區時釋放的以位元組為單位的平均空間的大小。
Extends:系統為回滾段增加的盤區的次數。
Shrinks:系統從回滾段中清除盤區（即回滾段收縮）的次數。回滾段每次清除盤區時，系統可能會從這個回滾段中消除一個或多個盤區。
Hwmsize:回滾段尺寸的上限，即回滾段曾經達到的最大尺寸。
(如果回滾段平均尺寸接近OPTIMAL的值，那麼說明OPTIMAL的值設定正確，如果回滾段動態增長次數或收縮次數很高，那麼需要提高OPTIMAL的值)
檢視回滾段的使用情況，哪個使用者正在使用回滾段的資源:
select s.username, u.name from v$transaction t,v$rollstat r,
v$rollname u,v$session s where s.taddr=t.addr and
t.xidusn=r.usn and r.usn=u.usn order by s.username;
如何檢視一下某個shared_server正在忙什麼:
SELECT a.username,a.machine,a.program,a.sid,
a.serial#,a.status,c.piece,c.sql_text
FROM v$session a,v$process b,v$sqltext c
WHERE b.spid=13161 AND b.addr=a.paddr
AND a.sql_address=c.address(+) ORDER BY c.piece;
資料庫共享池效能檢查:
Select namespace,gets,gethitratio,pins,pinhitratio,reloads,
Invalidations from v$librarycache where namespace in
('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
檢查資料過載比率:
select sum(reloads)/sum(pins)*100 "reload ratio" from
v$librarycache;
檢查資料字典的命中率:
select 1-sum(getmisses)/sum(gets) "data dictionary hit
ratio" from v$rowcache;
(對於library cache, gethitratio和pinhitratio應該大於90%,對於資料過載比率,reload ratio應該小於1%,對於資料字典的命中率,data dictionary hit ratio應該大於85%)
檢查共享記憶體的剩餘情況:
select request_misses, request_failures from v$shared_pool_reserved;
(對於共享記憶體的剩餘情況, request_misses 和request_failures應該接近0)
資料高速緩衝區效能檢查:
select 1-p.value/(b.value+c.value) "db buffer cache hit
ratio" from v$sysstat p,v$sysstat b,v$sysstat c where
p.name='physical reads' and b.name='db block gets' and
c.name='consistent gets';
檢查buffer pool HIT_RATIO執行
select name, (physical_reads/(db_block_gets+consistent_gets))
"MISS_HIT_RATIO" FROM v$buffer_pool_statistics WHERE (db_block_gets+ consistent_gets)> 0;
(正常時db buffer cache hit ratio 應該大於90%,正常時buffer pool MISS_HIT_RATIO 應該小於10%)
資料庫回滾段效能檢查:
檢查Ratio執行
select sum(waits)* 100 /sum(gets) "Ratio", sum(waits)
"Waits", sum(gets) "Gets" from v$rollstat;
檢查count/value執行:
select class,count from v$waitstat where class like '%undo%';
select value from v$sysstat where name='consistent gets';
(兩者的value值相除)
檢查average_wait執行:
select event,total_waits,time_waited,average_wait from v$system_event
where event like '%undo%';
檢查RBS header get ratio執行:
select n.name,s.usn,s.wraps, decode(s.waits,0,1,1- s.waits/s.gets)"RBS
header get ratio" from v$rollstat s,v$rollname n where s.usn=n.usn;
(正常時Ratio應該小於1%, count/value應該小於0.01%,average_wait最好為0，該值越小越好,RBS header get ratio應該大於95%)
殺會話的指令碼:
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6;
檢視排序段的效能:
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
7、檢視資料庫庫物件:
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
8、檢視資料庫的版本:
Select * from v$version;
9、檢視資料庫的建立日期和歸檔方式:
Select Created, Log_Mode, Log_Mode From V$Database;
10、捕捉執行很久的SQL:
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops , v$sql where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value;
11、檢視資料表的引數資訊:
SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, initial_extent,next_extent, min_extent, max_extent, pct_increase, FREELISTS,freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,last_analyzed FROM dba_tab_partitions
--WHERE table_name = :tname AND table_owner = :towner
ORDER BY partition_position;
12、檢視還沒提交的事務:
select * from v$locked_object;
select * from v$transaction;
13、查詢object為哪些程式所用:
select p.spid,s.sid,s.serial# serial_num,s.username user_name,
a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,
to_char(command), 'Action Code #' || to_char(command) ) action,
p.program oracle_process,s.terminal terminal,s.program program,s.status session_status from v$session s, v$access a, v$process p where s.paddr = p.addr and s.type = 'USER' and a.sid = s.sid and a.object='SUBSCRIBER_ATTR'order by s.username, s.osuser;
14、檢視回滾段:
SQL>col name format a10
SQL>set linesize 100
SQL>select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and v$rollstat.usn (+) = v$rollname.usn order by rownum;
15、耗資源的程式(top session):
select s.schemaname schema_name,decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action,status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[Oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st,v$session s,v$process p where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr order by st.value desc,p.spid asc,s.username asc,s.osuser asc;
根據PID查詢相應的語句:
SELECT a.username,
a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
FROM v$session a,v$process b,v$sqltext c WHERE b.spid=spid
AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
根據SID找ORACLE的某個程式:
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
監控當前資料庫誰在執行什麼SQL語句:
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
如何檢視資料庫中某使用者，正在執行什麼SQL語句
SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS
AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE='XXXXX' OR USERNAME='WACOS';
如何查出前臺正在發出的sql語句:
SQL> select user_name,sql_text from v$open_cursor where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'));
查詢當前所執行的SQL語句：
SQL> select program ,sql_address from v$session where paddr in (select addr
from v$process where spid=3556);
PROGRAM SQL_ADDRESS
------------------------------------------------ ----------------
sqlplus@ctc20 (TNS V1-V3) 000000038FCB1A90
SQL> select sql_text from v$sqlarea where address='000000038FCB1A90';
找出消耗CPU最高的程式對應的SQL語句：
set line 240
set verify off
column sid format 999
column pid format 999
column S_# format 999
column username format A9 heading "ORA User"
column program format a29
column SQL format a60
COLUMN OSname format a9 Heading "OS User"
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
Enter value for 1: PID（這裡輸入佔用CPU最高的程式對應的PID）
set termout off
spool maxcpu.txt
SELECT '++'||S.username username,
RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROM v$process P, v$session S,
v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+)
AND P.spid LIKE '%&&1%';
Enter value for 1: PID（這裡輸入佔用CPU最高的程式對應的PID）
spool off(這句放在最後執行)
CPU用率最高的2條SQL語句的獲取
執行：top，透過top獲得CPU佔用率最高的程式的pid。
SQL>select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr and v$process.spid in (pid);
col machine format a30
col program format a40
set line 200
SQL>select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') from v$session where paddr in(select addr from v$process where spid in([$spid]));
select sql_text from v$sqltext_with_newlines
where hash_value=(select SQL_HASH_VALUE from v$session where sid=&sid)
order by piece;
16、檢視鎖（lock）情況:
SQL>select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX','Transaction enqueue lock','UL','User supplied lock') lock_type,o.object_name object,decode(ls.lmode, 1, null, 2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null)lock_mode,o.owner,ls.sid,ls.serial# serial_num,ls.id1,ls.id2 from sys.dba_objects o,(select s.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2 from v$session s,v$lock l where s.sid=l.sid)ls where o.object_id=ls.id1 and o.owner<>'SYS' order by o.owner, o.object_name;
SQL>select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,decode(v$lock.type,'MR','Media Recovery',
'RT','Redo Thread','UN','User Name','TX', 'Transaction','TM','DML',
'UL','PL/SQL User Lock','DX','Distributed Xaction','CF','Control File',
'IS','Instance State','FS','File Set','IR','Instance Recovery',
'ST','Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalida-tion','LS','Log Start or Switch','RW','Row Wait','SQ','Sequence Number','TE','Extend Table','TT','Temp Table','Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,decode(lmode, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',
5, 'S/Row-X',6, 'Exclusive','Unknown') LockMode,decode(request, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X', 4, 'Share',5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id;
以DBA角色, 檢視當前資料庫裡鎖的情況可以用如下SQL語句：
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
SQL>select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
SQL>Select sql_address from v$session where sid=;
SQL>Select * from v$sqltext where address=;
SQL>select COMMAND_TYPE,PIECE,sql_text from v$sqltext where address=(select sql_address from v$session a where sid=18);
SQL>select object_id from v$locked_object;
SQL>select object_name,object_type from dba_objects where object_id=’’;
如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：
SQL>alter system kill session 'sid,serial#';
17、檢視等待（wait）情況:
SQL>SELECT v$waitstat.class,v$waitstat.count count, SUM(v$sysstat.value) sum_value FROM v$waitstat,v$sysstat WHERE v$sysstat.name IN('db block gets','consistent gets') group by v$waitstat.class,v$waitstat.count;
18、檢視sga情況:
SQL>SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC;
19、檢視catched object:
SQL>SELECT owner,name,db_link,namespace,type,sharable_mem,loads, executions,locks,pins,kept FROM v$db_object_cache;
20、檢視V$SQLAREA:
SQL>SELECT SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,
VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,
DISK_READS,BUFFER_GETS,ROWS_PROCESSED FROM V$SQLAREA;
21、檢視object分類數量:
select decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6, 'SEQUENCE','OTHER') object_type , count(*) quantity from sys.obj$ o where o.type# > 1 group by decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER' ,4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER') union select 'COLUMN', count(*) from sys.col$ union select 'DB LINK' , count(*) from all_objects;
22、有關connection的相關資訊:
1）檢視有哪些使用者連線
select s.osuser os_user_name,decode(sign(48 - command),1,to_char(command),
'Action Code #' || to_char(command))action,p.program oracle_process,
status session_status,s.terminal terminal,s.program program,
s.username user_name,s.fixed_table_sequence activity_meter,''query,
0 memory,0 max_memory,0 cpu_usage,s.sid,s.serial# serial_num
from v$session s,v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser;
2）根據v.sid檢視對應連線的資源佔用等情況
select n.name,v.value,n.class,n.statistic#
from v$statname n,v$sesstat v where v.sid=18 and v.statistic# = n.statistic# order by n.class, n.statistic#;
3）根據sid檢視對應連線正在執行的sql
select /*+ PUSH_SUBQ */ command_type,sql_text,sharable_mem, persistent_mem,runtime_mem,sorts,version_count,
loaded_versions,open_versions,users_opening,executions, users_executing,loads,first_load_time,invalidations, parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>'|| address sql_address,
'N' status from v$sqlarea where address = (select sql_address from v$session where sid=8);
根據pid檢視sql語句:
select sql_text from v$sql
where address in
(select sql_address from v$session
where sid in
(select sid from v$session where paddr in (select addr from v$process where spid=&pid)));
23、查詢表空間使用情況:
select a.tablespace_name "表空間名稱",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "佔用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空閒(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大擴充套件段(M)",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "取樣時間" from (select f.tablespace_name,sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f group by tablespace_name) a,
(select f.tablespace_name,sum(f.bytes) bytes_free
from dba_free_space f group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name;
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), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
24、查詢表空間的碎片程度:
SQL>select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;
SQL>alter tablespace name coalesce;
SQL>alter table table_name deallocate unused;
SQL>create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
SQL>select * from ts_blocks_v;
SQL>select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;
SQL>select 'alter tablespace '||TABLESPACE_NAME||' coalesce;'
from DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED<100
or PERCENT_BLOCKS_COALESCED<100;
由於自由空間碎片是由幾部分組成，如範圍數量、最大範圍尺寸等，我們可
用fsfi--free space fragmentation index（自由空間碎片索引）值來直觀體現:
fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))
rem fsfi value compute
rem fsfi.sql
column fsfi format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by 1;
spool fsfi.rep;
/
spool off;
可以看出，fsfi的最大可能值為100（一個理想的單檔案表空間）。隨著範
圍的增加，fsfi值緩慢下降，而隨著最大範圍尺寸的減少，fsfi值會迅速下降。
比如，在某資料庫執行指令碼fsfi.sql,得到以下fsfi值：
tablespace_name fsfi
------------------------------ -------
rbs 74.06
system 100.00
temp 22.82
tools 75.79
users 100.00
user_tools 100.00
ydcx_data 47.34
ydcx_idx 57.19
ydjf_data 33.80
ydjf_idx 75.55
---- 統計出了資料庫的fsfi值，就可以把它作為一個可比引數。在一個有著足夠
有效自由空間，且fsfi值超過30的表空間中，很少會遇見有效自由空間的問題。
當一個空間將要接近可比引數時，就需要做碎片整理了。
25、查詢有哪些資料庫例項在執行:
select inst_name from v$active_instances;
26、以DBA角色, 檢視當前資料庫裡鎖的情況：
select object_id,session_id,locked_mode from v$locked_object;
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
27、檢視錶是否是分割槽表：
select TABLE_NAME,PARTITIONED from user_tables where TABLE_NAME='LOCALUSAGE';
TABLE_NAME PAR
------------------------------ --- ---------
LOCALUSAGE YES
28、檢視分割槽表的分割槽名和相應的表空間名：
select TABLE_NAME, PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name like ‘%USAGE%’;
29、檢視索引是否是分割槽索引：
SELECT INDEX_NAME, TABLE_NAME, STATUS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME LIKE '%USAGE';
30、如果返回的PATITIONED為YES，請再執行如下語句來查詢分割槽索引的型別：SELECT index_name,table_name,locality FROM user_part_indexes;
31、Dual是Oracle中的一個實際存在的表，任何使用者均可讀取，常用在沒有目標表的Select中.
檢視系統時間：
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') shijian from dual;
32、檢視索引段中extent的數量：
select segment_name,count(*) from dba_extents
where segment_type='INDEX' and owner='SCOTT' group by segment_name;
33、檢視系統表中的使用者索引（用來檢查在system表空間內其他使用者索引的存在）：
SQL>select count(*) from dba_indexes where tablespace_name=’SYSTEM’ and owner NOT IN(‘SYS’,’SYSTEM’);
34、檢視wacos表空間內的索引的擴充套件情況：
SELECT SUBSTR(segment_name,1,20) "SEGMENT NAME",bytes, COUNT(bytes)
FROM dba_extents WHERE segment_name IN( SELECT index_name FROM dba_indexes
WHERE tablespace_name = 'WACOS') GROUP BY segment_name,bytes ORDER BY segment_name;
35、檢視錶空間資料檔案的讀寫效能:
SQL>Select name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm from v$filestat,v$datafile where v$filestat.file#=v$datafile.file#;
SQL>Select fs.name name,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt ,f.readtim,f.writetim
from v$filestat f, v$datafile fs where f.file# = fs.file# order by fs.name;
(注意：如果phyblkrd與phyrds很接近的話，則表明這個表空間中存在全表掃描的表，這些表需要調整索引或最佳化SQL語句)
36、轉換表空間為local方式管理:
SQL> exec sys.dbms_space_admin.tablespace_migrate_to_local('TBS_TEST') ;
37、檢視一下哪個使用者在用臨時段:
SELECT username,sid,serial#,sql_address,machine,program,tablespace,segtype,
contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr;
38、檢視佔io較大的正在執行的session:
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program, se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;
39、查詢前十條效能差的sql:
SELECT * FROM(SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10;
40、刪除使用者下所有表的語句:
select 'drop table '||table_name||' cascade constraints;' from user_tables;
41、檢視LOCK，並殺掉會話：
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode beading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||','||substr(T1.NAME,1,20) tab,
decode(L.LMODE, 1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER#= T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5;
--alter system kill session ' , ';
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
select sn.username,
m.sid,
m.type,
DECODE(m.lmode,0,'None',
1,'Null',
2,'Row Share',
3,'Row Excl.',
4,'Share',
5,'S/Row Excl.',
6,'Exclusive',
lmode,ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0,'None',
1,'Null',
2,'Row Share',
3,'Row Excl.',
4,'Share',
5,'S/Row Excl.',
6,'Exclusive',
request,ltrim(to_char(m.request,'990'))) request,
m.id1,
m.id2
from v$session sn,
v$lock m
where (sn.sid = m.sid and m.request!= 0)
or (sn.sid = m.sid and
m.request = 0 and lmode != 4 and
(id1 ,id2) in (select s.id1,
s.id2
from v$lock s
where request != 0 and s.id1 = m.id1 and s.id2 = m.id2)
)
order by id1,id2,m.request;
spool off
clear breaks
42.檢視WACOS表空間下所有的索引:
SQL>select 'analyze index '||segment_name||' validate structure;' from dba_segments where tablespace_name=’WACOS’and segment_type=’INDEX’;
43. 怎樣識別IO競爭和負載平衡:
col 檔名 format a35
select
df.name 檔名,
fs.phyrds 讀次數,
fs.phywrts 寫次數,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) 讀時間,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) 寫時間
from
v$datafile df,
v$filestat fs
where df.file#=fs.file#
order by df.name
/
檔名 讀次數 寫次數 讀時間 寫時間
-------------------------------------------- ---------- ---------- ---------- ----------
C:ORACLEORADATAORADBDR01.DBF 885 883 0 0
C:ORACLEORADATAORADBINDX01.DBF 885 883 0 0
C:ORACLEORADATAORADBOEM_REPOSITORY.ORA 885 883 0 0
C:ORACLEORADATAORADBRBS01.DBF 925 22306 0 0
C:ORACLEORADATAORADB YSTEM01.DBF 50804 155025 0 0
C:ORACLEORADATAORADBTEMP01.DBF 887 894 0 0
C:ORACLEORADATAORADBTOOLS01.DBF 886 892 0 0
C:ORACLEORADATAORADBUSERS01.DBF 885 883 0 0
已選擇8行。
其中：ORADB為資料庫名，因為本例中資料庫使預設安裝，沒有進行過最佳化、調整，
所以，一直在system表空間上做操作，導致system表空間所在的資料檔案SYSTEM01.DBF被讀寫的次數最多，
這也說明了，儘量不要在system表空間做與系統無關的操作，應給各個使用者建立單獨的表空間。
44. 檢視哪些session正在使用哪些回滾段:
col 回滾段名 format a10
col SID format 9990
col 使用者名稱 format a10
col 操作程式 format a80
col status format a6 trunc
SELECT r.name 回滾段名,
s.sid,
s.serial#,
s.username 使用者名稱,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程式
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
ORDER BY t.cr_get,t.phy_io;
45.檢查誰Lock了什麼物件:
set line 200
col "O/S-User" format a10
col "Ora-User" format a10
col "Obj Locked" format a30
select /*+RULE*/s.machine, s.osuser "O/S-User", s.username "Ora-User", s.sid "Session-ID",
s.serial# "Serial", s.process "Process-ID", s.status "Status",l.name "Obj Locked",
l.mode_held "Lock Mode"
from v$session s,dba_dml_locks l,v$process p
where l.session_id = s.sid and p.addr = s.paddr
/
46.造成等待的LOCK的資訊，比如LOCK型別等：
col event format a30
set line 160
col machine format a10
col username format a15
select b.sid,b.serial#,b.username,machine,event,wait_time,chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Enqueue Type" from v$session_wait a,v$session b
where event not like 'SQL*N%' and event not like 'rdbms%' and a.sid=b.sid
and b.sid>8 and event='enqueue'
order by username
/
47. List of the locked Oracle objects
set line 120
column object_name format a32
column OS_USER_NAME format a12
column orauser format a12
column sql_text format a32
column serial# format 999999
column sid format 99999
SELECT OS_USER_NAME, ORACLE_USERNAME AS orauser, s.sid, o.object_name,
o.object_type, s.serial#, a.sql_text
FROM v$locked_object l, dba_objects o, v$session s, v$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
SELECT 'ALTER SYSTEM KILL SESSION '''||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||''';'
AS "Statement to kill"
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
oracle資料庫效能監控的SQL
1. 監控事例的等待
SQL>select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4;
2. 回滾段的爭用情況
SQL>select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;
3. 監控表空間的 I/O 比例
SQL>select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id
order by df.tablespace_name;
4. 監控檔案系統的 I/O 比例
SQL>select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status,a.bytes,b.phyrds,b.phywrts from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某個使用者下找所有的索引
SQL>select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 監控 SGA 的命中率
SQL>select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
7. 監控 SGA 中字典緩衝區的命中率
SQL>select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses;
8. 監控 SGA 中共享快取區的命中率，應該小於1%
SQL>select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache from v$librarycache;
SQL>select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;
9. 顯示所有資料庫物件的類別和大小
SQL>select count(name) num_instances ,type ,sum(source_size) source_size,sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;
10. 監控 SGA 中重做日誌快取區的命中率，應該小於1%
SQL>SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 監控當前資料庫誰在執行什麼SQL語句
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 監控字典緩衝區
SQL>SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SQL>SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SQL>SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;(後者除以前者,此比率小於1%,接近0%為好)
SQL>SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE;
14. 查詢ORACLE字符集
SQL>select * from sys.props$ where name='NLS_CHARACTERSET';
15. 監控 MTS
SQL>select busy/(busy+idle) "shared servers busy" from v$dispatcher;
(此值大於0.5時，引數需加大)
SQL>select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
SQL>select count(*) from v$dispatcher;
SQL>select servers_highwater from v$mts;
(servers_highwater接近mts_max_servers時，引數需加大)
16. 碎片程度
SQL>select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;
SQL>alter tablespace name coalesce;
SQL>alter table name deallocate unused;
SQL>create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
SQL>select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;
檢視碎片程度高的表
SQL>SELECT segment_name table_name,COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY
segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
17. 表、索引的儲存情況檢查
SQL>select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
SQL>select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' group by segment_name;
18、找使用CPU多的使用者session
SQL>select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
(12是cpu used by this session)
表空間統計
A、 指令碼說明：
這是我最常用的一個指令碼，用它可以顯示出資料庫中所有表空間的狀態，如表空間的大小、已使用空間、使用的百分比、空閒空間數及現在表空間的最大塊是多大。
B、指令碼原文:
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),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
檢視無法擴充套件的段
A、 指令碼說明：
ORACLE對一個段比如表段或索引無法擴充套件時，取決的並不是表空間中剩餘的空間是多少，而是取於這些剩餘空間中最大的塊是否夠表比索引的“NEXT”值大，所以有時一個表空間剩餘幾個G的空閒空間，在你使用時ORACLE還是提示某個表或索引無法擴充套件，就是由於這一點，這時說明空間的碎片太多了。這個指令碼是找出無法擴充套件的段的一些資訊。
B、指令碼原文：
SELECT segment_name,
segment_type,
owner,
a.tablespace_name "tablespacename",
initial_extent/1024 "inital_extent(K)",
next_extent/1024 "next_extent(K)",
pct_increase,
b.bytes/1024 "tablespace max free space(K)",
b.sum_bytes/1024 "tablespace total free space(K)"
FROM dba_segments a,
(SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
AND next_extent>b.bytes
ORDER BY 4,3,1;
檢視段(表段、索引段)所使用空間的大小
A、 指令碼說明：
有時你可能想知道一個表或一個索引佔用多少M的空間，這個指令碼就是滿足你的要求的，把<>中的內容替換一下就可以了。
B、指令碼原文：
SELECT owner,
segment_name,
SUM(bytes)/1024/1024
FROM dba_segments
WHERE owner=
And segment_name=
GROUP BY owner,segment_name
ORDER BY 3 DESC;
檢視資料庫中的表鎖
A、 指令碼說明：
這方面的語句的樣式是很多的，各式一樣，不過我認為這個是最實用的，不信你就用一下，無需多說，鎖是每個DBA一定都涉及過的內容，當你相知道某個表被哪個session鎖定了，你就用到了這個指令碼。
B、指令碼原文：
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
-- AND
ORDER BY 1,2 ;
處理儲存過程被鎖
A、 指令碼說明：
實際過程中可能你要重新編譯某個儲存過程理總是處於等待狀態，最後會報無法鎖定物件，這時你就可以用這個指令碼找到鎖定過程的那個sid，需要注意的是查v$access這個檢視本來就很慢，需要一些布耐心。
B、指令碼原文：
SELECT * FROM V$ACCESS
WHERE owner=
And object
?
檢視回滾段狀態
A、? 指令碼說明
這也是DBA經常使用的指令碼，因為回滾段是online還是full是他們的關懷之列嘛
??? B、SELECT a.segment_name,b.status
? FROM Dba_Rollback_Segs a,
??????? v$rollstat b
??????? WHERE a.segment_id=b.usn
???????? ORDER BY 2
?? ??????
看哪些session正在使用哪些回滾段
????? A、 指令碼說明：
?當你發現一個回滾段處理full狀態，你想使它變回online狀態，這時你便會用alter rollback segment rbs_seg_name shrink,可很多時侯確shrink不回來，主要是由於某個session在用，這時你就用到了這個指令碼，找到了sid的serial#餘下的事就不用我說了吧。
B、指令碼原文
?SELECT? r.name 回滾段名,
??? s.sid,
??? s.serial#,
??? s.username 使用者名稱,
??? s.status,
??? t.cr_get,
??? t.phy_io,
??? t.used_ublk,
??? t.noundo,
??? substr(s.program, 1, 78) 操作程式
FROM?? sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE? t.addr = s.taddr and t.xidusn = r.usn
?-- AND r.NAME IN ('ZHYZ_RBS')
ORDER? BY t.cr_get,t.phy_io
?
檢視正在使用臨時段的session
A、 指令碼說明：
許多的時侯你在檢視哪些段無法擴充套件時，回顯的結果是臨時段，或你做表空間統計時發現臨段表空間的可用空間幾乎為0,這時按oracle的說法是你只有重新啟動資料庫才能回收這部分空間。實際過程中沒那麼複雜，使用以下這段指令碼把佔用臨時段的session殺掉，然後用alter tablespace temp coalesce;這個語句就把temp表空間的空間回收回來了。
B、 指令碼原文
SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype,contents FROM v$session se,v$sort_usage su
?WHERE se.saddr=su.session_addr;
?
?
DBA日常維護程式:
--?free.sql
--To?verify?free?space?in?tablespaces??
--Minimum?amount?of?free?space?
--document?your?thresholds:??
--?=??m??
SQL>SELECT?tablespace_name,?sum?(?blocks?)?as?free_blk?,?trunc?(?sum?(?bytes?)/(1024*1024)?)?as?free_m,?max?(?bytes?)?/?(1024)?as?big_chunk_k,?count?(*)?as?num_chunks?FROM?dba_free_space?GROUP?BY?tablespace_name;
1.?Space.sql??
--?space.sql??
--?To?check?free,?pct_free,?and?allocated?space?within?a?tablespace??
--?11/24/98??
SQL> col tablespace_name format a20
SQL>SELECT?tablespace_name,largest_free_chunk,nr_free_chunks,sum_alloc_blocks,sum_free_blocks,to_char(100*sum_free_blocks/sum_alloc_blocks,?'09.99')?||?'%'AS?pct_free?FROM?(?SELECT?tablespace_name?,?sum(blocks)?AS?sum_alloc_blocks?FROM?dba_data_files?GROUP?BY?tablespace_name)
,(SELECT?tablespace_name?AS?fs_ts_name,max(blocks)?AS?largest_free_chunk,count(blocks)?AS?nr_free_chunks,sum(blocks)?AS?sum_free_blocks?FROM
dba_free_space GROUP?BY?tablespace_name)?WHERE?tablespace_name=fs_ts_name;
2.?analyze5pct.sql??
--?analyze5pct.sql??
--?To?analyze?tables?and?indexes?quickly,?using?a?5%?sample?size??
--?(do?not?use?this?script?if?you?are?performing?the?overnight??
--?collection?of?volumetric?data)??
--?11/30/98??
BEGIN??
dbms_utility.analyze_schema?('&OWNER',?'ESTIMATE',?NULL,?5)?;??
END?;??
/??
3.?nr_extents.sql??
--?nr_extents.sql??
--?To?find?out?any?object?reaching???
--?extents,?and?manually?upgrade?it?to?allow?unlimited??
--?max_extents?(thus?only?objects?we?*expect*?to?be?big??
--?are?allowed?to?become?big)??
--?11/30/98??
SELECT?e.owner,?e.segment_type?,?e.segment_name?,?count(*)?as?nr_extents,s.max_extents,?to_char(sum(e.bytes)?/?(1024?*?1024)?,?'999,999.90')?as?MB?FROM?dba_extents?e,dba_segments?s?WHERE?e.segment_name?=?s.segment_name?GROUP?BY?e.owner,?e.segment_type,e.segment_name?,?s.max_extents?HAVING?count(*)?>?&THRESHOLD?OR?(?(?s.max_extents?-?count(*)?)??&&THRESHOLD?)?ORDER?BY?count(*)?desc;??
4.?spacebound.sql??
--?spacebound.sql??
--?To?identify?space-bound?objects.?If?all?is?well,?no?rows?are?returned.??
--?If?any?space-bound?objects?are?found,?look?at?value?of?NEXT?extent??
--?size?to?figure?out?what?happened.??
--?Then?use?coalesce?(alter?tablespace??coalesce?.??
--?Lastly,?add?another?datafile?to?the?tablespace?if?needed.??
--?11/30/98??
SELECT?a.table_name,a.next_extent,a.tablespace_name?
FROM?all_tables?a,(SELECT?tablespace_name,max(bytes)?as?big_chunk??
FROM?dba_free_space?GROUP?BY?tablespace_name)?f??
WHERE?f.tablespace_name?=?a.tablespace_name?AND?a.next_extent?>?f.big_chunk;??
B.?每晚處理程式??
1.?mk_volfact.sql??
--?mk_volfact.sql?(only?run?this?once?to?set?it?up;?do?not?run?it?nightly!)??
--?--?Table?UTL_VOL_FACTS??
CREATE?TABLE?utl_vol_facts(??
table_name?VARCHAR2(30),??
num_rows?NUMBER,??
meas_dt?DATE?)??
TABLESPACE?platab??
STORAGE(INITIAL?128k??
NEXT?128k??
PCTINCREASE?0??
MINEXTENTS?1??
MAXEXTENTS?unlimited)??
/??
--?Public?Synonym??
CREATE?PUBLIC?SYNONYM?utl_vol_facts?FOR?&OWNER..utl_vol_facts??
/??
--?Grants?for?UTL_VOL_FACTS??
GRANT?SELECT?ON?utl_vol_facts?TO?public??
/??
2.?analyze_comp.sql??
--??
--?analyze_comp.sql??
--??
BEGIN??
sys.dbms_utility.analyze_schema?(?'&OWNER','COMPUTE');??
END?;??
/??
3.?pop_vol.sql??
--??
--?pop_vol.sql??
--??
insert?into?utl_vol_facts??
select?table_name??
,?NVL?(?num_rows,?0)?as?num_rows??
,?trunc?(?last_analyzed?)?as?meas_dt??
from?all_tables?--?or?just?user_tables??
where?owner?in?('&OWNER')?--?or?a?comma-separated?list?of?owners??
/??
commit??
/??
??
C.?每週處理程式??
1.?nextext.sql??
--??
--?nextext.sql??
--??
--?To?find?tables?that?don't?match?the?tablespace?default?for?NEXT?extent.??
--?The?implicit?rule?here?is?that?every?table?in?a?given?tablespace?should??
--?use?the?exact?same?value?for?NEXT,?which?should?also?be?the?tablespace's??
--?default?value?for?NEXT.??
--??
--?This?tells?us?what?the?setting?for?NEXT?is?for?these?objects?today.??
--??
--?11/30/98??
SELECT?segment_name,?segment_type,?ds.next_extent?as?Actual_Next??
,?dt.tablespace_name,?dt.next_extent?as?Default_Next??
FROM?dba_tablespaces?dt,?dba_segments?ds??
WHERE?dt.tablespace_name?=?ds.tablespace_name??
AND?dt.next_extent?!=ds.next_extent??
AND?ds.owner?=?UPPER?(?'&OWNER'?)??
ORDER?BY?tablespace_name,?segment_type,?segment_name??
2.?existext.sql??
--??
--?existext.sql??
--??
--?To?check?existing?extents??
--??
--?This?tells?us?how?many?of?each?object's?extents?differ?in?size?from??
--?the?tablespace's?default?size.?If?this?report?shows?a?lot?of?different??
--?sized?extents,?your?free?space?is?likely?to?become?fragmented.?If?so,??
--?this?tablespace?is?a?candidate?for?reorganizing.??
--??
--?12/15/98??
SELECT?segment_name,?segment_type??
,?count(*)?as?nr_exts??
,?sum?(?DECODE?(?dx.bytes,dt.next_extent,0,1)?)?as?nr_illsized_exts??
,?dt.tablespace_name,?dt.next_extent?as?dflt_ext_size??
FROM?dba_tablespaces?dt,?dba_extents?dx??
WHERE?dt.tablespace_name?=?dx.tablespace_name??
AND?dx.owner?=?'&OWNER'??
GROUP?BY?segment_name,?segment_type,?dt.tablespace_name,?dt.next_extent??
3.?No_pk.sql??
--??
--?no_pk.sql??
--??
--?To?find?tables?without?PK?constraint??
--??
--?11/2/98??
SELECT?table_name??
FROM?all_tables??
WHERE?owner?=?'&OWNER'??
MINUS??
SELECT?table_name??
FROM?all_constraints??
WHERE?owner?=?'&&OWNER'??
AND?constraint_type?=?'P'??
4.?disPK.sql??
--??
--?disPK.sql??
--??
--?To?find?out?which?primary?keys?are?disabled??
--??
--?11/30/98??
SELECT?owner,?constraint_name,?table_name,?status??
FROM?all_constraints??
WHERE?owner?=?'&OWNER'?AND?status?=?'DISABLED’?AND?constraint_type?=?'P'??
5.?nonuPK.sql??
--??
--?nonuPK.sql??
--??
--?To?find?tables?with?nonunique?PK?indexes.?Requires?that?PK?names??
--?follow?a?naming?convention.?An?alternative?query?follows?that??
--?does?not?have?this?requirement,?but?runs?more?slowly.??
--??
--?11/2/98??
SELECT?index_name,?table_name,?uniqueness??
FROM?all_indexes??
WHERE?index_name?like?'&PKNAME%'??
SQL>select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
檢視錶空間物理檔案的名稱及大小:
SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
檢視回滾段名稱及大小:
SQL>select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+) order by segment_name;
如何檢視某個回滾段裡面，跑的什麼事物或者正在執行什麼sql語句:
SQL>select d.sql_text,a.name
from v$rollname a,v$transaction b,v$session c,v$sqltext d
where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address=
d.address and c.sql_hash_value=d.hash_value
and a.usn=1;
(備註：你要看哪個，就把usn=?寫成幾就行了)
檢視控制檔案:
SQL>select * from v$controlfile;
檢視日誌檔案:
SQL> col member format a50
SQL>select * from v$logfile;
如何檢視當前SQL*PLUS使用者的sid和serial#:
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid');
如何檢視當前資料庫的字符集:
SQL>select userenv('language') from dual;
SQL>select userenv('lang') from dual;
怎麼判斷當前正在使用何種SQL最佳化方式:
用explain plan產生EXPLAIN PLAN，檢查PLAN_TABLE中ID=0的POSITION列的值。
SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0;
如何檢視系統當前最新的SCN號：
SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe;
在ORACLE中查詢TRACE檔案的指令碼:
SQL>select u_dump.value || '/' || instance.value || '_ora_' ||
v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and
instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid');
SQL>select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from (select p.spid from sys.v_$mystat m,sys.v_$session s,
sys.v_$process p where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d;
如何檢視客戶端登陸的IP地址:
SQL>select sys_context('userenv','ip_address') from dual;
如何在生產資料庫中建立一個追蹤客戶端IP地址的觸發器：
SQL>create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
REM 記錄登陸資訊的觸發器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
insert into session_history
select username,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
from v$session where audsid = userenv('sessionid');
END;
查詢當前日期:
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
檢視所有表空間對應的資料檔名：
SQL>select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files;
檢視錶空間的使用情況:
SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space group by tablespace_name;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
column tablespace_name format a18;
column Sum_M format a12;
column Used_M format a12;
column Free_M format a12;
column pto_M format 9.99;
select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M' Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M' Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes,
(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,
sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100 FreePercentRatio from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by b.file_id,b.tablespace_name,b.bytes
order by b.tablespace_name) s group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc;
檢視資料檔案的hwm（可以resize的最小空間）和檔案頭大小:
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 "USED_SPACE(HWM)",
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
資料檔案大小及頭大小:
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 Used_space,
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+);
(執行以上查詢，我們可以如下資訊：
Totle_pace:該資料檔案的總大小，位元組為單位
Free_space:該資料檔案的剩於大小，位元組為單位
Used_space:該資料檔案的已用空間，位元組為單位
Data_space:該資料檔案中段資料佔用空間，也就是資料空間，位元組為單位
File_Head:該資料檔案頭部佔用空間，位元組為單位)
資料庫各個表空間增長情況的檢查：
SQL>select A.tablespace_name,(1-(A.total)/B.total)*100 used_percent
From (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B where A.tablespace_name=B.tablespace_name;
SQL>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), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
檢視各個表空間佔用磁碟情況:
SQL>col tablespace_name format a20;
SQL>select b.file_id file_ID,
b.tablespace_name tablespace_name,
b.bytes Bytes,
(b.bytes-sum(nvl(a.bytes,0))) used,
sum(nvl(a.bytes,0)) free,
sum(nvl(a.bytes,0))/(b.bytes)*100 Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;
資料庫物件下一擴充套件與表空間的free擴充套件值的檢查：
SQL>select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk
union select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,(select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;
Disk Read最高的SQL語句的獲取：
SQL>select sql_text from (select * from v$sqlarea order by disk_reads)
where rownum<=5;
查詢前十條效能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
sql_text FROM v$sqlarea ORDER BY disk_reads DESC)
WHERE ROWNUM<10 ;
等待時間最多的5個系統等待事件的獲取：
SQL>select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5;
檢視當前等待事件的會話:
col username format a10
set line 120
col EVENT format a30
select SE.Sid,s.Username,SE.Event,se.Total_Waits,SE.Time_Waited,SE.Average_Wait
from v$session S,v$session_event SE where S.Username is not null and SE.Sid=S.Sid
and S.Status='ACTIVE' and SE.Event not like '%SQL*Net%';
select sid, event, p1, p2, p3, wait_time, seconds_in_wait, state from v$session_wait where event not like '%message%' and event not like 'SQL*Net%' and event not like '%timer%' and event != 'wakeup time manager';
找到與所連線的會話有關的當前等待事件：
select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;
Oracle所有回滾段狀態的檢查：
SQL>select segment_name,owner,tablespace_name,initial_extent,next_extent,dba_rollback_segs.status from dba_rollback_segs,v$datafile where file_id=file#;
Oracle回滾段擴充套件資訊的檢查:
col name format a10
set linesize 140
select substr(name,1,40) name,extents,rssize,optsize,aveactive,extends,wraps,shrinks,hwmsize
from v$rollname rn,v$rollstat rs where (rn.usn=rs.usn);
extents:回滾段中的盤區數量。
Rssize:以位元組為單位的回滾段的尺寸。
optsize：為optimal引數設定的值。
Aveactive:從回滾段中刪除盤區時釋放的以位元組為單位的平均空間的大小。
Extends:系統為回滾段增加的盤區的次數。
Shrinks:系統從回滾段中清除盤區（即回滾段收縮）的次數。回滾段每次清除盤區時，系統可能會從這個回滾段中消除一個或多個盤區。
Hwmsize:回滾段尺寸的上限，即回滾段曾經達到的最大尺寸。
(如果回滾段平均尺寸接近OPTIMAL的值，那麼說明OPTIMAL的值設定正確，如果回滾段動態增長次數或收縮次數很高，那麼需要提高OPTIMAL的值)
檢視回滾段的使用情況，哪個使用者正在使用回滾段的資源:
select s.username, u.name from v$transaction t,v$rollstat r,
v$rollname u,v$session s where s.taddr=t.addr and
t.xidusn=r.usn and r.usn=u.usn order by s.username;
如何檢視一下某個shared_server正在忙什麼:
SELECT a.username,a.machine,a.program,a.sid,
a.serial#,a.status,c.piece,c.sql_text
FROM v$session a,v$process b,v$sqltext c
WHERE b.spid=13161 AND b.addr=a.paddr
AND a.sql_address=c.address(+) ORDER BY c.piece;
資料庫共享池效能檢查:
Select namespace,gets,gethitratio,pins,pinhitratio,reloads,
Invalidations from v$librarycache where namespace in
('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
檢查資料過載比率:
select sum(reloads)/sum(pins)*100 "reload ratio" from
v$librarycache;
檢查資料字典的命中率:
select 1-sum(getmisses)/sum(gets) "data dictionary hit
ratio" from v$rowcache;
(對於library cache, gethitratio和pinhitratio應該大於90%,對於資料過載比率,reload ratio應該小於1%,對於資料字典的命中率,data dictionary hit ratio應該大於85%)
檢查共享記憶體的剩餘情況:
select request_misses, request_failures from v$shared_pool_reserved;
(對於共享記憶體的剩餘情況, request_misses 和request_failures應該接近0)
資料高速緩衝區效能檢查:
select 1-p.value/(b.value+c.value) "db buffer cache hit
ratio" from v$sysstat p,v$sysstat b,v$sysstat c where
p.name='physical reads' and b.name='db block gets' and
c.name='consistent gets';
檢查buffer pool HIT_RATIO執行
select name, (physical_reads/(db_block_gets+consistent_gets))
"MISS_HIT_RATIO" FROM v$buffer_pool_statistics WHERE (db_block_gets+ consistent_gets)> 0;
(正常時db buffer cache hit ratio 應該大於90%,正常時buffer pool MISS_HIT_RATIO 應該小於10%)
資料庫回滾段效能檢查:
檢查Ratio執行
select sum(waits)* 100 /sum(gets) "Ratio", sum(waits)
"Waits", sum(gets) "Gets" from v$rollstat;
檢查count/value執行:
select class,count from v$waitstat where class like '%undo%';
select value from v$sysstat where name='consistent gets';
(兩者的value值相除)
檢查average_wait執行:
select event,total_waits,time_waited,average_wait from v$system_event
where event like '%undo%';
檢查RBS header get ratio執行:
select n.name,s.usn,s.wraps, decode(s.waits,0,1,1- s.waits/s.gets)"RBS
header get ratio" from v$rollstat s,v$rollname n where s.usn=n.usn;
(正常時Ratio應該小於1%, count/value應該小於0.01%,average_wait最好為0，該值越小越好,RBS header get ratio應該大於95%)
殺會話的指令碼:
select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
'orakill '||sid||' '||spid HOST_COMMAND,
'alter system kill session '''||A.sid||','||A.SERIAL#||'''' SQL_COMMAND
from v$session A,V$PROCESS B where A.PADDR=B.ADDR AND SID>6;
檢視排序段的效能:
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
7、檢視資料庫庫物件:
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
8、檢視資料庫的版本:
Select * from v$version;
9、檢視資料庫的建立日期和歸檔方式:
Select Created, Log_Mode, Log_Mode From V$Database;
10、捕捉執行很久的SQL:
column username format a12
column opname format a16
column progress format a8
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops , v$sql where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value;
11、檢視資料表的引數資訊:
SELECT partition_name, high_value, high_value_length, tablespace_name,pct_free, pct_used, ini_trans, max_trans, initial_extent,next_extent, min_extent, max_extent, pct_increase, FREELISTS,freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,last_analyzed FROM dba_tab_partitions
--WHERE table_name = :tname AND table_owner = :towner
ORDER BY partition_position;
12、檢視還沒提交的事務:
select * from v$locked_object;
select * from v$transaction;
13、查詢object為哪些程式所用:
select p.spid,s.sid,s.serial# serial_num,s.username user_name,
a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,
to_char(command), 'Action Code #' || to_char(command) ) action,
p.program oracle_process,s.terminal terminal,s.program program,s.status session_status from v$session s, v$access a, v$process p where s.paddr = p.addr and s.type = 'USER' and a.sid = s.sid and a.object='SUBSCRIBER_ATTR'order by s.username, s.osuser;
14、檢視回滾段:
SQL>col name format a10
SQL>set linesize 100
SQL>select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs, v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and v$rollstat.usn (+) = v$rollname.usn order by rownum;
15、耗資源的程式(top session):
select s.schemaname schema_name,decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action,status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[Oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st,v$session s,v$process p where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr order by st.value desc,p.spid asc,s.username asc,s.osuser asc;
根據PID查詢相應的語句:
SELECT a.username,
a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text
FROM v$session a,v$process b,v$sqltext c WHERE b.spid=spid
AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
根據SID找ORACLE的某個程式:
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
監控當前資料庫誰在執行什麼SQL語句:
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
如何檢視資料庫中某使用者，正在執行什麼SQL語句
SQL>SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS
AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE='XXXXX' OR USERNAME='WACOS';
如何查出前臺正在發出的sql語句:
SQL> select user_name,sql_text from v$open_cursor where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'));
查詢當前所執行的SQL語句：
SQL> select program ,sql_address from v$session where paddr in (select addr
from v$process where spid=3556);
PROGRAM SQL_ADDRESS
------------------------------------------------ ----------------
sqlplus@ctc20 (TNS V1-V3) 000000038FCB1A90
SQL> select sql_text from v$sqlarea where address='000000038FCB1A90';
找出消耗CPU最高的程式對應的SQL語句：
set line 240
set verify off
column sid format 999
column pid format 999
column S_# format 999
column username format A9 heading "ORA User"
column program format a29
column SQL format a60
COLUMN OSname format a9 Heading "OS User"
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';
Enter value for 1: PID（這裡輸入佔用CPU最高的程式對應的PID）
set termout off
spool maxcpu.txt
SELECT '++'||S.username username,
RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROM v$process P, v$session S,
v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+)
AND P.spid LIKE '%&&1%';
Enter value for 1: PID（這裡輸入佔用CPU最高的程式對應的PID）
spool off(這句放在最後執行)
CPU用率最高的2條SQL語句的獲取
執行：top，透過top獲得CPU佔用率最高的程式的pid。
SQL>select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where v$sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr and v$process.spid in (pid);
col machine format a30
col program format a40
set line 200
SQL>select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') from v$session where paddr in(select addr from v$process where spid in([$spid]));
select sql_text from v$sqltext_with_newlines
where hash_value=(select SQL_HASH_VALUE from v$session where sid=&sid)
order by piece;
16、檢視鎖（lock）情況:
SQL>select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX','Transaction enqueue lock','UL','User supplied lock') lock_type,o.object_name object,decode(ls.lmode, 1, null, 2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null)lock_mode,o.owner,ls.sid,ls.serial# serial_num,ls.id1,ls.id2 from sys.dba_objects o,(select s.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2 from v$session s,v$lock l where s.sid=l.sid)ls where o.object_id=ls.id1 and o.owner<>'SYS' order by o.owner, o.object_name;
SQL>select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,decode(v$lock.type,'MR','Media Recovery',
'RT','Redo Thread','UN','User Name','TX', 'Transaction','TM','DML',
'UL','PL/SQL User Lock','DX','Distributed Xaction','CF','Control File',
'IS','Instance State','FS','File Set','IR','Instance Recovery',
'ST','Disk Space Transaction','TS','Temp Segment','IV','Library Cache Invalida-tion','LS','Log Start or Switch','RW','Row Wait','SQ','Sequence Number','TE','Extend Table','TT','Temp Table','Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,decode(lmode, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X',4, 'Share',
5, 'S/Row-X',6, 'Exclusive','Unknown') LockMode,decode(request, 0, 'None',1, 'Null',2, 'Row-S',3, 'Row-X', 4, 'Share',5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id;
以DBA角色, 檢視當前資料庫裡鎖的情況可以用如下SQL語句：
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
SQL>select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
SQL>Select sql_address from v$session where sid=;
SQL>Select * from v$sqltext where address=;
SQL>select COMMAND_TYPE,PIECE,sql_text from v$sqltext where address=(select sql_address from v$session a where sid=18);
SQL>select object_id from v$locked_object;
SQL>select object_name,object_type from dba_objects where object_id=’’;
如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：
SQL>alter system kill session 'sid,serial#';
17、檢視等待（wait）情況:
SQL>SELECT v$waitstat.class,v$waitstat.count count, SUM(v$sysstat.value) sum_value FROM v$waitstat,v$sysstat WHERE v$sysstat.name IN('db block gets','consistent gets') group by v$waitstat.class,v$waitstat.count;
18、檢視sga情況:
SQL>SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC;
19、檢視catched object:
SQL>SELECT owner,name,db_link,namespace,type,sharable_mem,loads, executions,locks,pins,kept FROM v$db_object_cache;
20、檢視V$SQLAREA:
SQL>SELECT SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,
VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,
DISK_READS,BUFFER_GETS,ROWS_PROCESSED FROM V$SQLAREA;
21、檢視object分類數量:
select decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6, 'SEQUENCE','OTHER') object_type , count(*) quantity from sys.obj$ o where o.type# > 1 group by decode(o.type#,1,'INDEX',2,'TABLE',3,'CLUSTER' ,4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER') union select 'COLUMN', count(*) from sys.col$ union select 'DB LINK' , count(*) from all_objects;
22、有關connection的相關資訊:
1）檢視有哪些使用者連線
select s.osuser os_user_name,decode(sign(48 - command),1,to_char(command),
'Action Code #' || to_char(command))action,p.program oracle_process,
status session_status,s.terminal terminal,s.program program,
s.username user_name,s.fixed_table_sequence activity_meter,''query,
0 memory,0 max_memory,0 cpu_usage,s.sid,s.serial# serial_num
from v$session s,v$process p where s.paddr=p.addr and s.type = 'USER'
order by s.username, s.osuser;
2）根據v.sid檢視對應連線的資源佔用等情況
select n.name,v.value,n.class,n.statistic#
from v$statname n,v$sesstat v where v.sid=18 and v.statistic# = n.statistic# order by n.class, n.statistic#;
3）根據sid檢視對應連線正在執行的sql
select /*+ PUSH_SUBQ */ command_type,sql_text,sharable_mem, persistent_mem,runtime_mem,sorts,version_count,
loaded_versions,open_versions,users_opening,executions, users_executing,loads,first_load_time,invalidations, parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>'|| address sql_address,
'N' status from v$sqlarea where address = (select sql_address from v$session where sid=8);
根據pid檢視sql語句:
select sql_text from v$sql
where address in
(select sql_address from v$session
where sid in
(select sid from v$session where paddr in (select addr from v$process where spid=&pid)));
23、查詢表空間使用情況:
select a.tablespace_name "表空間名稱",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "佔用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空閒(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大擴充套件段(M)",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "取樣時間" from (select f.tablespace_name,sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f group by tablespace_name) a,
(select f.tablespace_name,sum(f.bytes) bytes_free
from dba_free_space f group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name;
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), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
24、查詢表空間的碎片程度:
SQL>select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;
SQL>alter tablespace name coalesce;
SQL>alter table table_name deallocate unused;
SQL>create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
SQL>select * from ts_blocks_v;
SQL>select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;
SQL>select 'alter tablespace '||TABLESPACE_NAME||' coalesce;'
from DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED<100
or PERCENT_BLOCKS_COALESCED<100;
由於自由空間碎片是由幾部分組成，如範圍數量、最大範圍尺寸等，我們可
用fsfi--free space fragmentation index（自由空間碎片索引）值來直觀體現:
fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))
rem fsfi value compute
rem fsfi.sql
column fsfi format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by 1;
spool fsfi.rep;
/
spool off;
可以看出，fsfi的最大可能值為100（一個理想的單檔案表空間）。隨著範
圍的增加，fsfi值緩慢下降，而隨著最大範圍尺寸的減少，fsfi值會迅速下降。
比如，在某資料庫執行指令碼fsfi.sql,得到以下fsfi值：
tablespace_name fsfi
------------------------------ -------
rbs 74.06
system 100.00
temp 22.82
tools 75.79
users 100.00
user_tools 100.00
ydcx_data 47.34
ydcx_idx 57.19
ydjf_data 33.80
ydjf_idx 75.55
---- 統計出了資料庫的fsfi值，就可以把它作為一個可比引數。在一個有著足夠
有效自由空間，且fsfi值超過30的表空間中，很少會遇見有效自由空間的問題。
當一個空間將要接近可比引數時，就需要做碎片整理了。
25、查詢有哪些資料庫例項在執行:
select inst_name from v$active_instances;
26、以DBA角色, 檢視當前資料庫裡鎖的情況：
select object_id,session_id,locked_mode from v$locked_object;
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
27、檢視錶是否是分割槽表：
select TABLE_NAME,PARTITIONED from user_tables where TABLE_NAME='LOCALUSAGE';
TABLE_NAME PAR
------------------------------ --- ---------
LOCALUSAGE YES
28、檢視分割槽表的分割槽名和相應的表空間名：
select TABLE_NAME, PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name like ‘%USAGE%’;
29、檢視索引是否是分割槽索引：
SELECT INDEX_NAME, TABLE_NAME, STATUS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME LIKE '%USAGE';
30、如果返回的PATITIONED為YES，請再執行如下語句來查詢分割槽索引的型別：SELECT index_name,table_name,locality FROM user_part_indexes;
31、Dual是Oracle中的一個實際存在的表，任何使用者均可讀取，常用在沒有目標表的Select中.
檢視系統時間：
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') shijian from dual;
32、檢視索引段中extent的數量：
select segment_name,count(*) from dba_extents
where segment_type='INDEX' and owner='SCOTT' group by segment_name;
33、檢視系統表中的使用者索引（用來檢查在system表空間內其他使用者索引的存在）：
SQL>select count(*) from dba_indexes where tablespace_name=’SYSTEM’ and owner NOT IN(‘SYS’,’SYSTEM’);
34、檢視wacos表空間內的索引的擴充套件情況：
SELECT SUBSTR(segment_name,1,20) "SEGMENT NAME",bytes, COUNT(bytes)
FROM dba_extents WHERE segment_name IN( SELECT index_name FROM dba_indexes
WHERE tablespace_name = 'WACOS') GROUP BY segment_name,bytes ORDER BY segment_name;
35、檢視錶空間資料檔案的讀寫效能:
SQL>Select name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm from v$filestat,v$datafile where v$filestat.file#=v$datafile.file#;
SQL>Select fs.name name,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt ,f.readtim,f.writetim
from v$filestat f, v$datafile fs where f.file# = fs.file# order by fs.name;
(注意：如果phyblkrd與phyrds很接近的話，則表明這個表空間中存在全表掃描的表，這些表需要調整索引或最佳化SQL語句)
36、轉換表空間為local方式管理:
SQL> exec sys.dbms_space_admin.tablespace_migrate_to_local('TBS_TEST') ;
37、檢視一下哪個使用者在用臨時段:
SELECT username,sid,serial#,sql_address,machine,program,tablespace,segtype,
contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr;
38、檢視佔io較大的正在執行的session:
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program, se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;
39、查詢前十條效能差的sql:
SELECT * FROM(SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10;
40、刪除使用者下所有表的語句:
select 'drop table '||table_name||' cascade constraints;' from user_tables;
41、檢視LOCK，並殺掉會話：
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode beading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||','||substr(T1.NAME,1,20) tab,
decode(L.LMODE, 1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER#= T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5;
--alter system kill session ' , ';
column username format A15
column sid format 9990 heading SID
column type format A4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
select sn.username,
m.sid,
m.type,
DECODE(m.lmode,0,'None',
1,'Null',
2,'Row Share',
3,'Row Excl.',
4,'Share',
5,'S/Row Excl.',
6,'Exclusive',
lmode,ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0,'None',
1,'Null',
2,'Row Share',
3,'Row Excl.',
4,'Share',
5,'S/Row Excl.',
6,'Exclusive',
request,ltrim(to_char(m.request,'990'))) request,
m.id1,
m.id2
from v$session sn,
v$lock m
where (sn.sid = m.sid and m.request!= 0)
or (sn.sid = m.sid and
m.request = 0 and lmode != 4 and
(id1 ,id2) in (select s.id1,
s.id2
from v$lock s
where request != 0 and s.id1 = m.id1 and s.id2 = m.id2)
)
order by id1,id2,m.request;
spool off
clear breaks
42.檢視WACOS表空間下所有的索引:
SQL>select 'analyze index '||segment_name||' validate structure;' from dba_segments where tablespace_name=’WACOS’and segment_type=’INDEX’;
43. 怎樣識別IO競爭和負載平衡:
col 檔名 format a35
select
df.name 檔名,
fs.phyrds 讀次數,
fs.phywrts 寫次數,
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) 讀時間,
(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) 寫時間
from
v$datafile df,
v$filestat fs
where df.file#=fs.file#
order by df.name
/
檔名 讀次數 寫次數 讀時間 寫時間
-------------------------------------------- ---------- ---------- ---------- ----------
C:ORACLEORADATAORADBDR01.DBF 885 883 0 0
C:ORACLEORADATAORADBINDX01.DBF 885 883 0 0
C:ORACLEORADATAORADBOEM_REPOSITORY.ORA 885 883 0 0
C:ORACLEORADATAORADBRBS01.DBF 925 22306 0 0
C:ORACLEORADATAORADB YSTEM01.DBF 50804 155025 0 0
C:ORACLEORADATAORADBTEMP01.DBF 887 894 0 0
C:ORACLEORADATAORADBTOOLS01.DBF 886 892 0 0
C:ORACLEORADATAORADBUSERS01.DBF 885 883 0 0
已選擇8行。
其中：ORADB為資料庫名，因為本例中資料庫使預設安裝，沒有進行過最佳化、調整，
所以，一直在system表空間上做操作，導致system表空間所在的資料檔案SYSTEM01.DBF被讀寫的次數最多，
這也說明了，儘量不要在system表空間做與系統無關的操作，應給各個使用者建立單獨的表空間。
44. 檢視哪些session正在使用哪些回滾段:
col 回滾段名 format a10
col SID format 9990
col 使用者名稱 format a10
col 操作程式 format a80
col status format a6 trunc
SELECT r.name 回滾段名,
s.sid,
s.serial#,
s.username 使用者名稱,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program, 1, 78) 操作程式
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE t.addr = s.taddr and t.xidusn = r.usn
ORDER BY t.cr_get,t.phy_io;
45.檢查誰Lock了什麼物件:
set line 200
col "O/S-User" format a10
col "Ora-User" format a10
col "Obj Locked" format a30
select /*+RULE*/s.machine, s.osuser "O/S-User", s.username "Ora-User", s.sid "Session-ID",
s.serial# "Serial", s.process "Process-ID", s.status "Status",l.name "Obj Locked",
l.mode_held "Lock Mode"
from v$session s,dba_dml_locks l,v$process p
where l.session_id = s.sid and p.addr = s.paddr
/
46.造成等待的LOCK的資訊，比如LOCK型別等：
col event format a30
set line 160
col machine format a10
col username format a15
select b.sid,b.serial#,b.username,machine,event,wait_time,chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Enqueue Type" from v$session_wait a,v$session b
where event not like 'SQL*N%' and event not like 'rdbms%' and a.sid=b.sid
and b.sid>8 and event='enqueue'
order by username
/
47. List of the locked Oracle objects
set line 120
column object_name format a32
column OS_USER_NAME format a12
column orauser format a12
column sql_text format a32
column serial# format 999999
column sid format 99999
SELECT OS_USER_NAME, ORACLE_USERNAME AS orauser, s.sid, o.object_name,
o.object_type, s.serial#, a.sql_text
FROM v$locked_object l, dba_objects o, v$session s, v$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
SELECT 'ALTER SYSTEM KILL SESSION '''||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||''';'
AS "Statement to kill"
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
oracle資料庫效能監控的SQL
1. 監控事例的等待
SQL>select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4;
2. 回滾段的爭用情況
SQL>select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;
3. 監控表空間的 I/O 比例
SQL>select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id
order by df.tablespace_name;
4. 監控檔案系統的 I/O 比例
SQL>select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status,a.bytes,b.phyrds,b.phywrts from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某個使用者下找所有的索引
SQL>select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 監控 SGA 的命中率
SQL>select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
7. 監控 SGA 中字典緩衝區的命中率
SQL>select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses;
8. 監控 SGA 中共享快取區的命中率，應該小於1%
SQL>select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache from v$librarycache;
SQL>select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;
9. 顯示所有資料庫物件的類別和大小
SQL>select count(name) num_instances ,type ,sum(source_size) source_size,sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;
10. 監控 SGA 中重做日誌快取區的命中率，應該小於1%
SQL>SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size
SQL>SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 監控當前資料庫誰在執行什麼SQL語句
SQL>SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 監控字典緩衝區
SQL>SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SQL>SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SQL>SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;(後者除以前者,此比率小於1%,接近0%為好)
SQL>SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE;
14. 查詢ORACLE字符集
SQL>select * from sys.props$ where name='NLS_CHARACTERSET';
15. 監控 MTS
SQL>select busy/(busy+idle) "shared servers busy" from v$dispatcher;
(此值大於0.5時，引數需加大)
SQL>select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
SQL>select count(*) from v$dispatcher;
SQL>select servers_highwater from v$mts;
(servers_highwater接近mts_max_servers時，引數需加大)
16. 碎片程度
SQL>select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;
SQL>alter tablespace name coalesce;
SQL>alter table name deallocate unused;
SQL>create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
SQL>select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;
檢視碎片程度高的表
SQL>SELECT segment_name table_name,COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY
segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
17. 表、索引的儲存情況檢查
SQL>select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
SQL>select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' group by segment_name;
18、找使用CPU多的使用者session
SQL>select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
(12是cpu used by this session)
表空間統計
A、 指令碼說明：
這是我最常用的一個指令碼，用它可以顯示出資料庫中所有表空間的狀態，如表空間的大小、已使用空間、使用的百分比、空閒空間數及現在表空間的最大塊是多大。
B、指令碼原文:
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),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
檢視無法擴充套件的段
A、 指令碼說明：
ORACLE對一個段比如表段或索引無法擴充套件時，取決的並不是表空間中剩餘的空間是多少，而是取於這些剩餘空間中最大的塊是否夠表比索引的“NEXT”值大，所以有時一個表空間剩餘幾個G的空閒空間，在你使用時ORACLE還是提示某個表或索引無法擴充套件，就是由於這一點，這時說明空間的碎片太多了。這個指令碼是找出無法擴充套件的段的一些資訊。
B、指令碼原文：
SELECT segment_name,
segment_type,
owner,
a.tablespace_name "tablespacename",
initial_extent/1024 "inital_extent(K)",
next_extent/1024 "next_extent(K)",
pct_increase,
b.bytes/1024 "tablespace max free space(K)",
b.sum_bytes/1024 "tablespace total free space(K)"
FROM dba_segments a,
(SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
AND next_extent>b.bytes
ORDER BY 4,3,1;
檢視段(表段、索引段)所使用空間的大小
A、 指令碼說明：
有時你可能想知道一個表或一個索引佔用多少M的空間，這個指令碼就是滿足你的要求的，把<>中的內容替換一下就可以了。
B、指令碼原文：
SELECT owner,
segment_name,
SUM(bytes)/1024/1024
FROM dba_segments
WHERE owner=
And segment_name=
GROUP BY owner,segment_name
ORDER BY 3 DESC;
檢視資料庫中的表鎖
A、 指令碼說明：
這方面的語句的樣式是很多的，各式一樣，不過我認為這個是最實用的，不信你就用一下，無需多說，鎖是每個DBA一定都涉及過的內容，當你相知道某個表被哪個session鎖定了，你就用到了這個指令碼。
B、指令碼原文：
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
-- AND
ORDER BY 1,2 ;
處理儲存過程被鎖
A、 指令碼說明：
實際過程中可能你要重新編譯某個儲存過程理總是處於等待狀態，最後會報無法鎖定物件，這時你就可以用這個指令碼找到鎖定過程的那個sid，需要注意的是查v$access這個檢視本來就很慢，需要一些布耐心。
B、指令碼原文：
SELECT * FROM V$ACCESS
WHERE owner=
And object
?
檢視回滾段狀態
A、? 指令碼說明
這也是DBA經常使用的指令碼，因為回滾段是online還是full是他們的關懷之列嘛
??? B、SELECT a.segment_name,b.status
? FROM Dba_Rollback_Segs a,
??????? v$rollstat b
??????? WHERE a.segment_id=b.usn
???????? ORDER BY 2
?? ??????
看哪些session正在使用哪些回滾段
????? A、 指令碼說明：
?當你發現一個回滾段處理full狀態，你想使它變回online狀態，這時你便會用alter rollback segment rbs_seg_name shrink,可很多時侯確shrink不回來，主要是由於某個session在用，這時你就用到了這個指令碼，找到了sid的serial#餘下的事就不用我說了吧。
B、指令碼原文
?SELECT? r.name 回滾段名,
??? s.sid,
??? s.serial#,
??? s.username 使用者名稱,
??? s.status,
??? t.cr_get,
??? t.phy_io,
??? t.used_ublk,
??? t.noundo,
??? substr(s.program, 1, 78) 操作程式
FROM?? sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
WHERE? t.addr = s.taddr and t.xidusn = r.usn
?-- AND r.NAME IN ('ZHYZ_RBS')
ORDER? BY t.cr_get,t.phy_io
?
檢視正在使用臨時段的session
A、 指令碼說明：
許多的時侯你在檢視哪些段無法擴充套件時，回顯的結果是臨時段，或你做表空間統計時發現臨段表空間的可用空間幾乎為0,這時按oracle的說法是你只有重新啟動資料庫才能回收這部分空間。實際過程中沒那麼複雜，使用以下這段指令碼把佔用臨時段的session殺掉，然後用alter tablespace temp coalesce;這個語句就把temp表空間的空間回收回來了。
B、 指令碼原文
SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype,contents FROM v$session se,v$sort_usage su
?WHERE se.saddr=su.session_addr;
?
?
DBA日常維護程式:
--?free.sql
--To?verify?free?space?in?tablespaces??
--Minimum?amount?of?free?space?
--document?your?thresholds:??
--?=??m??
SQL>SELECT?tablespace_name,?sum?(?blocks?)?as?free_blk?,?trunc?(?sum?(?bytes?)/(1024*1024)?)?as?free_m,?max?(?bytes?)?/?(1024)?as?big_chunk_k,?count?(*)?as?num_chunks?FROM?dba_free_space?GROUP?BY?tablespace_name;
1.?Space.sql??
--?space.sql??
--?To?check?free,?pct_free,?and?allocated?space?within?a?tablespace??
--?11/24/98??
SQL> col tablespace_name format a20
SQL>SELECT?tablespace_name,largest_free_chunk,nr_free_chunks,sum_alloc_blocks,sum_free_blocks,to_char(100*sum_free_blocks/sum_alloc_blocks,?'09.99')?||?'%'AS?pct_free?FROM?(?SELECT?tablespace_name?,?sum(blocks)?AS?sum_alloc_blocks?FROM?dba_data_files?GROUP?BY?tablespace_name)
,(SELECT?tablespace_name?AS?fs_ts_name,max(blocks)?AS?largest_free_chunk,count(blocks)?AS?nr_free_chunks,sum(blocks)?AS?sum_free_blocks?FROM
dba_free_space GROUP?BY?tablespace_name)?WHERE?tablespace_name=fs_ts_name;
2.?analyze5pct.sql??
--?analyze5pct.sql??
--?To?analyze?tables?and?indexes?quickly,?using?a?5%?sample?size??
--?(do?not?use?this?script?if?you?are?performing?the?overnight??
--?collection?of?volumetric?data)??
--?11/30/98??
BEGIN??
dbms_utility.analyze_schema?('&OWNER',?'ESTIMATE',?NULL,?5)?;??
END?;??
/??
3.?nr_extents.sql??
--?nr_extents.sql??
--?To?find?out?any?object?reaching???
--?extents,?and?manually?upgrade?it?to?allow?unlimited??
--?max_extents?(thus?only?objects?we?*expect*?to?be?big??
--?are?allowed?to?become?big)??
--?11/30/98??
SELECT?e.owner,?e.segment_type?,?e.segment_name?,?count(*)?as?nr_extents,s.max_extents,?to_char(sum(e.bytes)?/?(1024?*?1024)?,?'999,999.90')?as?MB?FROM?dba_extents?e,dba_segments?s?WHERE?e.segment_name?=?s.segment_name?GROUP?BY?e.owner,?e.segment_type,e.segment_name?,?s.max_extents?HAVING?count(*)?>?&THRESHOLD?OR?(?(?s.max_extents?-?count(*)?)??&&THRESHOLD?)?ORDER?BY?count(*)?desc;??
4.?spacebound.sql??
--?spacebound.sql??
--?To?identify?space-bound?objects.?If?all?is?well,?no?rows?are?returned.??
--?If?any?space-bound?objects?are?found,?look?at?value?of?NEXT?extent??
--?size?to?figure?out?what?happened.??
--?Then?use?coalesce?(alter?tablespace??coalesce?.??
--?Lastly,?add?another?datafile?to?the?tablespace?if?needed.??
--?11/30/98??
SELECT?a.table_name,a.next_extent,a.tablespace_name?
FROM?all_tables?a,(SELECT?tablespace_name,max(bytes)?as?big_chunk??
FROM?dba_free_space?GROUP?BY?tablespace_name)?f??
WHERE?f.tablespace_name?=?a.tablespace_name?AND?a.next_extent?>?f.big_chunk;??
B.?每晚處理程式??
1.?mk_volfact.sql??
--?mk_volfact.sql?(only?run?this?once?to?set?it?up;?do?not?run?it?nightly!)??
--?--?Table?UTL_VOL_FACTS??
CREATE?TABLE?utl_vol_facts(??
table_name?VARCHAR2(30),??
num_rows?NUMBER,??
meas_dt?DATE?)??
TABLESPACE?platab??
STORAGE(INITIAL?128k??
NEXT?128k??
PCTINCREASE?0??
MINEXTENTS?1??
MAXEXTENTS?unlimited)??
/??
--?Public?Synonym??
CREATE?PUBLIC?SYNONYM?utl_vol_facts?FOR?&OWNER..utl_vol_facts??
/??
--?Grants?for?UTL_VOL_FACTS??
GRANT?SELECT?ON?utl_vol_facts?TO?public??
/??
2.?analyze_comp.sql??
--??
--?analyze_comp.sql??
--??
BEGIN??
sys.dbms_utility.analyze_schema?(?'&OWNER','COMPUTE');??
END?;??
/??
3.?pop_vol.sql??
--??
--?pop_vol.sql??
--??
insert?into?utl_vol_facts??
select?table_name??
,?NVL?(?num_rows,?0)?as?num_rows??
,?trunc?(?last_analyzed?)?as?meas_dt??
from?all_tables?--?or?just?user_tables??
where?owner?in?('&OWNER')?--?or?a?comma-separated?list?of?owners??
/??
commit??
/??
??
C.?每週處理程式??
1.?nextext.sql??
--??
--?nextext.sql??
--??
--?To?find?tables?that?don't?match?the?tablespace?default?for?NEXT?extent.??
--?The?implicit?rule?here?is?that?every?table?in?a?given?tablespace?should??
--?use?the?exact?same?value?for?NEXT,?which?should?also?be?the?tablespace's??
--?default?value?for?NEXT.??
--??
--?This?tells?us?what?the?setting?for?NEXT?is?for?these?objects?today.??
--??
--?11/30/98??
SELECT?segment_name,?segment_type,?ds.next_extent?as?Actual_Next??
,?dt.tablespace_name,?dt.next_extent?as?Default_Next??
FROM?dba_tablespaces?dt,?dba_segments?ds??
WHERE?dt.tablespace_name?=?ds.tablespace_name??
AND?dt.next_extent?!=ds.next_extent??
AND?ds.owner?=?UPPER?(?'&OWNER'?)??
ORDER?BY?tablespace_name,?segment_type,?segment_name??
2.?existext.sql??
--??
--?existext.sql??
--??
--?To?check?existing?extents??
--??
--?This?tells?us?how?many?of?each?object's?extents?differ?in?size?from??
--?the?tablespace's?default?size.?If?this?report?shows?a?lot?of?different??
--?sized?extents,?your?free?space?is?likely?to?become?fragmented.?If?so,??
--?this?tablespace?is?a?candidate?for?reorganizing.??
--??
--?12/15/98??
SELECT?segment_name,?segment_type??
,?count(*)?as?nr_exts??
,?sum?(?DECODE?(?dx.bytes,dt.next_extent,0,1)?)?as?nr_illsized_exts??
,?dt.tablespace_name,?dt.next_extent?as?dflt_ext_size??
FROM?dba_tablespaces?dt,?dba_extents?dx??
WHERE?dt.tablespace_name?=?dx.tablespace_name??
AND?dx.owner?=?'&OWNER'??
GROUP?BY?segment_name,?segment_type,?dt.tablespace_name,?dt.next_extent??
3.?No_pk.sql??
--??
--?no_pk.sql??
--??
--?To?find?tables?without?PK?constraint??
--??
--?11/2/98??
SELECT?table_name??
FROM?all_tables??
WHERE?owner?=?'&OWNER'??
MINUS??
SELECT?table_name??
FROM?all_constraints??
WHERE?owner?=?'&&OWNER'??
AND?constraint_type?=?'P'??
4.?disPK.sql??
--??
--?disPK.sql??
--??
--?To?find?out?which?primary?keys?are?disabled??
--??
--?11/30/98??
SELECT?owner,?constraint_name,?table_name,?status??
FROM?all_constraints??
WHERE?owner?=?'&OWNER'?AND?status?=?'DISABLED’?AND?constraint_type?=?'P'??
5.?nonuPK.sql??
--??
--?nonuPK.sql??
--??
--?To?find?tables?with?nonunique?PK?indexes.?Requires?that?PK?names??
--?follow?a?naming?convention.?An?alternative?query?follows?that??
--?does?not?have?this?requirement,?but?runs?more?slowly.??
--??
--?11/2/98??
SELECT?index_name,?table_name,?uniqueness??
FROM?all_indexes??
WHERE?index_name?like?'&PKNAME%'??
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/101162/viewspace-914146/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DBA常用SQL語句系列SQL
- DBA常用SQL語句[sql server] 2SQLServer
- sqlserver dba常用的sql語句SQLServer
- postgresql dba常用sql查詢語句SQL
- 常用SQL語句SQL
- sql常用語句SQL
- mongodb dba常用的nosql語句MongoDBSQL
- MongoDB DBA常用的NoSQL語句（全）MongoDBSQL
- mysql dba常用的查詢語句MySql
- ORACLE DBA常用語句和指令碼Oracle指令碼
- oracle常用SQL語句OracleSQL
- 常用的SQL語句SQL
- SQL常用語句整理SQL
- 常用的SQL 語句SQL
- MySql 常用Sql語句MySql
- 常用 SQL 語句大全SQL
- 常用sql進階語句SQL
- SQL 常用語句一覽SQL
- 常用SQL語句彙總SQL
- sql server中常用語句SQLServer
- 史上最全：PostgreSQL DBA常用SQL查詢語句（建議收藏學習）SQL
- mysql 常用sql語句 簡介MySql
- MySQL中常用的SQL語句MySql
- 資料庫常用sql 語句資料庫SQL
- MYSQL 常用sql語句小結MySql
- 常用SQL語句優化技巧SQL優化
- Oracle維護常用SQL語句OracleSQL
- 常用Sql語句積累(二)SQL
- DBA常用SQLSQL
- 資料庫常用的sql語句大全--sql資料庫SQL
- Android原生SQLite常用SQL語句AndroidSQLite
- MySql常用操作SQL語句彙總MySql
- 資料庫常用操作SQL語句資料庫SQL
- 轉:維護常用SQL語句收集！SQL
- GreenPlum DBA常用SQLSQL
- DBA-常用到的動態檢視分析語句
- 織夢cms常用的SQL語句_dedecmsSQL
- MySQL中常用SQL語句的編寫MySql