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

Datapump Schema Level in Oracle 19c : In this article we will see Datapump Schema Level in Oracle 19c and how we can take one or multiple Schema export level backup and import individual or multiple Schema 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

Schema Exports/Imports Syntax :-

expdp system/password@PROD directory=EXP_DIR schemas=scott dumpfile=SCOTT.dmp logfile=SCOTT.log

and

impdp system/password@PROD directory=EXP_DIR schemas=scott dumpfile=SCOTT.dmp logfile=import_SCOTT.log

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 ~]$
 mkdir -p /u01/backup/export/
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 14 21:45:24 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> GRANT READ, WRITE ON DIRECTORY exp_dir TO scott;

Grant succeeded.

SQL>

EXPORT :

Individual Schemas From system Schema :

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

Export: Release 19.0.0.0.0 - Production on Mon Dec 14 21:40:09 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_SCHEMA_01":  system/******** directory=EXP_DIR schemas=scott dumpfile=SCOTT.dmp logfile=SCOTT.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.953 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/export/SCOTT.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Dec 14 21:42:53 2020 elapsed 0 00:02:36

[oracle@vm-1 ~]$

Individual Schemas From same Schema :

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

Export: Release 19.0.0.0.0 - Production on Mon Dec 14 21:50:32 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_SCHEMA_01":  scott/******** directory=EXP_DIR schemas=scott dumpfile=SCOTT.dmp logfile=SCOTT.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.953 KB       5 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/export/SCOTT.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Dec 14 21:52:44 2020 elapsed 0 00:02:09

[oracle@vm-1 ~]$

Multiple Schemas From system Schema :

[oracle@vm-1 ~]$ expdp system/system@PROD directory=EXP_DIR schemas=scott,prasanta dumpfile=SCOTT_PRASANTA.dmp logfile=SCOTT_PRASANTA.log

Export: Release 19.0.0.0.0 - Production on Mon Dec 14 23:17:32 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_SCHEMA_01":  system/******** directory=EXP_DIR schemas=scott,prasanta dumpfile=SCOTT_PRASANTA.dmp logfile=SCOTT_PRASANTA.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.953 KB       5 rows
. . exported "PRASANTA"."EMPLOYEES"                      6.140 KB      10 rows
. . exported "PRASANTA"."TUTORIALS_TBL"                      0 KB       0 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/export/SCOTT_PRASANTA.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Dec 14 23:21:40 2020 elapsed 0 00:04:04

[oracle@vm-1 ~]$

IMPORT :