Cancel data export and import

Cancel data export and import

I. Export mode (three modes) and Command Format

1. Full database mode

Exp username/password @ network service name full = y file = path \ file name. dmp log = path \ file name. log

2. User Mode (this mode is generally used)

Exp username/password @ network service name owners = (user 1, user 2, user 3 ,...) File = path \ file name. dmp log = path \ file name. log

3. Table mode

Exp username/password @ network service name tables = (table name 1, table name 2, Table Name 3 ,...) File = path \ file name. dmp log = path \ file name. log

4. In addition, you can export only a tablespace.

Exp username/password @ network service name tablespaces = (tablespace 1, tablespace 2, tablespace 3 ,...) File = path \ file name. dmp log = path \ file name. log

Ii. ImportMode(Three modes)And Command Format

1. Full database mode

Imp username/password @ network service name full = y file = path \ file name. dmp log = path \ ds110.log

2. User Mode (this mode is generally used)

Imp username/password @ network service name file = path \ file name. dmp fromuser = exported username touser = imported username 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 \ file name. dmp fromuser = exported user name touser = imported User Name log = path \ ds110.log

4. In addition, you can only import a tablespace.

Imp username/password @ network service name tablespaces = (tablespace 1, tablespace 2, tablespace 3 ,...) File = path \ file name. dmp fromuser = exported user name touser = imported User Name log = path \ ds110.log

Iii. Import Command Parameters

USERID username/password

Whether FULL Database Import (Y or N); default value: N

BUFFER data BUFFER size

User List exported by FROMUSER, that is, the user from which the import file is exported

List of users imported by TOUSER, that is, to which user

FILE Import FILE, which is a dmp FILE

SHOW only lists the file content (Y or N). The default value is N.

TABLES List of table names to be imported

Whether to IGNORE the error during the import process. The default value is N.

RECORDLENGTH record length. The default value is operating system-dependent.

GRANTS import permission (Y or N). The default value is Y.

Whether the INCTYPE import type is incremental (Y or N). The default value is N.

INDEXES: imports an index (Y or N). The default value is Y.

During the import process, whether to import a row of data. The default value for submitting a row (Y or N) is N, that is, after each table is imported, submit

ROWS imports row data (Y or N). The default value is Y.

PARFILE parameter file name

LOG File output by LOG

Whether DESTROY overwrites the data files (Y or N) in the tablespace. The default value is N.

INDEXFILE writes the table or index information to the specified file.

CHARSET: Character Set of the exported file. The default value is NLS_LANG.

ANALYZE executes the ANALYZE Statement (Y or N) during import. The default value is Y.

FEEDBACK displays the progress of each row during import. The default value is 0.

Iv. Description of exported Command Parameters

USERID username/password

Whether FULL Database Import (Y or N); default value: N

BUFFER data BUFFER size

List of users exported by the OWNER

FILE: the exported FILE is a dmp FILE.

TABLES List of table names to be exported

Whether COMPRESS is compressed (Y or N). The default value is Y.

The length of the RECORDLENGTH input/output record. The default value is operating system-dependent.

GRANTS export permission (Y or N). The default value is Y.

Whether the exported INCTYPE type is incremental (Y or N). The default value is N.

INDEXES exports the index (Y or N). The default value is Y.

Whether the RECORD records an incremental or accumulative Export (Y or N) in system sys. INCEXP, SYS. INCFIL. The default value is Y.

ROWS exports row data (Y or N). The default value is Y.

PARFILE parameter file name

Whether CONSTRAINTS exports CONSTRAINTS (Y or N). The default value is Y.

Whether to add a transaction (Y or N) to the CONSISTENT during the export operation. The default value is N.

LOG File output by LOG

STATISTICS executes the ANALYZE Statement (Y or N) during export. The default value is Y.

DIRECT direct path (N)

The progress of each row is displayed when FEEDBACK is exported. The default value is 0.

5. exported objects

1. Full database module

You can export all database objects owned by the sys user, including: table space definition, configuration file, user definition, role, system permission authorization, Role authorization, default role, resource costs, rollback segment definition, database link, virtual column, all directory aliases, all external function libraries, all object types, and all cluster definitions; for each table, you can also export the object type definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorization, analysis tables, column and Table Comments, audit information, all integrity constraints referenced, all synonyms, all views, all stored procedures, packages, functions, all triggers, analysis clusters, snapshots, jobs, all refresh groups and children

