how to clone oracle user

how can i clone oracle user using oracle 10g
tifa-tifaAsked:
Who is Participating?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.