数据库进阶：数据库管理员日常工作中必备的sql列表[1]

数据库管理员日常工作中必备的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;

[] [] []