Datapump Table Level in Oracle 19c - orakldba - ORACLE Tutorial

Datapump Table Level in Oracle 19c : In this article we will see Datapump Table Level in Oracle 19c and how we can take one or multiple table export level backup and import individual or multiple table in Oracle database.

This article guides you through the process of using the Export and Import utilities in Oracle Data Pump, or using the original Export and Import utilities in Oracle Database, to move table data from one database to another.

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.

Data Pump is useful for :

Moving data from one schema to another
Move data from one version of Oracle to another
Moving data from one OS to another
Creating logical backups

Table Exports/Imports Syntax :-

expdp system/password@PROD directory=EXP_DIR
 tables=scott.emp,scott.dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log
expdp scott/tiger@PROD directory=EXP_DIR tables=emp,dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log  -  same user

impdp system/password@PROD directory=EXP_DIR
 tables=scott.emp,scott.dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log
impdp scott/tiger@PROD directory=EXP_DIR tables=emp,dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log  -  same user

Prerequisite for Export/Import :

Atfirst select a existing user account or create a user account and create a directory object it can access. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.
Here we use an existing User ‘SCOTT’.

[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ mkdir -p /u01/backup/export/
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 13 09:43:08 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL>
SQL> CREATE OR REPLACE DIRECTORY exp_dir AS '/u01/backup/export/';

Directory created.

SQL>
SQL> GRANT READ, WRITE ON DIRECTORY exp_dir TO scott;

Grant succeeded.

SQL>

EXPORT :

Step-1 :- Individual tables From system Schema :

[oracle@vm-1 ~]$ expdp system/system@PROD directory=EXP_DIR tables=scott.emp dumpfile=EMP.dmp logfile=EMP.log

Export: Release 19.0.0.0.0 - Production on Sun Dec 13 10:09:27 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=EXP_DIR tables=scott.emp 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/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/backup/export/EMP.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 13 10:11:28 2020 elapsed 0 00:01:47

[oracle@vm-1 ~]$

Step-2 :- Individual tables From same Schema :

[oracle@vm-1 ~]$ expdp scott/tiger@PROD directory=EXP_DIR tables=emp dumpfile=EMP.dmp logfile=EMP.log

Export: Release 19.0.0.0.0 - Production on Sun Dec 13 10:12:24 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=EXP_DIR tables=emp 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/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/backup/export/EMP.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 13 10:13:16 2020 elapsed 0 00:00:50

[oracle@vm-1 ~]$

Step-3 :- Multiple tables From system Schema :

[oracle@vm-1 ~]$ expdp system/system@PROD directory=EXP_DIR tables=scott.emp,scott.dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log

Export: Release 19.0.0.0.0 - Production on Sun Dec 13 10:16:27 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=EXP_DIR tables=scott.emp,scott.dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.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/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/backup/export/EMP_DEPT.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 13 10:16:54 2020 elapsed 0 00:00:26

[oracle@vm-1 ~]$

Step-4 :- Multiple tables From same Schema :

[oracle@vm-1 ~]$ expdp scott/tiger@PROD directory=EXP_DIR tables=emp,dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.log

Export: Release 19.0.0.0.0 - Production on Sun Dec 13 10:17:34 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=EXP_DIR tables=emp,dept dumpfile=EMP_DEPT.dmp logfile=EMP_DEPT.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/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/backup/export/EMP_DEPT.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 13 10:17:50 2020 elapsed 0 00:00:14

[oracle@vm-1 ~]$

IMPORT :