Oracle数据导出-创建排除表

---

一、创建排除表exclude_table

create table exclude_table(
	id number(5),
	tablename varchar2(100) primary key
);

二、创建序列seq_exclude_table_id

create sequence seq_exclude_table_id
increment by 1
start with 1
nomaxvalue
nominvalue
nocache;

三、创建触发器tr_exclude_table_beforeinsert

create or replace trigger tr_exclude_table_beforeinsert
before insert on exclude_table
for each row
  begin
    select seq_exclude_table_id.nextval into :new.id from dual;
end;

四、插入测试数据，即排除表清单

insert into exclude_table values(null,'VIO_JDCZP');
insert into exclude_table values(null,'TFC_PASS_HIS');
insert into exclude_table values(null,'TFC_PASS');
insert into exclude_table values(null,'VIO_JDCZP_HIS');
insert into exclude_table values(null,'RECG_PASS_FEATURE');
insert into exclude_table values(null,'RM_STREAM_LOG');
insert into exclude_table values(null,'RECG_SUSP_RESULT_PHOTO');
insert into exclude_table values(null,'TFC_PASS_WPC');
insert into exclude_table values(null,'CMP3$112884');
insert into exclude_table values(null,'TFC_PASS_TODAY');
insert into exclude_table values(null,'RM_EWS_LOG');
insert into exclude_table values(null,'FRM_TRANS_ROWLOG_0214');
insert into exclude_table values(null,'RECG_DO_LOG');
insert into exclude_table values(null,'RECG_STAT_LOG');
insert into exclude_table values(null,'CTL_TRAFFIC_INDUCEMENT_FJ');
insert into exclude_table values(null,'TFC_PASS_SEND_ERR');
insert into exclude_table values(null,'FRM_TRANS_ROWLOG');
commit;

五、查询排除表清单

select * from exclude_table;

六、数据导出测试

6.1、导出除大表外的其他表数据，即小表数据

数据泵命令expdp导出小表数据：

expdp tzq/1@127.0.0.1/tzq network_link=tzq_jicheng directory=tzqdump dumpfile=20190915-RM-MIN.DMP SCHEMAS=RM logfile=20190915-RM-MIN.LOG exclude=TABLE:\"IN (select tablename from exclude_table)\"

6.2、导出剩余大表的100条数据

数据泵命令expdp导出大表的样例数据：

expdp tzq/1@127.0.0.1/tzq network_link=tzq_jicheng directory=tzqdump dumpfile=20190925-1538-RM-big-100.DMP SCHEMAS=RM logfile=20190925-1538-RM-big-100.LOG include=TABLE:\"IN (select tablename from exclude_table)\" query=\"where rownum<=100\"