How to export partition table by using expdp
Posted by Mir Sayeed Hassan on October 9th, 2017
How to export partition table by using expdp
Consider the below example tested in our test env & verified
Create the directory at OS Level to place the backup of the dumpfile
[oracle@testdb backup]$ mkdir db_dump_bkp
[root@testdb backup]# chmod -R 777 db_dump_bkp/
Create the directory at DB Level & assign the directory with OS dir name along with require privileges
SQL> create directory db_dump_bkp as '/backup/db_dump_bkp'; Directory created.
SQL> grant read,write on directory db_dump_bkp to system; Grant succeeded.
SQL> grant exp_full_database to system; Grant succeeded.
SQL> exit
Create a table with partition & export the specific partition by using the data pump
SQL> Create tablespace test_partition ‘/u01/app/oracle/oradata/prim/test_partition.dbf’ size 100m; Tablespace created
SQL> Create used mir identified by ***; User created.
SQL> alter user mir default tablespace test_partition; User altered.
SQL> select username,default_tablespace from dba_users where username='MIR'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ MIR TEST_PARTITION
SQL> connect mir Enter password: Connected.
SQL> CREATE TABLE sales_p(order_id NUMBER(20),order_date DATE)
PARTITION BY RANGE(order_date)
(PARTITION sales_p_jan VALUES LESS THAN (to_date('1/1/2018','dd/mm/yyyy')) tablespace test_partition
,PARTITION sales_p_feb VALUES LESS THAN (to_date('1/2/2018','dd/mm/yyyy')) tablespace test_partition
,PARTITION sales_p_mar VALUES LESS THAN (to_date('1/3/2018','dd/mm/yyyy')) tablespace test_partition
,PARTITION sales_p_dec VALUES LESS THAN (MAXVALUE));
Table created.
SQL> SELECT partitioned FROM user_tables WHERE table_name = 'SALES_P'; PAR --- YES
SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'SALES_P'; PARTITION_NAME ------------------------------ SALES_P_DEC SALES_P_FEB SALES_P_JAN SALES_P_MAR
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ TEST2 TEST1 SALES_P
SQL> desc sales_p; Name Null? Type ----------------------------------------- -------- ---------------------------- ORDER_ID NUMBER(20) ORDER_DATE DATE
SQL> insert into sales_p values(1,'1/1/2018'); 1 row created. SQL> insert into sales_p values(1,'1/2/2018'); 1 row created. SQL> insert into sales_p values(1,'1/3/2018'); 1 row created. SQL> commit; Commit complete.
Syntax:
[oracle@testdb db_dump_bkp]$ expdp username/password directory= dumpfile=<dumpfile_name with.dmp extension< logfile=& tables=
Example: To Export the specific partition table
[oracle@testdb db_dump_bkp]$ expdp system directory=db_dump_bkp dumpfile=sales_p_tp.dmp logfile=sales_tp.log tables=mir.SALES_P:SALES_P_FEB Export: Release 11.2.0.4.0 - Production on Mon Oct 9 17:15:20 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: ***** Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=db_dump_bkp dumpfile=sales_p_tp.dmp logfile=sales_tp.log tables=mir.SALES_P:SALES_P_FEB Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 8 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ . . exported "MIR"."SALES_P":"SALES_P_FEB" 5.5 KB 3 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded *************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /backup/db_dump_bkp/sales_p_tp.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 9 17:15:29 2017 elapsed 0 00:00:06 [oracle@testdb db_dump_bkp]$ ls sales_p_tp.dmp sales_tp.log