Category: oracle

How to mirror database Privileges of a Oracle database user to another database user?

Script for Mirroring Privileges of a user:


Scenario: A Database schema named “TEST2” is created and business wants to grant the privileges for the user same as already existing Database user “TEST1”.


Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.


SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST1′) FROM DUAL;


 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST1′) FROM DUAL;


 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST1′) FROM DUAL;

 The output will be a sequence of grant commands related to privileges assigned to TEST1 user. Now replace the string TEST1 with new user name “TEST2” and run the commands.

 This will grant all privileges same as TEST1 user to TEST2 user in Oracle Database.

Change Oracle Database to ArchiveLog Mode

Steps to change the database to Archivelog Mode:


1. Login to the database server. Connect to sqlplus as sysdba


$sqlplus / as sysdba


2. Shutdown the database


SQL> shutdown immediate


3. Take a full database Backup (Cold Backup in this case)


4. Startup the database in mount stage


SQL> startup mount


5. Enable Archivelog mode


SQL> alter database archivelog;


6. Open the Database.


SQL> Alter database open;


7. Verify the changes.


SQL> archive log list;