good
database_check.sql
Database report
=====
Set Heading Off
Set Termout Off
Column Var_Date new_value Var_Date noprint
Select To_Char(Sysdate, 'YYYY-MM-DD') Var_Date from v$database ;
Column Var_Database new_value Var_Database noprint
Select Name Var_Database from v$database ;
Set Termout On
alter system checkpoint;
alter system check datafiles;
spool Chk_db_&Var_Database._&Var_Date..log
@heading.sql
-- ----------------------------------------------------------------------- ---
-- Set Sql prompt (log.sql) ---
-- ----------------------------------------------------------------------- ---
Set Linesize 120
Set Pagesize 60
Set SQLPROMPT 'Sql+>'
Set Desc Linenum On
Set Arraysize 1
Set Long 2000
Set Serveroutput On size 900000 ;
-- Alter session Set nls_date_format="DD-MM-YYYY HH24:MI:SS";
Set Heading Off
Set Feedback Off
Set Verify Off
Column Var_Date new_value Var_Date noprint
-- Select To_Char(Sysdate, 'DD-MM-YYYY HH24:MI') Var_Date from v$database ;
Select To_Char(Sysdate, 'dd-Mon-yyyy:hh:mi') Var_Date from v$database ;
-- Prompt ---
Select
' ** &Var_Date ** DB : ' || Name || ' ** Inst. : '||Instance_Name||' ** '
from v$database , v$instance;
Set Heading On
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column username heading "Utilis." format A10
column terminal heading "Terminal" format A16
column program heading "Programme" format A32 word_wrapped
select s.sid, p.spid, substr(s.username,1,20) username, s.terminal, p.Program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = (select sid from v$mystat where rownum=1);
Set Heading Off
Set Termout Off
Column Var_Prompt new_value Var_Prompt noprint
Select
''''||Rpad(Lpad(Initcap(Substr(Name,1,4)),3,'*'),4,'*')||'>''' Var_Prompt
from v$database
;
Set SQLPROMPT &Var_Prompt
Set Termout On
-- Prompt
-- Prompt ---
Set Heading On
Set Feedback On
prompt
prompt -- ----------------------------------------------------------------------- ---
prompt -- Instance Information ---
prompt -- ----------------------------------------------------------------------- ---
Set Heading Off
Set Feedback Off
Set Verify Off
column status format a120 wrap heading "Status"
Select status_01 ||' | '||status_02 status
From
(Select ' Database_Name '||Lpad(Name,12) Status_01 from V$Database)
, (Select ' Database_Open_Mode '||Lpad(open_mode,11) Status_02 from V$database)
Union
Select status_01 ||' | '||status_02 status
From
(Select ' Instance_Name '||Lpad(Instance_Name,14) Status_02 from V$Instance)
, (Select ' Instance_Status '||Lpad(Database_Status,10) Status_01 from V$Instance)
Union
Select status_01||' | '||status_02 status
From
(Select ' Host_Name '||Lpad(Host_Name,18) status_02 from V$Instance)
, (Select ' Cpu_Count '||Lpad(value,8) status_01 from V$PARAMETER where name='cpu_count' and value is not null)
Union
Select status_01||' | '||status_02 status
From
(Select ' Startup_Time '||To_Char(Startup_Time, 'DD-MM-YYYY HH24:MI') Status_02 from V$Instance)
, (Select ' Instance_Logins '||Lpad(logins,14) Status_01 from V$Instance)
;
Select status_01||' | '||status_02 status
From
(Select ' DBID '||Lpad(DBID,18) Status_01 from V$Database)
, (Select ' Flashback '||Lpad(FLASHBACK_ON,20) Status_02 from V$database)
Union
Select status_01||' | '||status_02 status
From
(Select ' Version '||Lpad(Version,12) Status_01 from V$Instance)
, (Select ' Instance_Role '||Lpad(Instance_Role,16) Status_02 from V$Instance)
Union
Select status_01||' | '||status_02 status
From (Select ' Checkpoint_Chg# '||Lpad(checkpoint_change#,14) Status_01 from v$database)
, (Select ' Current_Scn '||Lpad(current_scn,20) Status_02 FROM v$database)
;
column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(select ' Dynamic SGA '||Lpad(decode( (select 'OK' From (Select upper(value) val from V$PARAMETER where name='db_cache_advice' and value = 'ON') t1 , (Select Upper(value) val from V$PARAMETER where name='statistics_level' and upper(value) in ('TYPICAL', 'ALL')) t2 , (select value val from V$PARAMETER where name = 'sga_target' ) t3 Where t1.val = 'ON' and t2.val in ('TYPICAL', 'ALL') and t3.val != '0' ) , 'OK', 'Installed', 'Not Installed'),17) status_01 from dual)
, (select ' '||Name||' (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),26-length(Name)) status_02 from V$PARAMETER where name = 'sga_target')
Union
Select status_01||' | '||status_02 status
From
(Select ' Db_Cache_Advice '||Lpad(value,12) status_01 from V$PARAMETER where name='db_cache_advice')
, (Select ' Statistics_Level '||Lpad(value,14) status_02 from V$PARAMETER where name='statistics_level')
Union
Select status_01||' | '||status_02 status
From
(select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) status_01 from V$PARAMETER where upper(name) = 'SGA_MAX_SIZE')
, (select ' Free Memory (Mb)' || Lpad(To_Char(current_size/1024/1024),16) status_02 from V$SGA_DYNAMIC_FREE_MEMORY )
;
--Union
--Select status_01||' | '||status_02 status
-- From
-- (Select ' Cache Read F. 0.85 (Mb) '|| Lpad(to_char(size_for_estimate),6) status_01 FROM v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size') AND advice_status = 'ON' and estd_physical_read_factor >= 0.84 and estd_physical_read_factor < 0.86 and rownum=1)
-- , (Select ' Cache Read F. 0.75 (Mb) '|| Lpad(to_char(size_for_estimate)),8) status_02 FROM v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size') AND advice_status = 'ON' and estd_physical_read_factor >= 0.74 and estd_physical_read_factor < 0.76 and rownum=1)
--;
Select status_01 ||' | '||status_02 status
From
(select ' Automatic PGA '||Lpad(decode( (select 'OK' From (Select upper(value) val from V$PARAMETER where upper(name)='WORKAREA_SIZE_POLICY' and Upper(value) = 'AUTO') t1, (select value val from V$PARAMETER where Upper(name) = 'PGA_AGGREGATE_TARGET' and value != '0') t2 Where t1.val = 'AUTO' and t2.val != '0') , 'OK', 'Installed', 'Not Installed'),15) status_01 from dual)
, (SELECT ' Pga consumption (Mb) '||Lpad(round((VALUE/1024/1024)),11) status_02 FROM V$PGASTAT WHERE NAME = 'total PGA allocated')
Union
Select status_01||' | '||status_02 status
From
(select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) status_01 from V$PARAMETER where Upper(name)='PGA_AGGREGATE_TARGET')
, (Select ' Workarea_size_Policy '||Lpad(value,11) status_02 from V$PARAMETER where Upper(name)='WORKAREA_SIZE_POLICY')
;
Select status_01||' | '||status_02 status
From
(Select ' Cursor_Sharing '||Lpad(value,12) status_01 from V$PARAMETER where name='cursor_sharing')
, (Select ' Remote_login_p. '||Lpad(value,14) status_02 from V$PARAMETER where name='remote_login_passwordfile')
Union
Select status_01||' | '||status_02 status
From
(Select ' Default Temp '||Lpad(property_value,14) status_01 from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE')
, (Select ' Undo tablespace '||Lpad(value,16) status_02 from V$PARAMETER where name='undo_tablespace')
Union
Select status_01||' | '||status_02 status
From
(Select ' Tempfile '||Lpad(decode(To_Char(count(*)),'0', 'KO !!!', 'OK'),15) status_01 from dba_temp_files where tablespace_name in (Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE' and property_value != 'SYSTEM') and (Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE' and property_value != 'SYSTEM') is not null)
, (Select ' Undo Suppr. Er. '||Lpad(value,16) status_02 from V$PARAMETER where name='undo_suppress_errors')
;
-- (Select ' Nb Tempfile '||Lpad(To_Char(count(*)),14) status_01 from dba_temp_files where tablespace_name in (Select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'))
select ' '||Name||' '||lpad (value,52-length(Name)) from V$PARAMETER where name like '%stand%' and value != '0' and (select ' '||Name||' '||Rpad (value,68-length(Name)) dest from V$PARAMETER where name like '%log_archive_dest%' and upper(value) like '%SERVICE%' and value != '0') is not null ;
select ' '||Name||' '||Rpad (value,68-length(Name)) dest from V$PARAMETER where name like '%log_archive_dest%' and name not like '%log_archive_dest_state_%' and value != '0';
select ' '||Name||' '||Rpad (value,68-length(Name)) from V$PARAMETER where name in (select substr(name,1,17)||'state_'||substr(name,-1,1) from V$PARAMETER where name like '%log_archive_dest%' and name not like '%log_archive_dest_state_%' and value != '0');
select ' Obsolete Parameter : '||Name||' '||lpad (Name,31-length(Name)) from v$obsolete_parameter where isspecified='TRUE';
select ' Database log mode '||log_mode "Parameter" from V$DATABASE
--union
--select ' Archive destination '||value from V$PARAMETER where (name='log_archive_dest' or name = 'log_archive_dest_1') and value is not null
select ' Spfile '||value from V$PARAMETER where name='spfile' and value is not null
union
select ' Background Dump Dest '||value from V$PARAMETER where name='background_dump_dest' and value is not null
union
select ' Db_recovery_file_dest '||value from V$PARAMETER where name='db_recovery_file_dest' and value is not null
union
select ' Db_recovery_file_dest_size '||value from V$PARAMETER where name='db_recovery_file_dest_size' and value is not null and value !='0'
;
select ' Filesystemio_options '||Lpad(value,12) from V$PARAMETER where name='filesystemio_options' and value is not null
;
Declare
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
W_Texte := 'Select force_logging from v$database';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
Dbms_OutPut.Put_Line('-- Force_Logging '||W_Temp);
--
Dbms_Sql.Close_Cursor(Curs);
--
Exception
When Others then Null;
End;
/
Select status_01||' | '||status_02 status
From
(Select ' Index No Log. Or N. '||Lpad(count(*),10) status_01 from dba_indexes where (logging='NO' or logging is null ) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
, (Select ' Table No Log. Or N. '||Lpad(count(*),12) status_02 from dba_tables where iot_type is null and (logging='NO' or logging is null ) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB') and temporary ='N')
Union
Select status_01||' | '||status_02 status
From
(Select ' Index Part. No Log. '||Lpad(count(*),8) status_01 from dba_ind_partitions where logging='NO' and index_owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
, (Select ' Index SubPart. No Log. '||Lpad(count(*),7) status_02 from dba_ind_subpartitions where logging='NO' and index_owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
Union
Select status_01||' | '||status_02 status
From
(Select ' Table Row Movement '||Lpad(count(*),11) status_01 from dba_tables where ROW_MOVEMENT='ENABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
, (Select ' Table No Row Movement '||Lpad(count(*),10) status_02 from dba_tables where ROW_MOVEMENT='DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'OUTLN', 'ORDSYS', 'XDB', 'DMSYS', 'ORACLE_OCM', 'XDB'))
;
Set Heading Off
Set Termout Off
Column Var_DB_LAST_ANALYZED_IND new_value Var_DB_LAST_ANALYZED_IND noprint
Select
to_char(max(last_analyzed), 'DD-MM-YYYY HH24:MI') Var_DB_LAST_ANALYZED_IND
from
dba_indexes
where
owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS')
;
Column Var_DB_LAST_ANALYZED_TAB new_value Var_DB_LAST_ANALYZED_TAB noprint
Select
to_char(max(last_analyzed), 'DD-MM-YYYY HH24:MI') Var_DB_LAST_ANALYZED_TAB
from
dba_tables
where
owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS')
;
Set Termout On
Select status_01||' | '||status_02 status
From
(Select ' Monitoring On '||Lpad(count(*),16) status_01 from Dba_Tables where monitoring = 'YES' and temporary = 'N' and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
, (Select ' Monitoring Off '||Lpad(count(*),17) status_02 from Dba_Tables where monitoring = 'NO' and temporary = 'N' and Table_Name not in (Select table_name From Dba_External_Tables) and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
Union
Select status_01 ||' | '||status_02 status
From
(Select ' Last Tab. An. '||Lpad('&Var_DB_LAST_ANALYZED_TAB.',16) status_01 from dual)
, (select ' Last Ind. An. '||lpad('&Var_DB_LAST_ANALYZED_IND.',18) Status_02 from dual)
;
Select status_01 ||' | '||status_02 status
From
(select ' Cascade '||lpad(replace(dbms_stats.get_param('cascade'),'DBMS_STATS.', ''),22) Status_01 from dual)
, (select ' Degree '||lpad(dbms_stats.get_param('degree'),23) Status_02 from dual)
Union
Select status_01 ||' | '||status_02 status
From
(select ' Estimate % '||lpad(replace(dbms_stats.get_param('estimate_percent'),'DBMS_STATS.', ''),19) Status_01 from dual)
, (select ' Opt. '||lpad(replace(dbms_stats.get_param('method_opt'),'DBMS_STATS.', ''),27) Status_02 from dual)
Union
Select status_01 ||' | '||status_02 status
From
(select ' No Invalid.'||lpad(replace(dbms_stats.get_param('no_invalidate'),'DBMS_STATS.', ''),19) Status_01 from dual)
, (select ' Granularity '||lpad(dbms_stats.get_param('granularity'),20) Status_02 from dual)
Union
Select status_01 ||' | '||status_02 status
From
(Select ' Last Start Dt.'||Lpad(to_char(last_start_date, 'DD-MM-YYYY HH24:MI'), 16) status_01 from Dba_Scheduler_Jobs Where JOB_NAME = 'GATHER_STATS_JOB')
, (select ' AutoStats '||lpad(dbms_stats.get_param('autostats_target'),22) Status_02 from dual)
;
Prompt
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_SGA Is
select ' SGA (Mb) '||Lpad(To_Char(Round(sum (value)/1024/1024)),8) status_02 from v$sga;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(40);
--
X Varchar2(100);
Nb_Tf Number(8);
SGA Varchar2(40);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_SGA;
Fetch Cur_SGA Into SGA;
Close Cur_SGA;
--
If X Is Not Null Then
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_temp_files) dtft_size';
--
Else
--
W_Texte := 'Select ''Database Space (Mb) ''||Lpad(To_Char(Round((nb_ctl.nb * ctl_size.the_size) ';
W_Texte := W_texte ||' + (rlf_size.the_size/1024) ';
W_Texte := W_texte ||' + (dtf_size.the_size/1024) ';
W_Texte := W_texte ||' + (nvl(dtft_size.the_size,0)/1024))),8) From ';
W_Texte := W_texte ||' (select count(1) nb from v$controlfile) nb_ctl ';
W_Texte := W_texte ||' , (select round(sum(record_size)/1024) the_size from V$CONTROLFILE_RECORD_SECTION) ctl_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from v$log) rlf_size ';
W_Texte := W_texte ||' , (select round(sum(bytes)/1024) the_size from dba_data_files) dtf_size ';
--
End If;
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 40);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
-- Dbms_OutPut.Put_Line('-- Nb. Tempfiles '||W_Temp);
Dbms_OutPut.Put_Line('-- '||W_Temp||' | '||SGA||' --');
--
Dbms_Sql.Close_Cursor(Curs);
--
End;
/
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='DBA_TEMP_FILES'
;
--
Cursor Cur_Df Is
Select Count(*)
From dba_data_files
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Df;
Fetch Cur_Df Into Nb_Df;
Close Cur_Df;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From dba_temp_files';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
-- Dbms_OutPut.Put_Line('-- Nb. Tempfiles '||W_Temp);
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5)||' | Nb. Tempfiles '||Lpad(W_Temp,5)||' --' );
-- Session number 1 | Active session number 1
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Nb. Datafiles '||Lpad(To_Char(Nb_Df),5));
End If;
--
End;
/
column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(Select ' shared_pool_size (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),22-length(Name)) status_01 from V$PARAMETER where name='shared_pool_size')
, (Select ' Shared_pool_reserved_s. (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),28-length(Name)) status_02 from V$PARAMETER where name='shared_pool_reserved_size')
Union
Select status_01
From
(SELECT ' shared_pool Cons. (Mb) '|| Lpad(To_Char(round(SUM(BYTES)/1024/1024)),7) status_01 FROM V$SGASTAT WHERE POOL = 'shared pool')
;
Declare
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(200);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
W_Texte := 'select ''-- ''|| component ||'' (Mb)/ Current ''||Lpad(To_char(Round(current_size/1024/1024) ),15-length(component))||'' | User Specified ''||Lpad(To_char(Round( user_specified_size/1024/1024) ),28-length(component)) from v$sga_dynamic_components where component=''shared pool''';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 200);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
Dbms_OutPut.Put_Line(W_Temp);
--
Dbms_Sql.Close_Cursor(Curs);
--
Exception
When Others then Null;
End;
/
select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) from V$PARAMETER where name like '%pga_max_size%' and value != '0'
union
select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) from V$PARAMETER where name like '%a_target%' and value != '0' and Upper(name)!='PGA_AGGREGATE_TARGET'
union
select ' '||Name||' (Mb)'||Lpad(To_char(Round(to_number(value)/1024/1024)),25-length(Name)) from V$PARAMETER where name like '%e_target%' and value != '0'
union
select ' '||Name||' (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),24-length(Name)) from V$PARAMETER where name like '%cache_size%' and value != '0' and name != 'db_cache_size'
;
--union
--select ' '||Name||' (Mb) '||Lpad(To_char(Round(to_number(value)/1024/1024)),33-length(Name)) from V$PARAMETER where name like '%shared%size' and value != '0'
--select ' '||Name||' (Kb) '||Lpad(To_char(Round(to_number(value)/1024)),33-length(Name)) from V$PARAMETER where name like '%area_%size' and value != '0'
--;
Declare
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Df Number(8);
--
Begin
--
W_Texte := 'select Lpad(to_char(round(bytes/1024/1024)),30-length(Name)) from v$sgainfo where name =''Free SGA Memory Available''';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
Dbms_OutPut.Put_Line('-- Free SGA Memory Av. (Mb) '||W_Temp);
--
Dbms_Sql.Close_Cursor(Curs);
--
Exception
When Others then Null;
End;
/
column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(Select ' Session number (client) '||Lpad(To_Char(count(*)),5) status_01 from v$session where SERIAL#!=1 and osuser is not null)
, (Select ' Undo Block Used '||Lpad(To_Char(Nvl(Sum(A.USED_UBLK),0)),7) status_02 from v$transaction A, v$session B, v$process C where C.PID = B.SID and A.ADDR = B.TADDR)
Union
Select status_01||' | '||status_02 status
From
(Select ' Active session number '||Lpad(To_Char(count(*)),3) status_01 from v$session where SERIAL#!=1 and osuser is not null and status='ACTIVE')
, (Select ' Killed session number '||Lpad(To_Char(count(*)),5) status_02 from v$session where SERIAL#!=1 and osuser is not null and status='KILLED')
Union
Select status_01||' | '||status_02 status
From
(Select ' Param. Processes '||Lpad(value,11) status_01 from V$PARAMETER where name='processes')
, (Select ' Param. Sessions '||Lpad(value,13) status_02 from V$PARAMETER where name='sessions')
;
Select status_01||' | '||status_02 status
From
(select ' Data Buffer Hit ratio '||Lpad(Substr(To_Char((1-a.value/(b.value+c.value))*100),1,4),5) status_01 From v$sysstat a, v$sysstat b , v$sysstat c where a.name = 'physical reads' and b.name = 'db block gets' and c.name = 'consistent gets')
, (select ' Library Buffer Hit ratio '||Lpad(Substr(To_Char(sum(pins) / (sum(pins) + sum(reloads)) *100),1,4),5) status_02 from v$librarycache)
Union
Select status_01||' | '||status_02 status
From
(Select ' Dict. Buffer Hit ratio '||Lpad(Substr(To_Char((1-(sum(getmisses)/sum(gets)))*100),1,4),7) status_01 from v$rowcache)
, (Select ' Switch number (last hour) '||Lpad(To_char(count(*)),4) status_02 from sys.v_$log_history where first_time > sysdate - 1/24)
;
Select status_01||' | '||status_02 status
From
(Select ' Sort_Area_Size (Kb) '||Lpad(value/1024,10) status_01 from V$PARAMETER where name='sort_area_size')
, (Select ' Db_File_Multi_Block_RC. '||Lpad(value,8) status_02 from V$PARAMETER where name='db_file_multiblock_read_count')
Union
Select status_01||' | '||status_02 status
From
(Select ' Optimizer Mode '||Lpad(value,15) status_01 from V$PARAMETER where name='optimizer_mode')
, (Select ' Optimizer_Index_Cost_Adj '||Lpad(value,7) status_02 from V$PARAMETER where name='optimizer_index_cost_adj')
Union
Select status_01||' | '||status_02 status
From
(Select ' Hash_Join_Enabled '||Lpad(value,12) status_01 from V$PARAMETER where Upper(name)='HASH_JOIN_ENABLED')
, (Select ' Optimizer_Index_Caching '||Lpad(value,8) status_02 from V$PARAMETER where Upper(name)='OPTIMIZER_INDEX_CACHING')
Union
Select status_01||' | '||status_02 status
From
(Select ' Db_Files '||Lpad(value,21) status_01 from V$PARAMETER where Upper(name)='DB_FILES')
, (Select ' Db_Writer_Processes '||Lpad(value,12) status_02 from V$PARAMETER where Upper(name)='DB_WRITER_PROCESSES')
;
-- ----------------------------------------------------------------------- ---
-- Redo Size ---
-- ----------------------------------------------------------------------- ---
Set Termout Off
create or replace function show_redo_size return varchar2
Is
--
Cursor CX Is
Select distinct To_Char(round(bytes/1024/1024))
from v$log ;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := W_Texte||' ';
W_Texte := Replace(W_Texte,' ', '/');
W_Texte := Substr(W_Texte,2, Instr(W_Texte, '/', -1, 1)-2);
If Instr(W_Texte, '/', 1,2) != 0 then
W_Texte := Substr(W_Texte, 1, Instr(W_Texte, '/', 1,2))||'...';
End If;
--
Return W_Texte;
--
End;
/
-- show error
Set Termout On
Select status_01||' | '||status_02 status
From
(select distinct ' Redo size (Mb)'|| Lpad(show_redo_size ,'18') Status_02 from v$log)
, (Select ' Nb Redo Gpr.'|| Lpad(To_Char(count(distinct lf.group#)),'18') status_01 from v$logfile lf)
;
Set Termout Off
Drop function show_redo_size;
Set Termout On
-- ----------------------------------------------------------------------- ---
-- File Status ---
-- ----------------------------------------------------------------------- ---
--column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' V$Datafile Status '||Lpad('OK',12)
, 'SYSTEM', ' V$Datafile Status '||Lpad('OK',12)
, ' V$Datafile Status '||Lpad('KO !!!',12)
) STATUS_01 from v$datafile)
, (select distinct
decode (status
, 'ONLINE', ' V$Tempfile Status '||Lpad('OK',14)
, 'SYSTEM', ' V$Tempfile Status '||Lpad('OK',14)
, ' V$Tempfile Status '||Lpad('KO !!!',14)
) STATUS_02 from v$tempfile)
Union
Select status_01||' | '||status_02 status
From
(select distinct
decode (status
, 'ONLINE', ' Dba_Tablespaces Status '||Lpad('OK',7)
, 'READ ONLY', ' Dba_Tablespaces Status '||Lpad('OK',7)
, ' Dba_Tablespaces Status '||Lpad('KO !!!',7)
) STATUS_01 from dba_tablespaces)
, (select distinct
decode (status
, 'CURRENT', ' V$Log Status '||Lpad('OK',19)
, 'ACTIVE', ' V$Log Status '||Lpad('OK',19)
, 'INACTIVE', ' V$Log Status '||Lpad('OK',19)
, ' V$Log Status '||Lpad('KO !!!',19)
) STATUS_02 from v$log)
union
Select status_01||' | '||status_02 status
From
(select
distinct
decode (count(1)
, 0, ' V$Recover_File '||Lpad('OK',15)
, ' V$Recover_File '||Lpad('KO !!!',15)
) STATUS_01 from v$recover_file)
, (select
distinct
decode (count(1)
, 0, ' V$Recovery_Log '||Lpad('OK',17)
, ' V$Recovery_Log '||Lpad('KO !!!',17)
) STATUS_02 from v$recovery_log)
;
-- ----------------------------------------------------------------------- ---
-- Tablespaces Backup Mode / Lock Number / Tablespace > 90% ---
-- ----------------------------------------------------------------------- ---
Select status_01||' | '||status_02 status
From
(Select ' Tablespace in Backup Mode '||Lpad(To_Char(Count(*)),4) status_01 from v$backup where status != 'NOT ACTIVE')
, (select ' Tablespace > 90% '||Lpad(To_Char(count(*)),4) status_02 from ( select sum (bytes)/1048576 free, max (bytes)/1048576 fragmax, tablespace_name from sys.dba_free_space group by tablespace_name ) fsp, ( select sum(bytes)/1048576 alloc, tablespace_name from sys.dba_data_files group by tablespace_name) df, dba_tablespaces dt where fsp.tablespace_name (+) = df.tablespace_name and df.tablespace_name = dt.tablespace_name and dt.status = 'ONLINE' and (((alloc - nvl (free, 0)) / alloc) * 100 > 90))
Union
Select status_01||' | '||status_02 status
From
(select ' Lock number '||Lpad(To_Char(count(*)),8) status_01 from v$session s, v$process p, v$lock l, dba_objects o where s.paddr = p.addr And l.sid=s.sid And l.id1 = o.object_id And s.username is NOT NULL And l.ctime > 60)
, (select ' Indexes unusable '||Lpad(To_Char(count(*)),8) status_02 from dba_indexes where status = 'UNUSABLE')
Union
Select status_01||' | '||status_02 status
From
(select ' Indexes SubPart. unusable '||Lpad(To_Char(count(*)),6) status_02 from dba_ind_subpartitions where status = 'UNUSABLE')
, (select ' Indexes Part. unusable '||Lpad(To_Char(count(*)),7) status_01 from dba_ind_partitions where status = 'UNUSABLE')
Union
Select status_01||' | '||status_02 status
From
(Select ' Rman Corrupt. Blck. ' || Lpad(To_Char(count(*)),10) status_01 from v$backup_corruption)
, (Select ' Rman Crpt. Blck. Last Bck. ' || Lpad(To_Char(count(*)),5) status_02 from V$DATABASE_BLOCK_CORRUPTION)
;
-- ----------------------------------------------------------------------- ---
-- Jobs Broken / Objects Invalid / Shared_Pool Failure / Latch > 1% ---
-- ----------------------------------------------------------------------- ---
--Select status_01 ||' | '||status_02 status
-- From
-- (select ' Background Proc. Error '||Lpad(To_Char(Count(*)),6) status_01 from v$bgprocess where paddr <> '00' and error > 0)
-- , (select ' Archive Dest. Error '||Lpad(To_Char(Count(*)),8) status_02 From v$archive_dest where status not in ('VALID', 'INACTIVE'))
--;
Declare
--
Cursor Cur_Req Is
Select distinct object_name
from dba_objects
where object_name='V$ARCHIVE_DEST'
;
--
Cursor Cur_Bg Is
Select Count(*)
From v$bgprocess
where paddr <> '00'
and error > 0
;
--
W_Texte Varchar2(2000);
Curs Integer;
Return_code Integer;
W_Temp Varchar2(20);
--
X Varchar2(100);
Nb_Tf Number(8);
Nb_Bg Number(8);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
Open Cur_Bg;
Fetch Cur_Bg Into Nb_Bg;
Close Cur_Bg;
--
If X Is Not Null Then
--
W_Texte := 'Select To_Char(Count(*)) From v$archive_dest where status not in (''VALID'', ''INACTIVE'')';
-- Dbms_OutPut.Put_Line(' W_Texte : '||W_Texte);
--
Curs := Dbms_Sql.Open_Cursor;
--
Dbms_Sql.Parse(Curs, W_texte, Dbms_Sql.Native);
Dbms_Sql.Define_Column(Curs, 1, W_Temp, 20);
--
Return_Code := Dbms_Sql.Execute(Curs);
--
IF dbms_sql.FETCH_ROWS(Curs)>0 THEN
-- Dbms_OutPut.Put_Line(' Ok');
Dbms_Sql.Column_Value(Curs, 1, W_Temp);
End If;
--
-- Dbms_OutPut.Put_Line('Return_Code : '||To_Char(Return_Code));
-- Dbms_OutPut.Put_Line('-- Nb. Tempfiles '||W_Temp);
Dbms_OutPut.Put_Line('-- Background Proc. Error '||Lpad(To_Char(Nb_Bg),6)||' | Archive Dest. Error '||Lpad(W_Temp,8)||' --' );
--
Dbms_Sql.Close_Cursor(Curs);
--
Else
Dbms_OutPut.Put_Line('-- Background Proc. Error '||Lpad(To_Char(Nb_Bg),6));
End If;
--
End;
/
Select status_01||' | '||status_02 status
From
(select ' Jobs Broken '||Lpad(To_Char(Count(*)),8) status_01 From dba_jobs Where broken != 'N')
, (select ' Objects Invalid '||Lpad(To_Char(Count(*)),8) status_02 From dba_objects where status != 'VALID')
Union
Select status_01||' | '||status_02 status
From
(select ' Shared_Pool Failure '||Lpad(To_Char(Count(*)),8) status_01 From v$shared_pool_reserved where request_failures != 0)
, (select ' Latch > 1% '||Lpad(To_Char(Count(*)),8) status_02 From v$latch where (misses/decode(gets,0,1,gets))*100 >= 1 or (immediate_misses/decode(immediate_misses+immediate_gets,0,1,immediate_misses+immediate_gets)*100) >= 1)
;
-- ----------------------------------------------------------------------- ---
-- Trigger Disabled / Constraint Disabled ---
-- ----------------------------------------------------------------------- ---
column status format a120 wrap heading "Status"
Select status_01||' | '||status_02 status
From
(select ' Trigger Disabled '||Lpad(To_Char(Count(*)),8) status_01 From dba_triggers where status != 'ENABLED')
, (select ' Constraint Disabled '||Lpad(To_Char(Count(*)),8) status_02 From dba_constraints where status != 'ENABLED')
Union
Select status_01||' | '||status_02 status
From
(select ' Trigger Enabled '||Lpad(To_Char(Count(*)),9) status_01 From dba_triggers where status = 'ENABLED')
, (select ' Constraint Enabled '||Lpad(To_Char(Count(*)),9) status_02 From dba_constraints where status = 'ENABLED')
;
Select status_01||' | '||status_02 status
From
(Select ' Schedule Ok '||Lpad(count(*),16) status_01 from Dba_Scheduler_Jobs where state <> 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
, (Select ' Schedule Disabled '||Lpad(count(*),13) status_02 from Dba_Scheduler_Jobs where state = 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM', 'MDSYS', 'ORDSYS', 'OUTLN', 'XDB', 'DMSYS'))
Union
Select status_01||' | '||status_02 status
From
(Select ' Alert Last Hour '||Lpad(count(*),14) status_01 from dba_outstanding_alerts where Creation_Time > sysdate -1/24)
, (Select ' Advisor Last Hour '||Lpad(count(*),14) status_02 from dba_advisor_tasks where execution_end > sysdate -1/24)
Union
Select status_01 status
From
(Select ' Sheduler enable '||Lpad(decode(count(value), 0, 'OK', 1, 'KO'),14) status_01 from Dba_Scheduler_Global_Attribute Where Attribute_name = 'SCHEDULER_DISABLED')
;
--column status format a46 wrap heading "Status"
--prompt
--prompt
--prompt -- Installed options :
--prompt -- ----------------------------------------------------------------------- ---
--select
-- ' - ' || parameter || ' option'
-- from
-- sys.v_$option
-- where
-- value = 'TRUE'
--;
set head on
Prompt
-- ----------------------------------------------------------------------- ---
-- V$Recovery_Log Status ---
-- ----------------------------------------------------------------------- ---
column Thread# format 9999999 heading "Thread"
column Sequence# format 9999999 heading "Sequence"
column archive_name format A60 wrap heading "Archive|Name"
column Time heading "Time"
select
*
from
v$recovery_log
order
by thread#
;
-- ----------------------------------------------------------------------- ---
-- V$Recover_File Status ---
-- ----------------------------------------------------------------------- ---
column file# format 9999999 heading "File#"
column online format A10 heading "Online"
column online_status format A10 heading "Online|Status"
column error format A20 wrap heading "Error"
column change format 99999 heading "Change"
column Time heading "Time"
select
*
from
v$recover_file
order
by file#
;
-- ----------------------------------------------------------------------- ---
-- V$Log Status ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$log
where
status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Log Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on redogroup -
skip 1
column redogroup format 99999 heading "Group"
column redothread format 99999 heading "Thread"
column redosequence format 999999 heading "Sequence"
column file_name format A55 heading "RedoLog Name"
column KB format 99999999B heading "Size|(Kb)"
column status format A5 heading "Stat." Trunc
select
lf.group# redogroup
, l.thread# redothread
, l.sequence# redosequence
, lf.member file_name
, l.bytes/1024 Kb
, l.status status
from
v$logfile lf
, v$log l
where
l.group# = lf.group#
and
l.status not in ('ACTIVE', 'CURRENT', 'INACTIVE')
order
by lf.group#
, l.thread#
, l.sequence#
, lf.member
;
clear breaks
-- ----------------------------------------------------------------------- ---
-- V$Tempfile Status ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$tempfile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Tempfile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on status skip 1
column name format a60 wrap heading "DataFile Name"
select
name
, status
from
V$tempfile
where
status not in ('ONLINE', 'SYSTEM')
Order
By Status
, Name
;
clear breaks
-- ----------------------------------------------------------------------- ---
-- V$Datafile Status ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$datafile
where
status not in ('ONLINE', 'SYSTEM')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- V$Datafile Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
clear breaks
break on status skip 1
column name format a60 wrap heading "DataFile Name"
column fid format 999999 wrap heading "DataFile Id"
select
name
, FILE# fid
, status
from V$datafile
where status not in ('ONLINE', 'SYSTEM')
Order By Status, Name
;
clear breaks
-- ----------------------------------------------------------------------- ---
-- Dba_Tablespaces Status ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
dba_tablespaces
where
status not in ('ONLINE', 'READ ONLY')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Dba_Tablespaces Status ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column tblsp format a20 wrap heading "Tablespace Name"
column contents format a10 heading "Content"
clear breaks
break on contents -
skip 1
select
contents
, tablespace_name tblsp
, status
from
dba_tablespaces
where
status not in ('ONLINE', 'READ ONLY')
order
by contents
;
-- ----------------------------------------------------------------------- ---
-- Tablespaces Backup Mode ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select 'X'
from v$backup bck , dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database);
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Tablespaces in Backup Mode ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column tablespace_name format a18 wrap heading "Tablespace Name"
column file_name format a50 wrap heading "DataFile Name"
column status format a12 wrap heading "Status"
column change# format 999999999999 heading "Change"
column time format a18 wrap heading "Time"
select
df.tablespace_name
, df.file_name
, bck.status
, bck.change#
, To_Char(bck.time,'DD-MM-YYYY HH24:MI') time
from
v$backup bck
, dba_data_files df
where bck.file# = df.file_id
and bck.status != 'NOT ACTIVE'
and 'ARCHIVELOG' = (select log_mode from v$database)
order by df.tablespace_name, df.file_name;
-- ----------------------------------------------------------------------- ---
-- Background process in error ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select 'X'
from v$bgprocess
where paddr <> '00'
and error > 0
order by paddr
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Background process in error ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column name format a6
column description format a30
column error format 99999999999
select
paddr
, name
, description
, error
from v$bgprocess
where paddr <> '00'
and error > 0
order by paddr
;
-- ----------------------------------------------------------------------- ---
-- Archive Destination in error ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$archive_dest
where
status not in ('VALID', 'INACTIVE')
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Archive Destination in error ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column dest_id format 99999 heading "Dest.|Id"
column dest_name format a19 heading "Dest.|Name"
column error format a30 wrap heading "Error"
column status format a9 heading "Status"
column binding format a10 heading "Binding"
column target format a10 heading "Target"
column name_space format a10 heading "Name|Space"
select
dest_id
-- , dest_name
, status
, binding
, name_space
, target
, error
from v$archive_dest
where status Not in ('VALID', 'INACTIVE')
;
-- ----------------------------------------------------------------------- ---
-- Lock list ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Lock list ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
column username heading "Utilis." format A11
column sid heading "Id" format 9999
column spid heading "Unix" format A7
column state heading "Etat" format A15
column Lmode_H heading "Lock mode" format A15
column terminal heading "Terminal" format A14
column command heading "C" format 99
Column serial# heading "Serial#" format 99999
column ctime heading "Duration" format 999999
column logon heading "Date Connexion" format A16
column object_name heading "Object Name" format A25
select
s.sid
, s.serial#
, p.spid
, substr(s.username,1,15) username
, s.terminal
, l.type
, to_char(s.logon_time,'DD-MM-YYYY HH24:MI') logon
, decode(l.lmode, 1,'null' , 2,'Row share'
, 3,'Row Exclusive' , 4,'Share'
, 5,'Share Row Excl.' , 6,'Exclusive') Lmode_H
, o.object_name
, Round(ctime/60) ctime
from
v$session s
, v$process p
, v$lock l
, dba_objects o
where s.paddr = p.addr
And l.sid=s.sid
And l.id1 = o.object_id
And s.username is NOT NULL
And l.ctime > 60
;
-- ----------------------------------------------------------------------- ---
-- Rman Block Corruption ---
-- ----------------------------------------------------------------------- ---
Declare
--
Cursor Cur_Req Is
select
'X'
from
v$DATABASE_BLOCK_CORRUPTION
;
--
X Varchar2(1);
--
Begin
--
X := Null;
--
Open Cur_Req;
Fetch Cur_Req Into X;
Close Cur_Req;
--
If X Is Not Null Then
Dbms_OutPut.Put_Line('-- ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
Dbms_OutPut.Put_Line('-- Rman Block Corruption ---');
Dbms_OutPut.Put_Line('-- ----------------------------------------------------------------------- ---');
End If;
--
End;
/
select * from V$DATABASE_BLOCK_CORRUPTION;
prompt
prompt -- ----------------------------------------------------------------------- ---
prompt -- Tablespaces ---
prompt -- ----------------------------------------------------------------------- ---
Set Heading Off
Set Termout Off
create or replace function show_autoext_tbs(P_Tablespace_Name In Varchar2) return varchar2
Is
--
Cursor CX Is
Select distinct Substr(autoextensible,1,1)
from dba_data_files
where tablespace_name=P_Tablespace_Name
Union
Select distinct Substr(autoextensible,1,1)
from dba_temp_files
where tablespace_name=P_Tablespace_Name
;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := Replace(Substr(W_Texte, 2, 4), ' ', '/');
--
Return W_Texte;
--
End;
/
-- show error
create or replace function show_increment_tbs(P_Tablespace_Name In Varchar2, P_Db_Block_Size In Number) return varchar2
Is
--
Cursor CX Is
Select distinct To_Char(round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
from dba_data_files
where tablespace_name=P_Tablespace_Name
Union
Select distinct To_Char(Round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
from dba_temp_files
where tablespace_name=P_Tablespace_Name
;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := W_Texte||' ';
W_Texte := Replace(W_Texte,' ', '/');
W_Texte := Substr(W_Texte,2, Instr(W_Texte, '/', -1, 1)-2);
If Instr(W_Texte, '/', 1,2) != 0 then
W_Texte := Substr(W_Texte, 1, Instr(W_Texte, '/', 1,2))||'...';
End If;
--
Return W_Texte;
--
End;
/
-- show error
Set Termout On
Set Heading On
clear breaks
break on contents -
skip 1
compute Sum of alloc used maxbytes nfree nbfrag on contents
column tblsp format a22 wrap heading "Tablespace|Name"
column Alloc format 99999999 heading "Alloc|(Mb)"
column file_name format a45 wrap heading "DataFile Name"
column rfno format 999 heading "R.|Fno"
-- column inc format 9999999 heading "Incr|(Mb)"
column inc format a15 heading "Incr|(Mb)"
column maxbytes format 9999999 heading "Sum Max|(Gb)"
column user_bytes format 999999 heading "Userbytes|(Mb)"
column free format 999999 heading "Free|(Mb)"
column nfree format 99999 heading "Max|Free|(Mb)"
column used format 99999999 heading "Used|(Mb)"
column pused format 990.9 heading "%Used"
column maxpused format 990.9 heading "Max|%Used"
column contents format a5 heading "Cont."
column fragmax format 99999 heading "Larg.|Free|Ext|(Mb)"
column nbfrag format 99999 heading "Nb|frag"
column nbfile format 99 heading "Nb|F."
column autoext format a3 wrap heading "Auto|Ext."
column ftype format a2 wrap heading "F.|T."
column ext_manage format a5 wrap heading "Ext.|M."
column seg_sp_mg format a2 wrap heading "S.|M."
column seg_space_mg format a7 wrap heading "Space|Mg."
set linesize 145
Select
dt.contents contents
, dt.Tablespace_Name tblsp
, df.alloc alloc
, df.alloc - nvl(fs.free,0) used
, ((df.alloc - nvl(fs.free,0))* 100)/df.alloc pused
, dt.autoext autoext
, df.summaxbytes/1024 maxbytes
, dt.Inc inc
-- , nvl(fs.free,0) free
-- , fs.nbfrag
, Nvl(fs.fragmax,0) fragmax
-- , dt.ext_manage
-- , dt.seg_sp_mg seg_sp_mg
, Decode(dt.ext_manage||' '||dt.seg_sp_mg,'LOC MA', ' LMTS', 'LOC AU', ' ASSM',dt.ext_manage||' '||dt.seg_sp_mg) seg_space_mg
, df.nbf nbfile
, df.ftype ftype
-- , decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0)) nfree
, 100 - ((decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0))) * 100 / decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))) maxpused
from
( select
Substr(contents,1,4) contents
, tablespace_name
, Substr(extent_management,1,3) ext_manage
, Substr(segment_space_management,1,2) seg_sp_mg
, show_autoext_tbs(tablespace_name) autoext
, show_increment_tbs (tablespace_name, BLOCK_SIZE) inc
from dba_tablespaces
) dt
, ( select
Sum(Bytes/1024/1024) alloc
, Max(MaxBytes/1024/1024) maxbytes
, Sum(MaxBytes/1024/1024) summaxbytes
, count(*) nbf
, tablespace_name
, 'D' ftype
from Dba_Data_Files
Group
By tablespace_name
Union
select
Sum(Bytes/1024/1024) alloc
, Max(MaxBytes/1024/1024) maxbytes
, Sum(MaxBytes/1024/1024) summaxbytes
, count(*) nbf
, tablespace_name
, 'T' ftype
from Dba_Temp_Files
Group
By tablespace_name
) df
, (select
Nvl(Sum(nvl(bytes/1024/1024,0)),0) free
, Nvl(max (bytes)/1048576,0) fragmax
, tablespace_name
, count(*) nbfrag
from dba_free_space --(Select distinct tablespace_name, bytes from dba_free_space)
Group
By tablespace_name
order by max (bytes)/1048576 desc
) fs
Where
df.tablespace_name = fs.tablespace_name (+)
and
df.tablespace_name = dt.tablespace_name
Group
By dt.contents
, dt.Tablespace_Name
, df.alloc
, df.maxbytes
, df.summaxbytes
, df.nbf
, dt.Inc
-- , nvl(fs.free,0)
, df.alloc - nvl(fs.free,0)
-- , fs.nbfrag
, fs.fragmax
, dt.autoext
, dt.ext_manage
, df.ftype
, dt.seg_sp_mg
Order
By contents
, maxpused desc
, dt.inc
;
Prompt
Set Heading Off
Set Termout Off
Drop function show_autoext_tbs;
Drop function show_increment_tbs;
Set Heading On
Set Termout On
set heading on
-- Spool off
spool off
-- exit
=========================================================================
Server Detail:- apt-amd-02.aptus.co.uk:192.168.2.251 Inst Name and Id->orcl=1, Db Name= orcl OS:- x86_64/Linux 2.4.xx Uptime:-7days18hours33minutes27seconds
cluster_database FALSE
cluster_database_instances 1
db_recovery_file_dest /opt/app/oracle/flash_recovery_area
FlashBack NO
** 22-Oct-2011:06:06 ** DB : ORCL ** Inst. : orcl **
Id Unix Utilis. Terminal Programme
----- ------- ---------- ---------------- --------------------------------
40 19570 SYS pts/22 oracle@apt-amd-02 (TNS V1-V3)
-- ----------------------------------------------------------------------- --
-- Instance Information --
-- ----------------------------------------------------------------------- --
Cpu_Count 1 | Host_Name apt-amd-02
Database_Name ORCL | Database_Open_Mode READ WRITE
Instance_Logins ALLOWED | Startup_Time 14-10-2011 11:33
Instance_Status ACTIVE | Instance_Name orcl
Checkpoint_Chg# 18077481 | Current_Scn 18077491
DBID 1267852645 | Flashback NO
Version 11.2.0.1.0 | Instance_Role PRIMARY_INSTANCE
Db_Cache_Advice ON | Statistics_Level TYPICAL
Dynamic SGA Not Installed | sga_target (Mb) 0
sga_max_size (Mb) 804 | Free Memory (Mb) 280
Automatic PGA Not Installed | Pga consumption (Mb) 200
pga_aggregate_target (Mb) 0 | Workarea_size_Policy AUTO
Cursor_Sharing EXACT | Remote_login_p. EXCLUSIVE
Default Temp TEMP | Undo tablespace UNDOTBS1
Background Dump Dest /opt/app/oracle/diag/rdbms/orcl/orcl/trace
Database log mode ARCHIVELOG
Db_recovery_file_dest /opt/app/oracle/flash_recovery_area
Db_recovery_file_dest_size 4070572032
Spfile /opt/app/oracle/product/11.2/dbs/spfileorcl.ora
Filesystemio_options none
-- Force_Logging NO
Index No Log. Or N. 85 | Table No Log. Or N. 36
Index Part. No Log. 196 | Index SubPart. No Log. 0
Table Row Movement 0 | Table No Row Movement 1059
Last Tab. An. 16-10-2011 06:01 | Last Ind. An. 16-10-2011 06:01
Monitoring On 1030 | Monitoring Off 0
Cascade AUTO_CASCADE | Degree NULL
Estimate % AUTO_SAMPLE_SIZE | Opt. FOR ALL COLUMNS SIZE AUTO
No Invalid. AUTO_INVALIDATE | Granularity AUTO
-- Database Space (Mb) 5705 | SGA (Mb) 800 --
-- Nb. Datafiles 12 | Nb. Tempfiles 1 --
shared_pool Cons. (Mb) 308
shared_pool_size (Mb) 0 | Shared_pool_reserved_s. (Mb) 16
-- shared pool (Mb)/ Current 308 | User Specified 0
-- Free SGA Memory Av. (Mb) 280
Active session number 8 | Killed session number 0
Param. Processes 150 | Param. Sessions 247
Session number (client) 9 | Undo Block Used 0
Data Buffer Hit ratio 98.1 | Library Buffer Hit ratio 99.3
Dict. Buffer Hit ratio 99.5 | Switch number (last hour) 0
Db_Files 200 | Db_Writer_Processes 1
Optimizer Mode ALL_ROWS | Optimizer_Index_Cost_Adj 100
Sort_Area_Size (Kb) 64 | Db_File_Multi_Block_RC. 95
Nb Redo Gpr. 3 | Redo size (Mb) 520
Dba_Tablespaces Status OK | V$Log Status OK
V$Datafile Status OK | V$Tempfile Status OK
V$Recover_File OK | V$Recovery_Log OK
Indexes Part. unusable 0 | Indexes SubPart. unusable 0
Lock number 3 | Indexes unusable 0
Rman Corrupt. Blck. 0 | Rman Crpt. Blck. Last Bck. 0
Tablespace in Backup Mode 0 | Tablespace > 90% 1
-- Background Proc. Error 0 | Archive Dest. Error 0 --
Jobs Broken 0 | Objects Invalid 3
Shared_Pool Failure 1 | Latch > 1% 1
Trigger Disabled 12 | Constraint Disabled 37
Trigger Enabled 951 | Constraint Enabled 11867
Alert Last Hour 0 | Advisor Last Hour 2
Schedule Ok 4 | Schedule Disabled 0
Sheduler enable OK
-- ---
-- ----------------------------------------------------------------------- ---
-- Lock list ---
-- ----------------------------------------------------------------------- ---
Id Serial# Unix Utilis. Terminal TY Date Connexion Lock mode Object Name Duration
----- ------- ------- ----------- -------------- -- ---------------- --------------- ------------------------- --------
35 3865 19220 SYS UNKNOWN AE 22-10-2011 05:59 Share ORA$BASE 7
40 956 19570 SYS pts/22 AE 22-10-2011 06:04 Share ORA$BASE 2
1 1831 10536 SYS pts/6 AE 19-10-2011 10:15 Share ORA$BASE 4071
-- ----------------------------------------------------------------------- --
-- Tablespaces --
-- ----------------------------------------------------------------------- --
Larg.
Free
Tablespace Alloc Used Aut Sum Max Incr Ext Space Nb F. Max
Cont. Name (Mb) (Mb) %Used Ext (Gb) (Mb) (Mb) Mg. F. T. %Used
----- ---------------------- --------- --------- ------ --- -------- --------------- ------ ------- --- -- ------
PERM DROP1 2 1 50.0 N 0 0 1 ASSM 1 D 50.0
TSAPEXU 110 98 88.6 Y 0 10 13 ASSM 1 D 32.5
RMAN 50 7 13.9 N 0 0 43 ASSM 1 D 13.9
PERFSTAT 1000 115 11.5 N 0 0 885 ASSM 1 D 11.5
TEST 20 1 5.0 N 0 0 19 ASSM 1 D 5.0
SYSTEM 830 813 98.0 Y 32 10 8 LMTS 1 D 2.5
SYSAUX 830 715 86.1 Y 32 10 53 ASSM 1 D 2.2
USERS 458 411 89.7 Y 32 1.3 47 ASSM 1 D 1.3
TSAPEXF 50 1 2.0 Y 0 10 49 ASSM 1 D 0.3
EXAMPLE 100 79 78.8 Y 32 .6 19 ASSM 1 D 0.2
ANUJTEST 10 8 80.0 Y 32 5 2 ASSM 1 D 0.0
***** --------- --------- --------
sum 3460 2248 161
TEMP TEMP 326 326 100.0 Y 32 .6 0 LMTS 1 T 1.0
***** --------- --------- --------
sum 326 326 32
UNDO UNDOTBS1 325 28 8.7 Y 32 5 283 LMTS 1 D 0.1
***** --------- --------- --------
sum 325 28 32
set pagesize 200 linesize 200
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
SQL> r
1 SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
2 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
3 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
4 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
5 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
6 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
7 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
8 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
9 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
10 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
11 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
12 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
13 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
14 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
15 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
16 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
17 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
18 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
19 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
20 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
21 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
22 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
23 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
24 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
25 TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
26 FROM V$LOG_HISTORY
27 GROUP BY TRUNC(FIRST_TIME)
28* ORDER BY TRUNC(FIRST_TIME) DESC
DG Dat 12AM 01AM 02AM 03AM 04AM 05AM 06AM 07AM 08AM 09AM 10AM 11AM 12PM 1PM 2PM 3PM 4PM 5PM 6PM 7PM 8PM 9PM 10PM 11PM
------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Jun 02 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0
Jun 01 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5 7 2 0 0 0 0 0 0 0
May 26 0 0 0 0 0 0 0 0 0 7 3 5 0 0 0 0 0 0 0 0 0 0 0 0
May 25 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 1 0 0
Apr 16 0 0 0 0 0 0 8 0 0 0 0 0 0 0 0 0 0 0 0 2 8 1 0 0
Apr 14 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 1 0 5 0 0 0
Apr 13 0 0 0 0 0 0 0 0 0 0 0 0 3 0 4 9 0 0 6 7 1 0 0 0
Apr 25 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 1 0 0 0 0
Apr 22 0 0 0 0 0 0 0 0 2 0 0 2 0 0 0 0 1 0 0 0 0 0 0 0
Apr 21 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 2 3 4 2 0 0 0 0 0
Apr 14 0 0 0 0 0 0 0 0 0 0 0 1 2 0 0 0 0 0 0 0 0 0 0 0
Apr 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0
12 rows selected.
