Oracle8i/9i exp/IMP experience

　 I. Common 8i exp options
1. Full. This is used to export the entire database. When rows = N is used together, the structure of the entire database can be exported. For example:

Exp sys file =./db_str.dmp log =./db_str.log full = y rows = n compress = y direct = y

2. Buffer and feedback. When exporting a large amount of data, I will consider setting these two parameters. For example:

Exp new file = yw97_2003.dmp log = yw97_2003_3.log feedback = 10000 buffer = 100000000 tables = wo4, OK _yt

3. Fill and log. The two parameters respectively specify the DMP name and log name for the backup, including the file name and directory. For example, see the preceding figure.

It should be noted that exp can be directly backed up to the tape, that is, file =/dev/rmt0 (the name of the tape device) is used, but we generally do not do this for two reasons: 1. This operation will be much slower. 2. Currently, tape libraries are generally used. You are not advised to operate the tapes directly. If you do not use a tape library, you can use it with the tar of UNIX.

If you really want to use exp directly to tape, you can refer to the Metalink article "exporting to tape on UNIX systems" (document No.: 30428.1). This article provides a detailed explanation.

4. The compress parameter will merge chunks while exporting, and try to compress the data to the initial extent. The default value is N. It is generally recommended to use. The direct parameter tells exp to directly read the data, instead of using select to read the data in the table as the traditional exp, thus reducing the SQL statement processing process. It is also recommended. However, in some cases, direct parameters cannot be used.

5. How can I use sysdba to execute exp/imp?

This is a very practical problem. Sometimes we need to use sysdba to execute exp/IMP, for example, to transmit the exp/imp of the tablespace, sysdba is required for executing exp/imp with sys users in 9i. We can use the following method to connect to exp/IMP:

Exp "'sys/sys as sysdb'" file = 1.dmp tables = gototop. t rows = N

6. the query parameter is followed by the where condition. It is worth noting that the entire WHERE clause must be enclosed by "". The write method of the where clause is the same as that in the select clause, for All "and 'on UNIX platforms, use/u000069 to block their special meanings:

Exp gototop/gototop file = 1.dmp log = 1.log tables = cyx. t query = "where c1 = 20 and C2 = gototop"

For Windows, the following format is used:

Exp C/C @ NCN file = C. dmp log = C. Log tables = T query = "where id = 1 and name = 'gototop '"""

Ii. Common 8i imp options
1. Use fromuser and touser to import data from one schema to another.

2. Ignore, grants, and indexes. The ignore parameter ignores the existence of the table and continues the import. This is useful when you need to adjust the storage parameters of the table, we can create a table with reasonable storage parameters according to the actual situation, and then import the data directly. Grants and indexes indicate whether to import authorization and indexes. If you want to use the new storage parameter to re-create the index, or to speed up the import, you can consider setting indexes to n, grants is generally y.

Another exp/IMP parameter is parfile, which is a parameter file used to define exp/imp. That is to say, the above parameters can be written in a parameter file, but we rarely use it.

Iii. Oracle9i exp Function Description
Oracle9i exp adds some new parameters on the basis of the original, which are mainly divided into the following parts by function:

1. object_consistent-used to set the exp object to read-only to maintain object consistency. The default value is N.

2. flashback_scn and flashback_time-are added to support the flashback function.

3. resumable, resumable_name, and resumable_timeout-are added to support resumable space allocation.

4. tts_full_check-used to use dependency check when transferring tablespaces.

5. template-is used to support IAS.

6. tablespaces-set the tablespace export mode. I personally think that for general users, this is the most practical new parameter. It allows users to have another option based on the original full, owner, and tables, making exp more flexible.

4. What are the exp/IMP problems of different versions?
Generally, it is not a problem to import data from a lower version to a later version. The trouble is to import data from a later version to a lower version. Before Oracle9i, EXP/IMP between Oracle versions can be solved through the following methods:

1. Run catexp. SQL of the base version on the High Version database;

2. Use exp of a lower version to export data of a later version;

3. Use the lower-version IMP to import the database to the database of the actual version;

4. Run the catexp. SQL script in the later version of the database.

But in 9i, the above method cannot solve the problem. If you use the base version exp/IMP directly, the following error occurs:

EXP-00008: Oracle error % lu encountered

ORA-00904: Invalid column name

This is a published bug that can be solved only after oracle10.0. The bug number is 2261722. You can go to Metalink to view details about this bug.

Bug is a bug. We still need to do our work. We should solve the bug by ourselves before we have Oracle Support. Execute the following SQL statement in Oracle9i to recreate the exu81rls view.

Create or replace view exu81rls

(Objown, objnam, policy, polown, polsch, polfun, mongots, chkopt, enabled, spolicy)

As select U. Name, O. Name, R. pname, R. pfschma, R. ppname, R. pfname,

Decode (bitand (R. stmt_type, 1), 0, '', 'select ,')

| Decode (bitand (R. stmt_type, 2), 0, '', 'insert ,')

| Decode (bitand (R. stmt_type, 4), 0, '', 'Update ,')

| Decode (bitand (R. stmt_type, 8), 0, '', 'delete ,'),

R. check_opt, R. enable_flag,

Decode (bitand (R. stmt_type, 16), 0, 0, 1)

From user $ U, OBJ $ o, RLS $ R

Where U. User # = O. Owner #

And R. OBJ # = O. OBJ #

And (uid = 0 or

Uid = O. Owner # Or

Exists (select * From session_roles where role = 'select _ catalog_role ')

)

/

Grant select on SYS. exu81rls to public;

/

V. Other problems
This article only discusses some situations of exp/IMP in Oracle8i and 9i. For earlier versions, except query parameters cannot be used in 8.0.x, there is little difference. In the absence of query, We can first create a temporary intermediate table using the query conditions in the database, and then export the intermediate table using exp. As for oracle7, because there are few users currently, gototop does not intend to explain it in detail here. If you have any requirements, you can refer to the Metalink Document: "Overview of export and import in oracle7" (document No.: 61949.1 ). You can obtain detailed parameters of exp/IMP through exp/IMP help = y.

For more information about the tablespace to be transmitted, refer to the metelink document below.

[Note: 77523.1] transportable tablespaces -- An Example to setup and use.

[Note: 100698.1] perform tablespace point-in-time recovery using transportable tablespace.

During parallel exp/IMP operations, if the IMP process creates an index, we do not recommend that you run more than five imp operations at the same time. If you want to speed up, You can do not create an index at Imp, in this way, you can run a few more times as long as the memory permits, and then create the required index in the SQL script.