Oracle 18c Datapump-Table level

Oracle 18c Datapump-Table level

Description:-

  • In this article we are going to see the Oracle 18c Datapump-Table level
  • We can take export single table or multiple tables with the help of TABLES data pump parameter.

Syntax:-

expdp system/password tables=hr.dept,hr.emp dumpfile=data.dmp directory=TEST_DIR
expdp hr/hr tables=dept,emp dumpfile=data.dmp directory=TEST_DIR-same user

Let’s start the Demo:-

Pre-steps:-create a directory in the filesystem and creates a directory object in the database and grants privileges to the HR user.

[oracle@testdb ~]$ mkdir –p /u01/app/oracle/datapump
[oracle@testdb ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 18.0.0.0.0 Production on Sat Jun 30 05:50:19 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> alter session set container=pdb1;
Session altered.

SQL> create or replace directory test_dir as '/u01/app/oracle/datapump';
Directory created.

SQL> grant read,write on DIRECTORY test_dir to hr;
Grant succeeded.

Demo 1:-Export individual tables From system Schema

[oracle@testdb human_resources]$ expdp system/Chennai#123@pdb1 tables=hr.employees directory=TEST_DIR dumpfile=emp.dmp logfile=emp.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 06:26:28 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@pdb1 tables=hr.employees directory=TEST_DIR dumpfile=emp.dmp logfile=emp.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 30 06:26:58 2018 elapsed 0 00:00:29

Demo 2:-Export individual tables From HR Schema

[oracle@testdb human_resources]$ expdp hr/hr@pdb1 tables=employees directory=TEST_DIR dumpfile=emp1.dmp logfile=emp1.log 

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 06:30:05 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@pdb1 tables=hr.employees directory=TEST_DIR dumpfile=emp1.dmp logfile=emp1.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp1.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 30 06:30:26 2018 elapsed 0 00:00:20

Demo 3:-Export multiple tables From HR Schema

[oracle@testdb human_resources]$ expdp hr/hr@pdb1 tables=employees,departments directory=TEST_DIR dumpfile=emp3.dmp logfile=emp3.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 06:38:27 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@pdb1 tables=employees,departments directory=TEST_DIR dumpfile=emp3.dmp logfile=emp3.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
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/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/emp3.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jun 30 06:38:43 2018 elapsed 0 00:00:15

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba