User Info

[mzhu]

Hi there,

Does anybody know which file contains user information such as user name, password and privileges etc.

I am trying to figure out one schema's backup/restore for a particular user. I know I can do the tablespace backup/restore, but I don't know how to backup/restore user information.

Thanks for any help.

[jdorlon]

are you restoring from a backup or just trying to create the same user in another database?

If the latter, then all of this information is available in the database in views like DBA_USERS, DBA_ROLES, DBA_ROLE_PRIVS, DBA_TAB_PRIVS. In this case it might be easiest to get a GUI tool like EZSQL (www.ezsql.net) or if you look around on the internet you can probably find some scripts to extract this information.

If are trying to restore from an export you can use the FROMUSER and TOUSER options to restore a single user.

hope this helps,
-John

[mzhu]

I need to restore from a backup. But I don't want to back/restore whole database, just one use's schema and this user's information (name. password, privileges etc.)

[jmodic]

You can't restore that from backup.

You canonly use import (if you have corresponding export dmp), otherwise you would heva to perform a complete restore/recovery of the entire database to get those user information.

[mzhu]

Thanks for the response.

Do I have to export the whole database in order to get the
user information? If I just need to export a part of database,
which part do I need to export?

[jmodic]

Depends on what do you mean by "... to get the user information...". If you have a user allready created and want to import just his objects, then an export done in user mode is sufficient. On the other hand, if you wand your import session also to create the user (setting username, password, default tablespaces, quotas, privileges etc etc) you have to import from previous *full* export.

[mzhu]

In my case, I need to backup/restore all data related to one user. As user's password, privileges etc can be changed, I also want to backup/restore these information except for this user's schema objects. The following is what I read from "Oracle8i Backup and Recovery Guide Release 8.1.5"
about using Export and Inport for Supplemental backup:

"To recover a database using Export files and the Import utility:
1. Re-create the database structure, including all tablespaces and users. (Note: These re-created structures should not have objects in them.)
2. Import the appropriate Export files to restore the database..."

So, it looks like import cannot restore USER (you have to re-create it). am I right?

[jdorlon]

I have had users created by IMP. As long as that user's DEFAULT and TEMP tablespaces exist it should create the user for you. But after that it will start building the objects too. You could just kill the import process after you see it has created the user. Then you might have a few objects to drop, but not too many if you are quick!

-John