Today, my colleague said that he has applied for a dump file exported from production and needs to import the test database for testing. The operations previously performed are basically export from the current database and import from this database, such as imp test/*** @ test tables = tbl_fuel file = H: \ dev \ data \ tbl_fuel.dmp ignore = y. Today, dump is exported from the user of a database and then imported to the user of another database.
Export: exp p_x/** FILE = tables. dmp TABLES = e. pre_ldp, e. tbl_flight log = tables. log rows = y
Import errors: imp test/*** @ test tables = (tbl_a, tbl_ B) file = tables. dmp log = test. log full = y prompt: IMP-00024: Only one mode (TABLES, FROMUSER, or FULL) can be specifiedTABLES, FROMUSER, and FULL parameters can Only use one.
Imp test/*** @ test file = tables. dmp log = test. log ignore = n
Tip: Warning: the objects were exported by P_X, not by you
Import done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set
Import server uses ZHS16GBK character set (possible charset conversion)
Export client uses ZHS16GBK character set (possible charset conversion)
Export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00031: Must specify FULL = Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully
FULL = Y, FROMUSER/TOUSER, and TABLES must be specified.
Imp test/*** @ test file = tables. dmp log = test. log ignore = n fromuser = user ouser = test
Tip: Warning: the objects were exported by P_X, not by you
Import done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set
Import server uses ZHS16GBK character set (possible charset conversion)
Export client uses ZHS16GBK character set (possible charset conversion)
Export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00034: Warning: FromUser "USER" not found in export file
Import terminated successfully with warnings.
Note: The From User "USER" is not included in the dump file. However, this method can be used to find the dump file exported by the user using exp.
Imp test/*** @ test file = tables. dmp log = test. log ignore = n tables = tbl_a prompt: Warning: the objects were exported by P_X, not by you
Import done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set
Import server uses ZHS16GBK character set (possible charset conversion)
Export client uses ZHS16GBK character set (possible charset conversion)
Export server uses UTF8 NCHAR character set (possible ncharset conversion)
. Importing P_X's objects into TEST
IMP-00033: Warning: Table "TBL_A" not found in export file
It indicates that the TBL_A table is not in the exported dump. In my opinion, because FROMUSER is not used here, the data contained in the dump is from the local test user by default. Therefore, an error is returned.
Imp test/*** @ test file = tables. dmp log = test. log ignore = n fromuser = e touser = test (table structure not created) imp test/*** @ test file = tables. dmp log = test. log ignore = y fromuser = e touser = test (the table structure has been created)
After the table is deleted, ignore = n. The dump source is e schema or user, which is imported to the test user.
Of course, because the test user does not have some import/export roles, the following permission operations may fail, but they do not affect the use of these tests. IMP-00017: following statement failed with ORACLE error 1917:
"Grant update on" TBL_A "TO" B _E ""
Parameter: fromuser specifies the object under the user to be imported. Therefore, if p_x is used, an error is returned because the table is not in the user p_x.
Touser specifies the user to import. This parameter must be used with fromuser.
Show Displays the file content on the screen, but does not import the file.
Ignore ignores the error of the object created during the import process (ensure that the data can be imported ), however, the error of writing data is not ignored (for example, the imported data has an error that violates the uniqueness constraint with the data in the current table ).
When this parameter is set to Y, the data rows will be submitted during the import process. If the buffer is set too small, data will be submitted frequently in the past, which may affect the performance. Another advantage of using commit is that when testing data is imported, if some errors are not inserted into the database, commit will submit the matched records after using commit, and will not cause the entire import operation to roll back, after all, it is a test, and there are less data problems.
Full import the entire file. Tables: List of imported table names. Multiple table names are separated by commas.
When rows is set to Y, data is imported. By default, data is imported. However, for some reason, you can set this parameter to N to not import data rows.