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

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.

SQL> 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

SQL> 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.

About HuuPV

My name is Huu. I love technology and especially Devops Skill such as Docker, vagrant, git so forth. I likes open-sources. so I created DevopsRoles.com site to share the knowledge that I have learned. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published. Required fields are marked *

Comment moderation is enabled. Your comment may take some time to appear.

This site uses Akismet to reduce spam. Learn how your comment data is processed.