2. User Module

Object Type, database link, sequence, and cluster definition. For each table, you can also export the object type definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorization, analysis tables, column and Table Comments, audit information, and integrity constraints, synonyms, views, stored procedures, packages, functions, triggers, analysis clusters, snapshots, jobs, refresh groups referenced by the current user's table

3. Table mode

You can export the object type definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorization, analysis tables, column and Table Comments, audit information, and table references used by a table. integrity constraints, triggers, you can also export triggers owned by other users and indexes owned by other users.

Vi. Import Order

1. Character Set

2. Table Definition

3. Table Data

4. Table Index

5. integrity constraints, triggers, and bitmap indexes

6. Views, functions, and processes

7. Package

VII. Advanced Applications

1. When loading data to an existing table, you must use the IGNORE = Y parameter.

2. How to add conditions when exporting tables

Exp username/password @ network service name file = path \ file name. dmp tables = (Table Name) query = \ "condition \"

3,

8. System Objects contained during full-Database Import

Configuration File profiles

Public Database Link

Synonym

Role

Rollback segment Definition

System audit options

System Permissions

Table space Definition

Tablespace quotas

User-Defined

Directory alias

IX. Problems with import tool imp

1. The database object already exists.

Generally, tables, sequences, functions/processes, and triggers under the target data should be completely deleted before data is imported; database objects already exist, and the import will fail Based on the default imp parameter; if the ignore = y parameter is used, the data content in the exp file will be imported. If the table has a unique keyword constraint, the data 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.

2. database objects are subject to primary and foreign key constraints

This occurs when several DMP files containing tables are imported into the database separately. If the data does not comply with the primary and foreign key constraints, the Import fails. Solution: import the data to the master table first and then import the dependency table; the primary and foreign key constraints of the disable object to be imported. After the data is imported, enable them

3. Insufficient Permissions

If you want to import user A's data to user B, you must have the imp_full_database permission for the import operation.

4. failed to allocate storage when importing large tables (greater than 80 M)

Compress = Y for the default EXP, that is, compress all data into one data block. If there is no continuous big data block during import, the import will fail, when exporting a large table larger than 80 Mb, remember to compress = N, and this 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 the information related to NLS_LANG in the NT Registry. After the import is complete, change it back.

6. The imp and exp versions are not compatible.

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

 

10. Export from one account and import from another account

Example of table export mode:

1. Export the PRNBSN_CONTENTTEMPLATE and PRNBSN_RELINFOCONTENT tables from the dbo_pwcdb account without data

ExpDbo_pwcdb/dbo_pwcdb@ BSY2-NQQX-IP50 file = d: \ daochu_noData.dmp tables =

(PRNBSN_CONTENTTEMPLATE, PRNBSN_RELINFOCONTENT) rows = n;

2. Import the dbo_smydb account

Imp dbo_smydb/dbo_smydb @ BSY2-SMY-IP11 full = y file = d: \ daochu_noData.dmpTouser = dbo_smydbIgnore = y;

 

11. Use the PL/SQL Developer tool to Export Import triggers, stored procedures, sequences, indexes, and other objects.

1. log on to PL/SQL Developer under account A, and select Tools> Export user objects from the menu.

2. Take the export stored procedure as an example and select:

 

In the procedure object, select the Stored procedure object to be exported, set the name of the output file, and click Export.

3. If you have an account with the same name in the oracle database to be imported, you can directly create a command window, copy the exported fun_proc. SQL content, and paste it into the command window, it will be automatically executed; equivalent to importing through SQL scripts;

If the oracle database to be imported contains an account with another name, such as account B. Open the fun_proc. SQL file,Find all accounts A and replace all accounts BAnd then use the B account to log on to the PL/SQL Developer tool, create a new command window, paste the file content, and execute.

Note: The generated stored procedure name will be in the following format: Create procedure account A. the stored procedure name must be replaced with Create procedure Account B. The stored procedure name will be displayed.

For the import of other objects, refer to the object import method of stored procedures.