Importing and Exporting Oracle Database

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.1- Import/Export SCHEMA (11g)

First of all, you need to define a DBA directory (a virtual 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 folder 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';
Directory of user system has been created. Only users with DBA authority can use it. Use can also grant the privileges of using 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 dump file in some user on Oracle, you need to specify source SCHEMA in dump file and the target SCHEMA.
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
impdp command means:
  • Import from dump learningsql.DMP file
  • From  learningsql source Schema (in dump file)
  • To learningsql2 target Schema
Note: You may not need to create learningsql2 user first; impdb command will create user and import in by itself. However, password is unidentified, so you need to reset.
The results run successfully: