Reverse engineering object DDL and finding object

If there is a task in Oracle for which the wheel has been reinvented many times, it is that of generating database object DDL. There are numerous scripts floating in different forums doing the same thing. Some of them work great, while others work only until a specific version. Sometimes the DBAs prefer to create the scripts themselves. Apart from the testing overhead, these scripts require substantial insight into the data dictionary. As new versions of the database are released, the scripts need to be modified to fit the new requirements.

Starting from Oracle 9i Release 1, the DBMS_METADATA package has put an official end to all such scripting effort. This article provides a tour of the reverse engineering features of the above package, with a focus on generating the creation DDL of existing database objects. The article also has a section covering the issue of finding object dependencies.

Why do we need to reverse engineer object creation DDL

We need them for several reasons:

• Database upgrade from earlier versions when for various reason export-import is the only way out. But huge databases would require a precreated structure - importing data with several parallel processes into individual tables.
• Moving development objects into production. The cleanest method is to reverse engineer the DDL of the existing objects and run them in the production.
• For learning the various parameters that an object has been created with. When we create an object, we do not specify all the options, letting Oracle pick the defaults. We might want to view the defaults that have been picked up, or we might want to crosscheck the parameters of the object. For that we need Enterprise Manager, Toad, or some other tool, or self-developed queries in the data dictionary. Now DBMS_METADATA get the clean complete DDL with all options.

Modes of usage of the Metadata Package

• A set of functions that can be used with SQL. This is known as the browsing interface. The functions in the browsing interface are GET_DDL, GET_DEPENDENT_DDL, GET_GRANTED_DDL
• A set of functions that can be used in PLSQL, which is in fact a superset of (1). They support filtering, and optional turning on and turning off of some clause in the DDL. The flexibilities provided by the programmer interface are rarely required. For general use the browsing interface is sufficient - more so if the programmer knows SQL well.

Retrieving DDL information by SQL

As mentioned in the section above, GET_DDL, GET_DEPENDENT_DDL and GET_GRANTED_DDL are the three functions in this mode. The next few sections discuss them in detail. The objects on which the examples are tested are given in Table 9.

GET_DDL

The general syntax of GET_DDL is
GET_DDL(object_type, name, schema, version, model, transform).

Version, model and transform take the default values "COMPATIBLE", "ORACLE", and "DDL" - further discussion of these is not in the scope of this article.

object_type can be any of the object types given in Table 8 below. Table 1 shows a simple usage of the GET_DDL function to get all the tables of a schema. This function can only be used to fetch named objects, that is, objects with type N or S in Table 8. We will see in a later section how the "/" at the end of the DDL can be turned on by default.

Table 1 (DBMS_METADATA.GET_DDL Usage)

SQL> set head off
SQL> set long 1000
SQL> set pages 0
SQL> show user
USER is "REVRUN"
SQL>
SQL> select DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')||'/' from dual;

