Copy user using DBMS_METADATA

October 8, 2010 | By

Hi friends,

How to copy one user to make another user same as without any objects. In another words we can say that how to copy empty user with all grants,privileges and roles for creating same another user.

Yes it can be possible using DBMS_METADATA package. How can we do that? Let see example of same.

Spool following command using set pages 0 and set feedback off.

SELECT DBMS_METADATA.GET_DDL(‘USER’, USERNAME) || ‘;’ DDL
FROM DBA_USERS;
SELECT DBMS_METADATA.GET_DDL(‘ROLE’, role)||’;’ DDL
FROM dba_roles;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’, role) || ‘;’ DDL
FROM role_role_privs;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’, role) || ‘;’ DDL
FROM role_sys_privs;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’, role) || ‘;’ DDL
FROM role_tab_privs;
Now spool off

Using above commands in DBMS_METADATA package we can make DDL script for creating new empty user. Sometimes this script is very useful to make same user with same role, privileges,default tablespace and temporary tablespace specification.

Dbametrix is one of leading remote Oracle DBA support service provider and we offer remote Oracle DBA support service with very low cost. Client of Dbametrix can able to put trust on company because Dbametrix delivers cost effective remote dba plan using Service Level Agreement SLA and response time matrix. Due to this reason every client gets maximum satisfaction from our deliverable.

Dbametrix has launched a new remote  services called ET support. It is quite different and best in its class structure and especially helpful to cost cutting and save your I.T budget. It contains more features with very less cost. In short we can say it is absolutely cost effective remote service plan. For more detail kindly check our site.

Thanks and regards,

Dbametrix Solutions

Be Sociable, Share!

Tags: , , , , ,

Category: blog, blogging, business, database, database services, remote dba, remote services, software, technology

Comments (1)

Trackback URL | Comments RSS Feed

  1. Sheldon says:

    Hi,

    This is great write up and it does help when you doing the nity grity manually …

    Thanks for sharing ..

    Regards,
    Sheldon.