Get DDL user metadata using dbms_metadata.get_ddl

I will start posting some of my old personal notes, that are still valid on day a day oracle database support, just clarifying that they are not new but it can help

this note is just for get DDL metada from a specific user, it will extract most of the metadata related to it.


set heading off;
set echo off;
Set pages 999;
set long 90000;

SYS@orcl > SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2 'ROLE_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
--------------------------------------------------------------------------------
   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2   'OBJECT_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT SELECT ON "HR"."EMPLOYEES" TO "SCOTT"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2  'SYSTEM_GRANT','SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------
  GRANT UNLIMITED TABLESPACE TO "SCOTT"

SYS@orcl >  SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

DBMS_METADATA.GET_DDL('ROLE','RESOURCE')
--------------------------------------------------------------------------------
   CREATE ROLE "RESOURCE"

SYS@orcl > SELECT DBMS_METADATA.GET_GRANTED_DDL(
  2  'SYSTEM_GRANT','RESOURCE') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE')
--------------------------------------------------------------------------------
  GRANT CREATE INDEXTYPE TO "RESOURCE"
  GRANT CREATE OPERATOR TO "RESOURCE"
  GRANT CREATE TYPE TO "RESOURCE"
  GRANT CREATE TRIGGER TO "RESOURCE"
  GRANT CREATE PROCEDURE TO "RESOURCE"
  GRANT CREATE SEQUENCE TO "RESOURCE"
  GRANT CREATE CLUSTER TO "RESOURCE"

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s