How to backup and restore records in database (Oracle 10)

Question

I have a table in Oracle for users. I am going to install new schema and want to backup all users with passwords and other fields.

I tried exp and int utilities, but imp doesn't recover anything. I created temporary user in USERS table. Then I did backup with command:

exp user_owner/password file=file.dmp table=USERS rows=yes indexes=no

After that I deleted the temporary username and I tried to restore with:

imp user_owner/password file=file.dmp table=users fromuser=user_owner

Export file created by EXPORT:V10.02.01 via conventional path import done in UTF8 character set and AL16UTF16 NCHAR character set . importing USER_OWNER's objects into USER_OWNER . importing USER_OWNER's objects into USER_OWNER IMP-00015: following statement failed because the object already exists: bla bla bla Import terminated successfully with warnings.

In the USERS table temporary user didn't appear.

Please advice how can I perform such task like backup and restore rows (with values) of the table in Oracle.

Answer 1

Use IGRNORE=Y as a parameter to your import. This will ignore creation errors.

Type imp help=y at the command line for more information.

Answer 2

The specific error you got on import is because by default it will try to create the table, not just the data in it. You can use the IGNORE=Y flag to avoid that problem. But it will try to insert all the users that existed, not just the one you deleted, which might cause you other problems. Or, it could fail for those rows if there's a unique index.