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
