oracle| Script
In a longer period of interaction with Oracle, each DBA, especially some heroes have a variety of scripting tools to complete a variety of purposes, so very convenient and quick to complete the day-to-day work, the following part of my commonly used to show you, this article mainly focused on database management, these scripts have been rigorously tested.
1. Table Space Statistics
A, Script Description:
This is one of my most common scripts used to show the state of all the tablespaces in the database, such as the size of the table space, the amount of space used, the percentage used, the amount of free space, and the size of the largest block of table space now.
B, the original script:
Select Upper (F.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (Round (d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,2), ' 990.99 ') "Use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max 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. View segments that cannot be extended
A, Script Description:
Oracle's inability to extend a segment, such as a table segment or index, depends not on how much space is left in the table space, but whether the largest block in the remaining space is sufficient for the table to be larger than the "NEXT" value of the index, so sometimes a table space is left with several g free spaces, When you use Oracle to suggest that a table or index cannot be extended, that is why there are too many fragments of space. This script is to find some information about segments that cannot be extended.
B, the original script:
SELECT Segment_name,
Segment_type,
Owner
A.tablespace_name "Tablespacename",
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 spaces (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. View the size of the space used by the segment (table segment, index segment)
A, Script Description:
Sometimes you may want to know how much m is occupied by a table or an index, and this script is to meet your requirements and replace the contents of the <>.
B, the original script:
SELECT owner,
Segment_name,
SUM (bytes)/1024/1024
From Dba_segments
WHERE owner=<segment owner>
and Segment_name=<your table or index name>
GROUP by Owner,segment_name
Order by 3 DESC;
4, view the table lock in the database
A, Script Description:
The style of this statement is many, all kinds of the same, but I think this is the most practical, do not believe you use, needless to say, the lock is every DBA must be involved in the content, when you know that a table is locked by which session, you use this script.
B, the original script:
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, processing stored procedures are locked
A, Script Description:
In the actual process, you may want to recompile a stored procedure is always in the waiting state, the end will be unable to lock the object, then you can use this script to find the lock process that SID, you need to note that the v$access view is very slow, need some cloth patience.
B, the original script:
SELECT * from V$access
WHERE Owner=<object owner>
and Object<procedure name>
6. View Rollback segment Status
A, Script description
This is also a script often used by DBAs, because the rollback segment is online or full is their concern
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, see which sessions are using which rollback segment
A, Script Description:
When you find a rollback segment that handles full state and you want to turn it back on the online state, you will use alter ROLLBACK segment rbs_seg_name shrink, many times Hou does not come back, mainly because a session is in use , and then you use this script, find the SID serial# the rest of the things I don't need to say.
B, the original script
SELECT r.name rollback segment name,
S.sid,
s.serial#,
S.username User Name,
S.status,
T.cr_get,
T.phy_io,
T.USED_UBLK,
T.noundo,
SUBSTR (S.program, 1, 78) Operating procedures
From Sys.v_$session s,sys.v_$transaction T,sys.v_$rollname R
WHERE t.addr = s.taddr and T.xidusn = R.usn
--R.name in (' Zhyz_rbs ')
ORDER BY T.cr_get,t.phy_io
8, view the session that is using the temporary segment
A, Script Description:
A lot of the time when you see which segments cannot be extended, the result of the Echo is a temporary segment, or you find that the free space of the Pro Section table space is almost 0 when you do the table space statistics, then Oracle says you have to restart the database to reclaim this space. The actual process is not so complicated, use the following script to kill the session that occupies the temporary segment, and then use alter tablespace temp COALESCE, this statement takes the space of the temp tablespace back.
B, the original script
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
adjourned
