REM Programma: ora2html.sql REM Oracle configuration HTML report REM Autore: Bartolomeo Bogliolo mail@meo.bogliolo.name meo REM http://meoshome.it.eu.org/ REM Data: 1-APR-98 REM Versione: 1.0.35b REM Note: REM 1-APR-98 mail@meo.bogliolo.name REM Versione HTML iniziale basata su gen.sql, passaggio alla lingua inglese REM 1-MAY-98 mail@meo.bogliolo.name REM Better formatting, totals on TBS REM Invalid objects and tuning paramters REM Fragmented objs REM IAS Schemas Versions, 10g compatibility REM JOBS and DB Links REM no IAS, more performance and status infos REM tabled menu contents, 1.0.18c: faster lock select, 1.0.19 log switching REM 1.0.20 more summary info, 1.0.21 Partitioning, (b) Custom statistics, (c) Spatial REM 1.0.22 OS Infos (10g, 11g only), minor changes REM 30-JUN-11 mail@meo.bogliolo.name REM 1.0.23 Rule hint on dba_jobs_running (to avoid a performance bug in 9.2) REM 1-JAN-12 mail@meo.bogliolo.name REM 1.0.24 A bit more info on partitioning REM 1-MAY-12 mail@meo.bogliolo.name REM 1.0.25 More default password checks REM 1-AUG-12 mail@meo.bogliolo.name REM 1.0.26 Recycle Bin space usage REM 1-OCT-12 mail@meo.bogliolo.name REM 1.0.27 More RAC info, more custom plugins (a,b) bug fixing REM 24-MAY-13 mail@meo.bogliolo.name REM 1.0.28 More licensing info: feature_info details on DBA_FEATURE_USAGE_STATISTICS REM 1.0.29 DBcpu (a) segment_type, per schema size (b) OPEN user count REM (c) Partitions compression (d) SQLcl checked REM 14-FEB-17 mail@meo.bogliolo.name REM 1.0.30 New CSS, new plugins (Oracle 12c, RMAN, ...) (a) SGA parameters (b) Users' expiry date REM 1-AUG-19 mail@meo.bogliolo.name REM 1.0.31 RU and RUP updated REM 14-JAN-20 mail@meo.bogliolo.name REM 1.0.32 RU and RUP updated REM 31-OCT-20 mail@meo.bogliolo.name REM 1.0.33 RU and RUP updated REM 1-APR-21 mail@meo.bogliolo.name REM 1.0.34 RU and RUP updated (b) Halloween release update (c) 1 April 2022 release update (d) Enabled jobs (e) Last RUs REM 1-AUG-22 mail@meo.bogliolo.name REM 1.0.35 RU and RUP updated, CS in Summary (a) List all parameters (b) data types create view v_tab_occ as select tablespace_name,sum(bytes) bytes, max(extent_id)+1 max_extent from sys.dba_extents group by tablespace_name; create view v_tab_free as select tablespace_name,max(bytes) bytes from sys.dba_free_space group by tablespace_name; create table v_big_obj as select segment_name, segment_type, tablespace_name, owner, sum(bytes) bytes from sys.dba_extents group by segment_name, segment_type, tablespace_name, owner order by bytes desc; create table v_frg_obj as select segment_name, segment_type, tablespace_name, owner, count(*) extents, sum(bytes) bytes from sys.dba_extents group by segment_name, segment_type, tablespace_name, owner order by extents desc; create table v_log_sd as select count(*)/7 log_sd from sys.v_$log_history where first_time > sysdate-7; set colsep ' ' set pagesize 9999 set linesize 130 set heading off set feedback off set timing off set define off set sqlprompt '' ttitle off spool ora2html.htm select ' ', value, ' - ora2html Oracle Statistics '|| '' from v$parameter where name like 'db_name'; select '

' from dual; select '

'||substr(value,1,25)||'

' from v$parameter where name ='db_name'; select '

' from dual; select '

Statistics generated on: '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual; select 'by: '||user from dual; select 'using: ora2html.sql v.1.0.35b' from dual; select '
Software by ' from dual; select 'Meo Bogliolo

' from dual; select '

' "Status" from dual; select '

' from dual; select '
Summary
Item', 'Value' from dual; select '
'||' Database :', '', ''||value from v$parameter where name like 'db_name' union select '
'||' Version :', '', ''||substr(banner,instr(banner, '.',1,1)-2,11) from sys.v_$version where banner like 'Oracle%' union select '
'||' Created :', '', ''|| to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database union select '
'||' Started :', '', ''|| to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') from v$instance union select '
'||' DB Size (MB) :', '', ''||to_char(sum(bytes)/(1024*1024),'999,999,999,999') from sys.dba_data_files union select '
'||' SGA (MB) :', '', ''||to_char(sum(value)/(1024*1024),'999,999,999,999') from sys.v_$sga union select '
'||' Log archiving :', '', ''||log_mode from v$database union select '
'||' Defined Users / OPEN:', '', ''||to_char(count(*),'999999999999') ||' / '|| to_char(sum(decode(account_status,'OPEN',1,0)),'999999999999') from sys.dba_users union select '
'||' Defined Schemata :', '', ''||to_char(count(distinct owner),'999999999') from dba_objects where owner not in ('SYS', 'SYSTEM') and object_type = 'TABLE' union select '
'||' Defined Tables :', '', ''||to_char(count(*),'999999999999') from dba_objects where owner not in ('SYS', 'SYSTEM') and object_type = 'TABLE' union select '
'||' Used Space (MB) :', '', ''||to_char(sum(bytes)/(1024*1024),'999,999,999,999') from sys.dba_extents union select '
'||' Sessions / USER / ACTIVE:', '', ''||to_char(count(*),'999999999999') from gv$session order by 2; select ' / '|| to_char(count(*),'999999999999') from gv$session where type='USER'; select ' / '|| to_char(count(*),'999999999999') from gv$session where status='ACTIVE' and type='USER'; select '
'||' Character set :', '', ''|| value$ from sys.props$ where name = 'NLS_CHARACTERSET'; select '
'||' Hostname :', '', ''|| host_name from gv$instance union select '
'||' Instance :', '', ''||instance_name from gv$instance; select '
'||' Archiver :', '', ''|| archiver from v$instance; select '
'||' RedoLog Writes Day (MB) :', '', ''||to_char(avg(bytes)*log_sd/(1024*1024),'999999999999') from v_log_sd, sys.v_$log group by log_sd; select '

' from dual; select '

