Export and Import in Oracle

Introduction

In this tutorial, you will learn how to Export and Import in Oracle 11g. This process is essential for database administrators to backup, transfer, and restore database schemas efficiently.

1. Create DBA Directory
2. Export all SCHEMA using expdp
3. Import all SCHEMA using impd

Export and Import in Oracle

Syntax for IMPDP command

IMPDP TABLE_EXISTS_ACTION PARAMETER EXPLAINED

TABLE_EXISTS_ACTION

• SKIP: The default value for this parameter is SKIP.
• APPEND: the dump will be appended to the table and the existing data will remain unchanged.
• TRUNCATE: This option truncates the existing rows in the table and inserts the rows from the dump
• REPLACE: This option drops the current table and creates the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the CONTENT=DATA_ONLY for the impdp.

Create a DBA Directory in Oracle

Login into SQL Plus with user sys.

[oracle@DBOracle ~]$ sqlplus / as sysdba

Creating folder DBA MY_BACKUP_DIR in /tmp folder

SQL> create or replace directory MY_BACKUP_DIR AS '/tmp/oraclebackup';

DBA Directory is created by the user system. Only users grant DBA to use this folder. you can assign grant Other_User.

SQL> grant read,write on directory MY_BACKUP_DIR to Other_User;

Export all SCHEMA using expdp

Using expdp command to export all schema into a dump file.

[oracle@DBOracle ~]$ expdp \"sys/123456789@DEVOPSROLES AS SYSDBA\" DIRECTORY=MY_BACKUP_DIR DUMPFILE=exp_ora.dmp SCHEMAS=huupv LOGFILE=exp_ora.log

Import all SCHEMA using impd

The first, create a user huupv

SQL> create user huupv identified by 123456789;

Import from sysadmin

[oracle@DBOracle ~]$ impdp \"sys/123456789@DEVOPSROLES AS SYSDBA\" schemas=huupv directory=MY_BACKUP_DIR dumpfile=exp_ora.dmp logfile=imp_ora.log

Note:

• File dump: exp_ora.dmp
• Service Name: DEVOPSROLES
• To schema: huupv

Conclusion

Through this article, you have learned how to use the Export and Import in Oracle 11g to manage database schemas. These tools are crucial for database backup, restoration, and migration tasks. I hope will this your helpful. Thank you for reading the DevopsRoles page!