【Vegas原创】DBA Daily Manage(Oracle)持续更新中…

--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、%'
ORDER BY physical_reads DESC
--8、查看正在使用临时段的session
SELECT se.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 * from v$process;

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=1695
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;
--11、找出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 WHERE request>0)
ORDER BY id1, request;
--12、查看当前所有对象

select * from tab;
--13、查看数据文件放置的路径


col file_name format a50
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;


--14.查DG Archived log appled状态
select * from v$archived_log
where first_time > to_date('2008-11-10 12:00:00','YYYY-MM-DD HH24:Mi:SS') order by first_time
--15.查RMAN进度
select sid,serial#,context,sofar,totalwork,round(sofar/totalwork*100,2)"%_COMPLETE",last_update_time
from v$session_longops
where opname like 'RMAN%' and opname not like '%aggregate%' and totalwork!=0 and sofar<>totalwork
order by last_update_time desc
--16、记载同时存在主键和外键的关系。
select distinct c.owner,

c.table_name,

c.constraint_name,

t.column_name,

t.position,

c.constraint_type,

c.r_owner,

c.r_constraint_name,

p.table_name,

p.column_name,

p.position

from dba_constraints c, dba_cons_columns t, dba_cons_columns p

where c.owner = t.owner

and c.constraint_name = t.constraint_name -- 外键
and c.r_constraint_name = p.constraint_name --主键
and c.r_owner = p.owner

and t.position = p.position

and c.owner in ('TEST')

AND C.constraint_type = 'R'

order by c.owner,

c.table_name,

c.constraint_name,

t.column_name,

t.position,

c.constraint_type,

c.r_owner,

c.r_constraint_name,

p.table_name,

p.column_name,

p.position
--17、top ten sql
SELECT *

FROM (SELECT b.username username,

a.disk_reads

/ DECODE (a.executions, 0, 1, a.executions) rds_exec_ratio,

a.sql_text STATEMENT

FROM v$sqlarea a, dba_users b

WHERE a.parsing_user_id = b.user_id

ORDER BY rds_exec_ratio DESC)

WHERE ROWNUM < 11
ORDER BY rds_exec_ratio DESC