' from dual; select '

' from dual; select '
Version check
Version', ' Supported Release', ' Last releases', ' Notes' from dual; select '
', banner from v$version where banner like 'Oracle%'; REM supported select ' ', decode(substr(banner,instr(banner, '.',1,1)-2, instr(banner, '.',1,2)-instr(banner, '.',1,1)+2), '12.2', 'YES', '18.0', 'NO', '19.0', 'YES', '21.0', 'YES', 'NO') from v$version where banner like 'Oracle%'; REM last releases (n, n-1) select ' ', decode(substr(banner,instr(banner, '.',1,1)-2, instr(banner, '.',1,2)-instr(banner, '.',1,1)+2), '12.2', 'NO', '18.0', 'NO', '19.0', 'YES', '21.0', 'YES', 'NO') from v$version where banner like 'Oracle%'; select ' Last Release Updates (12.2+): 21.7, 19.16, 18.15, 12.2.0.1.220419' from dual; select '
Last Patch Set Updates (12.1-): 12.1.0.2.220719, 11.2.0.4.201020, 10.2.0.5.19; 9.2.0.8, 8.1.7.4, 7.3.4.5' from dual; select '

' from dual; select '

' from dual; select '' version from sys.v_$version; select '
Versions
'||banner||'

' from dual; select '

ComponentDescription', 'Version' from dual; select '
'||comp_id comp, ''||comp_name des, ''||version ver from dba_registry order by 1; select '

' from dual; select '

' from dual; select '

' from dual; select '
Tablespaces
Tablespace', 'Total', 'Occuped', 'PCT', 'Max_free', 'Max_extent' from dual; select '
'|| a.tablespace_name tablespace, ''||to_char(sum(a.bytes),'999,999,999,999,999') total, ''||to_char(nvl(b.bytes,0),'999,999,999,999,999') occuped, ''||nvl(substr(to_char(trunc(b.bytes/sum(a.bytes)*100)),1,3),'0')||'%' pct, ''||to_char(nvl(c.bytes,0),'999,999,999,999,999') max_free, ''||to_char(nvl(b.max_extent,0),'999,999') max_extent from sys.dba_data_files a, v_tab_occ b, v_tab_free c where a.tablespace_name = b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) group by a.tablespace_name,b.bytes,c.bytes,b.max_extent order by a.tablespace_name; select '
TOTAL' tablespace, ''||to_char(round(sum(a.bytes)/(1024*1024)),'999,999,999,999')||' MB' total from sys.dba_data_files a; select ''||to_char(round(sum(b.bytes)/(1024*1024)),'999,999,999,999')||' MB' total, ' -' pct, ' -' max_free, ' -' max_extent from v_tab_occ b; select '
' from dual; select '

' from dual; select '

' from dual; select '
Segments
Segment Type', 'Used Space' from dual; select '
'|| segment_type, ''||to_char(sum(bytes),'999,999,999,999,999') total from sys.dba_segments group by segment_type order by 2 desc; select '
TOTAL', ''||to_char(round(sum(bytes)/(1024*1024)),'999,999,999,999,999')||' MB' total from sys.dba_segments; select '

' from dual; set numwidth 5 select '

' from dual; select '

' from dual; select '
Schema/Object Matrix
Owner', ' Tabs', ' Prts', ' Idxs', ' Trgs', ' Pkgs', ' Body', ' Proc', ' Func', ' Seqs', ' Syns', ' Views', ' MVws', ' Jobs', ' Type', ' Oper', ' LOB', ' XML', ' Total' from dual; select '
'||owner owner, ''||sum(decode(object_type, 'TABLE',1,0)) tabs, ''||sum(decode(object_type, 'TABLE PARTITION',1,0)) patrs, ''||sum(decode(object_type, 'INDEX',1,0)) idxs, ''||sum(decode(object_type, 'TRIGGER',1,0)) trgs, ''||sum(decode(object_type, 'PACKAGE',1,0)) pkgs, ''||sum(decode(object_type, 'PACKAGE BODY',1,0)) pbod, ''||sum(decode(object_type, 'PROCEDURE',1,0)) proc, ''||sum(decode(object_type, 'FUNCTION',1,0)) func, ''||sum(decode(object_type, 'SEQUENCE',1,0)) seqs, ''||sum(decode(object_type, 'SYNONYM',1,0)) syns, ''||sum(decode(object_type, 'VIEW',1,0)) viws, ''||sum(decode(object_type, 'MATERIALIZED VIEW',1,0)) mvs, ''||sum(decode(object_type, 'JOB',1,0)) jbs, ''||sum(decode(object_type, 'TYPE',1,0)) typ, ''||sum(decode(object_type, 'OPERATOR',1,0)) oper, ''||sum(decode(object_type, 'LOB',1,0)) lobb, ''||sum(decode(object_type, 'XML SCHEMA',1,0)) xml, ''||count(*) alls from sys.dba_objects group by owner order by owner; select '
TOTAL' total, ''||sum(decode(object_type, 'TABLE',1,0)) tabs, ''||sum(decode(object_type, 'TABLE PARTITION',1,0)) patrs, ''||sum(decode(object_type, 'INDEX',1,0)) idxs, ''||sum(decode(object_type, 'TRIGGER',1,0)) trgs, ''||sum(decode(object_type, 'PACKAGE',1,0)) pkgs, ''||sum(decode(object_type, 'PACKAGE BODY',1,0)) pbod, ''||sum(decode(object_type, 'PROCEDURE',1,0)) proc, ''||sum(decode(object_type, 'FUNCTION',1,0)) func, ''||sum(decode(object_type, 'SEQUENCE',1,0)) seqs, ''||sum(decode(object_type, 'SYNONYM',1,0)) syns, ''||sum(decode(object_type, 'VIEW',1,0)) viws, ''||sum(decode(object_type, 'MATERIALIZED VIEW',1,0)) mvs, ''||sum(decode(object_type, 'JOB',1,0)) jbs, ''||sum(decode(object_type, 'TYPE',1,0)) typ, ''||sum(decode(object_type, 'OPERATOR',1,0)) oper, ''||sum(decode(object_type, 'LOB',1,0)) lobb, ''||sum(decode(object_type, 'XML SCHEMA',1,0)) xml, ''||count(*) alls from sys.dba_objects; select '

' from dual; select '

' from dual; select '

