How to get DDL for DB objects and Dependent Objects

It was very tough to get the DDL for database objects in eralier versions of Oracle. But from Oracle 9i, it is pretty easy to get the DDL of Database Objects.

In Oracle9i we have the exciting new dbms_metadata utility to display DDL directly from the data dictionary. Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy. You simply execute dbms_metadata. get_ddl.

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.


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

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

Here is the output. The only thing missing is the ending semicolons after each statement. Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.

CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Now we can modify the syntax to punch a whole schema. It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;

If the specified objectname is not found, this packaged procedure throws an error.

e.g.

SQL> SELECT dbms_metadata.get_dependent_ddl('INDEX','T1','SCOTT') FROM dual;
ERROR:
ORA-31608: specified object of type INDEX not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 2808
ORA-06512: at "SYS.DBMS_METADATA", line 4394
ORA-06512: at line 1


Note: Above all we need to use 'select' query to get the result rather 'execute'
all the parameters passing to the packged procedure are case sensitive in general in upper case
Note: Make sure you give proper sql*plus session settings like
set wrap on
column column_name a5000
set trimspool on
set echo off
set long 6000

The below are the object types that are supported.
OBJECT TYPES
----------------
ASSOCIATION
AUDIT
AUDIT_OBJ
CLUSTER
COMMENT
CONSTRAINT
CONTEXT
DB_LINK
DEFAULT_ROLE
DIMENSION
DIRECTORY
FUNCTION
INDEX
INDEXTYPE
JAVA_SOURCE
LIBRARY
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
OBJECT_GRANT
OPERATOR
OUTLINE
PACKAGE
PACKAGE_SPEC
PACKAGE_BODY
PROCEDURE
PROFILE
PROXY
REF_CONSTRAINT
ROLE
ROLE_GRANT
ROLLBACK_SEGMENT
SEQUENCE
SYNONYM
SYSTEM_GRANT
TABLE
TABLESPACE
TABLESPACE_QUOTA
TRIGGER
TRUSTED_DB_LINK
TYPE
TYPE_SPEC
TYPE_BODY
USER
VIEW


1. An Object types you can see from querying the DBA_OBJECTS view for object_type.
2. A predefined type supplied by Oracle for use in the DBMS_METADATA functions such as REF_CONSTRAINT, OBJECT_GRANT, etc.




DBMS_METADATA.GET_DEPENDENT_DDL:
Often times we need to determine how a particular object relates to other objects in our database. This is what the GET_DEPENDENT_DDL function was created for. With this function it is easy to determine such things as the grants on an object, the referential integrity between objects, and the indexes on an object. This function can be a bit confusing when supplying inputs to the function call. What is needed to remember is that your are asking for items that are dependent on a named object in Oracle. Here are a few examples to help you get familiar with the proper use of this powerful function.

Suppose you wanted to extract all source code for triggers where a particular table was used in? You no longer need to search through DBA_SOURCE you need only issue the following SQL. Please note that it is the object_type that is dependent on the named object . Extend this query a bit and you can see how you might be able to extract all source code for an object with a simple changing of TRIGGER to include PROCEDURE, PACKAGE, FUNCTION, and JAVA_SOURCE.

e.g.

SQL> SELECT dbms_metadata.get_dependent_ddl('INDEX','T','SCOTT') FROM dual;

