ITPub博客

首页 > 数据库 > Oracle > [oracle] 迁移表到新表空间

[oracle] 迁移表到新表空间

原创 Oracle 作者:匠人xin 时间:2021-01-22 22:33:45 0 删除 编辑

查看表是否是分区表
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.816406

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



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69993564/viewspace-2752242/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2021-01-19

  • 博文量
    31
  • 访问量
    11374