Tuesday, January 8, 2008

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;

4 comments:

Anonymous said...

thanx

small note: the formatting is terrible.

oracle r12 documentation said...

Wow. I am only having five of the above listed scripts that I use mostly. Its really exciting to have few more helpful scripts that you have posted. I recently got an opportunity in this field and loves to learn new points. Thanks for this great share.

raybanoutlet001 said...

ray ban sunglasses
ugg outlet
polo ralph lauren
nike outlet
ralph lauren uk
ugg outlet
louis vuitton sacs
ray ban sunglasses outlet
ray ban sunglasses

jeje said...

adidas nmd
michael kors factory outlet
ultra boost 3.0
balenciaga shoes
moncler jackets
bape hoodie
jordan retro 13
adidas superstar
kate spade outlet
lebron shoes