數據庫管理員日常工作中必備的sql列表
監控索引是否使用
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;
求數據文件的I/O分布
select dfnamephyrdsphywrtsphyblkrdphyblkwrtsingleblkrdsreadtimwritetim
from v$filestat fsv$dbfile df
where fsfile#=dffile# order by dfname;
求某個隱藏參數的值
col ksppinm format a
col ksppstvl format a
select ksppinm ksppstvl
from x$ksppi pi x$ksppcv cv
where cvindx=piindx and piksppinm like \_% escape \ and piksppinm like %meer%;
求系統中較大的latch
select namesum(gets)sum(misses)sum(sleeps)sum(wait_time)
from v$latch_children
group by name having sum(gets) > order by ;
求歸檔日志的切換頻率(生產系統可能時間會很長)
select start_recidstart_timeend_recidend_timeminutes from (select test* rownum as rn
from (select brecid start_recidto_char(bfirst_timeyyyymmdd hh:mi:ss) start_time
arecid end_recidto_char(afirst_timeyyyymmdd hh:mi:ss) end_timeround(((afirst_timebfirst_time)*)*) minutes
from v$log_history av$log_history b where arecid=brecid+ and bfirst_time > sysdate
order by afirst_time desc) test) y where yrn <
求回滾段正在處理的事務
select anamebxactscsidcserial#dsql_text
from v$rollname av$rollstat bv$session cv$sqltext dv$transaction e
where ausn=busn and busn=exidusn and ctaddr=eaddr
and csql_address=daddress and csql_hash_value=dhash_value order by anamecsiddpiece;
求出無效的對象
select alter procedure ||object_name|| compile;
from dba_objects
where status=INVALID and wner=& and object_type in (PACKAGEPACKAGE BODY);
/
select ownerobject_nameobject_typestatus from dba_objects where status=INVALID;
求process/session的狀態
select ppidpspidsprogramssidsserial#
from v$process pv$session s where spaddr=paddr;
求當前session的狀態
select snnamemsvalue
from v$mystat msv$statname sn
where msstatistic#=snstatistic# and msvalue > ;
求表的索引信息
select uitable_nameuiindex_name
from user_indexes uiuser_ind_columns uic
where uitable_name=uictable_name and uiindex_name=uicindex_name
and uitable_name like &table_name% and uiccolumn_name=&column_name;
顯示表的外鍵信息
col search_condition format a
select table_nameconstraint_name
from user_constraints
where constraint_type =R and constraint_name in (select constraint_name from user_cons_columns where column_name=&);
select rpad(childtable_name ) child_tablename
rpad(cpcolumn_name ) referring_columnrpad(parenttable_name ) parent_tablename
rpad(pccolumn_name ) referred_columnrpad(childconstraint_name ) constraint_name
from user_constraints childuser_constraints parent
user_cons_columns cpuser_cons_columns pc
where childconstraint_type = R and childr_constraint_name = parentconstraint_name and
childconstraint_name = cpconstraint_name and parentconstraint_name = pcconstraint_name and
cpposition = pcposition and childtable_name =&table_name
order by childownerchildtable_namechildconstraint_namecpposition;
顯示表的分區及子分區(user_tab_subpartitions)
col table_name format a
col partition_name format a
col high_value format a
select table_namepartition_nameHIGH_VALUE from user_tab_partitions where table_name=&table_name
使用dbms_xplan生成一個執行計劃
explain plan set statement_id = &sql_id for &sql;
select * from table(dbms_xplandisplay);
求某個事務的重做信息(bytes)
select snamemvalue
from v$mystat mv$statname s
where mstatistic#=sstatistic# and sname like %redo size%;
求cache中緩存超過其%的對象
select oowneroobject_typeoobject_namecount(bobjd)
from v$bh bdba_objects o
where bobjd = oobject_id
group by oowneroobject_typeoobject_name
having count(bobjd) > (select to_number(value)* from v$parameter where name = db_block_buffers);
求誰阻塞了某個session(g)
select sid username event blocking_session
seconds_in_wait wait_time
from v$session where state in (WAITING) and wait_class != Idle;
求session的OS進程ID
col program format a
select pspid OS Thread bname NameUser sprogram
from v$process p v$session s v$bgprocess b
where paddr = spaddr and paddr = bpaddr
UNION ALL
select pspid OS Thread susername NameUser sprogram
from v$process p v$session s where paddr = spaddr and susername is not null;
查會話的阻塞
col user_name format a
select /*+ rule */ lpad( decode(lxidusn ))||loracle_username user_name oowneroobject_namessidsserial#
from v$locked_object ldba_objects ov$session s
where lobject_id=oobject_id and lsession_id=ssid order by oobject_idxidusn desc ;
col username format a
col lock_level format a
col owner format a
col object_name format a
select /*+ rule */ susername decode(ltypetmtable lock txrow lock null) lock_level oowneroobject_namessidsserial#
from v$session sv$lock ldba_objects o
where lsid = ssid and lid = oobject_id(+) and susername is not null ;
求等待的事件及會話信息/求會話的等待及會話信息
select sesidsusernameseeventsetotal_waitssetime_waitedseaverage_wait
from v$session sv$session_event se
where susername is not null and sesid=ssid and sstatus=ACTIVE and seevent not like %SQL*Net% order by susername;
select ssidsusernamesweventswwait_timeswstateswseconds_in_wait
from v$session sv$session_wait sw
where susername is not null and swsid=ssid and swevent not like %SQL*Net% order by susername;
[] [] []
From:http://tw.wingwit.com/Article/program/SQL/201311/16149.html
