how to clone oracle user

how can i clone oracle user using oracle 10g
tifa-tifaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
It depends on what you mean by clone.

Duplicating the objects (tables, indexes, etc) can be done with exp/imp (or the datapump versions expdp/impdp).

If you want to just copy a user with the grants/privs/etc???  There user to be a 'create like' in Enterprise Manager.

or there's a few scripts out there.  For example:
http://www.dba-oracle.com/t_cloning_oracle_user_id.htm
0
BawerCommented:
one of the other way around in cloning is to use cold backup....
0
tifa-tifaAuthor Commented:
hi. yes, would like to copy a user with the grants/privs/etc
i tried the link http://www.dba-oracle.com/t_cloning_oracle_user_id.htm 
it could not open an sql file i think oF original user.
sp2-0310: unable to open file: 20100412_142927_PXF.sql
PXF is the original user
 
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BawerCommented:
you should edit the file..
0
tifa-tifaAuthor Commented:
i chacked in the BIN folder and there are no sql files..am i suppose to make these files myself?
0
BawerCommented:
i will suggest you use slightwv idea of datapump much more easier...
0
slightwv (䄆 Netminder) Commented:
>>would like to copy a user with the grants/privs/etc

Are these grants/privs ones the user themselves has created of privs granted to this user?

exp/imp is good for self contained schema objects but can be troublesome when others have granted permissions to a user.

Moving forward I suggest you take the time to create scripts and proper roles.  This makes this task much easier.

You can take a FULL export then 'show' what would be done on import without actually doing it.  This will capture all the grant commands you need.  I suggest never doing a FULL import unless you know exactly what is in the export file.  A FULL import of a FULL export can mess up the dictionary objects.

>>am i suppose to make these files myself?

Scanning the script you will notice a SPOOL command.  This creates a file in whatever directory sql*plus was started in (or possibly the start-in directory in Windows).
0
tifa-tifaAuthor Commented:
thanx slighWV
http://www.dba-oracle.com/t_cloning_oracle_user_id.htm 
is this the only link available for this type of thing??
0
slightwv (䄆 Netminder) Commented:
>>is this the only link available for this type of thing??

Probably not.  I Googled it and that was the first link that looked decent.

Since it is very hard to do this type of thing across multiple environments with 100% accuracy I suggest you use them as a starting point.  Once you understand what they are trying to do, you can tweak it for your specific environment.

I'm still not sure what you mean by 'clone'.  

Do you have a single schema (tables,indexes,views,???) that you want to copy somewhere?  

What types of grants/synonyms/objedcts have been created 'outside' this schema?

or do you have a main application schema and are wanting to quickly 'clone' application usernames?
0
vijaycptCommented:
Try with the script to clone users

SPOOL CREATE_USER_POS.SQL
select name from v$database;
SET LINESIZE 300
SET PAGES 300
SET LONG 1000
SET HEADING OFF
SELECT NAME FROM V$DATABASE;
SELECT DBMS_METADATA.GET_DDL('USER','POS') FROM DUAL;
#SELECT USERNAME,PASSWORD FROM DBA_USERS WHERE USERNAME='POS';
#SELECT USERNAME,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE FROM DBA_USERS WHERE USERNAME='POS';
SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';' FROM DBA_SYS_PRIVS WHERE GRANTEE='POS';
SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';' FROM DBA_ROLE_PRIVS WHERE GRANTEE='POS';
SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' FROM DBA_TAB_PRIVS WHERE GRANTEE='POS';
SELECT 'GRANT '||PRIVILEGE||' ON'||OWNER||'.'||TABLE_NAME||' TO'||GRANTEE||';' FROM DBA_COL_PRIVS WHERE GRANTEE='POS';
SELECT 'ALTER USER '||USERNAME||' QUOTA UNLIMITED ON  '||TABLESPACE_NAME||';' FROM DBA_TS_QUOTAS WHERE USERNAME='POS' AND MAX_BYTES=-1;
select 'create SYNONYM '||OWNER||'.'||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||TABLE_NAME||';' from DBA_SYNONYMS WHERE OWNER='POS';
SPOOL OFF
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavidSenior Oracle Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.