How to extract Oracle user DDL

In some cases, you need to duplicate an Oracle user, along with all it’s privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay.

Here’s how to do it:

set head off
set pages 0
set long 9999999
spool user_script.sql

SELECT DBMS_METADATA.GET_DDL('USER', '&&USERNAME') || '/' as DDL
FROM DBA_USERS
UNION
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&USERNAME') || '/' as DDL
FROM DBA_USERS
UNION
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&USERNAME') || '/' as DDL
FROM DBA_USERS
UNION
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&USERNAME') || '/' as DDL
FROM DBA_USERS;

spool off;

Now, if we were to do this for multiple users, I would suggest using a PL/SQL script to iterate over a list of users. Like so:

DECLARE
 v_ddl_user VARCHAR2(3000);
 v_ddl_rol_grant VARCHAR2(3000);
 v_ddl_sys_grant VARCHAR2(3000);
 v_ddl_obj_grant VARCHAR2(3000);
 type usr_type is table of VARCHAR2(30);
 usr usr_type := usr_type ('USER_1', 'USER_2', 'USER_3');
 BEGIN
   FOR i IN 1..usr.count LOOP
     SELECT DBMS_METADATA.GET_DDL('USER',usr(i))||'/' INTO v_ddl_user FROM DBA_USERS;
     SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',usr(i))||'/' INTO v_ddl_rol_grant FROM DBA_USERS;
     SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',usr(i))||'/' INTO v_ddl_sys_grant FROM DBA_USERS;
     SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',usr(i))||'/' INTO v_ddl_obj_grant FROM DBA_USERS;
     dbms_output.put_line(v_ddl_user);
     dbms_output.put_line(v_ddl_rol_grant);
     dbms_output.put_line(v_ddl_sys_grant);
     dbms_output.put_line(v_ddl_obj_grant);
    END LOOP;
 END;
/

Written on February 20th, 2018 by Samy Gejzenblozen

Tags:

#database #oracle

Social networks

You may also enjoy:

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more

20 Apr 2019 - less than 1 minute read

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

Forcing ASMM component to shrink

#oracle #database

20 Mar 2019 - 2 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a hung database and must be quickly resolved as the database isn’t accessible for your users anymore. As you cannot connect to the hung database, yo... Read more

20 May 2017 - 1 minute read