About Oracle dbms_metadata

Export entire schema definition (by username)

select dbms_metadata.get_granted_ddl('role_grant','') from dual;
 
select dbms_metadata.get_granted_ddl('object_grant','') from dual;

Using dbms_metadata for tablespaces;

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

Once a table or index definition has been accepted into the Oracle data dictionary, it can be difficult to reconstruct the DDL syntax from the dictionary without the help of specialized packages. Oracle provides the dbms_metadata package to extract table and index DDL, and we will explore how you can use dbms_metadata to extract the DDL for any table or index. This capability is very useful when you want to migrate a table definition onto a new Oracle database.

Traditionally, the extraction of DDL is called "punching" the DDL. The term punching dates back to the days when the DDL would be punched onto Hollerith cards.

The dbms_metadata package has 11 procedures, each tailored to a specific function. IN this theme we will focus on the use of the get_ddl procedure.

1. GET_XML

2. GET_DDL

3. OPEN,

4. SET_FILTER

5. SET_COUNT

6. GET_QUERY

7. SET_PARSE_ITEM

8. ADD_TRANSFORM

9. SET_TRANSFORM_PARAM

10. FETCH_XXX

11. CLOSE

All eleven of these procedures allow for flexible selection criteria and extraction of groups of objects.

To see how dbms_metadata works, get start by extracting the DLL for the book table in the sample database.

Note: to get the full benefit from this theme, it is important that you work-along with us during the code examples.

Note that get_ddl procedure accepts three arguments:

1 - The object type (table, index, view)

2 - The object name

3 - The schema owner

If you are connected as pubs/pubs, you do not need to supply the third argument because get_ddl will always look in your default schema first.

Here is the output from the get_ddl invocation:

Here we see that the entire DDL for the table has been extracted from the data dictionary, and you can now FTP this definition to any database and accurately recreate the BOOK table.

Now that we have the general idea behind get_ddl, let's look at how we can adjust the parameters for get_ddl to change the output. From the above statement, we can see that it also returns storage clauses. To specify that storage clauses are not to be returned in the SQL DDL, use the following invocation. Here we call the set_transform_param procedure which controls the default parameters for your session. Inside the set_transform_param procedure we invoke the session_transform procedure, setting the STORAGE parameter to FALSE.

Here we see that the storage clause has been omitted, and the resulting DDL will use the default storage clause values for objects in the USERS tablespace on the target database.

Note that you can easily re-set the parameters for get_ddl to their defaults by running the following command:

Now we should see how get_ddl is easy for extracting DDL from a single table.

Extracting a whole schema

But what if we have to move an entire schema? Fortunately, Oracle allows get_ddl to be invoked against the USER_TABLES view. The get_ddl function allows for SQL filters to be added to the command to restrict output. In the command below, we restrict access to only those tables inside the USER_TABLES view.

CREATE TABLE "PUBS"."BOOK_AUTHOR"
( "AUTHOR_KEY" VARCHAR2(11),
"BOOK_KEY"
VARCHAR2(6),
"ROYALTY"
NUMBER(2,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE
0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

To provide even more flexibility, we can add an SQL WHERE clause to restrict output to a specific group of tables. In this example, we only extract the DDL for tables in the PUBS schema that begin with BOOK. Try this command on your Oracle database:

spool pubs_schema.lst

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM user_tables u
WHERE table_name like 'BOOK%';

And here we see that our command only punched the tables that we require.

Issues with dbms_metadata

DBMS_METADATA

One of the most sacred job functions many DBAs perform is that of the "guardian of the database design", most often manifested as simply meaning the "keeper of the DDL". Even though there are many database change-management tools and data-modeling tools with model-to-database alteration/synchronization capabilities, numerous DBAs still generally rely on DDL scripts. Some DBAs might have those DDL scripts in a source-code version-control system, but even that is not the norm. So these "keepers of the DDL" have always had a staggering task in being able to produce current scripts for whatever objects the database might contain.

This challenge is only magnified by the sheer number of people, and in some cases, even applications tasked with creating database objects. In many cases, this database design sprawl is so bad that the database itself has become the central repository of the design through sheer necessity as the Oracle data dictionary may be the only guaranteed, accurate and up-to-date copy of the database design. Obviously, that is not a good place to be in, but many find themselves there nonetheless. So reverse engineering DDL from the data dictionary is quite often a necessary evil. Some people refer to reverse engineering as database, schema or object definition or DDL extraction.

In the early days when the Oracle database was much less robust and therefore much simpler, DBAs often wrote SQL scripts to generate the database objects' DDL code. These scripts simply queried that data dictionary and produced human readable SQL files of what the database design reality was at that particular time. The old_generate_table_ddl.sql SQL*Plus script, shown below, is a simple example of what such a script might have looked like.

When the old_generate_table_ddl.sql SQL*Plus script is run against the MOVIES demo schema, here is a sample of what the generated DDL looks like for one of the tables.

This is not too bad. But with the plethora of table structural design options such as clustering, partitioning, index organized tables, external tables and such, it is clear that this little script would need thousands of lines of code plus more of the same for indexes and views. Also, keep in mind all the fun database objects such as materialized views, materialized view logs, queue tables, and sequences, to name a few, not to mention the entire security model for the whole enchilada such as roles and grants. It is pretty clear that DDL generation scripts such as these have met their match.

So what is a body to do? Thankfully, Oracle came to the rescue with a package to implement database object reverse engineering, namely dbms_metadata. Not only that, but both SQL Developer and OEM make use of it. Therefore, for those who just need a quick and easy way to peruse their databases DDL, Oracle's free SQL Developer tool is probably good enough, as shown in the following screen snapshot. But for those who want to delve even further into the mysteries of everything the dbms_metadata package can do, read on.

The good news is that Oracle now provides a very robust mechanism for extracting or reverse engineering the database objects' DDL. But like anything else, there is also some bad news and here are the couple items worth note:

• Does not generate the DDL in required object dependency order for referential integrity constraints

• Is not very well documented (the Oracle Utilities manual describes the syntax and some basic cases - but not nearly enough)

• Slightly over-engineered - such references can be found on various blogs and presentations posted to the web

• IS NOT SUPPORTED FOR v7-8-9iR1 databases

• Seems to be buggy from what can be told on OTN

Please do not let the API's complexity and lack of robust documentation be a swaying factor - dbms_metadata is a must-have and use utility. An entire chapter could be written on just this one topic, but instead of trying to show the complete syntax reference and detailing all aspects, three very useful use case scenarios will be presented. These three examples will be used as is or serve as a good foundation upon which to add one's own modifications. And while there are just three examples, it should be noted that there are really just two ways to work with dbms_metadata: going for one object type at a time, or going for collections of object types simultaneously.

Get the Complete Oracle Utility Information  The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.