Oracle Database Import, Oracle Database Import

Oracle Database Import, Oracle Database Import

When the system was deployed at the electronic Institute in March October, the export and import of the Oracle database were successful using the exp and imp export database commands, I also published an article on Oracle Database Export and Import to explain the Export and Import processes.

Yesterday again on the server newly installed Oracle database with exp, imp Export Database, has been reported "EXP-0006: internal inconsistency error", so on the Internet Baidu, try other import database method, the expdp and impdp Data Pumps can also be used to export and import databases, and the Data Pump differs from the traditional export and import as follows:

1. EXP and IMP are client-segment tool programs, and EXPDP and IMPDP are server-side tool programs;

2. EXP and IMP are less efficient. EXPDP and IMPDP are more efficient;

3. Powerful Data Pump functions in parallel, filtering, conversion, compression, encryption, and interaction;

4. Data Pump versions earlier than 9i are not supported. EXP/IMP is applicable in a short term;

5. The same exp/imp data pump export includes four modes: export table, export scheme, export tablespace, and export database.

With the theoretical support, we will start the actual practice below.

Expdp export Oracle Database

1. Creating Directory under sqlplus allows us to flexibly read and write files in Oracle databases, greatly improving the ease of use and scalability of Oracle.

Command: createdirectory oracleDB as 'd: \ oracledb ';

2. Grant read and write permissions to specific users

Command: Grantread, write on directory oracleDB to radpcs;

3. Execute the expdp Export command in the dos window

Command: expdp radpcs/ictradpcs @ rdpcs directory = oracleDB dumpfile = 20150226.dmp logfile = 20150226. logFULL = y;

After the export is completed, the following describes how to import data to the Oracle database using impdp.

Import impdp to Oracle Database

1. log on to the Oracle database at the sysdba level

Command: -- sqlplus/nolog

-- Conn system/system @ radpcs as sysdba

2. Create a data table space

Command:

-- Create a data table space

Create tablespace RADPCS_DATA

LOGGING

DATAFILE 'd: \ OracleDB \ radpcs_data.dbf' SIZE 200 M REUSE AUTOEXTEND

On next 10 m maxsize 16383 M EXTENT MANAGEMENT LOCAL UNIFORM

SIZE 1024 K;

-- Create index tablespace

Create tablespace RADPCS_INDX

LOGGING

DATAFILE 'd: \ OracleDB \ radpcs_indx.dbf' SIZE 200 M REUSE AUTOEXTEND

On next 10 m maxsize 16383 M EXTENT MANAGEMENT LOCAL UNIFORM

SIZE 1024 K;

This step is critical. The created tablespace must have the same name and number as the original Oracle tablespace. Otherwise, the import will fail. If you do not know the name and number of the original tablespace, create a temporary tablespace for import. During the import process, follow the error message, for example, "RADPCS1_DATA tablespace does not exist.

3. Create a user and authorize the user

