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;


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다