REM Script Name : db_report.sql

REM Author : Craig Richards

REM Created : 07-December-2006

REM Last Modified :

REM Version : 1.0

REM

REM Modifications :

REM

REM Description :

SET PAUSE OFF

SET HEADING OFF

ACCEPT dbsid PROMPT "Enter SID for report : ";

SPOOL /admin/output/db_report_&dbsid

PROMPT Database Information

PROMPT ====================

SELECT banner from v$version;

SET HEADING ON

select DBID,name,LOG_MODE,created from v$database;

SET HEADING OFF

PROMPT

PROMPT Who Has the Dba privilege

PROMPT =========================

SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA';

PROMPT

PROMPT Who owns the recovery catalog

PROMPT =============================

SELECT grantee FROM dba_role_privs WHERE granted_role = 'RECOVERY_CATALOG_OWNER';

SET FEEDBACK OFF

PROMPT

PROMPT Map of datafiles, controlfiles, redo logs

PROMPT =========================================

PROMPT

PROMPT Datafiles

PROMPT =========

SELECT file_name FROM dba_data_files;

PROMPT

PROMPT Control Files

PROMPT =============

COLUMN name FORMAT A40

SELECT name FROM v$controlfile;

CLEAR COLUMNS

PROMPT

PROMPT Redo Logs

PROMPT =========

SELECT member FROM v$logfile;

SET PAUSE OFF

SET FEEDBACK OFF

SET PAGESIZE 66

SET TERMOUT OFF

SET TRIMSPOOL ON

TTITLE SKIP 2 CENTER 'FREE - Free space by Tablespace' skip 2

COLUMN dummy NOPRINT

COLUMN pct_used FORMAT 999.9 HEADING "%|Used"

COLUMN name FORMAT A16 HEADING "Tablespace Name"

COLUMN bytes FORMAT 9,999,999,999,999 HEADING "Total Bytes"

COLUMN used FORMAT 9,999,999,999,999 HEADING "Used"

COLUMN free FORMAT 999,999,999,999 HEADING "Free"

BREAK ON REPORT

COMPUTE SUM of bytes ON REPORT

COMPUTE SUM of free ON REPORT

COMPUTE SUM of used ON REPORT

SELECT a.tablespace_name name, b.tablespace_name dummy,

SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id ) bytes,

SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id ) -

SUM(a.bytes)/COUNT( DISTINCT b.file_id ) used,

SUM(a.bytes)/COUNT( DISTINCT b.file_id ) free,

100 * ( (SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id )) -

(SUM(a.bytes)/COUNT( DISTINCT b.file_id ) )) /

(SUM(b.bytes)/COUNT( DISTINCT a.file_id||'.'||a.block_id )) pct_used

FROM sys.dba_free_space a, sys.dba_data_files b

WHERE a.tablespace_name = b.tablespace_name

GROUP BY a.tablespace_name, b.tablespace_name;

SET TERMOUT ON

TTITLE OFF

PROMPT

PROMPT User Information

PROMPT ================

PROMPT

COLUMN username FORMAT A20

COLUMN profile FORMAT A10

COLUMN default_tablespace FORMAT A15

COLUMN temporary_tablespace FORMAT A15

select username, default_tablespace, temporary_tablespace,profile, created from dba_users;

CLEAR COLUMNS

PROMPT

PROMPT Database Parameters

PROMPT ===================

COLUMN name FORMAT A40

COLUMN value FORMAT A60

SET LINES 200

select name, value from v$parameter;

CLEAR COLUMNS

SET LINES 80

SET PAUSE OFF

COLUMN segment_name FORMAT A15

COLUMN tablespace_name FORMAT A15

PROMPT

PROMPT Rollback Segments

PROMPT =================

SELECT segment_name, tablespace_name, segment_id, status

FROM dba_rollback_segs;

SET PAUSE OFF

SET FEEDBACK OFF

COLUMN name FORMAT A20

SELECT name, optsize, shrinks, aveshrink, extends,wraps

FROM v$rollstat, v$rollname

WHERE v$rollstat.usn=v$rollname.usn;

PROMPT

PROMPT Invalid Objects in the database

PROMPT ===============================

SET LINES 200

COLUMN owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN object_type FORMAT A30

select owner, object_name, object_type from dba_objects where status = 'INVALID';

CLEAR COLUMNS

SET LINESIZE 80

PROMPT

PROMPT SGA Settings

PROMPT ============

SHOW SGA;

PROMPT

PROMPT Tablespace Information

PROMPT ======================

COLUMN tablespace_name FORMAT A25

SET LINES 200

SET HEADING ON

select tablespace_name, initial_extent, next_extent,min_extents,max_extents,extent_management from dba_tablespaces;

COLUMN file_name FORMAT A80

select file_name from dba_data_files where AUTOEXTENSIBLE = 'YES';

CLEAR COLUMNS

SET LINES 80

PROMPT

PROMPT DBMS SCHEDULER

PROMPT ==============

SELECT owner, job_name, job_type FROM dba_scheduler_jobs;

PROMPT

PROMPT Undo Stats

PROMPT ==========

PROMPT

SET LINES 200

SELECT TO_CHAR(MIN(Begin_Time), 'DD-MON-YYYY HH24:MI:SS') "Begin Time",

TO_CHAR(MAX(End_Time), 'DD-MON-YYYY HH24:MI:SS') "End Time",

SUM(Undoblks) "Total Undo Blocks Used",

SUM(Txncount) "Total Num Trans Exec",

MAX(Maxquerylen) "Longest Query(in secs)",

MAX(Maxconcurrency) "Highest Concurrent Trans Count",

SUM(Ssolderrcnt), SUM(Nospaceerrcnt)

FROM v$undostat;

SPOOL OFF

REM exit

REM End of Script