Copy user using DBMS_METADATA

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.

- Advertisement -

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 the above commands in the DBMS_METADATA package, we can make DDL script for creating a new empty user. Sometimes this script is very useful to make the same user with the same role, privileges, default tablespace, and temporary tablespace specification.

- Advertisement -

Dbametrix is one of the leading remote Oracle DBA support service providers and we offer remote Oracle DBA support service at a very low cost. A client of Dbametrix can able to put trust in the company because Dbametrix delivers a 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 deliverables.

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 at very little cost. In short, we can say it is an absolutely cost-effective remote service plan. For more detail, kindly check our site.

Thanks and regards,

Dbametrix Solutions

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -