正在看的ORACLE教程是:ORACLE应用经验(2)。 ------谁正在访问数据库? Select c.sid,c.serial#,c.username,a.object_id,b.object_name, c.program,c.status,d.name,c.osuser from v$Locked_object a, All_objects b, v$session c, audit_actions d where a.object_id=b.object_id and a.session_id =c.sid(+) and c.command=d.action; alter system kill session &1,&2;Select a.sid,a.serial#,a.username,a.status,a.program,b.name,a.osuser from v$session a,audit_actions b where a.command=b.action And username=&1; ------谁被锁住? Select a.sid,A.LOCKWAIT,b.name from v$session a,audit_actions b where a.command=b.action AND LOCKWAIT IS NOT NULL; ------谁在锁表? Select a.sid,audit_actions b where a.command=b.action AND STATUS=ACTIVE; Select sid,serial#,object_name,row_wait_block#, row_wait_row#,row_wait_file# from all_objects,v$session where row_wait_obj#=objectid and type=USER and lockwait is not null ; Select sl.username,sl.sid,sl.serial# from v$lock l1,v$session s1 where exists (select from v_$lock l2,v$session s2 where l2.sid=s2.sid and l2.id1=l1 and s2.lockwait=l2.kaddr and request=0 and l1.sid=s1.sid) ; select count() from v$session; select count() from sys.v_$process; select count() from sys.v_$transaction; ZYP_35.98 ------查看哪些包要固定 COLUMN OWNER FORMAT A10 Select owner,name,type, source_size+code_size+parsed_size+error_size BYPES from dba_object_size where type=PACKAGE BODY ORDER BY 4 DESC ; ------查看一个用户拥有哪些表空间的实体信息: Select tablespace_name,owner,segment_name,segment_type from dba_segments where owner-SyS and segmenttype-ROLLBACK order by tablespace_name,segment_name ; break on owner on segment_name COLUMN segment_name FORMAT A15 cOLUMN tablespace_name FORMAT A15 COLUMN file_name FORMAT A20 SELECT A.owner,a.segment_name,b.tablespace_name,b.file_name, sum(a.bytes) bytes from dba_extents a,dba_data_files b where a.file_id-b.file_id group by a.owner, b.tablespace_name,b.file_name ; ------看内存缓冲区使用效率的指数是命中率HITS: Hits=Logical_reads/(logical_reads+physical_reads) 其中:logical_reads=db_block_gets+consistent_reads select cur.value db,con.value con,phy.value phy, (cur.value+con.value)/cur.value+con.value+phy.value)*100 HITS from v$sysstat cur,v$sysstat con,v$sysstat phy where CUR.NAME=db block gets AND CON.NAME=consistent gets AND PHY.NAME=physical reads ; ------如何检测ROLLBACK SEGMENT竞争? select class,count from v$waitstat where class in (system undo header,system undo block, undo header,undo block) ; select sum(value) from v$sysstat where name in (db block gets,consistents gets) ; 若count/sum(value)大于1%,则应考虑增加ROLLBACK SEGMENT ------查看有事务在哪几个回退段中: COLUMN u FORMAT A15 COLUMN s FORMAT A15 COLUMN s FORMAT A80 select osuser o,username u,segment_name s,sa.sql_text from v$session s,v$transaction t,dba_rollback_segs r,v$sqlarea sa where s.taddr=t.addr and t.sidusn=r.segmant_id(+) and s.sql_address=sa.address(+) ;< (编辑:鄂州站长网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|