In this tutorial, I will export and import in Oracle 11g.
- Create DBA Directory
- Export all SCHEMA using expdp
- 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!