Oracle Export and Import

Oracle Export and Import

1. Use of export/import
The Oracle export/import tool is used to transmit data between databases.
The export exports data from the database to the dump file.
Import from the dump file to the data import database
Which of the following is the general use of them?
(1) transfer data between two databases
Between Oracle servers of the same version
Between Oracle servers of different versions
Between the same OS
Between different OS types
(2) used for database backup and recovery
(3) transfer from one schema to another Schema
(4) transfer from one tablespace to another tablespace

2. Dump File
The export is a binary file and cannot be edited manually. Otherwise, data is damaged.
This file is in the same format on any platform supported by Oracle and can be used on all platforms.

The dump file adopts the up-compatible mode during import, that is, the dump file of oralce7 can be imported.
To oracle8, but there may be problems between versions with major differences.

3. Export/Import Process
The dump file exported by export contains two basic types of data.
-DDL
-Data
The dump file contains all the DDL statements that re-create the data dictionary, which are basically readable.
.
However, it should be noted that you should never use a text editor to edit it. Oracle does not support this.

The following lists the Oracle Objects included in the dump file, which are divided into the table/user/full mode.
Image
Only available in full mode (such as public synonyms, users, roles, and rollback segm)
ENTs, etc)

Table mode user mode full database mode
------------------------------------------------------------------
---
Table definitions table Definitions
Table data table data
Owner's table grants owner's grants
Owner's table indexes owner's indexes Indexes
Table constraints table Constraints
Table triggers table triggers all triggers
Clusters
Database links
Job Queues
Refresh groups refresh groups
Sequences Sequences
Snapshots snapshots
Snapshot logs snapshot logs
Stored Procedures stored procedures
Private synonyms all synonyms
Views views
Profiles
Replication catalog
Resource Cost
Roles
Rollback segments
System Audit options
System privileges
Tablespace Definitions

Tablespace Quotas
User Definitions

4. Import object import order
When importing data, Oracle has a specific sequence, which may vary with the database version,
However
This is the case now.

1. tablespaces 14. snapshot logs
2. Profiles 15. Job Queues
3. Users 16. Refresh groups
4. Roles 17. Cluster Definitions
5. system privilege grants 18. Tables (also grants, comments, indexes, constraints, auditing)
6. Role grants
7. Default roles 19. referential integrity
8. tablespace quotas 20. posttables actions
9. resource costs 21. Synonyms
10. rollback segments 22. Views
11. database links 23. Stored Procedures
12. Sequences 24. triggers, ults and auditing
13. Snapshots

In this order, the dependency between objects may be solved. Trigger last imported
In the insert
Trigger is not triggered when data is transferred to the database. After the import, there may be proc in the invalid status.
Edure, mainly
It will affect some database objects during import, but import does not re-compile procedure, resulting in
In this case,
You can recompile it to solve this problem.

5. compatibility issues
The import tool can process dump files exported from Versions earlier than export 5.1.22.
E7 Import
Processes dump files of oracle6, and so on. However, if the Oracle version is different
. Specific
For more information, see related documents, such as parameter settings (compatible parameter)

6. view required by export
The view required by export is created by catexp. SQL. These internal views are used to organize dump files in export.
Data format.
Most views are used to collect and create DDL statements. Others are mainly used by Oracle developers.

These views may vary with Oracle versions, and each version may have new features.
So in the new
There will be errors when running the old dump file in the version. You can generally execute catexp. SQL to solve these problems,
Backward compatibility
The general steps are as follows:

When the exported database version is earlier than the target database version:
-Execute the old catexp. SQL statement in the target database to be imported.
-Use the old export to export the dump file
-Use the old import to import data to the database
-Execute the new catexp. SQL statement in the database to restore the export view of this version.

When the exported database version is newer than the target database version:
-Execute the new catexp. SQL statement in the target database to be imported.
-Use the new export to export the dump file
-Import data to the database using the new import.
-Execute the old catexp. SQL statement in the database to restore the export view of this version.

7. Fragment
An important application of export/import is fragment. Because if the first impport,

It will re-create table and then import data, so the whole table is continuously stored. In addition, default

The dump file generated by the export is a "compress" table. However

Is misunderstood. In fact, compress changes the value of the storage parameter initial. For example:

Create Table... storage (initial 10 K next 10k ..)
Now the data has been extended to 100 extent. If compress = y is used to export data,
The generated statement is stored (initial 1000 K next 10 K)

We can see that the value of next is not changed, and initial is the sum of all extent. Therefore

In the following case, Table A has four M extent, execute Delete from a, and then use compress = y to export

When data is output, the create table statement will have m initial extent. Even if this is

No data !! This is because even if the dump file is small, it will generate a huge
Of
Table.

In addition, it may exceed the datafile size. For example, there are 4 50 m data files, where Table A has

15 10 m extent. If you use compress = y to export data, there will be initial = 150 m,

Therefore, a m extent cannot be allocated during re-import, because a single extent cannot span multiple files.
.

8. Transmit data between user and tablespace
In general, the export data should be restored to its original location. If Scott's table

Or user-based export data. If the Scott user does not exist during import, an error is returned!

The data exported in full mode carries the create user information, so the user is created to store the data.
.

Of course, you can use the fromuser and touser parameters during import to determine the user to be imported.
Certificate
Touser already exists.

9. Influence of export/import on squence
In either case, export/import will apply to sequence.
(1) if the user is retrieving the sequence value during the export operation, the sequence may be inconsistent.

(2) If sequence uses the cache, the values in the cache will be ignored during the export operation.
,
Only the current value export is obtained from the data dictionary.

If you use sequence to update a column of data in the table during full export/import,

In either case, the data before the update is exported.

If you use the regular path method, insert statements are used for each row of data. consistency check and insert T
Rigger
In direct mode, some constraints and triggers may not be triggered.
Sequence. nextval will affect sequence.