Brief Introduction to oracle imp and exp commands

Use exp/imp to migrate data, especially between different versions and between different systems,
Of course, we recommend that you use expdp/impdp to import and export data. The following is a brief introduction to the basic operations of exp and imp.

1. Export and Import of the entire database

A. Before exporting and importing data, check the character set of the database and set the environment variable NLS_LANG.
Export NLS_LANG = AMERICAN_AMERICA.UTF8 (this is my parameter, depending on the actual situation)

# Querying oracle character set parameters
Select * from v $ nls_parameters
Select * from v $ nls_parameters where parameter = 'nls _ CHARACTERSET ';
Select value from nls_database_parameters t where t. PARAMETER = 'nls _ NCHAR_CHARACTERSET'
B. Execute the Export command. Users with certain permissions are required. Otherwise, only the objects owned by this user will be exported.

Exp system/root file =/backup/full. dmp log =/backup/full. log full = y

C Import command

Imp system/root file =/backup/full. dmp log =/backup/full2.log full = y ignore = y

2. User-based data export and import
Export NLS_LANG = AMERICAN_AMERICA.UTF8

A. Run the Export command. You can specify the owner or not, but cannot specify the full parameter.

Exp scott/root file =/backup/scott. dmp log =/backup/scott. log or
Exp system/root file =/backup/scott. dmp log =/backup/scott. log owner = scott

B. If there are no users or tablespaces, we need to create the specified tablespace and users first.

Create tablespace "hsj"
Logging
DATAFILE '/u01/oradata/orcl/heshengjun. ora' SIZE 500 M REUSE EXTENT
Management local segment space management auto;

Create user

Create user hsj identified by root
Default tablespace hsj
Temporary tablespace temp;

C. Grant scott permissions. The specific user depends on the user.

Grant unlimited tablespace to "SCOTT ";
Grant create database link to "SCOTT ";
Grant create any table to "SCOTT ";
Grant create any trigger to "SCOTT ";
Grant create any view to "SCOTT ";
Grant create database link to "SCOTT ";
Grant create procedure to "SCOTT ";
Grant create table to "SCOTT ";
Grant create tablespace to "SCOTT ";
Grant create trigger to "SCOTT ";
Grant drop any table to "SCOTT ";
Grant insert any table to "SCOTT ";
Grant update any table to "SCOTT ";
Grant connect to "SCOTT ";
Grant resource to "SCOTT ";
GRANT IMP_FULL_DATABASE to "SCOTT"
Grant dba to "SCOTT ";

D. Run the import command.

Imp scott/root file =/backup/scott. dmp log =/backup/scott2.log full = y

Or
 
Imp scott/root file =/backup/scott. dmp log =/backup/scott2.log fromuser = scott touser = scott

3. Export and Import based on table objects
Export NLS_LANG = AMERICAN_AMERICA.UTF8
A. Export the table objects demp and emp.
# Escape special characters in linux
Exp scott/root file =/backup/table. dmp log =/backup/table. log tables = \ (dept, emp \);
B. Import table objects demp and emp.
Imp scott/root file =/backup/table. dmp log =/backup/table2.log full = y
Or
Imp scott/root file =/backup/table. dmp log =/backup/table2.log tables = \ (dept, emp \);

Summary: other parameters, such as cache buffer and compress, are not configured. If you are interested, refer to the documentation for configuration.
Read the parameter file in expdp and impdp.

 

Supplement: For database migration, you must set the same character set. Or compatible character sets