Oracle data pump using expdp and impdp
Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.
All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string. This means that for unprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server file system.
So, the following are the steps to use data pump technology.
- Create a directory
- grant read, write permission on a directory to respective user.
- export the data or metadata using expdp.
- transfer the dump file to the respective destination database location.
- import the data using impdp utility
Step 1. Create a directory at OS level.
|
1 |
mkdir -p /u01/TEST/DBDUMP |
Step 2. Create a directory at a database level.
|
1 2 3 |
SYS> create directory DBDUMP as '/u01/TEST/DBDUMP'; Directory created. |
Step 3: Grant permissions.
|
1 2 3 4 5 |
SYS> SYS> grant read, write on directory DBDUMP to AUDIT_TEST; Grant succeeded. |
Export the data using the below command
expdp AUDIT_TEST directory=directory_name dumpfile=dumpfile_name.dmp logfile=logfile_name-exp.log tables=ABC
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
[oracle@stagdb DBDUMP]$ [oracle@stagdb DBDUMP]$ expdp AUDIT_TEST directory=DBDUMP dumpfile=ABCTABLE.dmp logfile=ABCTAB.log tables=ABC Export: Release 11.2.0.3.0 - Production on Tue Mar 27 19:51:07 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "AUDIT_TEST"."SYS_EXPORT_TABLE_01": AUDIT_TEST/******** directory=DBDUMP dumpfile=ABCTABLE.dmp logfile=ABCTAB.log tables=ABC 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 . . exported "AUDIT_TEST"."ABC" 5.445 KB 4 rows Master table "AUDIT_TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for AUDIT_TEST.SYS_EXPORT_TABLE_01 is: /u01/TEST/DBDUMP/ABCTABLE.dmp Job "AUDIT_TEST"."SYS_EXPORT_TABLE_01" successfully completed at 19:51:20 |
Step 4: Copy the ABCTABLE.dmp file to respective destination location.
Step 5: import the data using the below command.
impdp directory=DBDUMP logfile=abc.log dumpfile=ABCTABLE.dmp table_exist_action=replace remap_schema=AUDIT_TEST:SCOTT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
[oracle@stagdb DBDUMP]$ [oracle@stagdb DBDUMP]$ impdp directory=DBDUMP logfile=abc.log dumpfile=ABCTABLE.dmp table_exist_action=replace remap_schema=AUDIT_TEST:SCOTT LRM-00101: unknown parameter name 'table_exist_action' [oracle@stagdb DBDUMP]$ impdp directory=DBDUMP logfile=abc.log dumpfile=ABCTABLE.dmp table_exists_action=replace remap_schema=AUDIT_TEST:SCOTT Import: Release 11.2.0.3.0 - Production on Tue Mar 27 19:52:26 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: SCOTT Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_FULL_01": SCOTT/******** directory=DBDUMP logfile=abc.log dumpfile=ABCTABLE.dmp table_exists_action=replace remap_schema=AUDIT_TEST:SCOTT Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."ABC" 5.445 KB 4 rows Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 19:52:34 |
Important dictionary views:
|
1 2 3 4 5 |
DBA_DATAPUMP_JOBS USER_DATAPUMP_JOBS V$SESSION_LONGOPS |
Also read, Backup and Recovery concept.
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.