Schema Level Export & Import using Datapump in Oracle19c

DESCRIPTION:

In this tutorial we going to learn how to export & Import schema using expdp & Impdp using data pump in Oracle19cDatabase.

A schema is a collection of a logical structure of data or, database objects owned by a database user and shares the same name as the user.Using expdp export utility you can export any schema of your database or we can also say that using expdp export data pump we can take logical backup of any schema in Oracle database.

DIRECTORY:

create a directory object you can use CREATE DIRECTORY command.

Grant DATAPUMP_EXP_FULL_DATABASE role:

Apart from granting read and write privilege on the directory to the user we also need to grant DATAPUMP_EXP_FULL_DATABASE role to the user who wants to perform the export.

SCHEMA:

DATA

In below mentioned query we are going to export schemas at the values of SCHEMA parameter,

[oracle@oracle19c ~]$ expdp data/pump schemas=data directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 07:02:14 2021

Version 19.3.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 "DATA"."SYS_EXPORT_SCHEMA_01":  data/******** schemas=data directory=My_Dir dumpfile=schema.dmp logfile=schema.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/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/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Master table "DATA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/oradata/schema.dmp

Job "DATA"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jan 25 07:04:22 2021 elapsed 0 00:01:48

Export utility successfully completed.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 07:08:24 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0.

SQL> drop user data;

User dropped.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Now we are going to see IMPORT activity.

Performing the import of Schemas of your database is as simple as exporting them. Similar to expdp Schema export, we use SCHEMAS parameter to perform the import of the schema. SCHEMAS parameter specifies that user wants to perform Schema-Mode import. Also using Schema parameter helps you in choosing a particular schema from the multiple schema export to import

Executing this impdp data pump import command will import the schema DATA into your database.

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=schema.dmp logfile=schema.log

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 07:09:09 2021

Version 19.3.0.0.0

Username: system

Password:

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

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=My_Dir dumpfile=schema.dmp logfile=schema.log

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

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 07:09:33 2021 elapsed 0 00:00:05

Import action completed succesfully.Now we can connect to the schemas.

[oracle@oracle19c ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Mon Jan 25 07:09:38 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0 SQL> conn data/pump; Connected. Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8