DBA在维护oracle数据库时常用的sql语句

DBA在维护oracle数据库时常用的sql语句

1、文件IO统计
select TS.Name,
DF.Name File_Name,
FS.Phyblkrd Blocks_Read,
FS.Phyblkwrt Blocks_Written,
FS.Phyblkrd+FS.Phyblkwrt Total_IOs
from V$FILESTAT FS, V$DATAFILE DF,v$tablespace TS
where DF.File#=FS.File#
AND DF.ts#=TS.TS#
order by FS.Phyblkrd+FS.Phyblkwrt desc;

2、表空间统计
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

3、查看回滚段状态
SELECT a.segment_name,b.status
FROM Dba_Rollback_Segs a,
v$rollstat b
WHERE a.segment_id=b.usn
ORDER BY 2

4、查看哪些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 用户名,
s.status,
s.SQL_ADDRESS,
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

5、查看无法扩展的段
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

6、查看系统锁
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 ;

7、查看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,
st.p1,
st.p2,
st.p3,
st.STATE,
st.SECONDS_IN_WAIT,
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

8、查看正在使用临时段的session
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

9、查看某一个操作系统进程的SQL
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=****08
AND b.addr=a.paddr
AND a.sql_address=c.address(+)
ORDER BY c.piece

10、查看enqueue锁状态
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid
sess, id1, id2, lmode, request,
type FROM
V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
;

10、找出enqueue等待的holder进程
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid
sess, id1, id2, lmode, request,
type FROM
V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERErequest>0)
ORDER BY id1, request
;

11、查看hotblock
SELECT /*+ ordered */
E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,
E.EXTENT_ID EXTENT#,
X.DBABLK - E.BLOCK_ID + 1 BLOCK#,
X.TCH,
L.CHILD#
FROM SYS.V$LATCH_CHILDREN L, SYS.X$BH X, SYS.DBA_EXTENTS E
WHERE L.NAME = 'cache buffers chains'
AND L.SLEEPS > &SLEEP_COUNT
AND X.HLADDR = L.ADDR
AND E.FILE_ID = X.FILE#
AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1
ORDER BY X.TCH;