In a longer period of time with Oracle, each DBA, especially some heroes have a variety of scripting tools that complete various uses, so it is very convenient to complete the daily work, let me Part of commonly used to everyone, this article mainly focuses on database management, these scripts have been strictly tested.
1, table space statistics
A, script description:
This is my most common script that uses it to display the status of all tablespaces in the database, such as the size of the table space, the use of space, the percentage of use, how much the number of free space and the current table space is now.
B, the script original:
Select Upper (f.tablespace_name "Table Space Name",
D. Tot_Grootte_MB "Table Space Size (M)",
D. TOT_GROOTTE_MB - F. TOTAL_BYTES "The space (M) has been used,"
TO_CHAR ((D. Tot_Grootte_MB - F. TOTAL_BYTES) / D. TOT_GROOTTE_MB * 100, 2), '990.99') "Use Comparison",
F. Total_Bytes "Idle Space (M)",
F.max_bytes "Maximum Block (M)"
From
(Select TableSpace_name,
Round (SUM (Bytes) / (1024 * 1024), 2) Total_bytes,
Round (Max (Bytes) / (1024 * 1024), 2) MAX_BYTES
From sys.dba_free_space
Group by TableSpace_name) F,
(Select Dd.Tablespace_name, Round (SUM (DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
From sys.dba_data_files dd
Group by dd.tablespace_name) D
Where d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;
2, see paragraphs that cannot be extended
A, script description:
Oracle is not extended for a paragraph or index, which is not the remaining space in the table space, but is taken in the largest block in these remaining spaces. Is it comparable to the "next" value of the index, so Sometimes a table space remains in the free space, oracle or the index cannot be expanded when you use it, that is, because this is too much. This script is some of the information that cannot be expanded.
B, the script original:
Select segment_name,
Segment_type,
Owner,
A.Tablespace_name "TableSpaceename",
Initial_extent / 1024 "INITAL_EXTENT (K)",
Next_extent / 1024 "next_extent (k)",
PCT_INCREASE,
B.BYTES / 1024 "TableSpace Max Free Space (K)",
B. Sum_Bytes / 1024 "TableSpace Total Free Space (K)"
From DBA_SEGMENTS A,
(Select TableSpace_name, max (bytes) Bytes, SUM (Bytes) SUM_BYTES from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
Where a.tablespace_name = b.tablespace_name
And next_extent> B.BYTES
ORDER BY 4, 3, 1; 3, the size of the space used by the segment (table segment, index)
A, script description:
Sometimes you might want to know how much a table or an index take up, this script is to meet your requirements, replace the content in <>.
B, the script original:
SELECT OWNER,
Segment_name,
Sum (Bytes) / 1024/1024
From DBA_SEGMENTS
Where owner =
And segment_name =
GROUP BY OWNER, Segment_Name
ORDER BY 3 DESC;
4, check the table lock in the database
A, script description:
The style of this apartment is much, like all kinds, but I think this is the most practical, don't believe you, don't say more, the lock is the content that every DBA must be involved, when you know some Which SESSION is locked by a table, you used this script.
B, the script original:
SELECT A.OWNER,
A.Object_name,
B.xIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.Session_ID,
B. Oracle_Username,
B.OS_USER_NAME,
B.Process,
B.locked_mode,
C. Machine,
C.STATUS,
C. Server,
C.SID,
C. Serial #,
C.Program
From all_Objects a,
V $ locked_Object B,
Sys.gv_ $ session c
Where (a.Object_id = B.Object_ID)
AND (B.Process = C.Process)
- and
ORDER BY 1, 2;
5, process stored procedure is locked
A, script description:
In the actual process, you may have to recompile a store procedure, always in the waiting state, and finally will not lock the object. At this time, you can use this script to find the SID of the lock process, you need to pay attention to check V $ access this The view is very slow, and some cloth is concerned.
B, the script original:
SELECT * FROM V $ Access
Where owner =
And Object
6, check back to roll band status
A, script description
This is also the script that DBA is often used, because the rollback segment is ONLINE or full is their care.
B, select a.segment_name, b.status
From DBA_ROLLBACK_SEGS A,
V $ ROLLSTAT B
Where a.segment_id = B.USN
ORDER BY 2
7. What sessions are using it?
A, script description:
When you find a rollover segment processes the full state, you want to make it back to the online state, then you will use Alter Rollback Segment RBS_SEG_NAME Shrink, but you can't come back, it is mainly due to a session. At this time, you used this script. If you find the SID's serial #, you don't have to say it.
B, scripting original text
Select R.Name returns the name,
S.SID,
S.Serial #,
S.Username username,
S.status,
T.CR_GET,
T.PHY_IO,
T.USED_UBLK,
T.Noundo,
Substr (S.Program, 1, 78) Operator from sys.v_ $ session s, sys.v_ $ transaction t, sys.v_ $ rollname r
Where t.addr = s.taddr and t.xidusn = r.USN
- And R.Name in ('Zhyz_RBS')
Order by t.cr_get, t.phy_io
8, check the session in use
A, script description:
Many of you can't expand, when you can't extend it, the result of the echo is temporary, or when you do the table space statistics, you find that the available space of the temporal table space is almost 0. At this time, press Oracle's saying that you only have Start the database to recycle this part of space. It is not so complicated in the actual process, using the following scripts to kill the session of the tempo period, then use the ALTER TABLESPACE TEMP COALESCE; this statement will return the space of the TEMP table space.
B, scripting original text
Select Username,
SID,
Serial #,
SQL_ADDRESS,
Machine,
PROGRAM,
TableSpace,
Segtype,
Contents
From v $ session se,
V $ sort_usage su
Where se.saddr = Su.Session_Addr
(to be continued)
