temp_usage
Oracle Temp space usage
Temp space usage
SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text, vsu.tablespace,sum_blocks*dt.block_size/1024/1024 usage_mb
FROM ( SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
sum(blocks)*8192/1024/1024 "USAGE_MB",
sum(blocks) sum_blocks
FROM v$sort_usage
HAVING SUM(blocks)> 100
GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr) "VSU",
v$sqltext vst,
v$session vs,
v$process vp,
dba_tablespaces dt
WHERE vs.sql_id = vst.sql_id
-- AND vsu.sqladdr = vst.address
-- AND vsu.sqlhash = vst.hash_value
AND vsu.session_addr = vs.saddr
AND vs.paddr = vp.addr
AND vst.piece = 0
AND dt.tablespace_name = vsu.tablespace
order by usage_mb
Saturday 29 October 2011
Oracle version info and option info
Oracle version info ..
col comp_name format a40 select comp_name, status, version from dba_registry; COMP_NAME STATUS VERSION ---------------------------------------- -------------------------------------------- ------------------------------ Oracle Database Catalog Views VALID 12.2.0.1.0 Oracle Database Packages and Types VALID 12.2.0.1.0 Oracle XML Database VALID 12.2.0.1.0 set pagesize 200 select parameter, value from v$option; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- Partitioning TRUE Objects TRUE Real Application Clusters FALSE Advanced replication TRUE Bit-mapped indexes TRUE Connection multiplexing TRUE Connection pooling TRUE Database queuing TRUE Incremental backup and recovery TRUE Instead-of triggers TRUE Parallel backup and recovery TRUE col PRODUCT format a50 col VERSION format a20 select product, version from product_component_version ; PRODUCT VERSION -------------------------------------------------- -------------------- NLSRTL 12.2.0.1.0 Oracle Database 12c Enterprise Edition 12.2.0.1.0 PL/SQL 12.2.0.1.0 TNS for Linux: 12.2.0.1.0 select * from v$version ; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0
SELECT version FROM v$instance;
VERSION
--------------------
12.2.0.1.0
$ORACLE_HOME/OPatch/opatch lsinventory | awk '/^Oracle Database/ {print $NF}'
12.2.0.1.0
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@cloud-ora ~]$ $ORACLE_HOME/OPatch/opatch lsinventory | awk '/^Oracle Database/ {print $NF}'
12.2.0.1.0
[oracle@cloud-ora ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0/db_2
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/db_2/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.1.0/db_2/cfgtoollogs/opatch/opatch2017-09-24_03-56-50AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/12.1.0/db_2/cfgtoollogs/opatch/lsinv/lsinventory2017-09-24_03-56-50AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: cloud-ora
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
Oracle Datafile info
Oracle datafile info
Oracle all file info
set linesize 200
col name format a80
select name,'data_file' file_type, round(bytes/1024/1024) MB from v$datafile
union all
select name, 'temp_file',round(bytes/1024/1024) from v$tempfile
union all
select f.member, 'log_file', round(l.bytes/1024/1024) from v$log l, v$logfile f where f.group#=l.group#
union all
select name, 'control_file', (select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section) from v$controlfile
order by 2
/
NAME FILE_TYPE MB
-------------------------------------------------------------------------------- ------------ ----------
/opt/app/oracle/oradata/orcl/control01.ctl control_file 9
/opt/app/oracle/flash_recovery_area/orcl/control02.ctl control_file 9
/opt/app/oracle/oradata/orcl/users01.dbf data_file 590
/opt/app/oracle/oradata/orcl/example01.dbf data_file 100
/opt/app/oracle/oradata/orcl/anujtest.dbf data_file 10
/opt/app/oracle/oradata/orcl/tsapexf01.dbf data_file 50
/opt/app/oracle/oradata/orcl/tsapexu01.dbf data_file 110
/opt/app/oracle/oradata/orcl/test.dbf data_file 20
/opt/app/oracle/oradata/orcl/rman.dbf data_file 50
/opt/app/oracle/oradata/orcl/drop.dbf data_file 2
/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf data_file 1000
/opt/app/oracle/oradata/orcl/undotbs01.dbf data_file 325
/opt/app/oracle/oradata/orcl/system01.dbf data_file 830
/opt/app/oracle/oradata/orcl/sysaux01.dbf data_file 830
/opt/app/oracle/oradata/orcl/redo05.log log_file 520
/opt/app/oracle/oradata/orcl/redo06.log log_file 520
/opt/app/oracle/oradata/orcl/redo04.log log_file 520
/opt/app/oracle/oradata/orcl/temp01.dbf temp_file 326
18 rows selected.
col VALUE format a50
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest_size';
select 'all_data_files' file_type, round(sum(bytes)/1024/1024) MB from v$datafile
union all
select 'all_temp_files',round(sum(bytes)/1024/1024) from v$tempfile
union all
select 'all_log_files', round(sum(bytes)/1024/1024) from v$log l, v$logfile f where f.group#=l.group#
union all
select 'all_control_files', round(sum((select ceil(2 * sum(record_size * records_total)) from v$controlfile_record_section))/1024/1024)
from v$controlfile
order by 1
/
FILE_TYPE MB
----------------- ----------
all_control_files 18
all_data_files 3917
all_log_files 1560
all_temp_files 326
select round((( select sum(bytes) from v$datafile )
+ ( select sum(bytes) from v$tempfile )
+ ( select sum(bytes) from v$log l,v$logfile f where f.group#=l.group#)
+ ( select sum((select ceil(2 * sum(record_size * records_total))
from v$controlfile_record_section)) from v$controlfile))/1024/1024) "dbsize(MB)"
from dual
/
dbsize(MB)
----------
5821
col VALUE format a50
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest_size';
Oracle all file info
set linesize 200
col name format a80
select name,'data_file' file_type, round(bytes/1024/1024) MB from v$datafile
union all
select name, 'temp_file',round(bytes/1024/1024) from v$tempfile
union all
select f.member, 'log_file', round(l.bytes/1024/1024) from v$log l, v$logfile f where f.group#=l.group#
union all
select name, 'control_file', (select ceil(2 * sum(RECORD_SIZE * records_total)/1024/1024) meg from v$controlfile_record_section) from v$controlfile
order by 2
/
NAME FILE_TYPE MB
-------------------------------------------------------------------------------- ------------ ----------
/opt/app/oracle/oradata/orcl/control01.ctl control_file 9
/opt/app/oracle/flash_recovery_area/orcl/control02.ctl control_file 9
/opt/app/oracle/oradata/orcl/users01.dbf data_file 590
/opt/app/oracle/oradata/orcl/example01.dbf data_file 100
/opt/app/oracle/oradata/orcl/anujtest.dbf data_file 10
/opt/app/oracle/oradata/orcl/tsapexf01.dbf data_file 50
/opt/app/oracle/oradata/orcl/tsapexu01.dbf data_file 110
/opt/app/oracle/oradata/orcl/test.dbf data_file 20
/opt/app/oracle/oradata/orcl/rman.dbf data_file 50
/opt/app/oracle/oradata/orcl/drop.dbf data_file 2
/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf data_file 1000
/opt/app/oracle/oradata/orcl/undotbs01.dbf data_file 325
/opt/app/oracle/oradata/orcl/system01.dbf data_file 830
/opt/app/oracle/oradata/orcl/sysaux01.dbf data_file 830
/opt/app/oracle/oradata/orcl/redo05.log log_file 520
/opt/app/oracle/oradata/orcl/redo06.log log_file 520
/opt/app/oracle/oradata/orcl/redo04.log log_file 520
/opt/app/oracle/oradata/orcl/temp01.dbf temp_file 326
18 rows selected.
col VALUE format a50
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest_size';
select 'all_data_files' file_type, round(sum(bytes)/1024/1024) MB from v$datafile
union all
select 'all_temp_files',round(sum(bytes)/1024/1024) from v$tempfile
union all
select 'all_log_files', round(sum(bytes)/1024/1024) from v$log l, v$logfile f where f.group#=l.group#
union all
select 'all_control_files', round(sum((select ceil(2 * sum(record_size * records_total)) from v$controlfile_record_section))/1024/1024)
from v$controlfile
order by 1
/
FILE_TYPE MB
----------------- ----------
all_control_files 18
all_data_files 3917
all_log_files 1560
all_temp_files 326
select round((( select sum(bytes) from v$datafile )
+ ( select sum(bytes) from v$tempfile )
+ ( select sum(bytes) from v$log l,v$logfile f where f.group#=l.group#)
+ ( select sum((select ceil(2 * sum(record_size * records_total))
from v$controlfile_record_section)) from v$controlfile))/1024/1024) "dbsize(MB)"
from dual
/
dbsize(MB)
----------
5821
col VALUE format a50
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest';
SELECT name,value FROM sys.v_$parameter WHERE name = 'db_recovery_file_dest_size';
Oracle Redo Log Files Info
COL GROUP# FORMAT 999999 HEAD 'Group'
COL THREAD# FORMAT 999999 HEAD 'Thread'
COL SEQUENCE# FORMAT 99999999 HEAD 'Sequence'
COL size FORMAT A6 HEAD 'Size|Mb'
COL STATUS FORMAT A10
COL MEMBER FORMAT A60
COL switch_dt FORMAT A20
COL ARCHIVED FORMAT A7
BREAK ON THREAD# NODUP ON GROUP# NODUP SKIP 1 ON sizeMb NODUP ON switch_dt NODUP
COMPUTE NUMBER LABEL 'MEMBER:' OF SEQUENCE# ON GROUP#
SELECT L.GROUP#, L.THREAD#, L.SEQUENCE#, LPAD(TRUNC(BYTES/1024/1024),5) sizeMb, L.STATUS, MEMBER, TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24:MI:SS') switch_dt,
DECODE(ARCHIVED,'YES','Done..','Non') ARCHIVED
FROM V$LOG L, V$LOGFILE F
WHERE L.GROUP# = F.GROUP#
ORDER BY 1,3,6;
Oracle Database check report
Oracle database report
@database_check.sql
@database_check.sql
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
union
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 archive_name format A50 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
==============
@database_check.sql
number of logswitches per hour
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:-22days11hours37minutes44seconds
number of logswitches per hour
cluster_database FALSE
cluster_database_instances 1
db_recovery_file_dest
number of logswitches per hour
FlashBack NO
number of logswitches per hour
number of logswitches per hour
** 26-Nov-2011:10:04 ** DB : ORCL ** Inst. : orcl **
number of logswitches per hour
Id Unix Utilis. Terminal Programme
----- ------- ---------- ---------------- --------------------------------
44 25245 SYS pts/21 oracle@apt-amd-02 (TNS V1-V3)
-- ----------------------------------------------------------------------- --
-- Instance Information --
-- ----------------------------------------------------------------------- --
number of logswitches per hour
Cpu_Count 1 | Host_Name apt-amd-02
Database_Name ORCL | Database_Open_Mode READ WRITE
Instance_Logins ALLOWED | Startup_Time 04-11-2011 10:27
Instance_Status ACTIVE | Instance_Name orcl
number of logswitches per hour
Checkpoint_Chg# 20661659 | Current_Scn 20661670
DBID 1267852645 | Flashback NO
Version 11.2.0.1.0 | Instance_Role PRIMARY_INSTANCE
number of logswitches per hour
Db_Cache_Advice ON | Statistics_Level TYPICAL
Dynamic SGA Not Installed | sga_target (Mb) 0
sga_max_size (Mb) 804 | Free Memory (Mb) 280
number of logswitches per hour
Automatic PGA Not Installed | Pga consumption (Mb) 180
pga_aggregate_target (Mb) 0 | Workarea_size_Policy AUTO
number of logswitches per hour
Cursor_Sharing EXACT | Remote_login_p. EXCLUSIVE
Default Temp TEMP | Undo tablespace UNDOTBR
number of logswitches per hour
log_archive_dest /opt/app/oracle/admin/arch1
number of logswitches per hour
Background Dump Dest /opt/app/oracle/diag/rdbms/orcl/orcl/trace
Database log mode ARCHIVELOG
Db_recovery_file_dest_size 4070572032
number of logswitches per hour
Filesystemio_options none
-- Force_Logging NO
number of logswitches per hour
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 1067
number of logswitches per hour
Last Tab. An. 23-11-2011 22:02 | Last Ind. An. 17-11-2011 22:01
Monitoring On 1038 | Monitoring Off 0
number of logswitches per hour
Cascade AUTO_CASCADE | Degree NULL
Estimate % AUTO_SAMPLE_SIZE | Opt. FOR ALL COLUMNS SIZE AUTO
No Invalid. AUTO_INVALIDATE | Granularity AUTO
-- Database Space (Mb) 6073 | SGA (Mb) 800 --
-- Nb. Datafiles 12 | Nb. Tempfiles 1 --
number of logswitches per hour
shared_pool Cons. (Mb) 340
shared_pool_size (Mb) 0 | Shared_pool_reserved_s. (Mb) 15
-- shared pool (Mb)/ Current 308 | User Specified 0
-- Free SGA Memory Av. (Mb) 280
number of logswitches per hour
Active session number 9 | Killed session number 0
Param. Processes 150 | Param. Sessions 247
Session number (client) 14 | Undo Block Used 0
number of logswitches per hour
Data Buffer Hit ratio 98.3 | Library Buffer Hit ratio 99.1
Dict. Buffer Hit ratio 99.2 | Switch number (last hour) 0
number of logswitches per hour
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. 97
number of logswitches per hour
Nb Redo Gpr. 3 | Redo size (Mb) 520
number of logswitches per hour
Dba_Tablespaces Status OK | V$Log Status OK
V$Datafile Status OK | V$Tempfile Status OK
V$Recover_File OK | V$Recovery_Log OK
number of logswitches per hour
Indexes Part. unusable 0 | Indexes SubPart. unusable 0
Lock number 8 | Indexes unusable 0
Rman Corrupt. Blck. 0 | Rman Crpt. Blck. Last Bck. 0
Tablespace in Backup Mode 0 | Tablespace > 90% 3
-- Background Proc. Error 0 | Archive Dest. Error 0 --
number of logswitches per hour
Jobs Broken 0 | Objects Invalid 22
Shared_Pool Failure 1 | Latch > 1% 1
number of logswitches per hour
Trigger Disabled 12 | Constraint Disabled 37
Trigger Enabled 951 | Constraint Enabled 11873
number of logswitches per hour
Alert Last Hour 0 | Advisor Last Hour 1
Schedule Ok 4 | Schedule Disabled 0
Sheduler enable OK
-- ---
-- ----------------------------------------------------------------------- ---
-- Lock list ---
-- ----------------------------------------------------------------------- ---
number of logswitches per hour
Id Serial# Unix Utilis. Terminal TY Date Connexion Lock mode Object Name Duration
----- ------- ------- ----------- -------------- -- ---------------- --------------- ------------------------- --------
44 7990 25245 SYS pts/21 AE 26-11-2011 21:58 Share ORA$BASE 7
51 8867 24020 SCOTT pts/20 AE 26-11-2011 21:36 Share ORA$BASE 29
1 5658 19363 SYS pts/1 AE 26-11-2011 20:11 Share ORA$BASE 113
26 6399 15101 SCOTT pts/14 AE 24-11-2011 17:09 Share ORA$BASE 3175
47 12494 5858 SYS pts/24 AE 24-11-2011 14:41 Share ORA$BASE 3324
43 7069 1832 SYS pts/16 AE 24-11-2011 13:31 Share ORA$BASE 3393
47 12494 5858 SYS pts/24 TO 24-11-2011 14:41 Row Exclusive SDO_GEOR_DDL__TABLE$$ 3319
47 12494 5858 SYS pts/24 TM 24-11-2011 14:41 Row Exclusive ANUJ 3318
-- ----------------------------------------------------------------------- --
-- Tablespaces --
-- ----------------------------------------------------------------------- --
number of logswitches per hour
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 840 808 96.2 Y 32 10 17 LMTS 1 D 2.5
SYSAUX 830 753 90.7 Y 32 10 53 ASSM 1 D 2.3
USERS 641 586 91.4 Y 32 1.3 54 ASSM 1 D 1.8
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 3653 2456 161
TEMP TEMP 326 326 100.0 Y 32 .6 0 LMTS 1 T 1.0
***** --------- --------- --------
sum 326 326 32
UNDO UNDOTBR 500 14 2.9 Y 32 0 454 LMTS 1 D 0.0
***** --------- --------- --------
sum 500 14 32
Orc*>
Oracle archive log on USE_DB_RECOVERY_FILE_DEST
ORA-02097: parameter cannot be modified ORA-16018: cannot use LOG_ARCHIVE_DEST error in alert log file /opt/app/oracle/diag/rdbms/orcl/orcl/trace ======================================= Fri Oct 28 16:24:14 2011 Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_16275.trc: ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 97.07% used, and has 119123968 remaining bytes available. ************************************************************************ You have following choices to free up space from recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY. 2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands. ************************************************************************ Fri Oct 28 16:27:14 2011 ===================================== SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST USE_DB_RECOVERY_FILE_DEST ----- try not to use this area for archive log file , system will hang after destination full SQL> show parameter DB_RECOVERY_FILE_DEST NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /opt/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 3882M SQL> alter system set log_archive_dest = '/opt/app/oracle/admin/arch'; alter system set log_archive_dest = '/opt/app/oracle/admin/arch' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST SQL> alter system set DB_RECOVERY_FILE_DEST='' ; ----- unset this value first System altered. SQL> alter system set log_archive_dest = '/opt/app/oracle/admin/arch' scope=both ; System altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/app/oracle/admin/arch Oldest online log sequence 59 Next log sequence to archive 61 Current log sequence 61 SQL> alter system switch logfile; System altered. SQL> !ls -ltr /opt/app/oracle/admin/arch total 153136 -rw-r----- 1 oracle oinstall 156810752 2011-10-29 05:59 1_61_757353423.dbf
Subscribe to:
Posts (Atom)
