Export and Import in oracle

In this tutorial, I will export and import in Oracle 11g.

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: Default value for this parameter is SKIP.
• APPEND: the dump will be appended to the table and the existing data remains unchanged.
• TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
• REPLACE: This option drop the current table and create 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 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 creates 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 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 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

Thought the article, you can use Export and Import in Oracle as above. I hope will this your helpful. Thank you for reading the DevopsRoles page!