数据库状态检查综合脚本

最新推荐文章于 2022-11-14 19:05:08 发布

郭一军_guoyJoe

最新推荐文章于 2022-11-14 19:05:08 发布

阅读量2.8k

点赞数 2

分类专栏： Oracle Script

本文链接：https://blog.csdn.net/guoyJoe/article/details/8575009

版权

Oracle Script 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

----查询物理文件是否有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');

复制代码

----查看redo信息

select * from v$log;

复制代码

----查看redo文件状态

select * from v$logfile;

复制代码

----查看disable的约束

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');

复制代码

--查看pga使用

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;

复制代码

--share pool中未使用绑定变量的语句

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;

复制代码

--查看IO情况

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';

复制代码

--查看session top3

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;

复制代码

--查看undo的占用

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;

复制代码

--查询系统进程对应的SQL

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
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;
/

复制代码

--查看session IO

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;

复制代码

--查看session情况

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

郭一军_guoyJoe

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
数据库状态检查综合脚本

----查询物理文件是否有offlineselect name, bytes, status from v$datafile where status = 'OFFLINE';复制代码----查询失效对象SELECT owner, object_name, object_type FROM dba_objectsWHERE st
复制链接

扫一扫