RETRIEVING AN OBJECT’S DDL STATEMENT AND USER GRANTS

QUESTIONS
How can I generate a DDL script on an existing table or an existing view in an Oracle database?
How to get the DDLs of all the tables of a schema?
How to extract DDL script for tables, views, triggers, and other database objects in the SQL Plus
How to extract a user DDL from an Oracle database?

REQUEST
It’s been requested to backup database objects by extracting their DDL.

RELATED
RETRIEVING USER GRANT BY QUERYING A DATABASE VIEWS

SOLUTION
The GET_DDL and the GET_GRANTED_DDL functions of the DBMS_METADATA package allow you to extract the source for nearly everything in your database.

SYNTAX
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;

DBMS_METADATA.GET_GRANTED_DDL (
object_type     IN VARCHAR2,
grantee         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL',
object_count    IN NUMBER   DEFAULT 10000)
RETURN CLOB;

TESTCASE
Create the TESTCASE user and grant some privileges to it

-- The TESTCASE user 

CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;

-- Grant some system roles to the TESTCASE user

GRANT CONNECT, RESOURCE TO TESTCASE;

-- Grant some system privileges to TESTCASE user

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

-- Grant SELECT privilege on some system views to TESTCASE user

GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON V_$SESSION_EVENT TO TESTCASE;

Connect as the TESTCASE user and create a table with an index

-- Connect as the TESTCASE user

SQL> CONNECT TESTCASE/TESTCASE
Connected.

SQL> SHO USER
USER is "TESTCASE"

-- Create a simple table 

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;

-- Query from the created table 

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

 DIGIT TEXT
------ -----
     1 One
     2 Two
     3 Three
	 
-- Create an index on the table 

CREATE INDEX MYINDEX ON MYTABLE(DIGIT);

-- Create a directory 

CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/tmp';

At this point there are 3 manually created objects in the database – Table, Index and Directory.

Before retrieving any metadata for created objects, it is necessary to customize the DBMS_METADATA package output as by default it’s too verbose. The next statements will modify the output so it is nicely formatted.

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 formerly created objects through the GET_DDL function

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') FROM DUAL;

-- Connect as a privileged user

SQL> SHO USER
USER is "SYS"

-- Retrieve a DDL for the table 

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

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

-- Retrieve a DDL for the index

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

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

-- Retrieve a DDL for the directory 

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

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

To get OBJECT, SYSTEM, or ROLE privileges for the created user the GET_GRANTED_DDL is used

SELECT DBMS_METADATA.GET_GRANTED_DDL('TYPE_OF_GRANT','USERNAME') FROM DUAL;

-- Connect as a privileged user

SQL> SHO USER
USER is "SYS"

-- Retrieve SYSTEM privileges granted to the TESTCASE user 

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";

-- Retrieve OBJECT privileges granted to the TESTCASE user 

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;

-- Retrieve ROLEs granted to the TESTCASE user 

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

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

As can been seen from the above output that both DDL statements and grants returned by the functions are exactly the same as it was provided to the TESTCASE user

The GET_DDL function also retrieve DDL for other type of database objects such as DB_LINK, CONSTRAINT, USER, TABLESPACE and so on. Full list of supported objects can be found in the documentation. For instance

-- Connect as a privileged user

SQL> SHO USER
USER is "SYS"

-- Retrieve metadata for the TESTCASE user

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

   CREATE USER "TESTCASE" IDENTIFIED BY VALUES 'S:91DE9...F3E4B20' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

-- Retrieve metadata for the USERS tablespace

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;

That’s it. The testcase is done. Drop the TESTCASE user

SQL> DROP USER TESTCASE CASCADE;

User dropped.