Database Information

rem +--------------------------------------------------------------------------+
rem | Script ID:     DBInfo.sql
rem |
rem | Purpose:       Displays General Information About The Database.
rem |            
rem | Developer:     Hung Wu
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: v$Controlfile  v$Database  v$Datafile
rem |                v$Instance     v$Logfile   v$Sga       v$Version
rem |            
rem | Output:        n/a
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem |                lct 08/16/08 1.1 Added this top block, prompts, Mb calculation,
rem |                                 column definitions, formatting, spool,
rem +--------------------------------------------------------------------------+
Clear Screen
Set Pagesize 1000 Linesize 1000 Feedback Off Heading on Newpage 1
Ttitle Off

Column DBID                                        Format 9999999999
Column Value                                       Format 999,999,999 Trunc
Column Separator1        Heading ' '               Format A9
Column Blocked                                     Format A7
Column VERSION           Heading  Version          Format A10
Column STATUS            Heading  Status           Format A7
Column THREAD#           Heading  Thread#          Format 99
Column ARCHIVER          Heading  Archiver         Format A7
Column LOG_SWITCH_WAIT   Heading  Log|Switch|Wait  Format A6
Column LOGINS            Heading  Logins           Format A7
Column DATABASE_STATUS   Heading  Database|Status  Format A8
Column INSTANCE_Name     Heading  Instance|Name    Format A8
Column ACTIVE_STATE      Heading  Active|State     Format A6
Column BLOCKED           Heading  Blo-|cked        Format A4
Column PARALLEL          Heading  Para-|llel       Format A5
Column INSTANCE_ROLE     Heading  Instance|Role    Format A8 Word_Wrapped
Column HOST_NAME         Heading  Host|Name        Format A6 Word_Wrapped
Column Controlfile_Created                         Format A11
Column Controlfile_Time                            Format A11
Column Name                                        Format A10

Spool DBInfo.Txt
Prompt =================================================================================================================================
Prompt Table: v$Database

Select Created, Database_Role, Dataguard_Broker, Dbid, Db_Unique_Name,
    Flashback_On, Force_Logging, Guard_Status, Log_Mode, Name, Open_Mode
From v$Database;
Select Platform_Id, Platform_Name, Protection_Level, Protection_Mode, Switchover_Status,
    Version_Time,' ' As Separator1, Current_Scn, Standby_Became_Primary_Scn
From v$Database;
Select Fs_Failover_Current_Target, Fs_Failover_Observer_Host, Fs_Failover_Observer_Present,
    Fs_Failover_Status, Fs_Failover_Threshold,' ' As Separator1, ' ' As Separator1,
    Supplemental_Log_Data_All, Supplemental_Log_Data_Fk, Supplemental_Log_Data_Min,
    Supplemental_Log_Data_Pk, Supplemental_Log_Data_Ui
From v$Database;
Select Open_Resetlogs, Prior_Resetlogs_Time, Resetlogs_Time, ' ' As Separator1, ' '
    As Separator1, ' '   As Separator1, Controlfile_Change#, Controlfile_Created,
    Controlfile_Sequence#, Controlfile_Time, Controlfile_Type
From v$Database;
Select Archivelog_Change#, Archivelog_Compression, Archive_Change#, Remote_Archive,' '
    As Separator1, ' ' As Separator1, Activation#, Checkpoint_Change#, Last_Open_Incarnation#,
    Recovery_Target_Incarnation#, Switchover#
From v$Database;

Prompt =================================================================================================================================
Prompt Table: v$Instance

Select * from v$Instance;

Prompt =================================================================================================================================
Prompt Table: v$Version

Select * From  v$Version;

Prompt =================================================================================================================================
Prompt Table: v$SGA

Column Name Format A20
Column Kb   Format 9,999,999
Column Mb   Format    99,999
Column Gb   Format       999
Compute Sum    Label 'Total'    Of Value Kb Mb Gb On Report
Break on Report

Select Name, Value,
    Trunc(Value/128)         as Kb,
    Trunc(Value/128/128)     as Mb,
    Trunc(Value/128/128/128) as Gb
From v$SGA;

Prompt =================================================================================================================================
Prompt Table: v$ControlFile

Column Name Format A53

Select *
From v$ControlFile
Order By Name;

Prompt =================================================================================================================================
Prompt Table: v$DataFile

Column DirFromName Heading 'Directory (from Name)' Format A40
Column Name Format A13

Select Substr(Name, Instr(Name, '\', -1, 1)+1) Name,
    Substr(Name, 1, Instr(Name, '\', -1, 1)) DirFromName,
    File#, Status, Ts#, Rfile#, Enabled, Aux_Name,
    Creation_Change#, Creation_Time, Last_Change#, Last_Time
From V$Datafile
Order By Name;

Select Substr(Name, Instr(Name, '\', -1, 1)+1) Name,
    Plugged_In, Bytes, Create_Bytes,
    Blocks, Block_Size, Block1_Offset,
    First_Nonlogged_Scn, First_Nonlogged_Time,
    Checkpoint_Change#, Checkpoint_Time,
    Unrecoverable_Change#, Unrecoverable_Time
From V$Datafile
Order By Name;

Select Substr(Name, Instr(Name, '\', -1, 1)+1) Name,
    Offline_Change#, Online_Change#, Online_Time
From V$Datafile
Order By Name;

Prompt =================================================================================================================================
Prompt Table: v$LogFile
Column Member Format A50

Select *   From v$LogFile;

Spool Off
Prompt
Prompt Your data has been spooled to DBInfo.Txt
Prompt