Importing and Exporting Oracle Database

View more Tutorials:

Follow us on our fanpages to receive notifications every time there are new articles. Facebook Twitter

1- Import and Export in Oracle 11g

From Oracle 11 and onwards, Oracle uses a syntax different from previous Oracle versions' to import and export data. Basically, there are two ways of import/export:
  1. Import/Export some talbes of SCHEMA.
  2. Import/Export the whole SCHEMA (including objects, tables, views, functions, etc)
  1. Import/Export some talbes of SCHEMA.
  2. Import/Export the whole SCHEMA (including objects, tables, views, functions, etc)

1.1- Import/Export SCHEMA (11g)

First of all, you need to define a DBA Directory (DB Admin Directory) that attach to a real Path on your hard disk. In fact, you just need to declare one time and can use it in other turns.
For example, I define a DBA Directory as MY_BACKUP_DIR attaching to C:/oraclebackup folder.
Login to SQL Plus using user system:

- Create a directory named DBA MY_BACKUP_DIR attached to the actual directory on your hard drive
- Make sure that the C:/oraclebackup exist.

create directory MY_BACKUP_DIR AS 'C:/oraclebackup';
The DBA Directory created by the user system. Only users with DBA authority can use it. Use can also grant the privileges of using this DBA Directory for other users.

-- Grant read,write on directory to some user.

grant read,write on directory MY_BACKUP_DIR to Some_User;
You can query to see DBA Directory objects that have been created before and that are available in Oracle.

- Query all DBA directory in ORACLE
- By querying the view DBA_DIRECTORIES.

Select Directory_Name,Directory_Path From Dba_Directories;

1.1.1- Expdp

Use expdp to export the whole SCHEMA to a dump file.
To export you need open CMD and cd to the BIN directory of the Oracle.

cd C:\DevPrograms\db11g\product\11.2.0\dbhome_1\BIN

@rem DBA Directory: MY_BACKUP_DIR has been created in the previous step.
@rem SCHEMA export: learningsql

expdp system/admin DIRECTORY=MY_BACKUP_DIR DUMPFILE= learningsql.dmp SCHEMAS=learningsql LOGFILE=learningsql.log
Note: You can simultaneously export many Schemas to a dump file. For example:

expdp system/admin DIRECTORY=MY_BACKUP_DIR DUMPFILE= multi.dmp SCHEMAS=learningsql,simplehr LOGFILE= multi.log

1.1.2- Impdp

First you need to create a user learningsql2. Login in SQL Plus:
Execute statement to create a user:

-- User: learningsql2
-- Password: 1234

create user learningsql2 identified by 1234;
In essence you have created a empty SCHEMA.
Next, we import from dump file to Schema you have just created. In fact, in a dump file, there may be one or more SCHEMA. When you import a dump file into a certain Oracle schema, you need to specify source SCHEMA in dump file and the target SCHEMA.
Note that in ORACLE the name of SCHEMA is also the name of the User.
Open CMD and CD in the bin directory of Oracle:

cd C:\DevPrograms\db11g\product\11.2.0\dbhome_1\BIN
Execute impdp:

impdp system/admin DIRECTORY=MY_BACKUP_DIR DUMPFILE=learningsql.DMP REMAP_SCHEMA=learningsql:learingsql2 LOGFILE=learningsql_imp.log
  • Import from learningsql.DMP file
  • From source Schema: learningsql (in dump file)
  • To target Schema: learningsql2
Note: You may not need to create learningsql2 user first; impdb command will create this user. However, password is unidentified, so you need to reset.
The results run successfully:

View more Tutorials: