Home » RDBMS Server » Server Utilities » Exporting USER DLL only
icon7.gif  Exporting USER DLL only [message #136379] Wed, 07 September 2005 17:08 Go to next message
samweber
Messages: 5
Registered: September 2005
Junior Member
I have a DB with a large number of users. The schemas for these users have no data or objects. I want to export the creation DDL for most of these users but .. do not want to use a FULL export because it will bring over SYSTEM tablespace data I do not want. Is there a combination of export parameters that can bring over just the user objects without anything else?

Can you do a FULL export but not IMPORT designated schemas and still get the DDL for the users I do want?

Report message to a moderator

Re: Exporting USER DLL only [message #136485 is a reply to message #136379] Thu, 08 September 2005 04:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. You can use fromuser/touser options during import.
2. if your are looking into import only user DDL and not any data,
use ROWS=n.

Report message to a moderator

Re: Exporting USER DLL only [message #136546 is a reply to message #136485] Thu, 08 September 2005 12:02 Go to previous messageGo to next message
samweber
Messages: 5
Registered: September 2005
Junior Member
ok, so the idea would be to export FULL to pick up the user DDL, then use FROMUSER, TOUSER for each of say 500 users I want to create on the new system?

What I want essentially to create the same app users as were on the source DB but not bring over SYSTEM, SCOTT etc, etc.

Can you get the user creation DDL without a FULL export?

Report message to a moderator

Re: Exporting USER DLL only [message #136547 is a reply to message #136546] Thu, 08 September 2005 12:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What is your need?

1.)you want to export all users (except a few) with all data/objects
or
2.)you want to export all users (except a few) without data but only objects?
Or
3.)You want only the USER DDL ( no objects / empty schema)?

>>>>Can you get the user creation DDL

I believe, you are now talking about the need 3 option.

Please Look here
http://www.orafaq.com/forum/t/20026/0/


THis script generates the DDL user-by-user.
edit the script( to disable prompting for entry and instead query directly the dictionary objects for all users).
Spool the file.
Run against your target database.
ELse
take full export ( with rows=n, which is much faster).
do import with show=y logfile=somelogfile.sql
now actual import is not done.
Instead all DDL is dumped into somelogfile.sql
Edit the somelogfile.sql, to remove any entry you dont want.
run against the target.

Report message to a moderator

Re: Exporting USER DLL only [message #136551 is a reply to message #136547] Thu, 08 September 2005 12:52 Go to previous messageGo to next message
samweber
Messages: 5
Registered: September 2005
Junior Member
Yes, what I want is to duplicate all the users who have a particular default tablespace. The problem though is that this is at a customer site so I do not have their individual user passwords (and do not want to see theirs). A full export/import can dup their passwords too but .. brings in users I do not want. I actually have written a PL/SQL script which can dup all the users with generic passwords but.. would like to find a way to dup them with their original passwords so 500 users do not have to change their passwords again through the app.

Any thoughts there?

Report message to a moderator

Re: Exporting USER DLL only [message #136554 is a reply to message #136551] Thu, 08 September 2005 13:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you can get password from somefile.sql
or
generated it on the fly from source!

scott@9i > alter user test identified by test;

User altered.

scott@9i >  select username,password from dba_users where username='TEST';

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

scott@9i > alter user test identified by values '7A0F2B316C212D67';

User altered.

scott@9i > connect test/test
Connected.

Report message to a moderator

Re: Exporting USER DLL only [message #136560 is a reply to message #136554] Thu, 08 September 2005 14:02 Go to previous message
samweber
Messages: 5
Registered: September 2005
Junior Member
ok, so if you use the clause "identified by values 'encrypted_password'" it will convert the encrypted version for you back to the correct original... Great! That should solve the problem for me. That's very handy info.

Thanks very much for your help on this...

Sam

Report message to a moderator

Previous Topic: How to transport multilingual data to an Oracle database
Next Topic: SQLLDR (Message 2100 not found)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 09 01:44:33 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.