查看表空間的名稱(chēng)及大小

復(fù)制代碼代碼如下:

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; 

查看表空間物理文件的名稱(chēng)及大小: 

復(fù)制代碼代碼如下:

SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 

查看回滾段名稱(chēng)及大小: 

復(fù)制代碼代碼如下:

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; 

如何查看某個(gè)回滾段里面，跑的什么事物或者正在執(zhí)行什么sql語(yǔ)句: 

復(fù)制代碼代碼如下:

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; 

(備注：你要看哪個(gè)，就把usn=?寫(xiě)成幾就行了) 
查看控制文件: 
SQL>select * from v$controlfile; 
查看日志文件: 
SQL> col member format a50 
SQL>select * from v$logfile; 
如何查看當(dāng)前SQL*PLUS用戶的sid和serial#: 
SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid'); 
如何查看當(dāng)前數(shù)據(jù)庫(kù)的字符集: 
SQL>select userenv('language') from dual; 
SQL>select userenv('lang') from dual; 
怎么判斷當(dāng)前正在使用何種SQL優(yōu)化方式: 
用explain plan產(chǎn)生EXPLAIN PLAN，檢查PLAN_TABLE中ID=0的POSITION列的值。 
SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0; 
如何查看系統(tǒng)當(dāng)前最新的SCN號(hào)： 
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; 
如何在生產(chǎn)數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)追蹤客戶端IP地址的觸發(fā)器： 

復(fù)制代碼代碼如下:

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 記錄登陸信息的觸發(fā)器 
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; 

查詢當(dāng)前日期: 
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual; 
查看所有表空間對(duì)應(yīng)的數(shù)據(jù)文件名： 
SQL>select distinct file_name,tablespace_name,AUTOEXTENSIBLE from dba_data_files; 
查看表空間的使用情況: 

復(fù)制代碼代碼如下:

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; 

查看數(shù)據(jù)文件的hwm（可以resize的最小空間）和文件頭大小: 

復(fù)制代碼代碼如下:

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(+); 

數(shù)據(jù)文件大小及頭大小: 

復(fù)制代碼代碼如下:

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(+); 

(運(yùn)行以上查詢，我們可以如下信息： 

復(fù)制代碼代碼如下:

Totle_pace:該數(shù)據(jù)文件的總大小，字節(jié)為單位 
Free_space:該數(shù)據(jù)文件的剩于大小，字節(jié)為單位 
Used_space:該數(shù)據(jù)文件的已用空間，字節(jié)為單位 
Data_space:該數(shù)據(jù)文件中段數(shù)據(jù)占用空間，也就是數(shù)據(jù)空間，字節(jié)為單位 
File_Head:該數(shù)據(jù)文件頭部占用空間，字節(jié)為單位) 

數(shù)據(jù)庫(kù)各個(gè)表空間增長(zhǎng)情況的檢查： 

復(fù)制代碼代碼如下:

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; 

查看各個(gè)表空間占用磁盤(pán)情況: 

復(fù)制代碼代碼如下:

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; 

數(shù)據(jù)庫(kù)對(duì)象下一擴(kuò)展與表空間的free擴(kuò)展值的檢查： 

復(fù)制代碼代碼如下:

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語(yǔ)句的獲取： 

復(fù)制代碼代碼如下:

SQL>select sql_text from (select * from v$sqlarea order by disk_reads) 
where rownum<=5; 

查找前十條性能差的sql 

復(fù)制代碼代碼如下:

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 ; 

等待時(shí)間最多的5個(gè)系統(tǒng)等待事件的獲取： 

復(fù)制代碼代碼如下:

SQL>select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5; 

查看當(dāng)前等待事件的會(huì)話: 

復(fù)制代碼代碼如下:

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'; 

找到與所連接的會(huì)話有關(guān)的當(dāng)前等待事件： 

復(fù)制代碼代碼如下:

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所有回滾段狀態(tài)的檢查：