' from dual; select '
Schema/Segments Size
Owner', ' Tables', ' Indexes', ' Total Size' from dual; select '
'||owner owner, ''||to_char(sum(decode(segment_type, 'TABLE',bytes,0)),'999,999,999,999,999') tabs, ''||to_char(sum(decode(segment_type, 'INDEX', bytes,0)),'999,999,999,999,999') idxs, ''||to_char(sum(bytes),'999,999,999,999,999') tot from sys.dba_segments group by owner order by owner; select '
TOTAL' total, ''||to_char(sum(decode(segment_type, 'TABLE',bytes,0)),'999,999,999,999,999') tabs, ''||to_char(sum(decode(segment_type, 'INDEX', bytes,0)),'999,999,999,999,999') idxs, ''||to_char(sum(bytes),'999,999,999,999,999') tot from sys.dba_segments; select '

Top

' from dual; select '

' "Invalid Objects" from dual; select '

' from dual; select '
Invalid Objects
Owner', 'Table', 'Index', 'Trigger', 'Package', 'P. Body', 'Proc.', 'Func.', 'Sequence', 'Synonym', 'View', 'Total' from dual; select '
'||owner ||''||sum(decode(object_type, 'TABLE',1,0)) ||''||sum(decode(object_type, 'INDEX',1,0)) ||''||sum(decode(object_type, 'TRIGGER',1,0)) ||''||sum(decode(object_type, 'PACKAGE',1,0)) ||''||sum(decode(object_type, 'PACKAGE BODY',1,0)) ||''||sum(decode(object_type, 'PROCEDURE',1,0)) ||''||sum(decode(object_type, 'FUNCTION',1,0)) ||''||sum(decode(object_type, 'SEQUENCE',1,0)) ||''||sum(decode(object_type, 'SYNONYM',1,0)) ||''||sum(decode(object_type, 'VIEW',1,0)) ||''||count(*) from sys.dba_objects where status <> 'VALID' group by owner order by owner; select '

' from dual; select '

' from dual; select '
Invalid Indexes
Owner', 'Indexes', 'Status' from dual; select '
'||owner ||''|| count(*) ||''|| status from sys.dba_indexes where status <> 'VALID' and partitioned <>'YES' group by owner, status order by owner; select '
Owner', 'Index Partitions', 'Status' from dual; select '
'||index_owner ||''|| count(*) ||''|| status from sys.dba_ind_partitions where status <> 'USABLE' group by index_owner, status order by index_owner; select '

' from dual; select '

' from dual; select '
Invalid Datafiles
Datafile', 'Status' from dual; select '
#'||file#||' - '||name ||''|| status ||''|| enabled from sys.v$datafile where status <> 'ONLINE' and status <> 'SYSTEM'; select '

' from dual; select '

' from dual; select '
Invalid Blocks
File#', 'Block#', 'Corruption Type' from dual; select '
#'||file#, ''|| block#, ''|| CORRUPTION_TYPE from sys.v$database_block_corruption; select '

' from dual; select '

' from dual; rem missing: UNDO, NEXTED TABLE, TYPE2 UNDO, ... I know but they are a bit less important here select '

' from dual; select '
Space Usage
Tablespace', 'Total (MB)', 'Tables', 'Table Part.s', 'Table SubP.s', 'Indexes', 'Index Part.s', 'LOBs', 'Clusters' from dual; select '
'||tablespace_name tablespace, ''||to_char(round(sum(bytes/1048576)), '999,999,999'), ''||to_char(sum(decode(segment_type,'TABLE',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'TABLE PARTITION',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'TABLE SUBPARTITION',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'INDEX',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'INDEX PARTITION',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(substr(segment_type,1,3),'LOB',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'CLUSTER',round(bytes/1048576),0)), '999,999,999,999') from sys.dba_extents group by tablespace_name order by tablespace_name; select '
TOTAL (MB)', ''||to_char(round(sum(bytes/1048576)), '999,999,999'), ''||to_char(sum(decode(segment_type,'TABLE',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'TABLE PARTITION',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'TABLE SUBPARTITION',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'INDEX',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'INDEX PARTITION',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(substr(segment_type,1,3),'LOB',round(bytes/1048576),0)), '99,999,999,999,999'), ''||to_char(sum(decode(segment_type,'CLUSTER',round(bytes/1048576),0)), '999,999,999,999') from sys.dba_extents; select '

' from dual; select '

' from dual; select '
Container', 'Bytes' from dual; select '
Recycle Bin', ''||to_char(sum(space*8)*1024,'999,999,999,999') from dba_recyclebin; select '

' from dual; select '

' from dual; select '

' from dual; select '
Partitioning
Owner', '#Partitioned Tables' from dual; select '
', table_owner,'', count(distinct table_name) from dba_tab_partitions where table_owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') group by table_owner order by table_owner; select '

' from dual; select '

' from dual; select '
Partitioning Details
Owner', 'Table', 'Partitions', 'Rows', 'Est. Size' from dual; select '
',table_owner, '', TABLE_NAME, '', count(*), '',to_char(sum(num_rows),'999,999,999,999,999'), '',to_char(sum(blocks/128),'999,999,999,999') from dba_tab_partitions where table_owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') group by TABLE_OWNER, TABLE_NAME order by TABLE_OWNER, TABLE_NAME; select '

' from dual; select '

' from dual; select '
Partitions Details
Owner', 'Table', 'Tablespace', 'Partition', 'Rows', 'Sub.Partitions' from dual; select '
', table_owner,'', table_name,'', tablespace_name,'', partition_name,'', to_char(num_rows,'999,999,999,999'),'', subpartition_count from dba_tab_partitions where table_owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and rownum < 101 order by table_owner,table_name,partition_position; select '
...

' from dual; select '' from dual; select '

' from dual; select '
Parallel degree
Degree', 'Instances', '#Tables' from dual; select '
', degree,'', instances,'', count(*) from dba_tables where owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') group by degree, instances order by degree desc, instances desc; select '

' from dual; select '' from dual; select '

' from dual; select '
Compression
OwnerCompression', '#Tables' from dual; select '
', owner,'', compression,'', count(*) from dba_tables where owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and compression='ENABLED' group by owner, compression order by owner, compression; select '
TOTALCompressed Tables', count(*) from dba_tables where owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and compression='ENABLED'; select '
OwnerCompression', '#Indexes' from dual; select '
', owner,'', compression,'', count(*) from dba_indexes where owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and compression='ENABLED' group by owner, compression order by owner, compression; select '
TOTALCompressed Indexes', count(*) from dba_indexes where owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and compression='ENABLED'; select '
OwnerCompression', '#Partitions' from dual; select '
', table_owner,'', compression,'', count(*) from dba_tab_partitions where table_owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and compression='ENABLED' group by table_owner, compression order by table_owner, compression; select '
TOTALCompressed Tablespaces', count(*) from dba_tab_partitions where table_owner not in ('SYS','SYSTEM','SYSMAN','WMSYS') and compression='ENABLED'; select '

