Oracel Data Export Import

First, export mode (three modes) and command format

1. Full-Library mode

EXP username/password @ NETWORK SERVICE name full=y file= path \ filename. dmp log= path \ filename. log

2, user mode (generally use this mode)

EXP username/password @ NETWORK SERVICE name owners= (user 1, User 2, User 3,...) file= path \ filename. dmp log= path \ filename. log

3. Table mode

EXP username/password @ NETWORK SERVICE name tables= (table name 1, table name 2, table name 3, ...) ) file= path \ filename. dmp log= path \ file name. log

4. In addition, you can export only a table space

EXP username/password @ NETWORK SERVICE name tablespaces= (tablespace 1, table Space 2, tablespace 3,...) file= path \ filename. dmp log= path \ filename. log

Second, import Mode (three modes) and command format

1. Full-Library mode

IMP username/password @ NETWORK SERVICE name full=y file= path \ filename. dmp log= Path \ds110.log

2, user mode (generally use this mode)

IMP username/password @ NETWORK SERVICE name file= path \ file name. DMP fromuser= Exported user name touser= imported user name log= path \ds110.log

3. Table mode

IMP username/password @ NETWORK SERVICE name tables= (table name 1, table name 2, table name 3, ...) ) file= path \ filename. dmp fromuser= Exported user name touser= imported user name log= path \ds110.log

4. In addition, you can import only one table space

IMP username/password @ NETWORK SERVICE name tablespaces= (tablespace 1, tablespace 2, tablespace 3,...) file= path \ filename. dmp fromuser= Exported user name touser= imported user name log= path \ds110.log

III. instructions for importing command parameters

USERID username/password

Full library import (Y or n), default = N

Size of buffer data buffers

Fromuser the list of exported users, that is, from which user the import file was exported

Touser the list of imported users, which user to go to

File import files, which are DMP files

SHOW lists only the contents of the file (Y or N), the default is n

TABLES List of table names to import

IGNORE whether errors are ignored during the import process, and the default is N

RecordLength the length of the record, the default is operating system-dependent

GRANTS Import permissions (Y or N), default to Y

Inctype whether the imported type is incremented (Y or N) and the default is n

INDEXES Import index (y or N), default = y

Commit in the import process, whether to import a row of data, commit a row (Y or N) default to N, that is, after each table import, commit

Rows import row data (Y or N), default to Y

Parfile parameter file name

Log file for Output

DESTROY whether the data file (Y or N) on the table space is overwritten by default n

Indexfile to write table or index information to the specified file

CHARSET the character set of the exported file, which defaults to Nls_lang

ANALYZE Execute ANALYZE statement at import (Y or N) defaults to Y

FEEDBACK Show progress per few lines at import, default to 0

Iv. description of exported command parameters

USERID username/password

Full library import (Y or n), default = N

Size of buffer data buffers

List of users exported by OWNER

File export files, which are DMP files

TABLES List of table names to export

COMPRESS whether to compress (Y or N), the default is Y

RecordLength the length of the input output record, the default is operating system-dependent

GRANTS Export Permissions (Y or N), default to Y

Inctype whether the exported type is incremented (Y or N) and the default is n

INDEXES Export Index (y or N), default to Y

Whether the RECORD is in System Sys.incexp, SYS. An increment or cumulative export (Y or N) is recorded in the Incfil, and the default is Y

Rows export row data (Y or N), default to Y

Parfile parameter file name

CONSTRAINTS whether the constraint is exported (Y or N), and the default is Y

Consistent whether to add transactions (Y or N) when the export operation is performed, by default n

Log file for Output

STATISTICS execute Analyze Statement on export (Y or N) default to Y

Direct direct Path (N)

FEEDBACK Show progress on each of several lines when exporting, default to 0

V. Exported objects

1. Full Library Module

You can export all database objects owned by the SYS user, including: definition of tablespace, configuration file, user definition, role, System permission authorization, role authorization, default role, resource costs, definition of roll segment, database link, virtual column, all directory aliases, all external function libraries, All object types, all cluster definitions, and for each table, you can also export the object type definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorizations, parse tables, column and table annotations, audit information, all referenced integrity constraints, all synonyms, all views, all stored procedures, packages, functions, and All triggers, analysis clusters, snapshots, job, all refresh groups and children

