Oracle data export/import (exp/IMP) Summary

1 exp/imp

Used inDatabaseTo transfer data. Exp exports data from the database to the dump file. IMP imports data from the dump file to the database.BackupAnd restore.

Exp exports files in binary format and cannot be edited manually. Otherwise, data is damaged. This file is common on any platform supported by Oracle. Dump files are compatible with up to IMP.

2. How to run

The general exp/IMP command is in the $ ORACLE_HOME/bin directory.

We can execute SQL plus or command line commands, interactive and non-interactive.

Note: You must have sufficient permissions to ensure that the database can be connected (you can test with tnspng)

3. Exp

Export tool. Data backup in the database is compressed into a binary system file, which can be migrated between different operating systems.

Three modes:

A: User Mode: export data of all user objects and objects.

B: Table mode: export all user tables or specified tables

C: entire database: export all data objects in the database

Exp add to the exported query condition query = You can select a subset of the exported table

Parameters:
Keyword description (default)
----------------------------------------------
Userid username/password
Full export the entire file (N)
Buffer data buffer size
Owner owner username list
File output file (expdat. dmp)
Tables Table Name List
Compress imports a range (y)
Recordlength Io record length
Grants export permission (y)
Inctype incremental export type
Indexes export index (y)
Incremental export of record tracking (y)
Rows export data rows (y)
Parfile parameter file name
Constraints export limit (y)
Consistent cross tabulation consistency
Log File output by log Screen
Statistics Analysis object (estimate)
Direct direct path (N)
Triggers export trigger (y)
Feedback displays the progress of each X row (0)
Filesize maximum size of each dump file
Query the clause of the selected export table subset

4. IMP

Import tool. Import the binary file (dump file) generated by exp into the database.

Like exp, there are three modes.

Only users with imp_full_database and DBA permissions can import the entire database.

When importing data, Oracle has a specific sequence, which may vary with the database version. Generally

1. tablespaces

2. Profiles

3. Users

4. Roles

5. system privilege grants

6. Role grants indexes, constraints, Audi ting)

7. Default roles

8. tablespace Quotas

9. resource costs

10. rollback segments

11. database links

12. Sequences

13. Snapshots

14. snapshot logs

15. Job Queues

16. Refresh groups

17. Cluster Definitions

18. Tables (also grants, comments,

19. referential integrity

20. posttables actions

21. Synonyms

22. Views

23. Stored Procedures

24. triggers, defaults and auditing

parameter:
keyword description (default)
--------------------------------------------
userid username/password
full import of the entire file (N)
buffer data buffer size
fromuser User Name List
file input file (expdat. DMP)
touser username list
show only lists file content (n)
tables Table Name List
ignore creation error (N)
length of recordlength Io record
grants import permission (y)
inctype incremental Import Type
indexes import index (y)
commit submits array insertion (n)
rows imports data rows (y)
parfile parameter file name
log file output on the log screen
constraints import restrictions (y)
destroy overwrites the tablespace data file (N)
indexfile writes table/index information to the specified file
skip_unusable_indexes skips maintenance of unavailable indexes (n)
analyze executes the analyze Statement (y) in the dump file)
feedback shows every X rows (0) progress
toid_novalidate skips verification of the specified type id
filesize maximum size of each dump file
recalculate_statistics recalculates the statistical value (n)

5. Possible Problems

A: IMP and exp versions

Exp ora-00942 error: this is because the mounted table or view does not exist, mostly catexp. the SQL statement has not been run, so the export view cannot be executed. For example, catexp. if the SQL statement has been run, it may be a version error.

Exp -- 00003 error: No storage definition found for segment: usually run $ ORACLE_HOME/rdbms/admin/catexp. SQL can solve the problem. this error also occurs when a database of a later version of exp is exported. (Adding compress = y also seems to solve)

IMP can successfully import files generated by earlier exp versions. Files generated by later exp versions cannot be imported.

B: IMP and exp use different character sets.
If the character set is different, the import will fail. You can change the Unix environment variable or the information related to nls_lang in the NT Registry. After the import is complete, change it back.

C: The database objects are subject to the primary and foreign key constraints. If the database objects do not comply with the primary and foreign key constraints, data import will fail. you can import the dependency table to the master table first, and enable the disable object after importing data.

D: The database object already exists.
If the ignore = y parameter is used, the data content in the exp file will be imported.
If the table has a constraint that contains a unique keyword, the table will not be imported if the condition is not met.
If the table does not have a constraint for a unique keyword, record duplication occurs.

E: When importing data to a specified user or table space, determine whether the target user or table space already exists and have enough space.

F: Some user-defined types may fail to be imported.

G: Import trigger and sequence may also encounter errors.