' from dual; select '

' from dual; select '

' from dual; select '
Oracle Spatial
Owner', 'Spatial Tables' from dual; select '
', owner,'', count(*) from all_sdo_geom_metadata group by owner; select '
TOTAL', count(*) from all_sdo_geom_metadata; select '

Top

' from dual; select '

' from dual; select '

' from dual; select '
SGA
SGA element', 'Bytes', 'MB' from dual; select '
'||substr(name,1,25), ''||to_char(value,'999,999,999,999'), ''||to_char(value/(1024*1024),'999,999,999,999') from sys.v_$sga order by value desc; select '

' from dual; select '

' from dual; select '
Memory Usage
Pool', 'Name','MB' from dual; select '
'||pool, ''||name, ''||to_char(bytes/(1024*1024),'999,999,999,999') from (select pool, name, bytes from V$sgastat order by bytes desc) where rownum <=20; select '
...

' from dual; select '

' from dual; select '
Free Memory
Pool', 'Name','MB' from dual; select '
'||pool, ''||name, ''||to_char(bytes/(1024*1024),'999,999,999,999') from V$sgastat where name like 'free memory%'; select '

' from dual; select '

' from dual; select '
Parameters
Parameter', 'Value','IsDefault' from dual; select '
'||name||''||to_char(value,'999,999,999,999')||''||isdefault from sys.v$parameter where name in ('sga_target', 'sga_max_size', 'db_cache_size', 'shared_pool_size', 'memory_target', 'large_pool_size', 'java_pool_size', 'streams_pool_size', 'inmemory_size', 'memory_max_target', 'log_buffer', 'db_keep_cache_size', 'db_recycle_cache_size') order by isdefault, name; select '

' from dual; select '

' from dual; select '

' from dual; select '
Datafiles
Tablespace', 'Datafile', 'Bytes', 'Read #', 'Write #' from dual; select '
'||tablespace_name tablespace, ''||file_name data_file, ''||to_char(bytes,'999,999,999,999,999'), ''||to_char(phyrds,'999,999,999,999') "Read #", ''||to_char(phywrts,'999,999,999,999') "Write #" from sys.dba_data_files, v$filestat where file_id=file# order by tablespace_name,file_name; select '

' from dual; select 'Autoextend datafiles: ' from dual; select file_name data_file from sys.dba_data_files where autoextensible='YES'; select '

Not autoextensible datafiles: ' from dual; select file_name data_file from sys.dba_data_files where autoextensible<>'YES'; select '

' from dual; set numwidth 8 select '

' "Rollbacks" from dual; select '

' from dual; select '
Rollbacks
Rollback Segment', 'Tablespace', 'Bytes', 'Extents', 'Status' from dual; select '
'||substr(a.segment_name,1,25) rollback_segment, ''||substr(a.tablespace_name,1,25) tablespace, ''||to_char(sum(bytes),'999,999,999,999') bytes, ''||substr(max(extent_id)+1,1,7) extents, ''||substr(status,1,7) status from sys.dba_extents a, sys.dba_rollback_segs b where a.segment_name = b.segment_name and segment_type='ROLLBACK' group by a.tablespace_name,a.segment_name,status order by a.tablespace_name,a.segment_name; select '

' from dual; select '

' from dual; select '
Undo Parameters
Parameter', 'Value' from dual; select '
'||name||''||value from sys.v$parameter where name like 'undo%' order by name; select '

' from dual; select '

' from dual; select '
Undo Datafiles
Tablespace', 'Datafile', 'Bytes', 'Autoextensible' from dual; select '
'||tablespace_name tablespace, ''||file_name data_file, ''||to_char(bytes,'999,999,999,999,999'), ''||autoextensible from sys.dba_data_files where tablespace_name like 'UNDO%' order by tablespace_name,file_name; select '

' from dual; select '

' from dual; select '
Undo Extents
Tablespace', 'Status', '#', 'Bytes' from dual; select '
'||tablespace_name tablespace, ''||status, ''||count(*), ''||to_char(sum(BYTES),'999,999,999,999,999') from sys.DBA_UNDO_EXTENTS group by tablespace_name,status order by tablespace_name,status; select '

' from dual; select '

' from dual; select '
Tuned retention
AVG', 'MAX', 'MIN', 'Std DEV' from dual; select '
'||round(avg(TUNED_UNDORETENTION)), ''||max(TUNED_UNDORETENTION), ''||min(TUNED_UNDORETENTION), ''||round(stddev(TUNED_UNDORETENTION)) from v$undostat; select '

' from dual; select '

' from dual; select '

' from dual; select '
Log Files
Group#', 'Log File', 'Status', 'Bytes', 'Thread' from dual; select '
'||sys.v_$logfile.group# group_id, ''||member log_file, ''||sys.v_$log.status || ' ' ||sys.v_$logfile.status group_status, ''||to_char(bytes,'999,999,999,999') bytes, ''||thread# from sys.v_$logfile, sys.v_$log where sys.v_$logfile.group# = sys.v_$log.group# order by thread#, 1; select '

' from dual; select '

' from dual; select '' from dual; select '
Log SwitchesDaily
Date', ' Count' from dual; select '
'||trunc(first_time) switch_date, ''||to_char(count(*),'999,999,999,999') counter from sys.v_$log_history where first_time > sysdate -31 group by trunc(first_time) order by trunc(first_time) desc; select '
' from dual; select '		' from dual; select '
Log SwitchesHourly
Date',' Count' from dual; select '
'||to_char(first_time, 'YYYY-MM-DD HH24')||':00:00' switch_date, ''||to_char(count(*),'999,999,999,999') counter from sys.v_$log_history where first_time > sysdate -1.3 group by to_char(first_time, 'YYYY-MM-DD HH24') order by to_char(first_time, 'YYYY-MM-DD HH24') desc; select '

' from dual; select '

Archived Logs' from dual; select '
Creator','Registrar','Status','Archived','Count' from dual; select '
'||creator, ''||registrar, ''||status, ''||archived, ''||to_char(count(*),'999,999,999,999') counter from v$archived_log where deleted='NO' group by creator, registrar, archived, status order by status,creator,registrar; select '

