ORACLE-DBA Docs 11g

Scripts Every DBA Should Have

I. Display the Current Archivelog Status :

ARCHIVE LOG LIST;

II. Creating a Control File Trace File

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

III. Tablespace Free Extents and Free Space

column Tablespace_Name format A20

column Pct_Free format 999.99

select Tablespace_Name,

Max_Blocks,

Count_Blocks,

Sum_Free_Blocks,

100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free

from

(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks

from DBA_DATA_FILES

group by Tablespace_Name),

(select Tablespace_Name FS_TS_NAME,

MAX(Blocks) AS Max_Blocks,

COUNT(Blocks) AS Count_Blocks,

SUM(Blocks) AS Sum_Free_Blocks

from DBA_FREE_SPACE

group by Tablespace_Name)

where Tablespace_Name = FS_TS_NAME;

IV. Display Allocated Space & Quota by User

select * from DBA_TS_QUOTAS

order by Tablespace_Name, Username;

V. Show Allocated Storage for All Objects

column Segment_Name format A40

select Segment_Name, Extents, Blocks

from DBA_SEGMENTS

where Segment_Type = ‘&segment_type’

order by Segment_Name;

VI. Map a Tablespace’s Used and Free Space

select Segment_Name, File_Id, Block_Id, Blocks

from DBA_EXTENTS where Tablespace_Name = ‘&&tablespacename’

UNION

select ‘Free Space’, File_Id, Block_Id, Blocks

from DBA_FREE_SPACE where Tablespace_Name = ‘&&tablespacename’

order by 2,3;

VII. Blocks Used by Data in a Table

select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))

Blocks_Used from &table;

VIII. Reset a User’s Password

select ‘ALTER USER &&1 IDENTIFIED BY VALUES

‘||””||Password||””||';’

from DBA_USERS where Username = UPPER(‘&&1′);

IX. Query V$PARAMETER

column Name format A50

column Value format A28

select Name, Value from V$PARAMETER;

X. Show Statement Execution Path

select LPAD(‘ ‘,2*LEVEL)||Operation||’ ‘||Options

||’ ‘||Object_Name Q_PLAN

from PLAN_TABLE where Statement_ID = ‘TEST’