==========================================
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;