如何在Oracle数据库中按用户名重建索引

会计考友

如果你管理的Oracle数据库下某些应用项目有大量的修改删除操作， 数据索引是需要周期性的重建的。
它不仅可以提高查询性能， 还能增加索引表空间空闲空间大小。
在ORACLE里大量删除记录后， 表和索引里占用的数据块空间并没有释放。
重建索引可以释放已删除记录索引占用的数据块空间。
转移数据， 重命名的方法可以重新组织表里的数据。
1 T; i0 _4 [6 M/ u& U0 \) r下面是可以按ORACLE用户名生成重建索引的SQL脚本：
# C# U8 i6 Q" e5 _7 x# K3 eSET ECHO OFF;
3 x, B5 K  F4 M% ?SET FEEDBACK OFF;
SET VERIFY OFF;
SET PAGESIZE 0;
) Y3 \6 \+ P0 zSET TERMOUT ON;
SET HEADING OFF;
ACCEPT username CHAR PROMPT 'Enter the index username: ';
3 z, @7 W5 G) }" g6 Y  z; Ospool /oracle/rebuild_&username.sql;
: b2 `* [* L6 Z, D( oSELECT
* G; f" a  O9 `" |, m'REM +-----------------------------------------------+' || chr(10) ||
" F* U" E2 w1 R  W8 e'REM | INDEX NAME : ' || owner || '.' || segment_name
|| lpad('|', 33 - (length(owner) + length(segment_name)) )
7 o9 J. K: z& W$ D& _1 I|| chr(10) ||
4 Y1 n: F; a2 Q- Y* k'REM | BYTES : ' || bytes
|| lpad ('|', 34-(length(bytes)) ) || chr(10) ||
'REM | EXTENTS : ' || extents
|| lpad ('|', 34-(length(extents)) ) || chr(10) ||
'REM +-----------------------------------------------+' || chr(10) ||
  B/ F8 s8 W. O  l; Y* T7 H'ALTER INDEX ' || owner || '.' || segment_name || chr(10) ||
" i, ^% c) u8 j9 F: n2 e'REBUILD ' || chr(10) ||
'TABLESPACE ' || tablespace_name || chr(10) ||
'STORAGE ( ' || chr(10) ||
' INITIAL ' || initial_extent || chr(10) ||
# f6 G/ ?/ D: d' NEXT ' || next_extent || chr(10) ||
4 v2 T7 D( W* b% s: _6 H' q' MINEXTENTS ' || min_extents || chr(10) ||
' MAXEXTENTS ' || max_extents || chr(10) ||
' PCTINCREASE ' || pct_increase || chr(10) ||
');' || chr(10) || chr(10)
FROM dba_segments
' _5 `8 ^: |8 R/ HWHERE segment_type = 'INDEX'
# `1 }/ B7 ~% H5 S8 f6 x" sAND owner='&username'
ORDER BY owner, bytes DESC;
6 y' x: `% \& }- z5 Jspool off;
如果你用的是WINDOWS系统， 想改变输出文件的存放目录，修改spool后面的路径成：
) C: n6 p9 B- d9 l1 F6 t( Vspool c：\oracle\rebuild_&username.sql；
* y3 }5 h, t1 h. ^& ?! R; z4 A如果你只想对大于max_bytes的索引重建索引，可以修改上面的SQL语句：
( F# ^4 F: o  M" h8 B在AND owner='&username' 后面加个限制条件 AND bytes> &max_bytes
如果你想修改索引的存储参数， 在重建索引rebuild_&username.sql里改也可以。
+ ^* p( N; g0 f. O$ G5 @比如把pctincrease不等于零的值改成是零。
生成的rebuild_&username.sql文件我们需要来分析一下，它们是否到了需要重建的程度：
& ^, z+ B: B2 V0 n! ^分析索引，看是否碎片严重
SQL>ANALYZE INDEX &index_name VALIDATE STRUCTURE;
col name heading 'Index Name' format a30
9 {! ?$ y' x: b5 g- O$ v% [col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999
' S. P1 d3 ]( T, ycol lf_rows_used heading 'Used|Leaf Rows' format 99999999
col ratio heading '% Deleted|Leaf Rows' format 999.99999
( I, b4 Y0 U- v; D2 N9 \- SSELECT name,
3 F( e0 n/ o0 S/ h+ h4 M: cdel_lf_rows,
lf_rows - del_lf_rows lf_rows_used,
) d- D- \2 o; B" H. Kto_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
FROM index_stats where name = upper('&index_name');
6 r8 S9 L0 w# |: B* H当删除的比率大于15 - 20% 时，肯定是需要索引重建的。
经过删改后的rebuild_&username.sql文件我们可以放到ORACLE的定时作业里：
/ y+ U) i% q$ ~# a( v6 [1 c* e比如一个月或者两个月在非繁忙时间运行。
2 z/ d. ~9 s' E) k9 J2 f如果遇到ORA-00054错误，表示索引在的表上有锁信息，不能重建索引。
那就忽略这个错误， 看下次是否成功。
+ e0 j- }( Q$ g5 L7 V对那些特别忙的表要区别对待， 不能用这里介绍的方法，
) x5 |! l8 y( Z还要把它们的索引从rebuild_&username.sql里删去。