----查询物理文件是否有offline
- select name, bytes, status from v$datafile where status = 'OFFLINE';
- SELECT owner, object_name, object_type
- FROM dba_objects
- WHERE status = 'INVALID'
- and owner not in ('SYS', 'SYSTEM');
- select * from v$log;
- select * from v$logfile;
- SELECT owner, constraint_name, table_name, constraint_type, status
- FROM dba_constraints
- WHERE status = 'DISABLED'
- AND constraint_type in ('P', 'U', 'C', 'O', 'R', 'V')
- and owner not in ('SYS', 'SYSTEM');
- select s.username username,s.serial# serial,se.sid,n.name,
- max(se.value) maxmem
- from v$sesstat se,
- v$statname n,
- v$session s
- where n.statistic# = se.statistic#
- and s.sid=se.sid
- and s.username is not null
- group by n.name,se.sid,s.username,s.serial#
- order by 2;
- select d.plan_hash_value plan_hash_value,
- d.execnt execnt,
- a.hash_value hash_value,
- a.sql_text sql_text
- from v$sqltext a,
- (select plan_hash_value, hash_value, execnt
- from (select c.plan_hash_value,
- b.hash_value,
- c.execnt,
- rank() over(partition by c.plan_hash_value order by b.hash_value) as hashrank
- from v$sql b,
- (select count(*) as execnt, plan_hash_value
- from v$sql
- where plan_hash_value <> 0
- group by plan_hash_value
- having count(*) > 10
- order by count(*) desc) c
- where b.plan_hash_value = c.plan_hash_value
- group by c.plan_hash_value, b.hash_value, c.execnt)
- where hashrank <= 3) d
- where a.hash_value = d.hash_value
- order by d.execnt desc, a.hash_value, a.piece;
- select name, value
- from v$sysstat
- where name in
- ('table scans (short tables)', 'table scans (long tables)',
- 'index fast full scans (full)',
- 'index fast full scans (rowid ranges)',
- 'index fast full scans (direct read)', 'table scans (rowid ranges)',
- 'table scans (cache partitions)', 'table scans (direct read)',
- 'table scan blocks gotten');
- SELECT
- TO_CHAR(first_time,'MM/DD') DAY
- --, TO_CHAR(first_time,'YYYY/MM/DD') DAY2
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'00',1,0)) H00
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'01',1,0)) H01
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'02',1,0)) H02
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'03',1,0)) H03
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'04',1,0)) H04
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'05',1,0)) H05
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'06',1,0)) H06
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'07',1,0)) H07
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'08',1,0)) H08
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'09',1,0)) H09
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'10',1,0)) H10
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'11',1,0)) H11
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'12',1,0)) H12
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'13',1,0)) H13
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'14',1,0)) H14
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'15',1,0)) H15
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'16',1,0)) H16
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'17',1,0)) H17
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'18',1,0)) H18
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'19',1,0)) H19
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'20',1,0)) H20
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'21',1,0)) H21
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'22',1,0)) H22
- , SUM(DECODE(TO_CHAR(first_time, 'HH24'),'23',1,0)) H23
- , COUNT(*)||'('||trim(to_char(sum(blocks*block_size)/1024/1024/1024,'99,999.9'))||'G)' TOTAL
- FROM
- (select max(blocks) blocks,max(block_size) block_size,max(first_time) first_time
- from
- v$archived_log a
- where COMPLETION_TIME > sysdate - &day
- and dest_id = 1
- group by sequence#
- )
- group by TO_CHAR(first_time,'MM/DD'), TO_CHAR(first_time,'YYYY/MM/DD')
- order by TO_CHAR(first_time,'YYYY/MM/DD') desc;
- select substr(to_char(sn.snap_time, 'yyyy-mm-dd HH24:MI:SS'), 0),
- round(((new_db_block_gets.value - old_db_block_gets.value) *
- (select value from v$parameter where NAME = 'db_block_size')) / 1024 / 1024 / 1024,
- 2) "db block gets (GB)",
- round(((new_consistent_gets.value - old_consistent_gets.value) *
- (select value from v$parameter where NAME = 'db_block_size')) / 1024 / 1024 / 1024,
- 2) "consistent gets (GB)",
- round(((new_physical_reads.value - old_physical_reads.value) *
- (select value from v$parameter where NAME = 'db_block_size')) / 1024 / 1024 / 1024,
- 2) "physical reads (GB)"
- from perfstat.stats$sysstat old_db_block_gets,
- perfstat.stats$sysstat new_db_block_gets,
- perfstat.stats$sysstat old_consistent_gets,
- perfstat.stats$sysstat new_consistent_gets,
- perfstat.stats$sysstat old_physical_reads,
- perfstat.stats$sysstat new_physical_reads,
- perfstat.stats$snapshot sn
- where new_db_block_gets.snap_id = sn.snap_id
- and new_consistent_gets.snap_id = sn.snap_id
- and new_physical_reads.snap_id = sn.snap_id
- and old_db_block_gets.snap_id = sn.snap_id - 1
- and old_consistent_gets.snap_id = sn.snap_id - 1
- and old_physical_reads.snap_id = sn.snap_id - 1
- and old_db_block_gets.statistic# = 40
- and new_db_block_gets.statistic# = 40
- and old_consistent_gets.statistic# = 41
- and new_consistent_gets.statistic# = 41
- and old_physical_reads.statistic# = 42
- and new_physical_reads.statistic# = 42
- and (new_db_block_gets.value - old_db_block_gets.value) > 0
- and (new_consistent_gets.value - old_consistent_gets.value) > 0
- and (new_physical_reads.value - old_physical_reads.value) > 0
- order by substr(to_char(sn.snap_time, 'yyyy-mm-dd HH24:MI:SS'), 0) desc;
- select p.spid,
- c.object_name,
- c.subobject_name,
- b.session_id,
- b.oracle_username,
- b.os_user_name
- from v$process p, v$session a, v$locked_object b, all_objects c
- where p.addr = a.paddr
- and a.process = b.process
- and c.object_id = b.object_id
- and object_name !='OBJ--查看session IO
- [code]select b.username, a.*
- from v$sess_io a, v$session b
- where a.sid in
- (select x.sid
- from v$session x
- where x.status = 'ACTIVE'
- and x.PADDR not in (select paddr from v$bgprocess))
- and a.sid = b.sid
- and username is not null
- and username <> 'SYS';
- select *
- from (select row_number() over(partition by a.STATISTIC# order by a.value desc) size_rank,
- a.sid,
- b.name,
- a.value
- from v$sesstat a,v$statname b,v$session c
- where c.STATUS='ACTIVE'
- and c.USERNAME is not null
- and a.sid=c.sid
- and a.STATISTIC#=b.STATISTIC#
- order by a.value desc)
- where size_rank <= 3
- and name like '%&NAME%'
- and sid like '%&SID%'
- order by name, size_rank;
- select s.username,s.SID, u.name, r.RSSIZE, r.WRITES, r.HWMSIZE
- from v$transaction t, v$rollstat r, v$rollname u, v$session s
- where s.taddr = t.addr
- and t.xidusn = r.usn
- and r.usn = u.usn
- order by r.rssize desc;
- select c.username,a.sid,c.serial#,' is blocking ',d.username,b.sid,d.serial#
- from
- (select sid,id1,id2 from v$lock where block =1) a,
- (select sid,id1,id2 from v$lock where request > 0) b,
- (select sid,serial#,username from v$session ) c,
- (select sid,serial#,username from v$session ) d
- where a.id1=b.id1
- and a.id2=b.id2
- and a.sid=c.sid
- and b.sid=d.sid;
- select a.tablespace,
- a.SEGFILE#,
- a.segblk#,
- a.blocks,
- b.sid,
- b.serial#,
- b.username,
- b.osuser,
- b.status
- from v$sort_usage a, v$session b
- where a.session_addr = b.saddr;
- select /*+ordered */
- sql_text,
- spid,
- v$session.program,
- machine,
- process,
- sid,
- v$session.SERIAL#
- from v$process, v$session, v$sql
- where v$sql.address = v$session.sql_address
- and v$sql.hash_value = v$session.sql_hash_value
- and v$session.paddr = v$process.addr
- and v$process.spid = &spid;
- select TABLE_NAME,HWM,AVG_USED_BLOCKS,
- GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,sysdate analyze_dt
- from
- (SELECT A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,table_name,
- DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0),
- 0, 1,
- ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/8192, 0)
- ) + 2 AVG_USED_BLOCKS
- FROM USER_SEGMENTS A,
- USER_TABLES B
- WHERE SEGMENT_NAME = TABLE_NAME
- and TABLE_NAME in ('XXX')
- and SEGMENT_TYPE = 'TABLE'
- );
- CREATE OR REPLACE PROCEDURE start_monitor_index
- IS
- Cursor cur1 is
- select OWNER,INDEX_NAME from dba_indexes di where di.owner like '%BJ';
-
- p_rows cur1%ROWTYPE;
- p_str varchar2(200);
-
- begin
- open cur1;
- loop
- fetch cur1 into p_rows;
- exit when cur1%NOTFOUND;
- p_str:='alter index '||p_rows.owner||'.'||p_rows.index_name||' MONITORING USAGE';
- execute immediate p_str;
- --dbms_output.put_line(p_str);
- end loop;
- close cur1;
- dbms_output.put_line('finish');
- COMMIT;
- END;
-
- CREATE OR REPLACE PROCEDURE stop_monitor_index
- IS
- Cursor cur1 is
- select OWNER,INDEX_NAME from dba_indexes di where di.owner like '%BJ';
-
- p_rows cur1%ROWTYPE;
- p_str varchar2(200);
-
- begin
- open cur1;
- loop
- fetch cur1 into p_rows;
- exit when cur1%NOTFOUND;
- p_str:='alter index '||p_rows.owner||'.'||p_rows.index_name||' NOMONITORING USAGE';
- execute immediate p_str;
- --dbms_output.put_line(p_str);
- end loop;
- close cur1;
- dbms_output.put_line('finish');
- COMMIT;
- END;
- /
- create or replace view v$object_usage
- (index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
- as
- select io.name, t.name,
- decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
- decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
- ou.start_monitoring,
- ou.end_monitoring
- from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
- where io.owner# = userenv('SCHEMAID')
- and i.obj# = ou.obj#
- and io.obj# = ou.obj#
- and t.obj# = i.bo#;
- REM =============================================================
- REM
- REM rebuild_indx.sql
- REM
- REM Copyright (c) Oracle Software, 1998 - 2000
- REM
- REM Author : Jurgen Schelfhout
- REM
- REM The sample program in this article is provided for educational
- REM purposes only and is NOT supported by Oracle Support Services.
- REM It has been tested internally, however, and works as documented.
- REM We do not guarantee that it will work for you, so be sure to test
- REM it in your environment before relying on it.
- REM
- REM This script will analyze all the indexes for a given schema
- REM or for a subset of schema's. After this the dynamic view
- REM index_stats is consulted to see if an index is a good
- REM candidate for a rebuild or for a bitmap index.
- REM
- REM Database Version : 7.3.X and above.
- REM
- REM NOTE: If running this on 10g, you must exclude the
- REM objects in the Recycle Bin
- REM cursor c_indx is
- REM select owner, table_name, index_name
- REM from dba_indexes
- REM where owner like upper('&schema')
- REM and table_name not like 'BIN$%'
- REM and owner not in ('SYS','SYSTEM');
- REM
- REM Additional References for Recycle Bin functionality:
- REM Note.265254.1 Flashback Table feature in Oracle Database 10g
- REM Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
- REM
- REM =============================================================
-
- prompt
- ACCEPT spoolfile CHAR prompt 'Output-file : ';
- ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
- prompt
- prompt
- prompt Rebuild the index when :
- prompt - deleted entries represent 20% or more of the current entries
- prompt - the index depth is more then 4 levels.
- prompt Possible candidate for bitmap index :
- prompt - when distinctiveness is more than 99%
- prompt
- spool &spoolfile
-
- set serveroutput on
- set verify off
- declare
- c_name INTEGER;
- ignore INTEGER;
- height index_stats.height%TYPE := 0;
- lf_rows index_stats.lf_rows%TYPE := 0;
- del_lf_rows index_stats.del_lf_rows%TYPE := 0;
- distinct_keys index_stats.distinct_keys%TYPE := 0;
- cursor c_indx is
- select owner, table_name, index_name
- from dba_indexes
- where owner like upper('&schema')
- and owner not in ('SYS','SYSTEM');
- cursor c_indx is
- select owner, table_name, index_name
- from dba_indexes
- where owner like upper('&schema')
- and table_name not like 'BIN$%'
- and owner not in ('SYS','SYSTEM');
-
-
-
- begin
- dbms_output.enable (1000000);
- dbms_output.put_line ('Owner Index Name % Deleted Entries Blevel Distinctiveness');
- dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');
-
- c_name := DBMS_SQL.OPEN_CURSOR;
- for r_indx in c_indx loop
- DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
- r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
- ignore := DBMS_SQL.EXECUTE(c_name);
-
- select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
- decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
- into height, lf_rows, del_lf_rows, distinct_keys
- from index_stats;
- --
- -- Index is considered as candidate for rebuild when :
- -- - when deleted entries represent 20% or more of the current entries
- -- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
- -- Index is (possible) candidate for a bitmap index when :
- -- - distinctiveness is more than 99%
- --
- if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
- dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
- lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
- lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
- end if;
-
- end loop;
- DBMS_SQL.CLOSE_CURSOR(c_name);
- end;
- /
-
- spool off
- set verify on
- CREATE OR REPLACE PROCEDURE repair_chained_rows(f_owner in varchar) IS
- Cursor cur1 is
- select table_name from dba_tables dt where dt.owner = f_owner;
-
- -- type c_type is ref cursor;
- -- cur2 c_type;
-
- p_rows cur1%ROWTYPE;
- p_str varchar2(200);
- p_str2 varchar2(200);
- p_str3 varchar2(200);
- p_str4 varchar2(200);
- p_str5 varchar2(200);
- x number(10) default 1;
-
- begin
- open cur1;
- loop
- fetch cur1
- into p_rows;
- exit when cur1%NOTFOUND;
- p_str := 'analyze table ' || f_owner || '.' || p_rows.table_name ||
- ' list chained rows'; ---分析表
- execute immediate p_str; ---执行分析
- dbms_output.put_line(p_str);
-
- p_str2 := '
- select * from ' || f_owner || '.' || p_rows.table_name ||
- ' where rowid in (select HEAD_ROWID from chained_rows)';
- ---查找行链接的记录
- execute immediate p_str2;
-
- if SQL%NOTFOUND then
- dbms_output.put_line('NO CHAINED ROWS');
- p_str3 := 'create table ACR_TEMP' || x || ' as ' || p_str2;
- p_str4 := 'delete from ' || f_owner || '.' || p_rows.table_name ||
- ' where rowid in (select HEAD_ROWID from chained_rows)';
- p_str5 := 'insert into ' || f_owner || '.' || p_rows.table_name ||
- ' select * from ACR_TEMP' || x;
- dbms_output.put_line(p_str3);
- dbms_output.put_line(p_str4);
- dbms_output.put_line(p_str5);
- x := x + 1;
- else
- p_str3 := 'create table ACR_TEMP' || x || ' as ' || p_str2;
- p_str4 := 'delete from ' || f_owner || '.' || p_rows.table_name ||
- ' where (rowid in select HEAD_ROWID from chained_rows)';
- p_str5 := 'insert into ' || f_owner || '.' || p_rows.table_name ||
- ' select * from ACR_TEMP' || x;
- dbms_output.put_line('HAVE SOME CHAINED ROWS');
- x := x + 1;
- end if;
-
- end loop;
- close cur1;
- COMMIT;
- END;
- /
- select b.username, a.*
- from v$sess_io a, v$session b
- where a.sid in
- (select x.sid
- from v$session x
- where x.status = 'ACTIVE'
- and x.PADDR not in (select paddr from v$bgprocess))
- and a.sid = b.sid
- and username is not null
- and username <> 'SYS';
- SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(F) NO_MERGE(U) NO_MERGE(O) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",
- ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
- ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES, 0) / 1024 / 1024,NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024) ,2) "占用量(MB)",
- TO_CHAR(ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(U.BYTES / A.BYTES * 100, 0),NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)) ,2),'999.99')||'%' "占用率(MB)",
- ROUND(DECODE(D.CONTENTS, 'UNDO', NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,NVL(F.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
- D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型",
- D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理"
- FROM SYS.DBA_TABLESPACES D,
- (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT,
- CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO' ELSE '' END AS AUTOEXTENSIBLE
- FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
- (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
- (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
- FROM DBA_UNDO_EXTENTS
- WHERE STATUS IN ('UNEXPIRED', 'UNKNOWN')
- GROUP BY TABLESPACE_NAME) U
- WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
- AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+) AND NOT (D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY')
- --AND D.TABLESPACE_NAME LIKE :1
- UNION ALL
- SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(T) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",
- ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
- ROUND(NVL(T.BYTES, 0) / 1024 / 1024 ,2) "占用量(MB)",
- TO_CHAR(ROUND(NVL(T.BYTES / A.BYTES * 100, 0) ,2),'999.99')||'%' "占用率(MB)",
- ROUND((NVL(A.BYTES, 0) / 1024 / 1024 - NVL(T.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
- D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型",
- D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理"
- FROM SYS.DBA_TABLESPACES D,
- (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT,
- CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE))>=10000 THEN 'YES' ELSE 'NO' END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,'YES',10000,'NO',1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||'NO' ELSE '' END AS AUTOEXTENSIBLE
- FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A,
- (SELECT SS.TABLESPACE_NAME, SUM((SS.USED_BLOCKS * TS.BLOCKSIZE)) BYTES
- FROM GV$SORT_SEGMENT SS, SYS.TS$ TS
- WHERE SS.TABLESPACE_NAME = TS.NAME
- GROUP BY SS.TABLESPACE_NAME) T
- WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
- AND D.EXTENT_MANAGEMENT = 'LOCAL'
- AND D.CONTENTS = 'TEMPORARY'
- --AND D.TABLESPACE_NAME LIKE :2
- ORDER BY 6 DESC;
- select /*+rule*/ a.sid,
- a.username as "用户名",
- j.EVENT as "等待事件",
- /*j.P1TEXT,
- j.P1,
- j.P2TEXT,
- j.p2,
- j.p3text,
- j.p3,
- j.STATE,*/
- a.osuser as "OS用户名",
- a.machine "OS机器名",
- a.program "OS程序名",
- a.module "模块名",
- a.action "动作名",
- i.name as "操作命令",
- a.type "用户类型",
- a.logon_time "登陆时间",
- g.ospid "OS进程号",
- c.logical_reads "逻辑读",
- c.physical_reads "物理读",
- c.io_write "写IO",
- d.cputimes "CPU执行时间",
- e.memsize "共享内存占用",
- f.redosize "REDO大小",
- 'select sql_text from v$sqltext where address=''' || b.address ||
- ''' and hash_value=''' || b.hash_value || ''' order by piece' sql_text,
- 'select sql_text from v$sqltext where address=''' || k.address ||
- ''' and hash_value=''' || k.hash_value || ''' order by piece' prev_sql_text
- from (select sid,
- username,
- osuser,
- machine,
- program,
- module,
- action,
- command,
- type,
- logon_time,
- paddr,
- taddr,
- lockwait,
- sql_address,
- sql_hash_value,
- prev_sql_addr,
- prev_hash_value
- from v$session
- where status = 'ACTIVE'
- and username is not null
- and username <> 'SYS'
- and type <> 'BACKGROUND') a,
- AUDIT_ACTIONS i,
- v$session_wait j,
- (select address, hash_value, sql_text from v$sqltext where piece = 0) b,
- (select address, hash_value, sql_text from v$sqltext where piece = 0) k,
- (select sid,
- round((block_gets + consistent_gets) * 8 / 1024) logical_reads,
- round(physical_reads * 8 / 1024) physical_reads,
- round((block_changes + consistent_changes) * 8 / 1024) io_write
- from v$sess_io) c,
- (select sid, value / 100 cputimes
- from v$sesstat
- where statistic# = 11) d,
- (select sid, sum(value) memsize
- from v$sesstat
- where statistic# in (15, 20)
- group by sid) e,
- (select sid,
- sum((decode(statistic#, 115, value, 0) +
- decode(statistic#, 117, value, 0)) /
- (decode(statistic#, 120, value, 0) + 16)) redosize
- from v$sesstat
- group by sid) f,
- (select addr, spid ospid from v$process) g
- where (a.sql_address = b.address(+) and a.sql_hash_value = b.hash_value(+))
- and (a.prev_sql_addr = k.address(+) and a.prev_hash_value = k.hash_value(+))
- and (a.sid = c.sid(+))
- and (a.sid = d.sid(+))
- and (a.sid = e.sid(+))
- and (a.sid = f.sid(+))
- and (a.paddr = g.addr(+))
- and a.command = i.action
- and (a.sid = j.sid(+))
- order by c.logical_reads desc nulls last;
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036