Oracle export data scripts export. Par and import. Par

The content of the example export9.par is as follows:

USERID = CTGPC/xuyunsheng @ ctgpcweb
OWNER = CTGPC
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
CONSISTENT = Y (Note: CONSISTENT indicates consistency. CONSISTENT = Y indicates that when multiple tables of the database are exported, the image time points of these tables are the same. The exp command first puts all the tables involved in it into the rollback segment, and then exports it. If the table is large, it will occupy many rollback segments. It is not recommended. If you want to use it, it is best to operate only small tables)
Compress = Y (Note: Do not use compress = y if there are already deleted rows, which is not commonly used)
Direct = y
Buffer= 20000
File =/backup/ctgpc_20030623.dmp
Log =/backup/ctgpc_20030623.log

Command: EXP parfile =/filepath/export9.par

Note: To export data from all databases, replace the first and second rows with full = y.

The content of import9.par is as follows:

Fromuser = tgpms (Oracle cannot directly change the table owner. Export/Import can be used for this purpose .)
TOUSER = TGPMS2 (Note: You can change the table owner from FROMUSER to TOUSER. Users of FROMUSER and TOUSER can be different)
ROWS = Y
INDEXES = Y
GRANTS = Y
CONSTRAINTS = Y
Buffer= 409600
File =/backup/ctgpc_20030623.dmp
Log =/backup/import_20030623.log

Command: imp parfile =/filepath/import9.par

---------------------------------------------------------------------------------

The following describes how to use export/import.

-Move database objects from one user to another

Oracle cannot directly change the table owner. Export/Import can be used to achieve this purpose.

Assume that you want to change the table t owner user1 to user2. The specific steps are as follows:

-Exp system/manager tables = user1.t

-IMP system/manager fromuser = user1 touser = user2 tables = T

-Drop table user1.t

-Move database objects from one tablespace to another

When creating a table, you can specify the tablespace. Once the tablespace is determined, the portion can be changed at will. To move table t from tablespace tbs1 to tablespace tbs2, use the following method:

-Exp <user/passwd> tables = T

-Imp <user/passwd> tables = T indexfile = temp. SQL

-Drop table T

-Edit temp. SQL to retain only the required table creation commands and specify the tablespace as tbs2.

-Run temp. SQL as the table owner.

-Imp <user/passwd> tables = T ignore = Y

Bytes ------------------------------------------------------------------------------------

The following describes some common problems and solutions for using Export/Import.

-Export/Import use different character sets

The Export file contains character information. if both input and output use the byte character set, such as EBCDIC or US7ASCII, the character set is automatically converted during input. during conversion, if the characters in the target character set in the output file do not match, they are automatically set to the default characters.

For multi-byte character sets, such as ZHS16CGB231280, automatic conversion is usually not allowed. It can be automatically converted only when the string length remains unchanged.

-Insufficient space-fragmentation issues

Sometimes, even if the database still has enough space, there is an error that the IMPORT space is not enough. this is usually caused by fragments in the database, which means there are many small non-consecutive free spaces. the solution is to first restore the full database export (FULL = Y), SHUTDOWN the DATABASE, re-create database, and use import full = Y to restore data.

-Insufficient ROLLBACK segments

During the use of Export/Import, if the data volume is large, the error 'rollback segment is insufficient. create a ROLLBACK segment that is large enough to make it ONLINE and other ROLLBACK segments OFFLINE. in this way, Export/Import uses this large ROLLBACK segment to avoid the above phenomenon.