Top

' from dual; select '

' "Control Files" from dual; select '

Control File Informations

' from dual;
set heading on
column type format a32
select value files
 from v$parameter
 where name='control_files';
select *
 from v$controlfile_record_section;
set heading off
select '

' from dual; select '

' "Recovery Area" from dual; select '

Recovery Area Usage 

' from dual;
set heading on
column RECOVERY_DEST_SIZE format a50
column FREE_RECOVERY_PCT format a20
select substr(name||': '||value||'  ('||round(value/(1024*1024*1024))||'GB)',1,60) Recovery_Dest_Size 
from v$parameter where name='db_recovery_file_dest_size';
select * from v$flash_recovery_area_usage;
select trunc(100-sum(PERCENT_SPACE_USED)-sum(PERCENT_SPACE_RECLAIMABLE))||'%' Free_Recovery_pct  from v$flash_recovery_area_usage;
set heading off
select '

Top

' from dual; select '

' "Users" from dual; select '

' from dual; select '
Users
Username', 'Default Tablespace', 'Temporary Tablespace', 'Status', 'Profile', 'Expiry date' from dual; select '
'||substr(username,1,25) username, ''||substr(default_tablespace,1,25) default_tablespace, ''||substr(temporary_tablespace,1,25) temp_tablespace, ''||account_status, ''||profile, ''||expiry_date from sys.dba_users order by username; select '
TOTAL'||count(*)||'OPEN: '||sum(decode(account_status,'OPEN',1,0)) from sys.dba_users; select '

' from dual; select '

' "Profile" from dual; select '

' from dual; select '
DEFAULT Profile
Resource', 'Limit' from dual; select '
'||resource_name, ''||limit from sys.dba_profiles where profile='DEFAULT' order by resource_name; select '

' from dual; select '

' "PW users" from dual; select '

Password file users

' from dual;
set heading on
column username format a40
select USERNAME,INST_ID,SYSDBA,SYSOPER
  from gv$pwfile_users
 order by INST_ID,USERNAME;
set heading off
select '
' from dual; select '

' "defaultpw" from dual; select '

' from dual; select '
Users with default passwords
Username', 'Status' from dual; select '
',username, '', account_status from dba_users where password in ('E066D214D5421CCC', '24ABAB8B06281B4C', '72979A94BAD2AF80', '9AAEB2214DCC9A31', 'C252E8FA117AF049', 'A7A32CD03D3CE8D5', '88A2B2C183431F00', '7EFA02EC7EA6B86F', '9B616F5489F90AD7', '4A3BA55E08595C81', 'F894844C34402B67', '3F9FBD883D787341', '79DF7A1BD138CF11', '7C9BA362F8314299', '88D8364765FCE6AF', 'F9DA8977092B7B81', '9300C0977D7DC75E', 'A97282CE3D94E29E', 'AC9700FD3F1410EB', 'E7B5D92911C831E1', 'AC98877DE1297365', '66F4EF5650C20355', '84B8CBCA4D477FA3', 'D4C5016086B2DC6A', '5638228DAF52805F', 'D4DF7931AB130E37', 'D728438E8A5925E0', '545E13456B7DDEA0', '2FFDCBB4FD11D9DC', '56DB3E89EAE5788E', '402B659C15EAF6CB', '71E687F036AD56E5', '24ABAB8B06281B4C', 'A13C035631643BA0', '72979A94BAD2AF80', '4A3BA55E08595C81', '355CBEC355C10FEF', '80294AE45A46E77B', 'E7B5D92911C831E1', 'E74B15A3F7A19CA8', 'BEAA1036A464F9F0', 'B1344DC1B5F3D903', '58872B4319A76363', 'F894844C34402B67', '8A8F025737A9097A', '4DE42795E66117AE', '639C32A115D2CA57', '447B729161192C24', '8BF0DA8E551DE1B9', '482B65ME0BEAF6BB', '2D594E86F93B17A1', '970BAA5B81930A40') order by account_status desc, username; select '

See also 11g Users.

' from dual; select '

' "Licensing info" from dual; select '

' from dual; select '
Licensing
Detected Edition:', '' from dual; select 'Enterprise' from sys.v_$version where banner like '%Enterprise%' and banner like 'Oracle%'; select 'XE (Express)' from sys.v_$version where banner like '%Express%' and banner like 'Oracle%'; select 'Standard' from (select banner from sys.v_$version where banner like 'Oracle%') a where banner not like '%Enterprise%' and banner not like '%Express%'; select '
Parameter', 'Value' from dual; select '
USER max'|| users_max, '
SESSION max'|| sessions_max, '
SESSION curr./HiW.'|| sessions_current|| ' / ' || sessions_highwater from v$license; select '
CPU curr./HiW.'|| cpu_count_current|| ' / ' || cpu_count_highwater, '
CORE curr./HiW.'|| cpu_core_count_current|| ' / ' || cpu_core_count_highwater, '
SOCKET curr./HiW.'|| cpu_socket_count_current|| ' / ' || cpu_socket_count_highwater from v$license; select '
Diagnostic and tuning pack enabled'|| value from sys.v$parameter where name in ('control_management_pack_access'); select '
In-Memory enabled (12c)'|| value from sys.v$parameter where name in ('inmemory_query'); select '
Max PDBS (12cR2)'|| value from sys.v$parameter where name in ('max_pdbs'); select '

' from dual; select '

' from dual; select '
Options
Installed', 'Not installed
' from dual; select parameter ||', ' from v$option where value='TRUE'; select '' from dual; select parameter ||', ' from v$option where value='FALSE'; select '

' from dual; select '

' from dual; select '
Features Used
Name', 'Count', 'First', 'Last', 'Description' from dual; select '
'|| name, ''|| sum(DETECTED_USAGES), ''|| min(FIRST_USAGE_DATE), ''|| max(LAST_USAGE_DATE), ''|| DESCRIPTION || ' - ' || replace(replace(max(dbms_lob.substr(feature_info,128,1)),'<','<'),'>','>') from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED='TRUE' group by name, DESCRIPTION order by name; select '

' from dual; select '
Features NOT in Use
Name', 'Count', 'Description' from dual; select '
'|| name, ''|| sum(DETECTED_USAGES), ''|| DESCRIPTION from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED='FALSE' group by name, DESCRIPTION order by name; select '

' from dual; select '

