Mir Sayeed Hassan – Oracle Blog

How to export table level 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

Syntax to export the full db dump

[oracle@testdb db_dump_bkp]$ expdp username/password directory= dumpfile= logfile= table=

Example:

[oracle@testdb db_dump_bkp]$ expdp system directory=db_dump_bkp dumpfile=tbl_test1_bkp10oct17.dmp logfile=tbl_test1_bkp10oct17.log tables=mir.test1

Export: Release 11.2.0.4.0 - Production on Mon Oct 9 11:57:36 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=tbl_test1_bkp10oct17.dmp logfile=tbl_test1_bkp10oct17.log tables=mir.test1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ
. . exported "MIR"."TEST1"                               5.085 KB       5 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/tbl_test1_bkp10oct17.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 9 11:57:45 2017 elapsed 0 00:00:03

[oracle@testdb db_dump_bkp]$ ls

tbl_test1_bkp10oct17.dmp tbl_test1_bkp10oct17.log

Note:

If you want to export multiple tables to exports use the below syntax

tables=, , or more

Example:

tables=mir.test1,mir.test2 …..

Hence taken the backup of the table (test1) from mir schema using data pump(expdp)