Shulou(Shulou.com)06/01 Report--

Recently, when using oracle9i, I have encountered problems with database backup and recovery, which are often related to the use of exp and imp commands. In view of the problems encountered, a simple summary.

The background of the problem is that you need to import and export a table. The users involved in the database database1,dateabase2 are mainly user1,user2, and the backup objects are mainly table table1.

For the content of exp and Imp commands more online commentary, here mainly to solve specific problems, the use of only commonly used command items.

1. Export table table1 from database2

Exp userid=system/mypawd@dateabase2 tables= (user1.table1) rows=y log=d:\ log.txt file=d:\ table1.dmp

There is generally nothing wrong with this, and it is best to run it in a command line window. Export the table1 from the user1 scheme in the database2 database. Export seems to have a principle that which scheme (user) table can only be executed by the owner. In other words, although the system user is used, the database switches the user to user1, which is the owner of the table1, at the beginning of the exp when exp. However, users with relatively high privileges, such as sysem, are generally used for export.

Exp userid=lj/ljjtw@hmis1 full=n tables= (CT231) rows=y log=d:\ log1.txt file=d:\ ct2311.dmp

two。 The problems encountered in Imp are as follows:

1) only users with equivalent permissions can perform the import.

If you write the command like this, imp user2/mypawd@dateabase1 tables= (table1) rows=y log=d:\ log.txt file=d:\ table1.dmp

Tip: users with dba permission to export and import should also be imported by users with the same permissions. That is, the user user2 to be imported does not have the permission of dba and is not allowed to import

2) IMP-00033: warning: table not found in the exported file

Imp system/mypasswd@database2 rows=y log=d:\ log.txt file=d:\ table1.dmp

-- IMP-00033: warning: table not found in the exported file

By querying the relevant information, it should be a backup object without a specified scheme, and touser=user2 was added to the command, but the running problem still exists. Imp system/mypasswd@database2 rows=y log=d:\ log.txt file=d:\ table1.dmp touser=user2-- IMP-00033: warning: no table was found in the exported file

And think about whether it is necessary to establish a solution different from user2 in database1, and create a user3 scheme, or the same problem. Imp system/mypasswd@database2 rows=y log=d:\ log.txt file=d:\ table1.dmp touser=user3-- the user (scheme) established in the database2 database is different from the user1 in database1.

In the continued review, many people have pointed out a problem, that is, when the command is used, it must ensure the lowest integrity, that is, the scheme, the owner should specify, that is, fromuser=user1, touser=user2 should be given. The problem is solved.

Imp system/mypasswd@database2 fromuser=user1 touser=user2 rows=y log=d:\ log.txt file=d:\ table1.dmp

In fact, touser can also indicate different users in the target database, not only user2, but also other users, so that the owner of table1 in the target database is transformed into a specified user. This allows you to change the owner of a table, which usually cannot be changed by the owner of the data object in the Oracle database.

The import and export of data tables can have multiple tables. 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 scheme, you cannot specify tables= () at this time, so there will be conflicts.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.