' from dual; select '
High-Water Mark Statistics
Name', 'Maximum Value', 'Description' from dual; select '
'|| name, ''|| HIGHWATER, ''|| DESCRIPTION from DBA_HIGH_WATER_MARK_STATISTICS; select '

Top

' from dual; select '

' "Sessions" from dual; select '

' from dual; select '
Per-User sessions
User', 'InstID', 'Count', 'Active' from dual; select '
'||s.schemaname username, ''||s.inst_id, '', count(*), '', sum(decode(status,'ACTIVE',1,0)) from gv$process p, gv$session s where s.paddr = p.addr and s.inst_id = p.inst_id and type='USER' group by s.schemaname, s.inst_id order by 4 desc; select '
TOTAL (', count(distinct s.schemaname), ' distinct users )', max(s.inst_id), '', count(*), '', sum(decode(status,'ACTIVE',1,0)) from gv$process p, gv$session s where s.paddr = p.addr and s.inst_id = p.inst_id and type='USER'; select '

' from dual; select '

' from dual; select '
Current sessions
SID,serial', 'User', 'OS User', 'Process', 'Type', 'Status', 'Command', 'Program', 'Module', 'InstID', 'Logon' from dual; select '
'|| s.sid||','||s.serial# sid, ''||s.schemaname username, ''||s.osuser os_user, ''||p.spid process, ''||s.type type, ''||s.status status, ''||decode(s.command, 1,'Create table',2,'Insert',3,'Select', 4,'Create cluster',5,'Alter cluster',6,'Update',7,'Delete', 8,'Drop',9,'Create index',10,'Drop index',11,'Alter index', 12,'Drop table',15,'Alter table', 16, 'Drop Seq.', 17,'Grant',18,'Revoke', 19,'Create synonym',20,'Drop synonym',21,'Create view', 22,'Drop view',23,'Validate index',24,'Create procedure',25,'Alter procedure', 26,'Lock table',27,'No operation',28,'Rename', 29,'Comment',30,'Audit',31,'Noaudit',32,'Create ext. database', 33,'Drop ext. database',34,'Create database',35,'Alter database', 36,'Create rollback segment',37,'Alter rollback segment', 38,'Drop rollback segment',39,'Create tablespace', 40,'Alter tablespace',41,'Drop tablespace',42,'Alter session', 43,'Alter user',44,'Commit',45,'Rollback',46,'Savepoint', 47,'PL/SQL Exec',48,'Set Transaction', 60,'Alter trigger',62,'Analyze Table', 63,'Analyze index',71,'Create Snapshot Log', 72,'Alter Snapshot Log',73,'Drop Snapshot Log', 74,'Create Snapshot',75,'Alter Snapshot', 76,'Drop Snapshot',79,'Alter Role', 85,'Truncate table',86,'Truncate Cluster', 88,'Alter View',91,'Create Function',92,'Alter Function',93,'Drop Function', 94,'Create Package',95,'Alter Package',96,'Drop Package', 97,'Create PKG Body',98,'Alter PKG Body',99,'Drop PKG Body', 0,'No command', 'Other') Command, ''||substr(s.program,1,64) program, ''||module, ''||s.inst_id, ''||to_char(logon_time, 'YYYY-MM-DD HH24:MI:SS') from gv$process p, gv$session s where s.paddr = p.addr and s.inst_id = p.inst_id order by s.type, s.status, s.inst_id, s.sid; select '

' from dual; select '

' "Current SQL" from dual; select '

' from dual; select '
SQL
SID', 'User', 'Exec', 'Parse', 'Read', 'Get', 'Running SQL' from dual; select '
'||s.sid, ''||s.username, ''||q.executions exec, ''||q.parse_calls parse, ''||q.disk_reads read, ''||q.buffer_gets get , ''||replace(replace(q.sql_text,'<','<'),'>','>') sql from gv$session s, gv$sql q where s.sql_address=q.address and s.type <> 'BACKGROUND' and s.status = 'ACTIVE' and s.username <> 'SYS' and s.inst_id = q.inst_id order by s.sid; select '

' from dual; select '

' "Locks" from dual; select '

' from dual; select '
Lock
SID', 'Lock Type', 'Lock Mode', 'Request', 'Lock Count' from dual; select '
'||l.sid, ''||l.type, ''||decode(l.lmode, 0, 'WAITING', 1,'Null', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6,'Exclusive', l.lmode) lock_mode, ''||decode(l.request, 0,'HOLD', 1,'Null', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6,'Exclusive', l.request) request, '', count(*) lock_id from gv$lock l group by l.sid, l.type, l.lmode, l.request order by l.sid, l.type, l.lmode, l.request; select '

Top

' from dual; select '

' from dual; select '

Performance statistics
' from dual;
select 'A)  Hit ratio buffer cache (>80%): '||
  to_char(round(1-(
   sum(decode(name,'physical reads',1,0)*value) 
   /(sum(decode(name,'db block gets',1,0)*value) 
   +sum(decode(name,'consistent gets',1,0)*value))
  ), 3)*100) || '%'  statistic
 from v$sysstat
 where name in ('db block gets', 'consistent gets', 'physical reads')
 union
select 'B1) Misses library cache (<1%): '
  ||to_char(round(sum(reloads)/sum(pins)*100, 3)) || '%' 
 from v$librarycache
 union
select 'B1.'||ROWNUM||') Detailed misses library cache ('
  ||namespace || '-' ||to_char(pins)
  ||'): '||to_char(round(decode(pins,0,0,reloads/pins*100), 3))
  || '%' Statistica
 from v$librarycache
 union
select 'B2) Misses dictionary cache (<10%): '
  ||to_char(round(sum(getmisses)/sum(gets)*100, 3)) || '%' 
 from v$rowcache
 union
select 'C1) System undo header frequence (<1%): '
  ||to_char(round(avg(count)/sum(value)*100, 3)) || '%' 
 from v$waitstat w, v$sysstat s
 where w.class='system undo header' and
  name in ('db_block_gets', 'consistent gets')
 union
select 'C2) System undo block frequence (<1%): '
  ||to_char(round(avg(count)/sum(value)*100, 3)) || '%' 
 from v$waitstat w, v$sysstat s
 where w.class='system undo block' and
  name in ('db_block_gets', 'consistent gets')
 union
select 'C3) Undo header frequence (<1%): '
  ||to_char(round(avg(count)/sum(value)*100, 3)) || '%' 
 from v$waitstat w, v$sysstat s
 where w.class='undo header' and
  name in ('db_block_gets', 'consistent gets')
 union
