=============================================
NO 1: Display the Current Archivelog Status
=============================================
The command below, when entered via SQLDBA, will display the current archivelog status of the database. The script
will display the active log_archive_dest location, as well as the current and oldest online redo log sequence numbers.
ARCHIVE LOG LIST;
===========================================
NO 2: Creating a Control File Trace File
===========================================
The command below, when entered via SQLDBA, will create a trace file in your trace file destination directory. The
trace file will contain the complete CREATE CONTROLFILE command for the current database, saving you vast
quantities of time if you ever need to generate that command.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
=============================================
NO 3: Tablespace Free Extents and Free Space
=============================================
The query below will ONLY work in Oracle7.2 and higher. In previous versions, it was difficult to query
DBA_DATA_FILES and DBA_FREE_SPACE in one query, since both could have multiple rows for each tablespace.
Using 7.2’s FROM clause subquery feature, you can group the two queries separately, then join them on the
Tablespace_Name value. The result shows the largest free extent in the tablespace, the number of free extents in the
tablespace, the total free space in the tablespace, and the percentage of the tablespace’s available space that is free.
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;
================================================
No. 4: Display Allocated Space & Quota by User
================================================
DBA_TS_QUOTAS displays the allocated space, by user, in each tablespace, along with the quota for each of those
users. If a user has unlimited quota in a tablespace, the quota will be displayed as 0 or negative. Running this script is a
great quick way to see which users own objects in which tablespaces, and how close they are to their quotas. You’ll
need to SET LINESIZE 132 before running this script.
select * from DBA_TS_QUOTAS
order by Tablespace_Name, Username;
================================================
No. 5: Show Allocated Storage for All Objects
================================================
DBA_SEGMENTS shows the allocated storage for all objects in the database. Valid values for the &segment_type
variable include:
TEMPORARY–for currently used temporary segments. Watch them grow!
ROLLBACK–for rollback segments
INDEX–for indexes
TABLE–for tables
There is one record in DBA_SEGMENTS for each segment. There is a one-to-many relationship between
DBA_SEGMENTS and DBA_EXTENTS: DBA_SEGMENTS shows you the total space allocated to a segment;
DBA_EXTENTS shows the space allocated to each extent within the segment.
column Segment_Name format A40
select Segment_Name, Extents, Blocks
from DBA_SEGMENTS
where Segment_Type = ‘&segment_type’
order by Segment_Name;
==============================================
No. 6: Map a Tablespace’s Used and Free Space
==============================================
Since DBA_EXTENTS shows the starting block of each extent (the combination of File_ID and Block_ID), plus the
length of each extent (the Blocks column), you can map out the allocated space within a given tablespace as a series of
consecutive extents. DBA_FREE_SPACE shows the free extents, so the combination of DBA_EXTENTS and
DBA_FREE_SPACE produces a map of the used and unused free space within a tablespace. You can use this map of
the tablespace’s space to determine whether an object has been properly sized or if the tablespace needs to be
defragmented.
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;
=======================================
No. 7: Blocks Used by Data in a Table
=======================================
When you replace “sometable” with the name of a table in your database, the query below will return the number of
blocks used by the data in that table. The query examines the Block_ID (columns 1-8) and the File_ID (columns 15-18)
of the ROWID. The query then returns the number of distinct blocks used by the records within the table. The
ANALYZE command can also provide this information, but this query is faster.
select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))
Blocks_Used
from sometable;
================================
No. 8: Reset a User’s Password
================================
The script below generates an ALTER USER command that will reset a user’s password (the user whose username is
passed to the script as &&1). If you spool the output of this command, you will be able to reset the user’s password
without ever having to know what the password was. You can change the user’s password to anything you like, use the
account for testing purposes, and then reset the password when your testing is complete. For a detailed discussion, see
Chapter 9 of the Oracle DBA Handbook.
select ‘ALTER USER &&1 IDENTIFIED BY VALUES ‘
||””||Password||””||’;’
from DBA_USERS where Username = UPPER(‘&&1’);
=========================
No. 9: Query V$PARAMETER
=========================
Don’t try to guess what parameters are in effect. You can never tell for sure which INIT.ORA file was used to start an
instance, or if the INIT.ORA file has been modified since the database was started. The only sure way to tell the setting
of an initialization parameter is to query V$PARAMETER.
column Name format A50
column Value format A28
select Name, Value from V$PARAMETER;
======================================
No. 10: Show Statement Execution Path
======================================
This query selects the execution path from the PLAN_TABLE, ordered according to the hierarchy of the steps
involved. The query shown in the listing specifically queries for the execution path of a statement using the
Statement_ID of ‘TEST’. Prior to running this query, you should use the EXPLAIN PLAN command to populate
PLAN_TABLE.
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;