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的改变而失效，因此需要重建

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

3.里面如果是分区表，按如下move分区到新分区

    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';

如果这个表是自动分区的，修改新增分区默认表空间

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

4.里面如果是普通索引，按如下重建索引

alter index owner.tablename REBUILD TABLESPACE NTBS ONLINE;

如果online遇到如下错误

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

那么不加online即可（oracle规定一个数据块中至少存放一个索引节点，详细可参考https://blog.csdn.net/yidian815/article/details/16336911）

5.例如如果是分区索引，按如下重建索引

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';

如果分区为自增分区，修改分区索引的默认表空间

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

 

转载于:https://www.cnblogs.com/monkey6/p/11221643.html