RETRIEVING AN OBJECT’S DDL STATEMENT AND USER GRANTS

 
QUESTIONS

 
REQUEST

 
RELATED

 
TESTCASE

To begin with I’ll create a user for this test case and grant privileges on several SYS objects to it

CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE TO TESTCASE;
GRANT CREATE ANY DIRECTORY TO TESTCASE;
GRANT UNLIMITED TABLESPACE TO TESTCASE;
GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON V_$SESSION_EVENT TO TESTCASE;

Connect as a new user and create some simple objects

SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> SHO USER
USER is "TESTCASE"

CREATE TABLE MYTABLE(DIGIT NUMBER, TEXT VARCHAR2(10));
INSERT INTO MYTABLE VALUES (1,'One');
INSERT INTO MYTABLE VALUES (2,'Two');
INSERT INTO MYTABLE VALUES (3,'Three');
COMMIT;

COL DIGIT FOR 99999 
COL TEXT FOR A5
SELECT * FROM MYTABLE;

 DIGIT TEXT
------ -----
     1 One
     2 Two
     3 Three
	 

CREATE INDEX MYINDEX ON MYTABLE(DIGIT);
CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/tmp';

At this point there are 3 manually created objects in the database. But before retrieving any object definitions, it is necessary to customize the package output. By default the output of this package is too verbose and the next piece of code will modify output in the way It will be nice formatted and can be used to recreate an object

SET PAGES 0
SET LINES 3000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT a2500 WORD_WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

Now let’s get ddl statements of 3 objects by get_ddl procedure

DBMS_METADATA.GET_DDL (
   object_type     IN VARCHAR2,
   name            IN VARCHAR2,
   schema          IN VARCHAR2 DEFAULT NULL,
   version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model           IN VARCHAR2 DEFAULT 'ORACLE',
   transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Issue the following queries under SYS user to get the objects DDL’s statements

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','MYTABLE','TESTCASE') AS TXT FROM DUAL;

  CREATE TABLE "TESTCASE"."MYTABLE" ("DIGIT" NUMBER, "TEXT" VARCHAR2(10)) ;

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','MYINDEX','TESTCASE') AS TXT FROM DUAL;

  CREATE INDEX "TESTCASE"."MYINDEX" ON "TESTCASE"."MYTABLE" ("DIGIT") ;

SQL> SELECT DBMS_METADATA.GET_DDL('DIRECTORY','MYDIRECTORY') AS TXT FROM DUAL;

   CREATE OR REPLACE DIRECTORY "MYDIRECTORY" AS '/tmp';

As a next step I retrieve all grants that user has (object, system, role)

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TESTCASE') AS TXT FROM DUAL;

  GRANT UNLIMITED TABLESPACE TO "TESTCASE";
  GRANT CREATE ANY DIRECTORY TO "TESTCASE";

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TESTCASE') AS TXT FROM DUAL;

  GRANT SELECT ON "SYS"."V_$SESSION" TO "TESTCASE";
  GRANT SELECT ON "SYS"."V_$SESSION_EVENT" TO "TESTCASE";
  GRANT EXECUTE ON DIRECTORY "MYDIRECTORY" TO "TESTCASE" WITH GRANT OPTION;
  GRANT READ ON DIRECTORY "MYDIRECTORY" TO "TESTCASE" WITH GRANT OPTION;
  GRANT WRITE ON DIRECTORY "MYDIRECTORY" TO "TESTCASE" WITH GRANT OPTION;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TESTCASE') AS TXT FROM DUAL;

   GRANT "CONNECT" TO "TESTCASE";
   GRANT "RESOURCE" TO "TESTCASE";

As you can see objects DDL statements and grants are the same as it was issued before.
This package also retrieves object DDL statements for other object types like user, tablespace, db_links, constraints and so on. To know a full list of all supported objects, please refer to the official documentation

SQL> SELECT DBMS_METADATA.GET_DDL('USER','TESTCASE') AS TXT FROM DUAL;

   CREATE USER "TESTCASE" IDENTIFIED BY VALUES 'S:91DE979C538FC1E4AF7578DECD5D74D03C8E942454EBEA8341D416655697;T:544CD8A7EFA8B1D080B4A07892F7207587B0A4BFBD59B1F945F0F8D5A41FF76CB00F1EACF0ACF04742C5E69A3A2265AB93FB4B0F4C15407D8F06E4C08C352C7E2CF57B9CC0C50A660D926555AF3E4B20' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') AS TXT FROM DUAL;

  CREATE TABLESPACE "USERS" DATAFILE
  '/oracle/oradata/DBPILOT/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Drop testcase user

SQL> DROP USER TESTCASE CASCADE;

User dropped.