Initial use of Exp/imp in Oracle database

When I recently used oracle9i, I encountered problems with database backup and recovery, which are often related to the use of exp and IMP commands. For the problems encountered, a simple summary.

The background of the problem is the need to import and export a table. Database Database1,dateabase2 in the database involved in the user is mainly User1,user2, the Backup object is mainly table table1.

For more information on the content of exp and IMP commands, here is the main solution to the specific problem, using only the commonly used command items.

1. Export the table from DATABASE2 table1

Exp Userid=system/[email protected] tables= (user1.table1) rows=y log=d:\log.txt file=d:\table1.dmp

This is generally not a problem, preferably run in the Command Line window. Export the Table1 in the User1 scheme in the DATABASE2 database. The export seems to have a principle, which scheme (user) of the table, only the owner can execute. That is, although the system user is used, but at exp, the database will switch the user to User1, the owner of the Table1, at the beginning exp. However, the general export is still using a relatively high number of users such as Sysem.

Exp Userid=lj/[email protected] full=n tables= (CT231) rows=y log=d:\log1.txt file=d:\ct2311.dmp

2. In the case of IMP, the following problems were encountered:

1) The user can perform the import only if the permission is equal.

If such a command is written, imp User2/[email protected] tables= (table1) rows=y log=d:\log.txt file=d:\table1.dmp

Tip: Users with DBA authority are exported and imported by users with equivalent permissions. Is the user user2 to import, does not have DBA authority, does not allow import

2) IMP-00033: Warning: Table not found in export file

Imp system/[email protected] rows=y log=d:\log.txt file=d:\table1.dmp

--imp-00033: Warning: Table not found in export file

By querying the relevant data, should be a backup object without specifying a scheme, add touser=user2 to the command, run the problem or exist, imp system/[email protected] rows=y log=d:\log.txt file=d:\ Table1.dmp touser=user2--imp-00033: Warning: Table not found in export file

It is also the same question whether it is necessary to establish a plan with user2 in Database1 and to create a user3 scheme. Imp system/[email protected] rows=y log=d:\log.txt file=d:\table1.dmp touser=user3--user3 Real Database2 The user (scheme) established in the database differs from the User1 in Database1.

In the continuation of the review, many people have pointed out a problem, that is, when the order is used, must ensure that the minimum integrity, is the scheme, the owner to specify, that is, Fromuser=user1, touser=user2 to give. The problem is solved.

Imp system/[email protected] fromuser=user1 touser=user2 rows=y log=d:\log.txt file=d:\table1.dmp

In fact, Touser can also indicate that different users in the target database, not only can be user2, but also other users, so table1 in the target database of the owner is transformed into a specified user. This makes it possible to change the owner of a table, which is typically not changed by the owners of the data objects in the Oracle database.

The import and export of a data table can be more than one table. It is generally difficult to guarantee the integrity of table constraints and other objects created. If you specify Owner= (), which means that you want to export a scenario, you cannot specify tables= () at this point, and there is a conflict.

The entire database import and export is also possible. Of course, you can export the whole library, only the part of the scheme needed to modify the command parameters to use.

Initial use of Exp/imp in Oracle database