SQL DBA日常sql列表Word格式文档下载.docx
- 文档编号:18853462
- 上传时间:2023-01-01
- 格式:DOCX
- 页数:13
- 大小:21.34KB
SQL DBA日常sql列表Word格式文档下载.docx
《SQL DBA日常sql列表Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《SQL DBA日常sql列表Word格式文档下载.docx(13页珍藏版)》请在冰豆网上搜索。
selectstart_recid,start_time,end_recid,end_time,minutesfrom(selecttest.*,rownumasrn
from(selectb.recidstart_recid,to_char(b.first_time,'
yyyy-mm-ddhh24:
mi:
ss'
)start_time,
a.recidend_recid,to_char(a.first_time,'
)end_time,round(((a.first_time-b.first_time)*24)*60,2)minutes
fromv$log_historya,v$log_historybwherea.recid=b.recid+1andb.first_time>
sysdate-1
orderbya.first_timedesc)test)ywherey.rn<
30
--求回滚段正在处理的事务
selecta.name,b.xacts,c.sid,c.serial#,d.sql_text
fromv$rollnamea,v$rollstatb,v$sessionc,v$sqltextd,v$transactione
wherea.usn=b.usnandb.usn=e.xidusnandc.taddr=e.addr
andc.sql_address=d.addressandc.sql_hash_value=d.hash_valueorderbya.name,c.sid,d.piece;
--求出无效的对象
select'
alterprocedure'
||object_name||'
compile;
'
fromdba_objects
wherestatus='
INVALID'
andwner='
&
andobject_typein('
PACKAGE'
'
PACKAGEBODY'
);
/
selectowner,object_name,object_type,statusfromdba_objectswherestatus='
--求process/session的状态
selectp.pid,p.spid,s.program,s.sid,s.serial#
fromv$processp,v$sessionswheres.paddr=p.addr;
--求当前session的状态
selectsn.name,ms.value
fromv$mystatms,v$statnamesn
wherems.statistic#=sn.statistic#andms.value>
0;
--求表的索引信息
selectui.table_name,ui.index_name
fromuser_indexesui,user_ind_columnsuic
whereui.table_name=uic.table_nameandui.index_name=uic.index_name
andui.table_namelike'
table_name%'
anduic.column_name='
column_name'
--显示表的外键信息
colsearch_conditionformata54
selecttable_name,constraint_name
fromuser_constraints
whereconstraint_type='
R'
andconstraint_namein(selectconstraint_namefromuser_cons_columnswherecolumn_name='
1'
selectrpad(child.table_name,25,'
'
)child_tablename,
rpad(cp.column_name,17,'
)referring_column,rpad(parent.table_name,25,'
)parent_tablename,
rpad(pc.column_name,15,'
)referred_column,rpad(child.constraint_name,25,'
)constraint_name
fromuser_constraintschild,user_constraintsparent,
user_cons_columnscp,user_cons_columnspc
wherechild.constraint_type='
andchild.r_constraint_name=parent.constraint_nameand
child.constraint_name=cp.constraint_nameandparent.constraint_name=pc.constraint_nameand
cp.position=pc.positionandchild.table_name='
table_name'
orderbychild.owner,child.table_name,child.constraint_name,cp.position;
--显示表的分区及子分区(user_tab_subpartitions)
coltable_nameformata16
colpartition_nameformata16
colhigh_valueformata81
selecttable_name,partition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name='
--使用dbms_xplan生成一个执行计划
explainplansetstatement_id='
sql_id'
for&
sql;
select*fromtable(dbms_xplan.display);
--求某个事务的重做信息(bytes)
selects.name,m.value
fromv$mystatm,v$statnames
wherem.statistic#=s.statistic#ands.namelike'
%redosize%'
--求cache中缓存超过其5%的对象
selecto.owner,o.object_type,o.object_name,count(b.objd)
fromv$bhb,dba_objectso
whereb.objd=o.object_id
groupbyo.owner,o.object_type,o.object_name
havingcount(b.objd)>
(selectto_number(value)*0.05fromv$parameterwherename='
db_block_buffers'
--求谁阻塞了某个session(10g)
selectsid,username,event,blocking_session,
seconds_in_wait,wait_time
fromv$sessionwherestatein('
WAITING'
)andwait_class!
='
Idle'
--求session的OS进程ID
colprogramformata54
selectp.spid"
OSThread"
b.name"
Name-User"
s.program
fromv$processp,v$sessions,v$bgprocessb
wherep.addr=s.paddrandp.addr=b.paddr
UNIONALL
s.username"
fromv$processp,v$sessionswherep.addr=s.paddrands.usernameisnotnull;
--查会话的阻塞
coluser_nameformata32
select/*+rule*/lpad('
decode(l.xidusn,0,3,0))||l.oracle_usernameuser_name,o.owner,o.object_name,s.sid,s.serial#
fromv$locked_objectl,dba_objectso,v$sessions
wherel.object_id=o.object_idandl.session_id=s.sidorderbyo.object_id,xidusndesc;
colusernameformata15
collock_levelformata8
colownerformata18
colobject_nameformata32
select/*+rule*/s.username,decode(l.type,'
tm'
tablelock'
'
tx'
rowlock'
null)lock_level,o.owner,o.object_name,s.sid,s.serial#
fromv$sessions,v$lockl,dba_objectso
wherel.sid=s.sidandl.id1=o.object_id(+)ands.usernameisnotnull;
--求等待的事件及会话信息/求会话的等待及会话信息
selectse.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
fromv$sessions,v$session_eventse
wheres.usernameisnotnullandse.sid=s.sidands.status='
ACTIVE'
andse.eventnotlike'
%SQL*Net%'
orderbys.username;
selects.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait
fromv$sessions,v$session_waitsw
wheres.usernameisnotnullandsw.sid=s.sidandsw.eventnotlike'
--求会话等待的file_id/block_id
coleventformata24
colp1textformata12
colp2textformata12
colp3textformata12
selectsid,event,p1text,p1,p2text,p2,p3text,p3
fromv$session_wait
whereeventnotlike'
%SQL%'
andeventnotlike'
%rdbms%'
%mon%'
orderbyevent;
selectname,wait_timefromv$latchlwhereexists(select1from(selectsid,event,p1text,p1,p2text,p2,p3text,p3
)xwherex.p1=l.latch#);
--求会话等待的对象
colsegment_nameformata32
colsegment_typeformata32
selectowner,segment_name,segment_type
fromdba_extents
wherefile_id=&
file_idand&
block_idbetweenblock_idandblock_id+blocks-1;
--求buffercache中的块信息
selecto.OBJECT_TYPE,substr(o.OBJECT_NAME,1,10)objname,b.objd,b.status,count(b.objd)
fromv$bhb,dba_objectso
whereb.objd=o.data_object_idando.owner='
groupbyo.object_type,o.object_name,b.objd,b.status;
--求日志文件的空间使用
selectle.leseqcurrent_log_sequence#,100*cp.cpodr_bno/le.lesizpercentage_full
fromx$kcccpcp,x$kcclele
wherele.leseq=cp.cpodr_seq;
--求等待中的对象
select/*+rule*/s.sid,s.username,w.event,o.owner,o.segment_name,o.segment_type,
o.partition_name,w.seconds_in_waitseconds,w.state
fromv$session_waitw,v$sessions,dba_extentso
wherew.eventin(selectnamefromv$event_namewhereparameter1='
file#'
andparameter2='
block#'
andnamenotlike'
control%'
)
ando.owner<
>
sys'
andw.sid=s.sidandw.p1=o.file_idandw.p2>
=o.block_idandw.p2<
o.block_id+o.blocks
--求当前事务的重做尺寸
selectvalue
fromv$mystat,v$statname
wherev$mystat.statistic#=v$statname.statistic#andv$statname.name='
redosize'
--唤醒smon去清除临时段
columnpidnew_valueSmon
settermoutoff
selectp.pidfromsys.v_$bgprocessb,sys.v_$processpwhereb.name='
SMON'
andp.addr=b.paddr
settermouton
oradebugwakeup&
Smon
undefineSmon
--求回退率
selectb.value/(a.value+b.value),a.value,b.valuefromv$sysstata,v$sysstatb
wherea.statistic#=4andb.statistic#=5;
--求DISKREAD较多的SQL
selectst.sql_textfromv$sqls,v$sqltextst
wheres.address=st.addressands.hash_value=st.hash_valueands.disk_reads>
300;
--求DISKSORT严重的SQL
selectsess.username,sql.sql_text,sort1.blocks
fromv$sessionsess,v$sqlareasql,v$sort_usagesort1
wheresess.serial#=sort1.session_num
andsort1.sqladdr=sql.address
andsort1.sqlhash=sql.hash_valueandsort1.blocks>
200;
--求对象的创建代码
columncolumn_nameformata36
columnsql_textformata99
selectdbms_metadata.get_ddl('
TABLE'
)fromdual;
INDEX'
--求表的索引
setlinesize131
selecta.index_name,a.column_name,b.status,b.index_type
fromuser_ind_columnsa,user_indexesb
wherea.index_name=b.index_nameanda.table_name='
求索引中行数较多的
selectindex_name,blevel,num_rows,CLUSTERING_FACTOR,statusfromuser_indexeswherenum_rows>
10000andblevel>
0
selecttable_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,statusfromuser_indexeswherestatus<
VALID'
--求当前会话的SID,SERIAL#
selectsid,serial#fromv$sessionwhereaudsid=SYS_CONTEXT('
USERENV'
SESSIONID'
--求表空间的未用空间
colmbytesformat9999.9999
selecttablespace_name,sum(bytes)/1024/1024mbytesfromdba_free_spacegroupbytablespace_name;
--求表中定义的触发器
selecttable_name,index_type,index_name,uniquenessfromuser_indexeswheretable_name='
selecttrigger_namefromuser_triggerswheretable_name='
--求未定义索引的表
selecttable_namefromuser_tableswheretable_namenotin(selecttable_namefromuser_ind_columns);
--执行常用的过程
execprint_sql('
selectcount(*)fromtab'
execshow_space2('
--求freememory
select*fromv$sgastatwherename='
freememory'
selecta.name,sum(b.value)fromv$statnamea,v$sesstatbwherea.statistic#=b.statistic#groupbya.name;
查看一下谁在使用那个可以得回滚段,或者查看一下某个可以得用户在使用回滚段,
找出领回滚段不断增长的事务,再看看如何处理它,是否可以将它commit,再不行
就看看能否kill它,等等,查看当前正在使用的回滚段的用户信息和回滚段信息:
setlinesize121
SELECTr.name"
ROLLBACKSEGMENTNAME"
l.sid"
ORACLEPID"
p.spid"
SYSTEMPID"
s.username"
ORACLEUSERNAME"
FROMv$lockl,v$processp,v$rollnamer,v$sessions
WHEREl.sid=p.pid(+)ANDs.sid=l.sidANDTRUNC(l.id1(+)/65536)=r.usnANDl.type(+)='
TX'
ANDl.lmode(+)=6ORDERBYr.name;
--查看用户的回滚段的信息
selects.username,rn.namefromv$sessions,v$transactiont,v$ro
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL DBA日常sql列表 DBA 日常 列表