o7planning

Importing and Exporting Oracle Database

  1. Import and Export in Oracle 11g

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)
  • Import/Export some talbes of SCHEMA.
  • Import/Export the whole SCHEMA (including objects, tables, views, functions, etc)
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;
Result:

Newest Articles

Show More