Exporting Tables As Different User 1

[Michael42]

Hello,

I have a list of tables I need to export but must do this as a different user (with dba prvis) than the owner. Is there a way in a table mode export that I can get around putting the <schema>.tablename prefix in front of every table?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Yes, Oracle "exp" can be a little flakey that way. There are a couple of methods of circumventing the problem:

1) As the DBA user, export the entire schema of the table owner, then do an "imp" with these parameters:

...FROMUSER=<previous owner TOUSER=<new owner> TABLES=<table list>...

Or, you can export the tables with parameters:

...USERID=<owner>/<password> TABLES=<table list>...

...then use these "imp" parameters:

...FROMUSER=<previous owner> TOUSER=<new owner>...

If the problem is that you don't know the password of the table owner (to be able to do the "exp" as the table owner), then there is a way around that problem, but I'll wait to show you that trick until I know that you need that type of assistance.

Let us know what you decide.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Michael42]

Santa,

If the problem is that you don't know the password of the table owner (to be able to do the "exp" as the table owner), then there is a way around that problem, but I'll wait to show you that trick until I know that you need that type of assistance.

If you could please share how to do this, in my instance, I think this would be the ideal solution.

Thanks,

Michael42

 

[SantaMufasa]

Michael,

Connecting as some Oracle user (whose password you do not know) requires DBA privileges. Following is a proof-of-concept script that illustrates how you (as a DBA) can log in as another user:

SQL> select username, password from dba_users where username = 'TEST';

USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST                           7A0F2B316C212D67 [B][I]<- encrypted password value[/I][/B]

SQL> alter user test identified by michael; [B][I]<- assigned to user a new (interim) password[/I][/B]

User altered.

SQL> conn test/michael [B][I]<- successfully connected to target user while using new (interim) password[/I][/B]
Connected.
SQL> show user [B][I]<- confirms successful login[/I][/B]
USER is "TEST"
SQL> connect <DBA user>/<password> [B][I]<- reconnect as DBA[/I][/B]
Connected.
SQL> alter user test identified by [b]values[/b] '7A0F2B316C212D67'; [B][I]<- resurrected original user password[/I][/B]

User altered.

SQL> conn test/michael [B][I]<- confirmed that interim password no longer works[/I][/B]
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> select username, password from dba_users where username = 'TEST'; [B][I]<- confirmed that encrypted password matches original value[/I][/B]

USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST                           7A0F2B316C212D67

Let us know if you have questions about this process/method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[maswien]

excellent!

 

[Michael42]

Santa,

That is brilliant!

Thanks for posting sir.

Thanks,

Michael42

 

[SantaMufasa]

My pleasure!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]