[oracle] 迁移表到新表空间

查看表是否是分区表
select t.owner,p.table_name,count(p.partition_name) from dba_tab_partitions p,dba_tables t where p.table_name = t.table_name and t.table_name like '%xx%'  group by t.owner,p.table_name;

无

对已下表进行迁移

xx20210122

一、创建新的表空间
create tablespace tbs_bak datafile '+DATADG/REPORTDB/DATAFILE/tbs_bak01.dbf' size 1g autoextend on next 1g maxsize 30g;

二、表迁移
select sum(bytes/1024/1024/1024) from dba_segments where segment_name = 'xx20210122';
SUM(BYTES/1024/1024/1024)
-------------------------
           117.816406

alter table xx.xx20210122 move tablespace tbs_bak;

三、索引重建
col table_name for a30
col index_name for a30
select table_name,index_name,INDEX_TYPE,STATUS from dba_indexes where table_name = 'xx20210122';

TABLE_NAME               INDEX_NAME              INDEX_TYPE                                                   STATUS
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------ --------------------------------
xx20210122                  xx_IDX1          NORMAL                                                       VALID

select sum(bytes/1024/1024/1024) from dba_segments where segment_name = 'xx_IDX1';
SUM(BYTES/1024/1024/1024)
-------------------------
           30.0785522

四、并行重建索引
alter index xx_IDX1 rebuild tablespace tbs_bak parallel 8;
取消并行
ALTER  INDEX  xx_IDX1 noparallel;

五、分区表迁移
select t.owner,p.table_name,count(p.partition_name),sum(SUBPARTITION_COUNT) from dba_tab_partitions p,dba_tables t where p.table_name = t.table_name and t.table_name = 'xx20210115'  group by t.owner,p.table_name;
OWNER                                                                 TABLE_NAME                                      COUNT(P.PARTITION_NAME) SUM(SUBPARTITION_COUNT)
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
xx                                                                 xx20210115                               337            0

六、分区表迁移
select owner,tablespace_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name = 'xx20210115' group by owner,tablespace_name;

TABLESPACE_NAME            SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
xx                      13.8002319

七、索引
SYS@reportdb1>select owner,table_name,index_name,INDEX_TYPE,STATUS from dba_indexes where table_name = 'xx20210115';

no rows selected

八、分区迁移
SELECT 'alter table ods.' ||t.TABLE_NAME || ' move partition  '|| t.PARTITION_NAME ||' tablespace tbs_bak;'  FROM dba_tab_partitions t  WHERE table_name='xx20210115' and tablespace_name ='xx';