On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, August 17, 2008

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

No comments:

Post a Comment