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 user name/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 the exp user name/password of a tablespace @ network service name tablespaces = (tablespace 1, tablespace 2, tablespace 3 ,...) File = path \ file name. dmp log = path \ file name. log 2. Import mode (three modes) and Command Format 1. full database mode imp user name/password @ network service name full = y file = path \ file name. dmp log = path \ ds110.log 2. User Mode (usually this mode) 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 username touser = imported username 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 3. Import command parameter description USERID user name/password FULL whether FULL Database Import (Y or N ), the default value is the user list exported by FROMUSER, which is the user list from which the imported FILE is exported, that is, the user list to which the FILE is imported, the dmp file SHOW only lists the file content (Y or N). The default value is n tables. Whether to IGNORE the error during the import process is IGNORE, the default value is the length of n recordlength records. The default value is operating system-dependent GRANTS import permission (Y or N). The default value is y inctype import type (Y or N ), the default value is n indexes. ), The default value is y commit. During the import process, whether to import a row of data, and submit a row (Y or N) is N by default, that is, after each table is imported, submit ROWS to import row data (Y or N). The default value is y parfile. The LOG file output by the file name LOG of y parfile overwrites the data file (Y or N) in the tablespace) the default value is n indexfile, which writes table or index information to the character set of the CHARSET export file in the specified file. The default value is NLS_LANG ANALYZE, which runs the ANALYZE Statement (Y or N) during import) the default value is y feedback. The progress is displayed for each line during the import. The default value is 0. The exported command parameters indicate whether the USERID username/password is FULL Database Import (Y or N ), the default value is the data BUFFER size of the n buffer. The user list exported by the OWNER is the FILE export FILE, which is the list of table names to be exported by the dmp file tables, and whether COMPRESS is compressed (Y Or N). The default value is the length of input and output records of y recordlength. The default value is operating system-dependent GRANTS export permission (Y or N ), the default value is y inctype. The default value is n indexes. The default value is Y or N. The default value is y record in system SYS. INCEXP, SYS. INCFIL records an incremental or accumulative Export (Y or N). The default value is y rows to export row data (Y or N). The default value is y parfile. The parameter file name CONSTRAINTS indicates whether to export CONSTRAINTS, (Y or N). The default value is y consistent. whether to add a transaction (Y or N) during the export operation ), by default, the LOG file STATISTICS Output by N logs executes the ANALYZE Statement (Y or N) during export. The default value is y direct direct path (N) FE. EDBACK shows the progress of each row during export. The default value is 0. Export object 1. The full database module 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 Types, database link, sequence, 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 current User Integrity constraints, synonyms, views, stored procedures, packages, functions, triggers, analysis clusters, snapshots, jobs, refresh groups 3. objects that can be used to export tables in Table mode type Definitions, table definitions, table data, nested table data, table indexes, table constraints, table authorization, analysis tables, column and Table Comments, audit information, integrity constraints of table references, triggers, you can also export triggers owned by other users, indexes owned by other users 6. Import sequence 1, Character Set 2, Table Definition 3, table data 4, table index 5, integrity constraints, trigger, bitmap index 6, view, function, process 7, package 7, advanced application 1. When you load data to an existing table, you must use the IGNORE = Y parameter. 2. How to add the conditional Exp username/password @ network service name file = path \ file name when exporting the table. dmp tables = (Table Name) query = \ "condition \" 3. 8. System Object configuration file profiles public database link synonym role included during full Database Import System audit options system permission tablespace definition tablespace quotas User-Defined directory alias 9. problems that may occur in the import tool imp 1. database objects already exist in general conditions, before importing data, you should completely delete the tables, sequences, functions/processes, and triggers under the target data. If the database object already exists, 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. The database object has a primary foreign key constraint. 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 data import will fail. Solution: import the data to the primary table first, and then import the dependent table. After the disable target imports the primary and foreign key constraints of the object, enable them. 3. Users with insufficient permissions to import user A's data to user B for Import The imp_full_database permission is required. 4. When importing a large table (larger than 80 Mb), compress = Y indicates that all data is compressed into one data block when the default EXP of storage allocation fails, if a large data block does not exist during the import, the import will fail. When exporting a large table larger than 80 Mb, remember to compress = N, which will not cause this error. 5. If the character sets used by imp AND exp are different, the import will fail. You can change the unix environment variables or the information about NLS_LANG in the NT Registry. After the import is complete, the data can be changed back. 6. imp and exp versions cannot be compatible with imp. You can successfully import files generated by earlier exp versions. You cannot import files generated by later exp versions. 10. Export from an account, example of another account's table Import and Export mode: 1 export the PRNBSN_CONTENTTEMPLATE and PRNBSN_RELINFOCONTENT tables from the dbo_pwcdb account, without the data exp db O_pwcdb/dbo_pwcdb @ BSY2-NQQX-IP50 file = d: \ tables = (tables, tables) rows = n; 2 Import dbo_smydb account imp dbo_smydb/dbo_smydb @ BSY2-SMY-IP11 full = y file = d: \ daochu_noData.dmp touser = dbo_smydb ignore = 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. Use the export stored procedure as an example. In the procedure object, select, select the Stored Procedure object to be exported, set the output file name, 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 is automatically executed. It is equivalent to importing data using an SQL script. If the oracle database to be imported contains another account, such as account B. Open the fun_proc. SQL file and replace all account A names with account B. Then, use Account B to log on to the PL/SQL Developer tool, create A command window, paste the file content, and run the command. 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.