10 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' connect by prior ID = Parent_ID and Statement_ID = 'TEST' start with ID=1;