CREATE TABLE "REVRUN"."EMPLOYEE"
( "LASTNAME" VARCHAR2(60) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
"MI" VARCHAR2(2),
"SUFFIX" VARCHAR2(10),
"DOB" DATE NOT NULL ENABLE,
"BADGE_NO" NUMBER(6,0),
"EXEMPT" VARCHAR2(1) NOT NULL ENABLE,
"SALARY" NUMBER(9,2),
"HOURLY_RATE" NUMBER(7,2),
PRIMARY KEY ("BADGE_NO")
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 "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
/

GET_DEPENDENT_DDL

The general syntax of GET_DEPENDENT_DDL is
GET_DEPENDENT_DDL(object_type, base_object_name, base_object_schema,
version, model, transform, object_count)

Version, model and transform take the default values "COMPATIBLE", "ORACLE" and "DDL", and are not discussed futher. object_count takes the default of 10000 and can be left like that for most cases.

object_type can be any object of type D in Table 8. base_object_name is the base object on which the object_type objects are dependent.

The GET_DEPENDENT_DDL function allows the fetching of metadata for dependent objects with a single call. For some object types, other functions can be used for the same effect. For example, GET_DDL can be used to fetch an index by its name or GET_DEPENDENT_DDL can be used to fetch the same index by specifying the table on which it is defined. An added reason for using GET_DEPENDENT_DDL in this case might be that it gives the DDL of all dependent objects of that base object and the specific object type.

Table 2 shows a simple usage of GET_DEPENDENT_DDL.

Table 2 (GET_DEPENDENT_DDL example)

SQL> column aa format a132
SQL>
SQL> select DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','EMPLOYEE') aa from dual;
 
  CREATE OR REPLACE TRIGGER "REVRUN"."HOURLY_TRIGGER"
 before update of hourly_rate on
employee


for each row
begin :new.hourly_rate:=:old.hourly_rate;end;
ALTER TRIGGER "REVRUN"."HOURLY_TRIGGER" ENABLE
 
  CREATE OR REPLACE TRIGGER "REVRUN"."SALARY_TRIGGER"
 before insert or update of salary on
employee
for each row  WHEN (new.salary > 150000)  CALL check_sal(:new.salary)
ALTER TRIGGER "REVRUN"."SALARY_TRIGGER" ENABLE

GET_GRANTED_DDL

The general syntax of GET_GRANTED_DDL is
GET_GRANTED_DDL(object_type, grantee, version, model, transform, object_count)

Version, model and transform take the default values "COMPATIBLE", "ORACLE" and "DDL", and need no further discussion.
object_count takes the default of 10000, and can be left like that for most cases.

grantee is the user who is granting the object_types. The object types that can work in GET_GRANTED_DDL are the ones with type G in Table 8. Table 3 shows a simple usage of the GET_GRANTED_DDL function.

Table 3 (GET_GRANTED_DDL Usage)

SQL> set long 99999
SQL> column aa format a132
SQL> select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','REVRUN_USER') aa from dual;
 
  GRANT UPDATE ("SALARY") ON "REVRUN"."EMPLOYEE" TO "REVRUN_USER"
 
  GRANT UPDATE ("HOURLY_RATE") ON "REVRUN"."EMPLOYEE" TO "REVRUN_USER"
 
  GRANT INSERT ON "REVRUN"."TIMESHEET" TO "REVRUN_USER"
 
  GRANT UPDATE ON "REVRUN"."TIMESHEET" TO "REVRUN_USER"

Table 4 below classifies some common objects as Dependent Object (D), Named Object (N) or Granted Object (G). Some objects exhibit more than one such property. For a complete list, refer to the Oracle Documentation. However, the list below will meet most requirements.

Metadata information retrieval by programmatic interface

The programmatic interface is for fine-grained detailed control on DDL generation. The list of procedures available for use in the programmatic interface is as follows:

• OPEN
• SET_FILTER
• SET_COUNT
• GET_QUERY
• SET_PARSE_ITEM
• ADD_TRANSFORM
• SET_TRANSFORM_PARAM
• FETCH_xxx
• CLOSE

To make use of this interface one must write a PLSQL block. Considering the fact that several CLOB columns are involved, this is not simple. However, the next section shows how to use the SET_TRANSFORM_PARM function in SQLPLUS in order to perform most of the jobs done by this interface. If one adds simple SQL skills to it, the programmatic interface can be bypassed in almost all cases. To get details of the programmatic interface, the reader should refer to the documentation.

Using the SET_TRANSFORM_PARAM function in SQL Session

This function determines how the output of the DBMS_METADATA is displayed. The general syntax is
SET_TRANSFORM_PARAM(transform_handle, name, value).

transform_handle for SQL Sessions is DBMS_METADATA.SESSION_TRANSFORM
name is the name of the transform, and value is essentially TRUE or FALSE.

Table 4 shows how to get the DDL of tables not containing the word LOG in a good indented form and with SQL Terminator without a storage clause.

Table 4 (SET_TRANSFORM_PARAM Usage)

SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
 
PL/SQL procedure successfully completed.
 
SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
 
PL/SQL procedure successfully completed.
 
SQL> execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_metadata.get_ddl('TABLE',table_name) from user_tables
  2  where table_name not like '%LOG';
 
  CREATE TABLE "REVRUN"."EMPLOYEE"
   (    "LASTNAME" VARCHAR2(60) NOT NULL ENABLE,
        "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
        "MI" VARCHAR2(2),
        "SUFFIX" VARCHAR2(10),
        "DOB" DATE NOT NULL ENABLE,
        "BADGE_NO" NUMBER(6,0),
        "EXEMPT" VARCHAR2(1) NOT NULL ENABLE,
        "SALARY" NUMBER(9,2),
        "HOURLY_RATE" NUMBER(7,2),
         PRIMARY KEY ("BADGE_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM" ;
 
 CREATE TABLE "REVRUN"."TIMESHEET"
   (    "BADGE_NO" NUMBER(6,0),
        "WEEK" NUMBER(2,0),
        "JOB_ID" NUMBER(5,0),
        "HOURS_WORKED" NUMBER(4,2),
         FOREIGN KEY ("BADGE_NO")
          REFERENCES "REVRUN"."EMPLOYEE" ("BADGE_NO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "SYSTEM" ;
 
SQL>

Thus we see how a DDL requirement even with some filtering condition and a formatting requirement was met by the SQL browsing interface along with SET_SESSION_TRANSFORM.

Table 5 shows the name and meaning of the SET_SESSION_TRANSFORM parameters.

Table 5 (SET_SESSION_TRANSFORM "name" Parameters)

PRETTY (all objects) - If TRUE, format the output with indentation and line feeds.
Defaults to TRUE.

SQLTERMINATOR (all objects) - If TRUE, append a SQL terminator (; or /) to each 
DDL statement. Defaults to FALSE.

DEFAULT (all objects) - Calling SET_TRANSFORM_PARAM with this parameter set to 
TRUE has the effect of resetting all parameters for the transform to their default 
values. Setting this FALSE has no effect. There is no default.

INHERIT (all objects) - If TRUE, inherits session-level parameters. Defaults to 
FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by 
default the only transform parameters that apply are those explicitly set for that
transform handle. This has no effect if the transform handle is the session
transform handle.

SEGMENT_ATTRIBUTES (TABLE and INDEX) - If TRUE, emit segment attributes (physical
attributes, storage attributes, tablespace, logging). Defaults to TRUE.

STORAGE (TABLE and INDEX) - If TRUE, emit storage clause. (Ignored if 
SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLESPACE (TABLE and INDEX) - If TRUE, emit tablespace. (Ignored if 
SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

CONSTRAINTS (TABLE) - If TRUE, emit all non-referential table constraints.
Defaults to TRUE.

REF_CONSTRAINTS (TABLE) - If TRUE, emit all referential constraints (foreign key 
and scoped refs). Defaults to TRUE.

CONSTRAINTS_AS_ALTER (TABLE) - If TRUE, emit table constraints as separate ALTER
TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table 
constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that
CONSTRAINTS be TRUE.

FORCE (VIEW) - If TRUE, use the FORCE keyword in the CREATE VIEW statement. 
Defaults to TRUE.

DBMS_METADATA Security Model

The object views of the Oracle metadata model implement security as follows:

• Non-privileged users can see the metadata only of their own objects.
• SYS and users with SELECT_CATALOG_ROLE can see all objects.
• Non-privileged users can also retrieve object and system privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.
• If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.
• If non-privileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

Finding objects that are dependent on a given object

This is another type of requirement. While dropping a seemingly unimportant table or procedure from a schema one might like to know the objects that are dependent on this object.

The data dictionary view DBA_DEPENDENCIES or USER_DEPENDENCIES or ALL_DEPENDENCIES is the answer to these requirements. The columns of the ALL_DEPENDENCIES view are discussed in Table 6. ALL_DEPENDENCIES describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links. Only tables are left out of this view. However for finding table dependencies we can use ALL_CONSTRAINTS. The ALL_DEPENDENCIES view comes to the rescue in the very important area of finding dependencies between stored code objects.

Table 6 (Columns of ALL_DEPENDENCIES table)

Column               Description
------               -----------
OWNER                Owner of the object
NAME                 Name of the object
TYPE                 Type of object
REFERENCED_OWNER     Owner of the parent object
REFERENCED_NAME      Type of parent object
REFERENCED_TYPE      Type of referenced object
REFERENCED_LINK_NAME Name of the link to the parent object (if remote)
SCHEMAID             ID of the current schema
DEPENDENCY_TYPE      Whether the dependency is a REF dependency (REF) or not (HARD)

Table 7 below shows how to use the above view to get the dependencies. The example shows a case where we might want to drop the procedure CHECK_SAL, but we would like to find any objects dependent on it. The query below shows that a TRIGGER named SALARY_TRIGGER is dependent on it.

Table 7 (Use of the ALL_DEPENDENCIES view)

SQL> select name, type, owner
  2  from all_dependencies
  3  where referenced_owner = 'REVRUN'
  4  and referenced_name = 'CHECK_SAL';

 

NAME                           TYPE              OWNER
------------------------------ ----------------- ----------------------
SALARY_TRIGGER                 TRIGGER           REVRUN

CONCLUSION

This article is intended to give the minimum effort answer to elementary and intermediate level object dependency related issues. For advanced object dependency issues, this article points to the solution. As Oracle keeps on upgrading its versions, it is clear that they will be upgrading the DBMS_METADATA interface and ALL_DEPENDENCIES view along with it. The solutions developed along those lines will persist.

Table 8 (Classifying common database objects as Named, Dependent, Granted and Schema objects)

CONSTRAINT (Constraints)                       SND
DB_LINK (Database links)                       SN
DEFAULT_ROLE (Default roles)                   G
FUNCTION (Stored functions)                    SN
INDEX (Indexes)                                SND
MATERIALIZED_VIEW (Materialized views)         SN
MATERIALIZED_VIEW_LOG (Materialized view logs) D
OBJECT_GRANT (Object grants)                   DG
PACKAGE (Stored packages)                      SN
PACKAGE_SPEC (Package specifications)          SN
PACKAGE_BODY (Package bodies)                  SN
PROCEDURE (Stored procedures)                  SN
ROLE (Roles)                                   N
ROLE_GRANT (Role grants)                       G
SEQUENCE (Sequences)                           SN
SYNONYM (Synonyms)                             S
SYSTEM_GRANT (System privilege grants)         G
TABLE (Tables)                                 SN
TABLESPACE (Tablespaces)                       N
TRIGGER (Triggers)                             SND
TYPE (User-defined types)                      SN
TYPE_SPEC (Type specifications)                SN
TYPE_BODY (Type bodies)                        SN
USER (Users)                                   N
VIEW (Views)                                   SN

Table 9 (Creation script of the REVRUN Schema)

connect system/manager
drop user revrun cascade;
drop user revrun_user cascade;
drop user revrun_admin cascade;
 
create user revrun identified by revrun;
GRANT resource, connect, create session
     , create table
     , create procedure 
     , create sequence
     , create trigger
     , create view
     , create synonym
     , alter session
TO revrun;
 
create user revrun_user identified by user1;
create user revrun_admin identified by admin1;
 
grant connect to revrun_user;
grant connect to revrun_admin;
 

connect revrun/revrun
 
Rem Creating employee tables...
 
create table employee
( lastname varchar2(60) not null,
  firstname varchar2(20) not null,
 mi varchar2(2),
 suffix varchar2(10),
 DOB date not null,
 badge_no number(6) primary key,
 exempt varchar(1) not null,
 salary number (9,2),
 hourly_rate number (7,2)
)
/

 
create table timesheet
(badge_no number(6) references employee (badge_no),
 week number(2),
 job_id number(5),
 hours_worked number(4,2)
)
/
 
create table system_log
(action_time DATE,
 lastname VARCHAR2(60),
 action LONG
)
/
 
Rem grants...

grant update (salary,hourly_rate) on employee to revrun_user;
grant ALL on employee to revrun_admin with grant option;
 
grant insert,update on timesheet to revrun_user;
grant ALL on timesheet to revrun_admin with grant option;
 
Rem indexes...

create index i_employee_name on employee(lastname);
create index i_employee_dob on employee(DOB);
 
create index i_timesheet_badge on timesheet(badge_no);
 
Rem triggers
 
create or replace procedure check_sal( salary in number) as
begin
  return;  -- Demo code
end;
/
 
create or replace trigger salary_trigger before insert or update of salary on 
employee
for each row when (new.salary > 150000)
call check_sal(:new.salary)
/
 
create or replace trigger hourly_trigger before update of hourly_rate on 
employee
for each row
begin :new.hourly_rate:=:old.hourly_rate;end;
/