把表和索引移动到另外的表空间

查看表和索引所在的表空间所用视图：dba_segments，还可能会用dba_lobs;

1)移动的表：

alter table tb_name move tablespace tbs_name;

2)移动带clob，blob的字段：

alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as(tablespace lobs_tbs_name);

3)移动索引

alter index idx_name rebuild tablespace idx_tbs_name;

查看表空间里的内容

SQL> SELECT t.owner, t.segment_name, t.segment_type
  2    FROM dba_segments t
  3   WHERE t.tablespace_name = 'LOBTPS_NAME';

查看LOB表空间里的内容

SQL> SELECT t.owner, t.table_name, t.column_name, t.segment_name, t.index_name
  2    FROM dba_lobs t
  3   WHERE t.tablespace_name = 'LOBTPS_NAME';

新建一个临时表空间

CREATE TABLESPACE lobtbs1 DATAFILE '/data/directory/LOBTPS_NAME_MOVEBK.dbf' SIZE 200M;

alter user your_user_name  quota unlimited on LOBTPS_NAME_MOVEBK;

批量移动表

SQL>  SELECT 'alter table ' || t.owner || '.' || t.segment_name ||
  2          ' move tablespace LOBTPS_NAME;'
  3     FROM dba_segments t
  4    WHERE t.tablespace_name = 'LOBTPS_NAME_MOVEBK'
  5      AND t.segment_type = 'TABLE';

批量移动索引
SELECT 'alter index ' || index_name ||
       ' rebuild tablespace new_idx_tbs;'
FROM user_indexes
WHERE tablespace_name = 'OLD_IDX_TBS';

 

批量移动lob字段

SQL> SELECT 'alter table ' || t.owner || '.' || t.table_name ||
  2         ' move tablespace LOBTPS_NAME lob (' || t.column_name ||
  3         ') store as(tablespace LOBTPS_NAME_MOVEBK);' v_sql
  4    FROM dba_lobs t
  5   WHERE t.tablespace_name = 'LOBTPS_NAME';

SQL> alter database datafile 8 resize 100 M;
 
Database altered