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