您现在正在浏览：站长之家 >> 数据库 >> 详细内容

Oracle维护常用SQL语句（2）

时间:2007-12-23 来源:不详作者:迈克DB

14、回滚段查看
　　selectrownum,sys.dba_rollback_segs.segment_nameName,v$rollstat.extents
　　Extents,v$rollstat.rssizeSize_in_Bytes,v$rollstat.xactsXActs,
　　v$rollstat.getsGets,v$rollstat.waitsWaits,v$rollstat.writesWrites,
　　sys.dba_rollback_segs.statusstatusfromv$rollstat,sys.dba_rollback_segs,
　　v$rollnamewherev$rollname.name( )=sys.dba_rollback_segs.segment_nameand
　　v$rollstat.usn( )=v$rollname.usnorderbyrownum
15、耗资源的进程(topsession)
　　selects.schemanameschema_name,decode(sign(48-command),1,
　　to_char(command),’ActionCode#’||to_char(command))action,status
　　session_status,s.osuseros_user_name,s.sid,p.spid,s.serial#serial_num,
　　nvl(s.username,’[Oracleprocess]’)user_name,s.terminalterminal,
　　s.programprogram,st.valuecriteria_valuefromv$sesstatst,v$sessions,v$processp
　　wherest.sid=s.sidandst.statistic#=to_number(’38’)and(’ALL’=’ALL’
　　ors.status=’ALL’)andp.addr=s.paddrorderbyst.valuedesc,p.spidasc,s.usernameasc,s.osuserasc

dedecms.com

16、查看锁(lock)情况
　　select/* RULE*/ls.osuseros_user_name,ls.usernameuser_name,
　　decode(ls.type,’RW’,’Rowwaitenqueuelock’,’TM’,’DMLenqueuelock’,’TX’,
　　’Transactionenqueuelock’,’UL’,’Usersuppliedlock’)lock_type,
　　o.object_nameobject,decode(ls.lmode,1,null,2,’RowShare’,3,
　　’RowExclusive’,4,’Share’,5,’ShareRowExclusive’,6,’Exclusive’,null)
　　lock_mode,o.owner,ls.sid,ls.serial#serial_num,ls.id1,ls.id2
　　fromsys.dba_objectso,(selects.osuser,s.username,l.type,
　　l.lmode,s.sid,s.serial#,l.id1,l.id2fromv$sessions,
　　v$locklwheres.sid=l.sid)lswhereo.object_id=ls.id1ando.owner
　　<>’SYS’orderbyo.owner,o.object_name
17、查看等待(wait)情况
　　SELECTv$waitstat.class,v$waitstat.countcount,SUM(v$sysstat.value)sum_value
　　FROMv$waitstat,v$sysstatWHEREv$sysstat.nameIN(’dbblockgets’,
　　’consistentgets’)groupbyv$waitstat.class,v$waitstat.count
18、查看sga情况

　　SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC
19、查看catchedobject
　　SELECTowner,name,db_link,namespace,
　　type,sharable_mem,loads,executions,
　　locks,pins,keptFROMv$db_object_cache
20、查看V$SQLAREA
　　SELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,
　　VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
　　USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,
　　BUFFER_GETS,ROWS_PROCESSEDFROMV$SQLAREA
　　21、查看object分类数量
　　selectdecode(o.type#,1,’INDEX’,2,’TABLE’,3,’CLUSTER’,4,’VIEW’,5,
　　’SYNONYM’,6,’SEQUENCE’,’OTHER’)object_type,count(*)quantityfrom
　　sys.obj$owhereo.type#>1groupbydecode(o.type#,1,’INDEX’,2,’TABLE’,3
　　,’CLUSTER’,4,’VIEW’,5,’SYNONYM’,6,’SEQUENCE’,’OTHER’)unionselect
　　’COLUMN’,count(*)fromsys.col$unionselect’DBLINK’,count(*)from