Database Configuration Review Scripts

SET MARKUP HTML ON SPOOL ON
set echo off
set feedback off spool configReview.html

select * from v$instance;
Set lines 300
Set pages 55
SELECT * FROM NLS_DATABASE_PARAMETERS;
select sum(BYTES)/1024/1024/1024/1024 from dba_data_files;

select sum(BYTES)/1024/1024 from dba_temp_files;
select flashback_on from v$database;
select PROTECTION_MODE from v$database;
rem AIX mounted disk: mount
rem AIX free space – df -k
rem AIX swap space - lsps –a
host swap –l # solaris swap
column COMP_NAME form a35 col status form a11
col COMP_ID form a10
col VERSION form a11
select comp_id, comp_name, status, version from dba_registry;
col comments form a25
col NAMESPACE form a6
col action_time form a30
col ACTION form a15
col BUNDLE_SERIES form a3
select * from dba_registry_history;

clear columns
col INITIAL_ALLOCATION form a12
col LIMIT_VALUE form a10
select * from v$resource_limit;

col value form 999,999,999,999
col "APPROX. MB" form 999,999
select NAME, VALUE, value/1024/1024 "APPROX. MB" from v$sga;

clear columns
set lines 3000
col name form a60
select * from v$controlfile;

clear columns
set lines 3000
col member form a50
col GROUP# form 99
col MB form 99,999
col status form a9
select MEMBER, a.GROUP#, BYTES/1024/1024 MB, MEMBERS, ARCHIVED,
a.STATUS
from v$log a,
v$logfile b
where a.group# = b.group#
order by a.group#;

Clear column
col tablespace_name form a20
col max_frag_mb form 999,999,999
col size_mb form 99,999,999
col pctusd form 999.9

select total.tablespace_name tablespace_name,
a.status,
a.contents,
a.extent_management,
total.bytes/1024/1024 SIZE_MB,
count(free.bytes) NUM_FRAGS,
nvl(max(free.bytes)/1024/1024,0) MAX_FRAG_MB,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from dba_tablespaces a,
(select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) total,
dba_free_space free
where total.tablespace_name = free.tablespace_name(+)
and a.tablespace_name = total.tablespace_name
group by total.tablespace_name,
a.status,
a.contents,
a.extent_management,
total.bytes
order by pctusd desc;

col file_name form a50
select FILE_NAME, TABLESPACE_NAME, STATUS, BYTES/1024/1024 MB
from dba_temp_files;

col file_name form a45
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, STATUS,AUTOEXTENSIBLE from dba_data_files;

select owner, tablespace_name, count(*) TABLES from dba_tables where owner not like '%SYS%' and owner not in ('DBSNMP','OUTLN','XDB') group by owner, tablespace_name
order by owner, tablespace_name;

select owner, tablespace_name, count(*) INDEXES
from dba_indexes
where owner not like '%SYS%' and owner not in ('DBSNMP','OUTLN','XDB')
group by owner, tablespace_name
order by owner, tablespace_name;
select owner, table_name
from dba_tables a
where not exists (select 1 from dba_indexes b
where a.owner = b.owner
and a.table_name = b.table_name);

select owner, table_name, count(*)
from dba_indexes
where owner not like '%SYS%' and owner not in ('XDB')
group by owner, table_name
having count(*) > 5
order by 3 desc;

SELECT segment_owner, segment_name, segment_type
FROM TABLE (DBMS_SPACE.asa_recommendations ('FALSE', 'FALSE', 'FALSE'))
WHERE segment_type in ('INDEX', 'TABLE')
And recommendations like '%shrink%';

select owner, object_type, count(*)
from dba_objects
where status = 'INVALID'
group by owner, object_type
order by 3 desc ,2;

col USERNAME form a15
col ACCOUNT_STATUS form a16
select username, account_status, default_tablespace, temporary_tablespace
from dba_users where temporary_tablespace != 'TEMP' or default_tablespace != 'USERS';

col text form a50 wrap
col type form a12
col name form a30
col owner form a13
select distinct owner, name, type, text from dba_errors
order by 1, 2;
select * from dba_role_privs where granted_role = 'DBA' order by grantee;

set lines 300
clear column
col owner form a12
col host form a15
col db_link form a20
col username form a15
select OWNER, DB_LINK, USERNAME, HOST, CREATED
from dba_db_links;
Set lines 300
col SCHEMA_USER form a12
col what form a30 wrap
col interval form a23
col job form 99999
select job, schema_user, what, next_date, interval, failures, broken from dba_jobs;

select owner, table_name, logging from dba_tables where logging = 'NO' and owner not in ('SYS','SYSTEM','DBSNMP','XDB');

select FORCE_LOGGING from v$database;

spool off exit;