概述
今天这部分瘦身内容主要是为了清理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;
![3426afd31ccd825f193eead9e70cf2ea.png](https://i-blog.csdnimg.cn/blog_migrate/307ae2b4c6ddebac65de99e165fbf96a.jpeg)
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;
![183c08969e9831da1b62e4376bee2321.png](https://i-blog.csdnimg.cn/blog_migrate/69e3c29413cccd45260426da8c9c1905.jpeg)
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
![a3c73ed70874c500ac07a5610b4857f1.png](https://i-blog.csdnimg.cn/blog_migrate/e4d0c6327eb8cf2f1ac041f566ffcea6.jpeg)
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
![e52f4178fe69e88a9a29a3a349f9e997.png](https://i-blog.csdnimg.cn/blog_migrate/4de2bd7df237fced37cbc06709e7639a.jpeg)
篇幅有限,这里就介绍到这里了,在对比两边数据一致后就可以考虑删除数据了,但是这个删除的表都是上千万的,这里应该怎么正确的删除呢?哪种效率会比较高呢?
后面会分享删除源库数据及后续清理方面的内容,感兴趣的朋友可以关注下!
![74e3a96ccf62b68c37e971a1c1b17c2f.png](https://i-blog.csdnimg.cn/blog_migrate/12fb29788f1b131ec2f45d2ca62816af.jpeg)