ORACLE DBA常用SQL脚本工具->管理篇(1)

在较长时间的与 oracle 的交往中，每个 DBA 特别是一些大侠都有各种各样的完成各种用途的脚本工具，这样很方便的很快捷的完成了日常的工作，下面把我常用的一部分展现给大家，此篇主要侧重于数据库管理，这些脚本都经过严格测试。

** 1、 ** ** 表空间统计 **

** ** ** A、 ** ** 脚本说明 ** ：

> 这是我最常用的一个脚本，用它可以显示出数据库中所有表空间的状态，如表空间的大小、已使用空间、使用的百分比、空闲空间数及现在表空间的最大块是多大。

** B ** ** 、脚本原文 : **

> SELECT upper(f.tablespace_name) " 表空间名 ", > > d.Tot_grootte_Mb " 表空间大小 (M)", > > d.Tot_grootte_Mb - f.total_bytes " 已使用空间 (M)", > > to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') " 使用比 ", > > f.total_bytes " 空闲空间 (M)", > > f.max_bytes " 最大块 (M)" > > FROM > > (SELECT tablespace_name, > > round(SUM(bytes)/(10241024),2) total_bytes, > > round(MAX(bytes)/(10241024),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、查看无法扩展的段

** A、 ** 脚本说明：

ORACLE 对一个段比如表段或索引无法扩展时，取决的并不是表空间中剩余的空间是多少，而是取于这些剩余空间中最大的块是否够表比索引的“ NEXT ”值大，所以有时一个表空间剩余几个 G 的空闲空间，在你使用时 ORACLE 还是提示某个表或索引无法扩展，就是由于这一点，这时说明空间的碎片太多了。这个脚本是找出无法扩展的段的一些信息。

B 、脚本原文：

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 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、查看段 ( 表段、索引段 ) 所使用空间的大小

** A、 ** 脚本说明：

有时你可能想知道一个表或一个索引占用多少 M 的空间，这个脚本就是满足你的要求的，把 <> 中的内容替换一下就可以了。

** B、 ** 脚本原文：

SELECT owner,

segment_name,

SUM(bytes)/1024/1024

FROM dba_segments

WHERE owner=

  1<segment owner="">
  2
  3And segment_name=<your index="" name="" or="" table="">
  4
  5GROUP BY owner,segment_name 
  6
  7ORDER BY 3 DESC; 
  8
  94、  查看数据库中的表锁 
 10
 11** A、  ** 脚本说明： 
 12
 13这方面的语句的样式是很多的，各式一样，不过我认为这个是最实用的，不信你就用一下，无需多说，锁是每个  DBA  一定都涉及过的内容，当你相知道某个表被哪个  session  锁定了，你就用到了这个脚本。 
 14
 15** B、  ** 脚本原文： 
 16
 17SELECT A.OWNER, 
 18
 19A.OBJECT_NAME, 
 20
 21B.XIDUSN, 
 22
 23B.XIDSLOT, 
 24
 25B.XIDSQN, 
 26
 27B.SESSION_ID, 
 28
 29B.ORACLE_USERNAME, 
 30
 31B.OS_USER_NAME, 
 32
 33B.PROCESS, 
 34
 35B.LOCKED_MODE, 
 36
 37C.MACHINE, 
 38
 39C.STATUS, 
 40
 41C.SERVER, 
 42
 43C.SID, 
 44
 45C.SERIAL#, 
 46
 47C.PROGRAM 
 48
 49FROM ALL_OBJECTS A, 
 50
 51V$LOCKED_OBJECT B, 
 52
 53SYS.GV_$SESSION C 
 54
 55WHERE ( A.OBJECT_ID = B.OBJECT_ID ) 
 56
 57AND (B.PROCESS = C.PROCESS ) 
 58
 59\--  AND 
 60
 61ORDER BY 1,2  ; 
 62
 635、  处理存储过程被锁 
 64
 65** A、  ** 脚本说明： 
 66
 67实际过程中可能你要重新编译某个存储过程理总是处于等待状态，最后会报无法锁定对象，这时你就可以用这个脚本找到锁定过程的那个  sid  ，需要注意的是查  v$access  这个视图本来就很慢，需要一些布耐心。 
 68
 69** B、  ** 脚本原文： 
 70
 71SELECT * FROM V$ACCESS 
 72
 73WHERE owner=<object owner="">
 74
 75And object<procedure name="">
 76
 776、  查看回滚段状态 
 78
 79** A、  ** 脚本说明 
 80
 81这也是  DBA  经常使用的脚本，因为回滚段是  online  还是  full  是他们的关怀之列嘛 
 82
 83B  、  SELECT a.segment_name,b.status 
 84
 85FROM Dba_Rollback_Segs a, 
 86
 87v$rollstat b 
 88
 89WHERE a.segment_id=b.usn 
 90
 91ORDER BY 2 
 92
 937、  看哪些  session  正在使用哪些回滚段 
 94
 95A、  脚本说明： 
 96
 97当你发现一个回滚段处理  full  状态，你想使它变回  online  状态，这时你便会用  alter rollback segment rbs_seg_name shrink,  可很多时侯确  shrink  不回来，主要是由于某个  session  在用，这时你就用到了这个脚本，找到了  sid  的  serial#  余下的事就不用我说了吧。 
 98
 99B、脚本原文 
100
101&gt; SELECT  r.name  回滚段名  , 
102&gt; 
103&gt; s.sid, 
104&gt; 
105&gt; s.serial#, 
106&gt; 
107&gt; s.username  用户名  , 
108&gt; 
109&gt; s.status, 
110&gt; 
111&gt; t.cr_get, 
112&gt; 
113&gt; t.phy_io, 
114&gt; 
115&gt; t.used_ublk, 
116&gt; 
117&gt; t.noundo, 
118&gt; 
119&gt; substr(s.program, 1, 78)  操作程序 
120&gt; 
121&gt; FROM  sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r 
122&gt; 
123&gt; WHERE  t.addr = s.taddr and t.xidusn = r.usn 
124&gt; 
125&gt; \-- AND r.NAME IN ('ZHYZ_RBS') 
126&gt; 
127&gt; ORDER  BY t.cr_get,t.phy_io 
128
1298、  查看正在使用临时段的  session 
130
131A、  脚本说明： 
132
133许多的时侯你在查看哪些段无法扩展时，回显的结果是临时段，或你做表空间统计时发现临段表空间的可用空间几乎为  0,  这时按  oracle  的说法是你只有重新启动数据库才能回收这部分空间。实际过程中没那么复杂，使用以下这段脚本把占用临时段的  session  杀掉，然后用  alter tablespace temp coalesce;  这个语句就把  temp  表空间的空间回收回来了。 
134
135B、  脚本原文 
136
137SELECT username, 
138
139sid, 
140
141serial#, 
142
143sql_address, 
144
145machine, 
146
147program, 
148
149tablespace, 
150
151segtype, 
152
153contents 
154
155FROM v$session se, 
156
157v$sort_usage su 
158
159WHERE se.saddr=su.session_addr 
160
161(待续)</procedure></object></your></segment>