navicat 数据迁移一次只能一张表_记一次生产Oracle数据库千万级别以上的表迁移方案...

概述

今天这部分瘦身内容主要是为了清理XX表空间千万级别以上的大表数据，每个表只保留最近一年的数据。下面先看下目前大表的数据情况。

---

1、查看大表数据情况

1.1、先创建一张表用于存放表名及该表的行数

drop table table_rows purge;create table table_rows(table_name varchar(100),count_rows number);

1.2、统计数据脚本

declare v_table varchar(100); v_num number; v_sql varchar(500); cursor c1 is select table_name from user_tables; --这里可以考虑改成select table_name from dba_tables where owner='xxx',注意下面v_sql需对应加ownerbegin DBMS_OUTPUT.ENABLE(buffer_size => null); ----表示输出buffer不受限制 open c1; loop fetch c1 into v_table; if c1%found then v_sql := 'select count(*) from "' || v_table || '"'; execute immediate v_sql  into v_num; dbms_output.put_line('table_name： ' || v_table || ' count_rows： ' || v_num); insert into table_rows values (v_table, v_num); else exit; end if; end loop; commit;end;

1.3、查看表数据量

SELECT * FROM table_rows order by count_rows desc;

1.4、查看某个表每日增长情况

--快照只有8天，所以只能看最近8天的select obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD') start_day, sum(a.db_block_changes_delta) block_increase, sum(a.db_block_changes_delta)*8/1024/1024 MB_INCREASE from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj where sn.snap_id = a.snap_id and obj.object_id = a.obj# and obj.owner not in ('SYS', 'SYSTEM') and end_interval_time between to_timestamp('01-MAY-2019', 'DD-MON-RRRR') and to_timestamp('14-MAY-2019', 'DD-MON-RRRR') and object_name='TAB_SP_OTMSHIPMENT' group by obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME, 'RRRR-MON-DD') order by obj.owner, obj.object_name;

---

2、大表历史数据迁移

这里是计划迁移到另外一个数据库(专门做备份归档)

2.1、源库--导出表定义

expdp rfuser/"xxxxg" directory=dp_hwb dumpfile=TABLE_DDL.dmp LOGFILE=TABLE_DDL.log content=metadata_only TABLES=TAB_SP_OTMSHIPMENT,TAB_OTMSHIPMENT,TABSPSHIPMENT_LPN,TAB_TEMP_SHIPUNIT,CAR_APPLY_TRAYS_BOXES,MV_OTM_S_SHIP_UNIT_LINE,RF_TO_TMS_BR_TABRECEIVING,TAB_GOODSLABEL_PRIMARY....TABRECEIVING_RDC exclude=statistics parallel=4 cluster=no

2.2、源库--导出表数据

expdp rfuser/"xxxx" directory=dp_hwb dumpfile=TABLE_DATA.dmp LOGFILE=TABLE_DATA.log content=data_only TABLES=TAB_SP_OTMSHIPMENT....G_RDC exclude=statistics parallel=4 cluster=no

2.3、目的库--导入表定义

impdp rfuser/xxx directory=IMP_HWB dumpfile=TABLE_DDL.dmp LOGFILE=TABLE_DDL.log content=metadata_only remap_schema=RF_TSK:RFUSER emap_schema=RF_LDY:RFUSER remap_tablespace=RF_INDX:RF_ORDER remap_tablespace=FROM_OTM:RF_ORDER table_exists_action=replace full=y parallel=4 cluster=no;

2.3、目的库--导入表数据

impdp rfuser/rfuser_123 directory=IMP_HWB dumpfile=TABLE_DATA.dmp LOGFILE=TABLE_DATA.log content=data_only remap_schema=RF_TSK:RFUSER emap_schema=RF_LDY:RFUSER remap_tablespace=RF_INDX:RF_ORDER remap_tablespace=FROM_OTM:RF_ORDER full=y parallel=4 cluster=no;

3、验证数据完整性

select 'TAB_SP_OTMSHIPMENT', count(*) from TAB_SP_OTMSHIPMENT union all......select 'TABRECEIVING', count(*) from TABRECEIVING union allselect 'TABRECEIVING_RDC', count(*) from TABRECEIVING_RDC

---

篇幅有限，这里就介绍到这里了，在对比两边数据一致后就可以考虑删除数据了，但是这个删除的表都是上千万的，这里应该怎么正确的删除呢？哪种效率会比较高呢？

后面会分享删除源库数据及后续清理方面的内容，感兴趣的朋友可以关注下！