-- Create user radpcs identified by ictradpcsdefault tablespace quota unlimited on radpcs_dataquota unlimited on radpcs_indx; -- authorize grant privileges to radpcs; grant authenticateduser to radpcs; grant connect to radpcs; grant ctxapp to radpcs; grant dba to radpcs; grant delete_catalog_role to radpcs; grant ejbclient to radpcs; grant role to radpcs; grant exp_full_database to radpcs; grant role to radpcs; grant hs_admin_role to radpcs; grant imp_full_database to radpcs; grant privileges to radpcs; grant javaidpriv to radpcs; grant policyspriv to radpcs; grant javauserpriv to radpcs; grant java_admin to radpcs; grant java_deploy to radpcs; grant privileges to radpcs; grant oem_monitor to radpcs; grant olap_dba to radpcs; grant recovery_catalog_owner to radpcs; grant resource to radpcs; grant select_catalog_role to radpcs; grant xdbadmin to radpcs; -- Grant/Revoke system privileges grant administer database trigger to radpcs; grant alter any cluster to radpcs; grant alter any dimension to radpcs; grant alter any index to radpcs; grant alter any indextype to radpcs; grant alter any library to radpcs; grant alter any outline to radpcs; grant alter any procedure to radpcs; grant alter any role to radpcs; grant alter any sequence to radpcs; grant alter any snapshot to radpcs; grant alter any table to radpcs; grant alter any trigger to radpcs; grant alter any type to radpcs; grant alter database to radpcs; grant alter profile to radpcs; grant alter resource cost to radpcs; grant alter rollback segment to radpcs; grant alter session to radpcs; grant alter system to radpcs; grant alter tablespace to radpcs; grant alter user to radpcs; grant analyze any to radpcs; grant audit system to radpcs; grant backup any table to radpcs; grant become user to radpcs; grant comment any table to radpcs; grant create any cluster to radpcs; grant create any context to radpcs; grant create any dimension to radpcs; grant create any directory to radpcs; grant create any index to radpcs; grant create any indextype to radpcs; grant create any library to radpcs; grant create any operator to radpcs; grant create any outline to radpcs; grant create any procedure to radpcs; grant create any sequence to radpcs; grant create any snapshot to radpcs; grant create any synonym to radpcs; grant create any table to radpcs; grant create any trigger to radpcs; grant create any type to radpcs; grant create any view to radpcs; grant create cluster to radpcs; grant create database link to radpcs; grant create dimension to radpcs; grant create indextype to radpcs; grant create library to radpcs; grant create operator to radpcs; grant create procedure to radpcs; grant create profile to radpcs; grant create public database link to radpcs; grant create public synonym to radpcs; grant create role to radpcs; grant create rollback segment to radpcs; grant create sequence to radpcs; grant create session to radpcs; grant create snapshot to radpcs; grant create synonym to radpcs; grant create table to radpcs; grant create tablespace to radpcs; grant create trigger to radpcs; grant create type to radpcs; grant create user to radpcs; grant create view to radpcs; grant debug any procedure to radpcs; grant debug connect session to radpcs; grant delete any table to radpcs; grant drop any cluster to radpcs; grant drop any context to radpcs; grant drop any dimension to radpcs; grant drop any directory to radpcs; grant drop any index to radpcs; grant drop any indextype to radpcs; grant drop any library to radpcs; grant drop any operator to radpcs; grant drop any outline to radpcs; grant drop any procedure to radpcs; grant drop any role to radpcs; grant drop any sequence to radpcs; grant drop any snapshot to radpcs; grant drop any synonym to radpcs; grant drop any table to radpcs; grant drop any trigger to radpcs; grant drop any type to radpcs; grant drop any view to radpcs; grant drop profile to radpcs; grant drop public database link to radpcs; grant drop public synonym to radpcs; grant drop rollback segment to radpcs; grant drop tablespace to radpcs; grant drop user to radpcs; grant execute any indextype to radpcs; grant execute any library to radpcs; grant execute any operator to radpcs; grant execute any procedure to radpcs; grant execute any type to radpcs; grant flashback any table to radpcs; grant force any transaction to radpcs; grant force transaction to radpcs; grant global query rewrite to radpcs; grant any object privilege to radpcs; grant any role to radpcs; grant insert any table to radpcs; grant lock any table to radpcs; grant manage tablespace to radpcs; grant on commit refresh to radpcs; grant query rewrite to radpcs; grant restricted session to radpcs; grant resumable to radpcs; grant select any sequence to radpcs; grant select any table to radpcs; grant under any type to radpcs; grant under any view to radpcs; grant unlimited tablespace to radpcs; grant update any table to radpcs; grant select on dba_free_space to radpcs; grant select on dba_data_files to radpcs;

4. create Directory under sqlplus

Command: createdirectory oracleDB as 'd: \ oracledb ';

5. Grant the read/write permission to a specific user

Command: Grantread, write on directory oracleDB to radpcs;

6. Execute the impdp import command in the dos window.

Command: impdp radpcs/ictradpcs @ rdpcs directory = oracleDB dumpfile = 20150226.dmp logfile = 20150226.log;

After a long wait, the dos window prompts that the export is complete. If an error occurs during the import process, an error message is displayed. (if the data is complete, some errors can be ignored ).

Reference: http://blog.csdn.net/indexman/article/details/7849401