測試分區表部分導出

測試過程：

expdp user/pass directory=dump4dev tables=PDATA.PC_INV dumpfile=PDATA.PC_INV.dmp logfile=PDATA.PC_INV.log query='PDATA.PC_INV:"where data_date>=20140707 and data_date<=20140708"'

create table test(

t_date varchar2(10),

t_num number(10)

)

partition by list (t_date)

(partition p_20160220 values ('20160220'),

partition p_20160221 values ('20160221'),

partition p_20160222 values ('20160222'),

partition p_20160223 values ('20160223'),

partition p_20160224 values ('20160224'),

partition p_20160225 values ('20160225'),

partition p_20160226 values ('20160226'),

partition p_20160227 values ('20160227'),

partition p_20160228 values ('20160228'),

partition p_20160229 values ('20160229'),

partition p_20160301 values ('20160301'),

partition p_20160302 values ('20160302'),

partition p_20160303 values ('20160303')

);

insert into test values('20160220',1);

insert into test values('20160221',2);

insert into test values('20160222',3);

insert into test values('20160223',4);

insert into test values('20160224',5);

insert into test values('20160225',6);

insert into test values('20160226',7);

insert into test values('20160227',8);

insert into test values('20160228',9);

insert into test values('20160229',10);

insert into test values('20160301',11);

insert into test values('20160302',12);

insert into test values('20160303',12);

select name,COLUMN_NAME from dba_PART_KEY_COLUMNS where name='TEST';

SELECT TABLE_NAME,PARTITION_NAME FROM dba_tab_partitions WHERE TABLE_NAME='TEST';

select * from test partition(P_20160222);

expdp.par內容:

DIRECTORY=DUMPDIR

DUMPFILE=test.dmp

LOGFILE=test.log

TABLE=SCOTT.TEST

INCLUDE=TABLE_DATA:"IN (select partition_name from dba_tab_partitions where table_name in ('TEST') and substr(partition_name,3,8) < '20160301')"

expdp system/oracle parfile=/server/soft/expdp.par

expdp system/oracle directory=DUMPDIR dumpfile=test.dmp logfile=test.log EXCLUDE=TABLE_DATA:"IN (select partition_name from dba_tab_partitions where table_name in ('TEST') and substr(partition_name, 3, 8) < '20160301')"

P20160312

金華銀行數據倉庫分區表導出具體代碼：

編寫一個par文件：

expdp.par內容:

DIRECTORY=DUMPDIR

DUMPFILE=test.dmp

LOGFILE=test.log

TABLE=SCOTT.TEST

EXCLUDE=TABLE_DATA:"IN (select partition_name from dba_tab_partitions where table_name in ('TEST') and substr(partition_name,2,8) < '20150101')"

expdp system/oracle parfile=/server/soft/expdp.par