Database Software

Hi All,
I want to import a partition in an existing partitioned table. And for that I have followed below steps. But ultimately the outcome is not what I have expected. Now I am looking for your suggestion. My OS is RHEL 5.4 and DB version is 10.2.0.1.0

--Create Partition Table and insert data*

create table partition_test (
created_date date,
id number)
partition by range (created_date)
(partition part_min values less than (TO_DATE('01/11/2011','DD/MM/YYYY')),
partition part_nov11 values less than (TO_DATE('01/12/2011','DD/MM/YYYY')),
partition part_dec11 values less than (TO_DATE('01/01/2012','DD/MM/YYYY')),
partition part_jan12 values less than (TO_DATE('01/02/2012','DD/MM/YYYY')));


ALTER TABLE partition_test ADD (
CONSTRAINT partition_test_pk PRIMARY KEY (id)
);

insert into partition_test values ('02-Oct-2011',1);
insert into partition_test values ('03-Oct-2011',2);
insert into partition_test values ('01-Nov-2011',3);
insert into partition_test values ('02-Nov-2011',4);
insert into partition_test values ('03-Nov-2011',5);
insert into partition_test values ('01-Dec-2011',6);
insert into partition_test values ('02-Dec-2011',7);
insert into partition_test values ('01-Jan-2012',8);


select partition_name, num_rows from DBA_TAB_PARTITIONS where table_name = 'PARTITION_TEST';

PART_DEC11 2
PART_JAN12 1
PART_MIN 2
PART_NOV11 3

--Now I export with datapump of the whole table*
expdp username/passwd directory=BACKUPDUMP dumpfile=exp_part_test.dmp logfile=exp_part_test.log tables=username.partition_test

--Drop one partition and check the table*
alter table partition_test drop partition PART_NOV11 update global indexes;

select partition_name, num_rows from DBA_TAB_PARTITIONS where table_name = 'PARTITION_TEST';
PART_DEC11 2
PART_JAN12 1
PART_MIN 2


Now I want to restore this partition. so i execute below command

--impdp with table_exists_action=append*
impdp username/passwd dumpfile=exp_part_test.dmp logfile=impdp_test_part.log tables=username.PARTITION_TEST:PART_NOV11 exclude=INDEX exclude=table_statistics exclude=index_statistics directory=BACKUPDUMP table_exists_action=append

select partition_name, num_rows from DBA_TAB_PARTITIONS where table_name = 'PARTITION_TEST';
PART_DEC11 5
PART_JAN12 1
PART_MIN 2

--impdp with table_exists_action=replace*
impdp username/passwd dumpfile=exp_part_test.dmp logfile=impdp_test_part.log tables=username.PARTITION_TEST:PART_NOV11 exclude=INDEX exclude=table_statistics exclude=index_statistics directory=BACKUPDUMP table_exists_action=replace

select partition_name, num_rows from DBA_TAB_PARTITIONS where table_name = 'PARTITION_TEST';
PART_DEC11 0
PART_JAN12 0
PART_MIN 0
PART_NOV11 3

But I want the data like previous

PART_DEC11 2
PART_JAN12 1
PART_MIN 2
PART_NOV11 3

Any Idea how to get this?

Edited by: Megatron on Nov 28, 2011 2:17 AM

Edited by: Megatron on Nov 28, 2011 2:19 AM

Edited by: Megatron on Nov 28, 2011 2:22 AM