DBA常用SQL

查看表空间的名称及大小:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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>SELECTUPPER(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(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES
FROMSYS.DBA_FREE_SPACE GROUPBYTABLESPACE_NAME)F,
(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB FROMSYS.DBA_DATA_FILESDD
GROUPBYDD.TABLESPACE_NAME)D WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME

ORDERBY4DESC;

查看各个表空间占用磁盘情况:
SQL>coltablespace_nameformata20;
SQL>select b.file_idfile_ID,
b.tablespace_nametablespace_name,
b.bytesBytes,
(b.bytes-sum(nvl(a.bytes,0)))used,
sum(nvl(a.bytes,0))free,
sum(nvl(a.bytes,0))/(b.bytes)*100Percent
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_id,b.bytes
orderbyb.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>SELECTname,valueFROMv$sysstatWHEREnameIN('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