select 'C4) Undo block frequence (<1%): '
  ||to_char(round(avg(count)/sum(value)*100, 3)) || '%' 
 from v$waitstat w, v$sysstat s
 where w.class='undo block' and
  name in ('db_block_gets', 'consistent gets')
 union
select 'D)  Redo log space req. (near 0): '||to_char(value)  
 from v$sysstat
 where name ='redo log space requests'
 union
select 'E1) Hit ratio redo alloc (<1%): '
  ||decode(gets,0,'NA',to_char(round(misses/gets*100, 3)) || '%' )
 from v$latch
 where latch#=15
 union
select 'E2) Hit ratio immediate redo alloc (<1%): '
  ||decode(immediate_gets,0,'NA',
   to_char(round(immediate_misses/immediate_gets*100, 3)) || '%' )
 from v$latch
 where latch#=15
 union
select 'E3) Hit ratio redo copy (<1%): '
  ||decode(gets,0,'NA',to_char(round(misses/gets*100, 3)) || '%') 
 from v$latch
 where latch#=16
 union
select 'E4) Hit ratio immediate redo copy (<1%): '
  ||decode(immediate_gets,0,'NA',
   to_char(round(immediate_misses/immediate_gets*100, 3)) || '%' )
 from v$latch
 where latch#=16
 union
select 'F)  Free list contention (<1%): '
  || to_char(round(count/value*100, 3)) || '%' 
 from v$waitstat w, v$sysstat s
 where w.class='free list' and
  name in ('consistent gets')
 union
select 'G1) Sorts in memory: '||to_char(value)  
 from v$sysstat
 where name in ('sorts (memory)')
 union
select 'G2) Sorts on disk: '||to_char(value)  
 from v$sysstat
 where name in ('sorts (disk)')
 union
select 'H1) Short tables full scans: '||to_char(value)  
 from v$sysstat
 where name in ('table scans (short tables)')
 union
select 'H2) Long tables full scans: '||to_char(value)  
 from v$sysstat
 where name in ('table scans (long tables)')
 union
select 'I1 @'||inst_id||') Logon: '||to_char(value)  
 from gv$sysstat
 where name in ('logons cumulative')
 union
select 'I2 @'||gv$sysstat.inst_id||') Commit: '||to_char(value) ||
       ' TPS: '|| to_char( round(value/((sysdate-startup_time)*24*60*60),5) )
 from gv$sysstat,gv$instance
 where name in ('user commits') and gv$sysstat.inst_id=gv$instance.inst_id
 union
select 'I3 @'||inst_id||') Rollback: '||to_char(value)  
 from gv$sysstat
 where name in ('user rollbacks')
 union
select 'I4 @'||gv$sysstat.inst_id||') Exec: '||to_char(value) ||
       ' SQL/sec: '|| to_char( round(value/((sysdate-startup_time)*24*60*60),5) )
 from gv$sysstat,gv$instance
 where name in ('execute count') and gv$sysstat.inst_id=gv$instance.inst_id
 union
select 'L1 @'||gv$sysstat.inst_id||') DBcpu: '||to_char( round((value/100)/((sysdate-startup_time)*24*60*60),5) )  
 from gv$sysstat,gv$instance
 where name in ('DB time') and gv$sysstat.inst_id=gv$instance.inst_id
;
select '

' from dual; select '

' from dual; select '
Stale Statistics
Owner', 'Table Stale Stats#', 'Last Analyzed' from dual; select '
'||OWNER||''||count(*)||''||max(to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')) from dba_tab_statistics where STALE_STATS='YES' group by owner order by owner; select '
Owner', 'Index Stale Stats#', 'Last Analyzed' from dual; select '
'||OWNER||''||count(*)||''||max(to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')) from dba_ind_statistics where STALE_STATS='YES' group by owner order by owner; select '

' from dual; select '

' from dual; select '
I/O Statistics
Name', 'Value', 'Metric' from dual; select '
Small Reads'|| sum(decode(name,'physical read total IO requests',value,0)- decode(name,'physical read total multi block requests',value,0)), '
Small Writes'|| sum(decode(name,'physical write total IO requests',value,0)- decode(name,'physical write total multi block requests',value,0)), '
Large Reads'|| sum(decode(name,'physical read total multi block requests',value,0)), '
Large Writes'|| sum(decode(name,'physical write total multi block requests',value,0)), ''|| round(sum(decode(name,'physical read total IO requests',value,'physical write total IO requests',value, 'physical read total multi block requests',value,'physical write total multi block requests',value,0)- decode(name,'physical read total multi block requests',value,'physical write total multi block requests',value,0)) /((sysdate-startup_time)*24*60*60),5), 'IOP/s', '
Total MB Read'|| trunc(sum(decode(name,'physical read total bytes',value,0))/(1024*1024)), ''|| round(sum(decode(name,'physical read total bytes',value,0))/(1024*1024) /((sysdate-startup_time)*24*60*60),5), 'MB/s', '
Total MB Written'|| trunc(sum(decode(name,'physical write total bytes',value,0))/(1024*1024)) from v$sysstat,v$instance group by startup_time; select '

Top

' from dual; select '

' "Biggest Objects" from dual; select '

' from dual; select '
Biggest Objects
Object', 'Type', 'Owner', 'Tablespace', 'Bytes' from dual; select '
'||segment_name, ''||segment_type, ''||owner, ''||tablespace_name, ' '||to_char(bytes,'999,999,999,999,999') from v_big_obj where rownum <= 32 order by bytes desc; select '
...

' from dual; select '

' "Most Fragmented Objects" from dual; select '

' from dual; select '
Most Fragmented Objects
Object', 'Type', 'Owner', 'Tablespace', 'Extents', 'Bytes' from dual; select '
'||segment_name, ''||segment_type, ''||owner, ''||tablespace_name, ' '||to_char(extents,'999,999,999'), ' '||to_char(sum(bytes),'999,999,999,999,999') from v_frg_obj where rownum <= 32 group by segment_name, segment_type, owner, tablespace_name, extents order by extents desc; select '
...

' from dual; select '

' "PL/SQL" from dual; select '

' from dual; select '', '
PL/SQL
User', 'Type', 'Objects', 'Lines' from dual; select '
'||owner||''||type, ''||to_char(count(distinct name), '999,999,999')|| ''||to_char(count(*), '999,999,999') from dba_source group by owner, type order by owner, type; select '
TOTAL'||type, ''||to_char(count(distinct name||owner), '999,999,999')|| ''||to_char(count(*), '999,999,999') from dba_source group by type order by type; select '

