Oracle Data Pump Single parititon export/transport


[oracle@anardbrac1 ~]$ sqlplus '/as sysdba'
SQL> CREATE TABLESPACE transport_test_ts_1
 DATAFILE 'tt_ts1.dbf'
 SIZE 128K AUTOEXTEND ON NEXT 128K;
SQL> CREATE TABLESPACE transport_test_ts_2
 DATAFILE 'tt_ts2.dbf'
 SIZE 128K AUTOEXTEND ON NEXT 128K;
SQL> ALTER USER test
 QUOTA UNLIMITED ON transport_test_ts_1
 QUOTA UNLIMITED ON transport_test_ts_2;
SQL> CONN test/test
SQL> CREATE TABLE transport_test_tab (
 id NUMBER NOT NULL,
 code VARCHAR2(10) NOT NULL,
 description VARCHAR2(50),
 created_date DATE,
 CONSTRAINT transport_test_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01-10-2015','DD-MM-YYYY'))
 TABLESPACE transport_test_ts_1,
 PARTITION part_2011 VALUES LESS THAN (TO_DATE('01-10-2011','DD-MM-YYYY'))
 TABLESPACE transport_test_ts_2
);
SQL> INSERT INTO transport_test_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
SQL> INSERT INTO transport_test_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
SQL> INSERT INTO transport_test_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
SQL> INSERT INTO transport_test_tab VALUES (4, 'FOUR', '4 FOUR', ADD_MONTHS(SYSDATE,12));
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSPORT_TEST_TAB');
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN tablespace_name FORMAT A20
SQL> set linesize 1000
SQL> SELECT table_name, partition_name, tablespace_name, num_rows
FROM user_tab_partitions
Where table_name='TRANSPORT_TEST_TAB';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
TRANSPORT_TEST_TAB PART_2015 TRANSPORT_TEST_TS_1 2
TRANSPORT_TEST_TAB PART_2011 TRANSPORT_TEST_TS_2 2
SQL> ALTER TABLESPACE transport_test_ts_1 READ ONLY;
exit
[oracle@anardbrac1 ~]$ sqlplus '/as sysdba'
SQL> CREATE OR REPLACE DIRECTORY data_pump_dir AS '/u01/app11g/backup/';
SQL> GRANT READ, WRITE ON DIRECTORY data_pump_dir TO public;
[oracle@anardbrac1 ~]$ expdp system/oracle tables=test.transport_test_tab:part_2015 transportable=always directory=data_pump_dir dumpfile=part_2015.dmp
Export: Release 11.2.0.1.0 - Production on Wed NOV 18 13:35:17 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=test.transport_test_tab:part_2015 transportable=always directory=data_pump_dir dumpfile=part_2015.dmp 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
 /u01/app11g/backup/part_2015.dmp
******************************************************************************
Datafiles required for transportable tablespace TRANSPORT_TEST_TS_1:
 /u01/app11g/oracle/product/11.2.0/db_1/dbs/tt_ts1.dbf
Datafiles required for transportable tablespace USERS:
 /u01/app/oracle/oradata/EDUCATE/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 13:36:23
[oracle@anardbrac1 ~]$ sqlplus '/as sysdba'
SQL> DROP TABLE test.transport_test_tab;
SQL> DROP TABLESPACE transport_test_ts_1 INCLUDING CONTENTS;
SQL> DROP TABLESPACE transport_test_ts_2 INCLUDING CONTENTS AND DATAFILES;
[oracle@anardbrac1 ~]$ impdp system/oracle partition_options=departition dumpfile=part_2015.dmp transport_datafiles='tt_ts1.dbf'
$ impdp system/password partition_options=departition dumpfile=part_2007.dmp transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
Import: Release 11.2.0.1.0 - Production on Wed NOV 18 13:42:38 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** partition_options=departition dumpfile=part_2015.dmp transport_datafiles=tt_ts1.dbf 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE UNIQUE INDEX "TEST"."TRANSPORT_TEST_PK" ON "TEST"."TRANSPORT_TEST_TAB" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 635 OBJNO_REUSE 85559 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1 
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TEST"."TRANSPORT_TEST_TAB" ADD CONSTRAINT "TRANSPORT_TEST_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 635 OBJNO_REUSE 85559 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"TEST"."TRANSPORT_TEST_PK" creation failed
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at 13:42:50
[oracle@anardbrac1 ~]$ sqlplus '/as sysdba'
SQL> CONN test/test
SQL> EXEC DBMS_STATS.gather_schema_stats(USER);
SQL> COLUMN table_name FORMAT A30
SQL> COLUMN tablespace_name FORMAT A20
SQL> set linesize 1000
SQL> SELECT table_name, tablespace_name, partitioned, num_rows
FROM user_tables
Where tablespace_name like '%TRANSPORT_TEST_TS_1%';
TABLE_NAME TABLESPACE_NAME PAR NUM_ROWS
------------------------------ -------------------- --- ----------
TRANSPORT_TES_PART_2007 TRANSPORT_TEST_TS_1 NO 2
1 row selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: