Re: Exporting/Importing USER accounts

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 22 Jun 2002 15:26:56 +0200
Message-ID: <9gu8hu848jopn1tld0ambvbe4t035p4vk1_at_4ax.com>


On Sat, 22 Jun 2002 13:28:42 +0200, Rick Denoire <100.17706_at_germanynet.de> wrote:

>Hello
>
>I recently imported several schemas from one Unix based DB to a fresh
>NT based one, which was set up fresh. But it was a pain since I had to
>recreate the corresponding users on the new DB, including their roles,
>privileges, etc.
>
>Is there a way to extract this information as DDL scripts from a full
>dump made with the export utility?
>
>As you might think, recreating these objects is prone to errors, so as
>a matter of fact some of them occurred.
>
>I suppose that without the target accounts being there beforehand
>import would take place into the SYSTEM schema, which is bad...
>
>Any hints?
>
>Thanks
>
>Rick Denoire

Actually it is not that difficult to retrieve it from the datadictionary.

You need

dba_users where username = '<affected user>'
dba_ts_quotas where username = '<affected user>'
dba_role_privs where grantee = 'affected user>'
dba_tab_privs where grantee = 'affected user>'
dba_sys_privs where grantee = 'affected user>'

You can write a script starting with
set heading off feedback off pagesize 0
spool t.sql
select 'create user '||username||' identified by values '''||password||''' default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace from dba_users
where username = '<affected user>'
/
spool off
You probably get the idea

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jun 22 2002 - 15:26:56 CEST

Original text of this message