oracle 释放表空间到OS（resize）

1.查看表空间对象

SELECT OWNER AS OWNER,
             SEGMENT_NAME AS SEGMENT_NAME,
             SEGMENT_TYPE AS SEGMENT_TYPE,
             SUM (BYTES) / 1024 / 1024 AS SEGMENT_SIZE
        FROM DBA_SEGMENTS
       WHERE TABLESPACE_NAME = 'TBS'
    GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;

2.MOVE表

move后索引会因为rowid的改变而失效，因此需要重建

2.1.普通表

alter table owner.tablename move tablespace ntbs;
alter index owner.ix_tablename_id rebuild tablespace ntbs online;

2.2.分区表

 SELECT    'ALTER TABLE '
           || table_owner
           || '.'
           || TABLE_NAME
           || ' MOVE PARTITION '
           || PARTITION_NAME
           || ' TABLESPACE NTBS;'
      FROM DBA_TAB_PARTITIONS
     WHERE TABLE_OWNER = 'OWNER' AND TABLE_NAME = 'TABLE_NAMES';

2.3.自动分区表

自动分区表在MOVE完后还要修改默认表空间

SELECT    'alter table '
       || owner
       || '.'
       || table_name
       || ' modify default attributes tablespace NTBLS;'
  FROM DBA_TABLES
 WHERE OWNER = 'OWNER' AND TABLE_NAME = 'TABLE_NAME';

3.MOVE索引

3.1.普通索引

alter index owner.tablename REBUILD TABLESPACE NTBS ONLINE;

如果online遇到如下错误：

第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01450: 超出最大的关键字长度 (3215)

那么不加online即可（oracle规定一个数据块中至少存放一个索引节点），详细可参考细说ora-01450错误

3.2.分区索引

SELECT    'ALTER INDEX '
           || index_owner
           || '.'
           || index_name
           || ' REBUILD PARTITION '
           || PARTITION_NAME
           || ' TABLESPACE NTBS ONLINE;'
      FROM DBA_ind_PARTITIONS
     WHERE index_owner = 'index_owner' AND INDEX_NAME =  'index_name';

3.3.自动分区索引

在MOVE完分区后，还需要修改默认表空间

SELECT    'ALTER INDEX '
       || owner
       || '.'
       || index_name
       || ' modify default attributes tablespace ntbs;'
  FROM dba_indexes
 WHERE OWNER = 'index_owner' AND INDEX_NAME = 'index_name';