CREATE INDEX "SCOTT"."I_2" ON "SCOTT"."T" ("LNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

CREATE INDEX "SCOTT"."T_I" ON "SCOTT"."T" ("FNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"



'CONSTRAINT' and 'REF_CONSTRAINT' key words can be used only with get_dependent_ddl but not with get_ddl.

There are two constraints and one ref constraints defined on the table T4. If 'CONSTRAINT' is used then we see only the defination of two constraints but not the refconstraints. The same with 'REF_CONSTRAINT'.
Below are the examples.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','T4','SCOTT') from dua
l;

ALTER TABLE "SCOTT"."T4" ADD PRIMARY KEY ("A")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE

ALTER TABLE "SCOTT"."T4" ADD UNIQUE ("B")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE



Elapsed: 00:00:00.17
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','T4','SCOTT') from
dual;

ALTER TABLE "SCOTT"."T4" ADD FOREIGN KEY ("C")
REFERENCES "SCOTT"."T6" ("A") ENABLE



Elapsed: 00:00:00.12
SQL> SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','T4','SCOTT') from dual;
ERROR:
ORA-31603: object "T4" of type REF_CONSTRAINT not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

Elapsed: 00:00:00.12
SQL> SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','T4','SCOTT') from dual;
ERROR:
ORA-31603: object "T4" of type CONSTRAINT not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



DBMS_METADATA.GET_GRANTED_DDL
The purpose of the GET_GRANTED_DDL function is to generate the DDL required around grants given to a particular user.

If you wanted to generate the system grants given to a user.
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','') from dual;

If you wanted to generate the roles granted to a user.
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','') from dual;

If you wanted all the object grants given to a user.
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','') from dual;

In an interview i was asked to explain something about 'PRAGMA' keyword. I had no answer..here is the brief of it.

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.
The instruction is a statement that provides some instructions to the compiler.
Pragmas are defined in the declarative section in PL/SQL.

The following pragmas are available:

AUTONOMOUS_TRANSACTION:
Prior to Oracle 8.1, each Oracle session in PL/SQL could have at most one active transaction at a given time. In other words, changes were all or nothing. Oracle8i PL/SQL addresses that short comings with the AUTONOMOUS_TRANSACTION pragma. This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction. For instance, if rollback or commit needs to take place within the block without effective the transaction outside the block, this type of pragma can be used.

EXCEPTION_INIT:
The most commonly used pragma, this is used to bind a user defined exception to a particular error number.

For example:

Declare
I_GIVE_UP EXCEPTION;
PRAGMA EXCEPTION_INIT(I_give_up, -20000);

BEGIN
..

EXCEPTION WHEN I_GIVE_UP
do something..

END;

RESTRICT_REFERENCES:
Defines the purity level of a packaged program. This is not required starting with Oracle8i.

Prior to Oracle8i if you were to invoke a function within a package specification from a SQL statement, you would have to provide a RESTRICT_REFERENCE directive to the PL/SQL engine for that function. This pragma confirms to Oracle database that the function as the specified side-effects or ensures that it lacks any such side-effects.

Usage is as follows:

PRAGMA RESTRICT_REFERENCES(function_name, WNDS [, WNPS] [, RNDS], [, RNPS])

WNDS: Writes No Database State. States that the function will not perform any DMLs.

WNPS: Writes No Package State. States that the function will not modify any Package variables.

RNDS: Reads No Database State. Analogous to Write. This pragma affirms that the function will not read any database tables.

RNPS: Reads No Package State. Analogous to Write. This pragma affirms that the function will not read any package variables.

SERIALLY_REUSABLE:
This pragma lets the PL/SQL engine know that package-level data should not persist between reference to that data.

Package data (global variables in package specification etc.) by default persists for an entire session (or until a package is recompiled). Globally accessible data structures can cause some side effects. For instance, what if a cursor is left open in a package. In addition, a program can use up lots of real memory (UGA) and then not release it if the data is stored in a package-level structure.

In order to manage this, Oracle8i introduced the SERIALLY_REUSABLE pragma. This pragma is used in packages only and must be defined BOTH in specification and in the body.

The advantage is that based on the pragma, a package state can be reduced to a single call of a program unit in the package as opposed to the package being available for the whole session.

Decode and NULL

As a rule, comparison to NULL should always return NULL. However, DECODE is an exception as it evaluates NULL == NULL

SQL> SELECT decode(null,null,1,0) FROM dual;
DECODE(NULL,NULL,1,0)
---------------------
1

Hierarchical Queries

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:

START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

• The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop
• In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
• ... PRIOR expr = expr
• or
• ... expr = PRIOR expr
•

If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. For example:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...


PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.
Oracle processes hierarchical queries as follows:

• A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
• The CONNECT BY condition is evaluated.
• Any remaining WHERE clause predicates are evaluated.

Oracle then uses the information from these evaluations to form the hierarchy using the following steps:
1. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.
2. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.
3. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
4. If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause.

Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

In hierarchical queries the syntax is as follows,
SELECT EMP.*,sys_connect_by_path(ename,'/'),LEVEL,connect_by_iscycle,connect_by_isleaf,CONNECT_BY_ROOT ename
FROM EMP
CONNECT BY NOCYCLE PRIOR empno=mgr
ORDER siblings BY ename


sys_connect_by_path(ename,'/'):
SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.
Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.

connect_by_iscycle:
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of aCONNECT BY loop in the data.

connect_by_isleaf:
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.

Level:
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children

Prior:
In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator. If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions. PRIOR evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error. Please refer to “Hierarchical Queries” for more information on this operator, including examples.

connect_by_root:
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.
Restriction on CONNECT_BY_ROOT You cannot specify this operator in the START WITH condition or the CONNECT BY condition

siblings:
The SIBLINGS keyword is valid only if you also specify the hierarchical_query_clause (CONNECT BY). ORDER SIBLINGS BY preserves any ordering specified in the hierarchical query clause and then applies theorder_by_clause to the siblings of the hierarchy

Note:
1. In hierarchical queries where clause (not joins) is executed after the connect by clause.
2. Level can be used in the where clause of hierarchical query.
3. Where cant be placed after the connect by clause.

Here is a sample query which includes all the keywords

select empno, sys_connect_by_path(ename,'/') path, connect_by_root(ename), connect_by_isleaf, connect_by_iscycle
from emp
start with mgr is null
connect by nocycle prior empno = mgr
order siblings by ename
/

The simple implementation of hierarchical query is as followed by,
ORA11GR1> select * from counter where mgr=0;

X Y MGR
---------- ---------- ----------
1 A 0


It then applies the connect by TO EACH RECORD RETURNED BY THE START WITH. So, once this start with record is "output", it becomes the "prior" record. So, we take "X" from this prior record
ORA11GR1> select x from counter where mgr = 0;

X
----------
1



and use that value to retrieve the next level of records by MGR:
ORA11GR1> select * from counter where (select x from counter where mgr=0) = mgr;

X Y MGR
---------- ---------- ----------
2 B 1
3 C 1


so, that start with record is connected to these two records. Now, they each in turn become the "prior" record, so we'll take X=2 to find any MGR=2 records and output them (whence they themselves become the prior record and so on)

resulting in the hierarchy:
ORA11GR1> SELECT rpad('*',2*level,'*') data, x,y,mgr,LEVEL
2 FROM (SELECT x,y,mgr FROM counter )
3 START WITH mgr=0 CONNECT BY PRIOR x=mgr
4 ORDER BY mgr
5 /

DATA X Y MGR LEVEL
---------- ---------- ---------- ---------- ----------
** 1 A 0 1
**** 2 B 1 2
**** 3 C 1 2
****** 4 D 2 3
****** 6 F 2 3
****** 5 E 3 3
******** 7 G 4 4
******** 8 H 6 4

8 rows selected.


one level=1 (start with)
connected to the two records such that their MGR = level 1's X=1 value.


If you leave off the start with, that is like saying "start with 1=1, EVERY record". So every record is a level one record (and maybe someone elses level 2, 3, 4, whatever....)
ORA11GR1> SELECT rpad('*',2*level,'*') data, x,y,mgr,LEVEL
2 FROM (SELECT x,y,mgr FROM counter )
3 START WITH 1=1 CONNECT BY PRIOR x=mgr
4 ORDER BY mgr
5 /

DATA X Y MGR LEVEL
---------- ---------- ---------- ---------- ----------
** 1 A 0 1
**** 2 B 1 2
**** 3 C 1 2
** 2 B 1 1
** 3 C 1 1
**** 4 D 2 2
****** 6 F 2 3
** 6 F 2 1
**** 6 F 2 2
** 4 D 2 1
****** 4 D 2 3
****** 5 E 3 3
**** 5 E 3 2
** 5 E 3 1
******** 7 G 4 4
****** 7 G 4 3
** 7 G 4 1
**** 7 G 4 2
**** 8 H 6 2
** 8 H 6 1
******** 8 H 6 4
****** 8 H 6 3

22 rows selected.