oracle迁移到新环境,[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';