2. User Module

Object types, database links, sequences, cluster definitions, and for each table, you can also export the object type definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorizations, parse tables, column and table annotations, audit information, the integrity constraints referenced by the tables belonging to the current user, synonyms, views, stored procedures, Package, function, trigger, analysis cluster, snapshot, job, refresh groups

3. Table mode

You can export the object type definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorizations, parse tables, column and table annotations, audit information, table-referenced integrity constraints, triggers, and other user-owned triggers for the table, as well as export the indexes of other users ' users

Vi. Order of Import

1. Character Set

2. Table Definition

3. Table Data

4. Table Index

5. Integrity constraints, triggers, bitmap indexes

6. Views, functions, procedures

7. Package

Vii. Advanced Applications

1. When loading data into an existing table, the parameter ignore = Y must be used

2. How to add a condition when exporting a table

EXP username/password @ NETWORK SERVICE name file= path \ file name. DMP tables= (table name) query = \ "Condition \"

3.

Viii. system objects that are included in the full library import

Configuration file Profiles

Public database Links

Synonyms

Role

Definition of the roll segment

System Audit Options

System permissions

Table Space Definition

Tablespace quotas

User Defined

Directory aliases

IX. Import Tool imp may have problems

1. Database objects already exist

Generally, the data should be completely deleted before the target data table, sequence, function/process, trigger, etc.; the database object already exists, the import fails by the default imp parameter, if the parameter ignore=y, the data content inside the exp file will be imported; If the table has a unique keyword constraint , the non-conditional will not be imported, and if the table does not have a constraint on a unique keyword, it will cause record duplication

2. Database objects have primary foreign key constraints

This occurs when several DMP files containing the table are imported into the database, and the data fails to import if the primary foreign key constraint is not met, and the workaround is to import the primary table, import the dependent table, disable the primary foreign KEY constraint of the target Import object, import the data, and then enable them

3. Insufficient authority          

If you want to import a user's data into a B user, the user doing the import operation needs to have imp_full_database permission

4. Storage allocation failure when importing large tables (greater than 80M)

The default exp, compress = Y, that is, to compress all the data on a block of data, when imported, if there is no successive large data block, it will fail to import, export more than 80M large table, remember compress= N, will not cause this error.

5. 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 NT registration table Nls_lang related information, after the import is completed and then changed back.

6. IMP and EXP versions are not compatible

IMP can successfully import low version exp-generated files and cannot import high-version exp-generated files

X. Export from one account, another account import

Example of export table pattern:

1 export prnbsn_contenttemplate,prnbsn_relinfocontent Two tables without data from DBO_PWCDB account

Exp dbo_pwcdb/dbo_pwcdb@BSY2-nqqx-ip50 file=d:\daochu_nodata.dmp tables=

(prnbsn_contenttemplate,prnbsn_relinfocontent) Rows=n;

2 Import dbo_smydb Account

Imp dbo_smydb/[email protected] full=y file=d:\daochu_nodata.dmp touser=dbo_smydb ignore=y;

Xi. use the PL/SQL Developer tool to export import triggers, stored procedures, sequences, indexes, and other objects.

1 a account login PL/SQL Developer tool, select Tools from the menu---> Export user objects

2 to export the stored procedure as an example, select as:

In the procedure object, select the stored procedure object that you want to export, set the output file name, and click Export.

3 If you need to import the Oracle library, is the same name of the account, you can create a new command window, and then copy the contents of the exported fun_proc.sql, paste into the command window, automatically executed; equivalent to the import by SQL script;

If you need to import an Oracle library, it is another name for the account, such as the B account. Then you need to open the Fun_proc.sql file, Find all the A account name is replaced by the B account , and then the B account login PL/SQL Developer tool, create a new command window, paste the contents of the file, execute.

Note: The generated stored procedure name will look like the CREATE PROCEDURE account a. Stored procedure name so you need to replace the CREATE PROCEDURE account B. Stored procedure name. Open it and see it.

Imports of other objects refer to how stored procedure objects are imported.

Oracel Data Export Import