正在看的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.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name from v$session a,audit_actions b where a.command=b.action AND LOCKWAIT IS NOT NULL; ------誰在鎖表? Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name from v$session a,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#=object_id 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 segment_type_-'ROLLBACK' order by tablespace_name, owner, 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, a.segment_name, b.tablespace_name, b.file_name ;
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
[1] [2] 下一頁
正在看的ORACLE教程是:ORACLE應用經驗(2)。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(+) ;
