Oracle通过move表来整理碎片操作步骤

Oracle通过move表来整理碎片操作步骤

适用场景：
数据库经常性的通过delete来清数，导致数据库出现高水位，通过对表move或者行迁移来降低高水位。

操作前提：
在对表进行move前要注意，move操作时如果不指定表空间，则在原表空间移动数据块，在这个表空间中需要至少额外一倍该表大小的空间。
数据重组后，其 rowid 发生了改变。index是通过 rowid 来 fetch 数据行的，所以表上的 index 需要 rebuild。

检查事项：
操作前先确认业务是否已经停止。

检查表空间大小是否足够使用。

SQL>set linesize 120
COLUMN tablespace_name FORMAT a20
COLUMN status FORMAT a10
COLUMN ext_management FORMAT a15
COLUMN count for 9999
COLUMN CONTENTS FORMAT a10
COLUMN ASSM FORMAT a10
set pages 999
SELECT   d.tablespace_name,
         round(NVL (a.bytes - NVL (f.bytes, 0), 0) / 1024 / 1024) used_mb,
         round(NVL (a.bytes / 1024 / 1024, 0)) total_mb,         
         round(NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0),2) ratio,
         d.status,
         a.COUNT,
         d.contents,
         d.extent_management ext_management
  FROM   sys.dba_tablespaces d,
         (  SELECT   tablespace_name, SUM (bytes) bytes, COUNT (file_id) COUNT
              FROM   dba_data_files
          GROUP BY   tablespace_name) a,
         (  SELECT   tablespace_name, SUM (bytes) bytes
              FROM   dba_free_space
          GROUP BY   tablespace_name) f
 WHERE       d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = f.tablespace_name(+)
         AND NOT d.contents LIKE 'UNDO'
         AND NOT (d.extent_management LIKE 'LOCAL'
                  AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT   d.tablespace_name,
         round(NVL (t.bytes, 0) / 1024 / 1024),
         round(NVL (a.bytes / 1024 / 1024, 0)),         
         round(NVL (t.bytes / a.bytes * 100, 0),2),
        d.status,
         a.COUNT,
         d.contents,
         d.extent_management
  FROM   sys.dba_tablespaces d,
         (  SELECT   tablespace_name, SUM (bytes) bytes, COUNT (file_id) COUNT
              FROM   dba_temp_files
          GROUP BY   tablespace_name) a,
         (  SELECT   ss.tablespace_name,
                     SUM ( (ss.used_blocks * ts.blocksize)) bytes
              FROM   gv$sort_segment ss, sys.ts$ ts
             WHERE   ss.tablespace_name = ts.name
          GROUP BY   ss.tablespace_name) t
 WHERE       d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = t.tablespace_name(+)
         AND d.extent_management LIKE 'LOCAL'
         AND d.contents LIKE 'TEMPORARY'
UNION ALL
SELECT   d.tablespace_name,
         round(NVL (u.bytes, 0) / 1024 / 1024),
         round(NVL (a.bytes / 1024 / 1024, 0)),         
         round(NVL (u.bytes / a.bytes * 100, 0),2),
         d.status,
         a.COUNT,
         d.contents,
         d.extent_management
  FROM   sys.dba_tablespaces d,
         (  SELECT   tablespace_name, SUM (bytes) bytes, COUNT (file_id) COUNT
              FROM   dba_data_files
          GROUP BY   tablespace_name) a,
         (  SELECT   tablespace_name, SUM (bytes) bytes
              FROM   dba_undo_extents
             WHERE   status IN ('ACTIVE', 'UNEXPIRED')
          GROUP BY   tablespace_name) u
 WHERE       d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = u.tablespace_name(+)
         AND d.contents LIKE 'UNDO'
ORDER BY   4 desc;t.sql

Move前后都要检查索引状态

SQL> select index_owner,index_name,column_name,colum_position,status from dba_ind_columns where table_name= ‘tableName’ order by 1,2,3;

操作步骤：
对表进行move操作，move到原本表空间内

SQL> alter table tabName move;

重建索引开启4个并行

SQL> alter index indexName rebuild parallel 4;

索引创建完后并行度调至回1（即不适用并行）

SQL> alter index indexNamen noparallel;

重建主键

SQL> alter index PkName rebuild;

风险提示：

进行move操作时，查询 v$locked_objects视图，可以看到表上加了 exclusive lock锁，所以切勿在业务时间段进行move表操作。