' from dual; select '' "Libraries" from dual; select '

' from dual; select '', '
Libraries
Owner', 'Library', 'File', 'Status', 'Dynamic' from dual; select '
'||owner,''||library_name,''||file_spec,''||status,''||dynamic from all_libraries where owner not in ('SYS','XDB','MDSYS','ORDSYS'); select '

Top' from dual; select '

' "Data Type" from dual; select '

'
  from dual;
select '',
 '
Data Type Usage
User',
 'Data Type',
 '#',
 'Max Length',
 'Max Precision'
  from dual;
select '
'||owner||''|| data_type,
	''||to_char(count(*), '999,999,999')||
	''||to_char(max(DATA_LENGTH), '999,999,999')||
	''||to_char(max(DATA_PRECISION), '999,999,999')
  from all_tab_columns
 where owner not in ('SYS','XDB','MDSYS','ORDSYS')
 group by owner, data_type
 order by owner, data_type;

select '
TOTAL'|| data_type,
	''||to_char(count(*), '999,999,999')||
	''||to_char(max(DATA_LENGTH), '999,999,999')||
	''||to_char(max(DATA_PRECISION), '999,999,999')
  from all_tab_columns
 where owner not in ('SYS','XDB','MDSYS','ORDSYS')
 group by data_type
 order by data_type;
select '

' from dual; select '

' "JOBS" from dual; select '

' from dual; select '
Jobs
Job Id', 'User', 'Interval', 'Command', 'Total Time' from dual; select '
'||job||''||schema_user||''||interval||''||what||''||round(total_time) from dba_jobs; select '
' from dual; select '

' from dual; select '
Scheduler Jobs
Job Name', 'User', 'Interval', 'Start', 'Command', 'Count', 'Last Duration', 'Enabled' from dual; select '
'||job_name||''||owner||''||repeat_interval||''||start_date, ''||job_action, program_name, ''||run_count||''||last_run_duration||''||enabled from dba_scheduler_jobs; select '
' from dual; select '

' from dual; select '
Running Jobs
Job Id','SID', 'Last', 'Failures' from dual; select /*+ rule */ '
'||job||''||sid||''||last_date||''||failures from dba_jobs_running; select '
' from dual; select '

' from dual; select '
Last executed Jobs
Log Id','Name', 'Log Date', 'Actual Date', 'Status', 'Errors' from dual; select * from (SELECT '
'||l.log_id, ''||l.job_name, ''||TO_CHAR (l.log_date, 'YYYY/MM/DD HH24:MI:SS.FF TZH:TZM'), ''||TO_CHAR (r.actual_start_date,'YYYY/MM/DD HH24:MI:SS.FF TZH:TZM'), ''||r.status, ''||r.errors FROM dba_scheduler_job_log l, dba_scheduler_job_run_details r WHERE l.log_id = r.log_id(+) ORDER BY l.log_date DESC) where rownum <20; select '
' from dual; select '

' from dual; select '
Data Pump Jobs
Owner', 'Job Name', 'State' from dual; select '
'||owner_name||''||job_name||''||state from dba_datapump_jobs; select '

' from dual; select '

' "RMAN" from dual; select '

' from dual; select '
RMAN Configuration
Parameter', 'Value' from dual; select '
'||name||''||value from v$rman_configuration order by conf#; select '

Top

' from dual; select '

' "Remote Database Links" from dual; select '

' from dual; select '
Database Links
OwnerDB Link', 'User', 'Instance' from dual; select '
'||owner||''||db_link||''||username||''||host from dba_db_links order by host, username, owner, db_link; select '
' from dual; select '

' from dual; select '
Directories
OwnerDirectoryPath' from dual; select '
'||owner||''||directory_name||''||directory_path from dba_directories order by owner, directory_name; select '

Top

' from dual; select '

' "Oracle Parameters" from dual; select '

' from dual; select '
Oracle Parameters
Parameter', 'Value' from dual; select '
'||name||''||value from sys.v$parameter where isdefault ='FALSE' order by name; select '
Hidden Parameters', 'Value' from dual; select '
'||ksppinm||''||ksppstvl from x$ksppcv cv, x$ksppi pi where cv.indx = pi.indx and translate(ksppinm,'_','#') like '#%' and bitand(ksppiflg/256,1) <> 1 and ksppinm like '%optimizer%' order by ksppinm; select '

' from dual; select '

'
 from dual;
select '
All Parameters
Parameter',
 'Value'
from dual;
select '
'||name||''||value
from sys.v$parameter
order by name; 
select '

Top

' from dual; select '

' "NLS Settings" from dual; select '

' from dual; select '
NLS Settings
Parameter', 'Value' from dual; select '
'||name||''||value$ from sys.props$ where name like 'NLS%CHARACTER%' order by name; select '

' from dual; select '

' "OS" from dual; select '

' from dual; select '
Operating System Infos
Parameter', 'Value' from dual; select '
Platform'||platform_name from v$database; select '
'||stat_name||''||to_char(value, '999,999,999,990.0') from v$osstat where stat_name in ('LOAD','PHYSICAL_MEMORY_BYTES','NUM_CPUS') order by stat_name; select '

' from dual; select '

' from dual; select '
Timezone info
SYSDATE', 'CURRENT_DATE', 'DB TIMEZONE', 'Session TIMEZONE', 'OS TIMEZONE' from dual; SELECT '
'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') sys_date ,''||TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH24:MI') cur_date ,''||DBTIMEZONE DB_TZ ,''||SESSIONTIMEZONE SESS_TZ ,''||TO_CHAR(SYSTIMESTAMP, 'TZR') OS_TZ FROM DUAL; select '

Top

' from dual; rem set long 100000 set pagesize 9000 SELECT dbms_xdb.cfg_get FROM dual; select '

Plugins

' h from dual; start custom.sql select '

Top

' h from dual; select '

Generating migration scripts in log directory...
' h from dual; select '

Statistics generated on: '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'

' from dual; select 'For more info contact ' from dual; select 'Meo Bogliolo.

' from dual; set newpage 1 spool off drop view v_tab_occ; drop view v_tab_free; drop table v_big_obj; drop table v_frg_obj; drop table v_log_sd; REM Generate schema migration scripts (great contribution by G. Tagliafico) set define on @SCHEMA_INFO_4_EXPIMP exit