spool check_database_liuzhou.log
prompt
prompt ####################### Execution Time ################################
set linesize 140
set pagesize 9999
select sysdate as current_date from dual;
prompt ####################### Database Version ################################
select * from v$version;
prompt ####################### Instance Information ################################
col instance_number for 99999999
col INSTANCE_NAME for a14
col host_name for a12
col status for a8
col STARTUP_TIME for a16
col THREAD# for 999999999
col paraller for a6
select INSTANCE_NUMBER,INSTANCE_NAME,host_name,STATUS,STARTUP_TIME,THREAD#,parallel from Gv$instance;
prompt ####################### Database Information ################################
set linesize 240
col dbid for 999999999999999
col "Database Name" for a8
col open_mode for a12
col created for a16
col log_mode for a12
col checkpoint_change# for 999999999999999
col controlfile_type for a12
col controlfile_created for a12
col controlfile_change# for 999999999999999
col controlfile_time for a12
col resetlogs_change# for 999999999999999
col resetlogs_time for a12
select dbid, name "Database Name",open_mode,flashback_on,open_mode, log_mode from v$database;
select created ,checkpoint_change# ,controlfile_type,controlfile_created,controlfile_change#,controlfile_time,resetlogs_change#,resetlogs_time from v$database;
prompt ####################### SCN Information ################################
col time for a20
col scn for 99999999999999999999999
col Headroom for 999999999999999
SELECT to_char(tim,'yyyy-mm-dd hh24:mi:ss') time,scn,round((chk16kscn-scn)/24/3600/16/1024,1) Headroom
FROM
(
select tim, scn,
((
((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(tim,'DD'))-1))*24*60*60) +
(to_number(to_char(tim,'HH24'))*60*60) +
(to_number(to_char(tim,'MI'))*60) +
(to_number(to_char(tim,'SS')))
) * (16*1024)) chk16kscn
from
(select sysdate tim,checkpoint_change# scn from v$database))
ORDER BY tim;
prompt ####################### Parameter File ################################
col name for a8
col value for a80
select name,value from gv$parameter where name='spfile';
prompt ####################### List Spfiles Kept In ASM Instance ################################
SELECT full_path, dir, sys
FROM
(SELECT
CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT g.name gname,
a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%SPFILE%';
prompt ####################### Non-default Parameter ################################
col name for a40
col Instance_name for a12
col value for a70
SELECT p.name,i.instance_name ,p.value
FROM gv$parameter p, gv$instance i
WHERE p.inst_id = i.inst_id
and isdefault='FALSE'
ORDER BY p.name , i.instance_name;
prompt ####################### ASM Disk Groups User Space Information ################################
SELECT name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM v$asm_diskgroup
ORDER BY name;
prompt ####################### Memory Size ################################
col name for a16
col "SIZE(M)" for a14
select 'SGA' AS NAME,ROUND(sum(value)/1024/1024,2)||'M' AS "SIZE(M)" from v$sga
UNION
select 'PGA' AS NAME,ROUND(value/1024/1024,2)||'M' AS "SIZE(M)" from v$pgastat where name='total PGA allocated'
UNION
select 'TOTAL' AS NAME,((SELECT ROUND(sum(value)/1024/1024,2) from v$sga)+(select ROUND(value/1024/1024,2) from v$pgastat where name='total PGA allocated'))||'M' AS "SIZE(M)" FROM DUAL ;
prompt ####################### Control file ################################
col name for a60
SELECT NAME FROM v$CONTROLFILE;
prompt ####################### Database Size ################################
col Datasize for a15
col Freesize for a15
col usedsize for a15
col tempsize for a15
select (select round(sum(bytes/1024/1024/1024),2)||'G' from dba_data_files) Datasize,
(select round(sum(bytes/1024/1024/1024),2)||'G' from dba_free_space) Freesize,
(select round(sum(bytes/1024/1024/1024),2) from dba_data_files)-(select round(sum(bytes/1024/1024/1024),2) from dba_free_space)||'G' usedsize,
(select round(sum(bytes/1024/1024/1024),2)||'G' from dba_temp_files) tempsize from dual;
prompt ####################### Tablespace Information ################################
col name for a34
col tablespace_name for a30
col file_name for a38
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)
SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)
"USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)" FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)
SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM v$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
prompt ####################### Undo Tablespace Information ################################
col tablespace_name for a30
col status for a12
col "Bytes(M)" for a14
SELECT tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"
FROM dba_undo_extents
GROUP BY tablespace_name, status;
prompt ####################### Datafile Information ################################
col tablespace_name for a30
col file_name for a30
col AUTOEXTENSIBLE for a8
col status for a12
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,
DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;
prompt ####################### Datafile Header Information ################################
col ts# for 99999
col file# for 99999
col error
col tablespace_name for a30
col status for a12
select ts#,file#,TABLESPACE_NAME,status,ERROR,FORMAT,recover,FUZZY,
CREATION_TIME CREATE_TIME,
checkpoint_change# "SCN",
RESETLOGS_CHANGE# "RESETLOGS SCN"
from v$datafile_header
order by 1,2;
/*
fhsta
64 normal rman fuzzy
4 normal fuzzy
8192 system good
8196 system fuzzy
0 normal good
8256 system rman fuzzy
*/
select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x\$kcvfh;
prompt ####################### Datafile I/O Performance Information ################################
SELECT ts.name AS ts,fs.phyrds "Reads",fs.phywrts "Writes",fs.phyblkrd AS br,fs.phyblkwrt AS bw,fs.readtim/100 "RTime*s",fs.writetim/100 "WTime*s"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts,ts.phyrds "Reads",ts.phywrts "Writes",ts.phyblkrd AS br,ts.phyblkwrt AS bw,ts.readtim /100 "RTime*s",ts.writetim/100 "WTime*s"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file#
ORDER BY 1;
prompt ####################### Redo Information ################################
SELECT thread#,a.group#,a.sequence#,a.bytes/1024/1024 "SIZE(M)",first_change# "First SCN",a.FIRST_TIME,a.ARCHIVED "Archive",a.status,MEMBER
FROM gv$log a, gv$logfile b
WHERE a.group# = B.GROUP#
ORDER BY thread#,a.sequence# DESC;
prompt ####################### Redo Log Switch Frequency Information ################################
select INST_ID,THREAD#,sequence#,to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,
round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,2) minutes
from gv$log_history
where first_time > sysdate - 1
order by first_time, minutes;
prompt ####################### LGWR I/O Performance Information ################################
select total_waits,time_waited,average_wait,time_waited / total_waits as avg_time from gv$system_event
where event = 'log file parallel write';
prompt ####################### Flashback Diagnostic Information ################################
WITH flashback_database_log AS
(SELECT ROUND((SYSDATE - oldest_flashback_time) * 24 * 60, 2) oldest_log_minutes,
retention_target retention_target_minutes,
flashback_size / 1048576 flashback_size_mb,
estimated_flashback_size / 1048576 estimated_flashback_size_mb
FROM v$flashback_database_log),
flashback_database_logfile AS
(SELECT COUNT(*) logs,
SUM(BYTES / 1048576) size_mb,
MIN(first_time) oldest_log,
MAX(first_time) latest_log
FROM v$flashback_database_logfile),
flashback_usage AS
(SELECT file_type,
ROUND(mb_used, 2) mb_used,
ROUND(mb_reclaimable, 2) mb_reclaimable,
DECODE(total_mb, 0, 0, ROUND(mb_used * 100 / total_mb, 2)) percent_space_used,
DECODE(total_mb, 0, 0, ROUND(mb_reclaimable * 100 / total_mb, 2)) percent_space_reclaimable,
number_of_files,
total_mb db_recovery_file_dest_mb,
flashback_retention_target,
oldest_record,
ROUND((sysdate - oldest_record) * 24 * 60, 2) oldest_record_age_sec
FROM (SELECT SUM(DECODE(NAME,
'db_recovery_file_dest_size',
VALUE / 1048576,
0)) total_mb,
SUM(DECODE(NAME, 'db_flashback_retention_target', VALUE, 0)) flashback_retention_target
FROM v$parameter
WHERE NAME IN ('db_recovery_file_dest_size',
'db_flashback_retention_target')),
(SELECT 'FLASHBACKLOG' file_type,
NVL(SUM(BYTES) / 1048576, 0) mb_used,
sum(CASE
WHEN last_time <= (sysdate - (tgt.value / 1440)) THEN
bytes / 1048576
ELSE
0
END) mb_reclaimable,
COUNT(*) number_of_files,
MIN(first_time) oldest_record
FROM (select bytes,
lead(first_time) over(order by first_time asc) last_time,
first_time
from v$flashback_database_logfile) fla_log,
(SELECT value value
FROM v$parameter
WHERE name = 'db_flashback_retention_target') tgt
UNION
SELECT 'BACKUPPIECE' file_type,
NVL(SUM(BYTES / 1048576), 0) mb,
SUM(CASE
WHEN dl.rectype = 13 THEN
(BYTES / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(start_time) oldest_record
FROM v$backup_piece bp, x\$kccagf dl
WHERE is_recovery_dest_file = 'YES'
AND deleted = 'NO'
AND bp.recid = dl.recid(+)
AND dl.rectype(+) = 13
UNION
SELECT 'ARCHIVELOG' file_type,
NVL(SUM(blocks * block_size) / 1048576, 0) mb,
SUM(CASE
WHEN dl.rectype = 11 THEN
(LOG.blocks * LOG.block_size / 1048576)
ELSE
0
END) reclaimable_mb,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$archived_log log, x\$kccagf dl
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
AND dl.recid(+) = log.recid
AND dl.rectype(+) = 11
UNION
SELECT 'ONLINELOG' file_type,
SUM(BYTES / 1048576) mb,
0 reclaimable,
COUNT(*) no_of_files,
MIN(first_time) oldest_record
FROM v$logfile lf,
(SELECT group#, BYTES, first_time
FROM v$standby_log
UNION
SELECT group#, BYTES, first_time FROM v$log) l
WHERE l.group# = lf.group#
AND lf.is_recovery_dest_file = 'YES'
UNION
SELECT 'IMAGECOPY',
NVL(SUM(blocks * (block_size / 1048576)), 0) mb,
0 reclaimable_mb,
COUNT(*) no_of_files,
MIN(creation_time) oldest_record
FROM v$datafile_copy
WHERE deleted = 'NO'
AND is_recovery_dest_file = 'YES'
UNION
SELECT 'CONTROLFILE',
NVL(SUM(block_size * file_size_blks) / 1048576, 0) mb,
0 reclaimable,
COUNT(*) no_of_files,
NULL oldest_record
FROM v$controlfile
WHERE is_recovery_dest_file = 'YES'))
SELECT order_, NAME, VALUE
FROM(
SELECT 0 order_, NAME, VALUE
FROM v$parameter
WHERE NAME LIKE 'db_recovery_file%'
UNION
SELECT 3, 'oldest flashback log (minutes)', TO_CHAR(ROUND(oldest_log_minutes, 2))
FROM flashback_database_log
UNION
SELECT 1, 'retention target (minutes)', TO_CHAR(ROUND(retention_target_minutes, 2))
FROM flashback_database_log
UNION
SELECT 2, 'estimated size for flashback logs (MB)', TO_CHAR(ROUND(estimated_flashback_size_mb, 2))
FROM flashback_database_log
UNION
SELECT 2, 'Current flashback log count', TO_CHAR(logs)
FROM flashback_database_logfile
UNION
SELECT 3, 'Most recent flashback log (minutes)', TO_CHAR(ROUND((SYSDATE - latest_log) * 24 * 60, 2))
FROM flashback_database_logfile
UNION
SELECT 4, 'Total size of all files in MB', TO_CHAR(ROUND(SUM(mb_used), 2))
FROM flashback_usage
UNION
SELECT 5, 'Total size of reclaimable files in MB', TO_CHAR(ROUND(SUM(mb_reclaimable), 2))
FROM flashback_usage
UNION
SELECT 6, 'unused space in MB', TO_CHAR(ROUND(MIN(db_recovery_file_dest_mb) - SUM(mb_used)))
FROM flashback_usage)
ORDER BY order_, NAME;
prompt ####################### Recyblebin Information ################################
select value from gv$parameter where name='recyclebin';
prompt ####################### Supplemental Log Information ################################
select SUPPLEMENTAL_LOG_DATA_MIN "MIN",
SUPPLEMENTAL_LOG_DATA_PK "PK",
SUPPLEMENTAL_LOG_DATA_UI "UI",
SUPPLEMENTAL_LOG_DATA_FK "FK",
SUPPLEMENTAL_LOG_DATA_ALL "ALL"
from v$database;
prompt ####################### Archive Log Information ################################
col name for a50
select
thread#,sequence# sequence#,
FIRST_CHANGE# FIRST_CHANGE#,
FIRST_TIME FIRST_TIME,
NEXT_CHANGE# NEXT_CHANGE#,
NEXT_TIME NEXT_TIME,
name from (
select rownum rn,a.* from
(
select
sequence#,thread#,
FIRST_CHANGE#,
FIRST_TIME,
NEXT_CHANGE#,
NEXT_TIME,
name from v$archived_log
where DELETED='NO'
order by NEXT_TIME desc
) a
) where rn<10;
prompt ####################### Recover File Information ################################
select file#,online_status "STATUS",change# "SCN",time"TIME" from v$recover_file;
prompt ####################### Hot Backup Information ################################
select * from v$backup;
prompt ####################### Recover Log Information ################################
select THREAD#,SEQUENCE# SEQUENCE#,
TIME "TIME"
from v$recovery_log;
prompt ####################### Rman Backup Information ################################
SELECT A.RECID "BACKUP SET",
A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1, 'Incr-1',
0, 'Incr-0',
B.INCREMENTAL_LEVEL)
"Type LV",
B.CONTROLFILE_INCLUDED "including CTL",
DECODE (A.STATUS,
'A', 'AVAILABLE',
'D', 'DELETED',
'X', 'EXPIRED',
'ERROR')
"STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.TAG "Tag",
A.HANDLE "Path"
FROM Gv$BACKUP_PIECE A, Gv$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;
prompt ####################### Lock Information ################################
SELECT /*+ RULE */ LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME,
DECODE(LS.TYPE, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
'Transaction enqueue lock', 'UL', 'User supplied lock') LOCK_TYPE, O.OBJECT_NAME
OBJECT, DECODE(LS.LMODE, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4,
'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', NULL) LOCK_MODE, O.OWNER,
LS.SID, LS.SERIAL# SERIAL_NUM, LS.ID1, LS.ID2 FROM SYS.DBA_OBJECTS O, (SELECT
S.OSUSER, S.USERNAME, L.TYPE, L.LMODE, S.SID, S.SERIAL#, L.ID1, L.ID2 FROM
v$SESSION S, v$LOCK L WHERE S.SID = L.SID) LS WHERE O.OBJECT_ID = LS.ID1 AND
O.OWNER <> 'SYS' ORDER BY O.OWNER, O.OBJECT_NAME;
select /*+no_merge(a) no_merge(b) */
(select username from v$session where sid=a.sid) blocker,
a.sid, 'is blocking',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2
order by a.sid;
prompt ####################### Connect User Session Information ################################
select count(*) from v$session;
select sid,serial#,username,program,machine,status from v$session;
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Cursor Information ################################
prompt <hr align="left" width="300">
select sid, count(*) cnt
from v$open_cursor
group by sid
having count(*) >= 1000
order by cnt desc;
prompt ####################### Trigger Information ################################
select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS
from dba_triggers
where owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES');
prompt ####################### Which Table Not Build Index(Less Than 2G) Information ################################
select segment_name, bytes/1024/1024/1024 "GB", blocks, tablespace_name
from dba_segments
where segment_type = 'TABLE'
and owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
and segment_name not in (select table_name from dba_indexes where owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES'))
and bytes / 1024 / 1024 / 1024 >= 2
order by GB desc;
prompt ####################### Which Partition Table Not Build Index(Less Than 2G) Information ################################
select segment_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks)
from dba_segments
where segment_type = 'TABLE PARTITION'
and owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
and segment_name not in (select table_name from dba_indexes where owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES'))
group by segment_name
having sum(bytes)/1024/1024/1024>=2
order by GB desc;
prompt ####################### Statistical Is Collected Information ################################
--10g
select t.job_name,t.program_name,t.state,t.enabled
from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB';
--11g
select client_name,status from dba_autotask_client;
select window_next_time,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;
prompt ####################### Statistical Isn't Collected For A Long Time Information ################################
select owner, count(*)
from dba_tab_statistics t
where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
and table_name not like 'BIN$%'
and owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
group by owner
order by owner;
prompt ####################### Which Table Not Build Partition (Less Than 2G) Information ################################
select owner,
segment_name,
segment_type,
sum(bytes) / 1024 / 1024 / 1024 object_size
from dba_segments
where owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
and segment_type = 'TABLE'
group by owner, segment_name, segment_type
having sum(bytes) / 1024 / 1024 / 1024 >= 10
order by object_size desc;
prompt ####################### Foreign Key Not Build Index Information ################################
select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from dba_cons_columns where owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')) a,
dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
and b.owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL
(select count(*)
from dba_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5,
cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
and i.index_owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
group by i.index_name);
prompt ####################### Expansion Of The Exception Object Information ################################
select Segment_Name, Segment_Type, TableSpace_Name,
(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)*100>=95
order By Percent;
prompt ####################### System Tablespace Both System And Sys User Object Information ################################
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES');
prompt ####################### Next Expansion Object Space In The Max Value Information ################################
select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
prompt ####################### Job Information ################################
select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from sys.dba_jobs;
prompt ####################### Unusable Job Information ################################
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
prompt ####################### User Privilege and Default Tablespace Information ################################
select
username,
default_tablespace dts,
temporary_tablespace tts,
profile prof,
granted_role || '-' ||
decode(admin_option,'YES','A',' ') ||
decode(granted_role,'YES','G',' ') role
from
dba_users,
dba_role_privs
where
dba_users.username = dba_role_privs.grantee and
username not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
order by
1,2,3,4 ;
prompt ####################### Unusable Index Information ################################
Select owner, index_name, status
From dba_indexes
where status = 'UNUSABLE'
and owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
order by 1, 2 ;
prompt ####################### Unusable Index Partition Information ################################
select index_owner, index_name, partition_name
from dba_ind_partitions
where status ='UNUSABLE'
and index_owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES') order by 1,2;
prompt ####################### Unusable Index Subpartition Information ################################
Select Index_Owner, Index_Name, partition_name, SUBPARTITION_NAME
From DBA_IND_SUBPARTITIONS
Where status = 'UNUSABLE' and index_owner not in ('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES') order by 1, 2;
prompt ####################### Tables With No Primary Key Information ################################
SELECT owner, table_name
FROM dba_tables
WHERE 1 = 1
AND owner NOT IN
('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
MINUS
SELECT owner, table_name
FROM dba_constraints
WHERE constraint_type = 'P'
AND owner NOT IN
('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES');
prompt ####################### Tables With No Unique Key Or Index Information ################################
SELECT owner, table_name
FROM dba_all_tables
WHERE 1 = 1
AND owner NOT IN
('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
MINUS
SELECT owner, table_name
FROM dba_constraints
WHERE constraint_type = 'U'
AND owner NOT IN
('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES')
MINUS
SELECT owner, table_name
FROM dba_indexes
WHERE uniqueness = 'UNIQUE'
AND owner NOT IN
('SYS','SYSTEM',
'SYSMAN',
'EXFSYS',
'WMSYS',
'OLAPSYS',
'OUTLN',
'DBSNMP',
'ORDSYS',
'ORDDATA',
'APPQOSSYS',
'APEX_030200',
'OWBSYS',
'ORDPLUGINS',
'MDSYS',
'CTXSYS',
'AURORA$ORB$UNAUTHENTICATED',
'XDB',
'MGMT_VIEW',
'ANONYMOUS',
'DMSYS',
'SI_INFORMTN_SCHEMA',
'MDDATA',
'DIP',
'ORACLE_OCM',
'OWBSYS_AUDIT',
'ANONYMOUS',
'XS$NULL',
'APEX_PUBLIC_USER',
'SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'TSMSYS',
'FLOWS_030000',
'FLOWS_FILES');
prompt ####################### Invalid Object Information ################################
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
prompt ####################### load profile ################################
set pagesize 99
set linesize 160
col snap_date for a6
col "TIME" for a6
col "elapse(min)" for 999999
col "DB time(min)" for 999999
col redo for 999999
col "redo/s" for 999999
col logical for 999999
col "logical/s" for 999999
col physical for 999999
col "phy/s" for 999999
col execs for 999999
col "execs/s" for 999999
col parse for 999999
col "parse/s" for 999999
col hardparse for 999999
col "hardparse/s" for 999999
col trans for 999999
col "trans/s" for 999999
select s.snap_date,
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
to_char(round(s.seconds/60,2)) "elapse(min)",
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
s.redosize redo,
round(s.redosize / s.seconds, 2) "redo/s",
s.logicalreads logical,
round(s.logicalreads / s.seconds, 2) "logical/s",
physicalreads physical,
round(s.physicalreads / s.seconds, 2) "phy/s",
s.executes execs,
round(s.executes / s.seconds, 2) "execs/s",
s.parse,
round(s.parse / s.seconds, 2) "parse/s",
s.hardparse,
round(s.hardparse / s.seconds, 2) "hardparse/s",
s.transactions trans,
round(s.transactions / s.seconds, 2) "trans/s"
from (select curr_redo - last_redo redosize,
curr_logicalreads - last_logicalreads logicalreads,
curr_physicalreads - last_physicalreads physicalreads,
curr_executes - last_executes executes,
curr_parse - last_parse parse,
curr_hardparse - last_hardparse hardparse,
curr_transactions - last_transactions transactions,
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
to_char(currtime, 'yy/mm/dd') snap_date,
to_char(currtime, 'hh24:mi') currtime,
currsnap_id endsnap_id,
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
from (select a.redo last_redo,
a.logicalreads last_logicalreads,
a.physicalreads last_physicalreads,
a.executes last_executes,
a.parse last_parse,
a.hardparse last_hardparse,
a.transactions last_transactions,
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
b.end_interval_time lasttime,
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
b.startup_time
from (select snap_id,
dbid,
instance_number,
sum(decode(stat_name, 'redo size', value, 0)) redo,
sum(decode(stat_name,
'session logical reads',
value,
0)) logicalreads,
sum(decode(stat_name,
'physical reads',
value,
0)) physicalreads,
sum(decode(stat_name, 'execute count', value, 0)) executes,
sum(decode(stat_name,
'parse count (total)',
value,
0)) parse,
sum(decode(stat_name,
'parse count (hard)',
value,
0)) hardparse,
sum(decode(stat_name,
'user rollbacks',
value,
'user commits',
value,
0)) transactions
from dba_hist_sysstat
where stat_name in
('redo size',
'session logical reads',
'physical reads',
'execute count',
'user rollbacks',
'user commits',
'parse count (hard)',
'parse count (total)')
group by snap_id, dbid, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.dbid = (select dbid from v$database where rownum = 1 )
and a.instance_number = (select instance_number from v$instance where rownum = 1 )
order by end_interval_time)) s,
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time'
and a.dbid = (select dbid from v$database where rownum = 1 )
and a.instance_number = (select instance_number from v$instance where rownum = 1 )) t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc ,time asc;
prompt ####################### TOP 5 Event ################################
set linesize 120
col event for a30
col wclas for a16
SELECT event "Event", totwa "Waits", twsec "Time(s)", avgms "Avg(ms)",
ROUND(RATIO_TO_REPORT(twsec) OVER () * 100,1) pctwa , wclas "Wait Class"
FROM
(SELECT
NVL(substr(event,1,30),' ') event,
AVG(total_waits) totwa,
ROUND(AVG(time_waited_micro)/1000000, 0) twsec,
ROUND(SUM(time_waited_micro)/
SUM(total_waits)/1000, 2) avgms,
NVL(substr(wait_class,1,13),' ') wclas
FROM Gv$SYSTEM_EVENT
WHERE WAIT_CLASS <> 'Idle'
GROUP BY event, wait_class
HAVING SUM(total_waits) > 0
UNION ALL
SELECT
'CPU time' event,
0 totwa,
AVG(icput) twsec,
0 avgms,
' ' wclas
FROM
(
select inst_id, sum(VALUE)/100 icput
FROM Gv$SYSSTAT
WHERE name in ('CPU used when call started',
'CPU used by this session')
group by inst_id) ORDER BY twsec desc) WHERE ROWNUM <6;
spool off
exit;