Wednesday, October 28, 2020
dbametrix
More
    Home Oracle DBA Tips Copy user using DBMS_METADATA

    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 -
    dbametrix

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

    - Advertisement -
    dbametrix

    Dbametrix is one of the leading remote Oracle DBA support service provider 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 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 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

    - Advertisement -
    dbametrix

    1 COMMENT

    1. Hi,

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

      Thanks for sharing ..

      Regards,
      Sheldon.

    Comments are closed.

    - Advertisment -
    dbametrix

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments