[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;无对已下表进行迁移 xx一、创建新的表空间 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 = 'xx'; SUM(BYTES/1024/1024/1024) -------------------------            117.816406alter table xx.xx 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 = 'INTF_CL_LOAN_REC20210122';TABLE_NAME               INDEX_NAME              INDEX_TYPE                                                   STATUS ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------ -------------------------------- xx             xx_IDX1          NORMAL                                                       VALIDselect 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 = 'xx'  group by t.owner,p.table_name; OWNER                                                                 TABLE_NAME                                      COUNT(P.PARTITION_NAME) SUM(SUBPARTITION_COUNT) -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- xx                                                                 xx                                                                337            0 六、分区表迁移 select owner,tablespace_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name = 'xx' group by owner,tablespace_name;TABLESPACE_NAME            SUM(BYTES/1024/1024/1024) ------------------------------ ------------------------- TS_ODS_DATA                      13.8002319七、索引 select owner,table_name,index_name,INDEX_TYPE,STATUS from dba_indexes where table_name = 'xx';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='xx' and tablespace_name ='xx';