DBA가 가지고 있어야 할 10가지 SQL

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

출처 : http://dbtech.co.kr/bbs/?bo_c=1040&bo_v=357