Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle dbms_metadata Tips


Oracle Tips by Burleson Consulting

 

About Oracle dbms_metadata


You can use the new utility package called dbms_metadata that will easily display DDL and stored procedures directly from the data dictionary. Using this powerful utility, you can punch individual objects or an entire Oracle schema. Best of all, it is easy to use. You simply execute dbms_metadata.get_ddl, specify the object names, and Oracle will extract ready-to-use DDL.

To punch off all table and indexes for the EMP table, execute dbms_metadata. get_ddl, select from DUAL, and provide all required parameters as shown in Listing A.  Please see the Oracle script collection for a complete set of Oracle security scripts:

Listing A:

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;


Export entire schema definition (by username)


For large migrations, you can modify the dbms_metadata syntax to punch a whole schema. As you can see in Listing C, it is easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. This syntax will punch all table and index definitions for the entire schema, in this example, the PUBS schema.  Note: you can also use the expdp utility to export a schema definition.

Listing C:

set pagesize 0
set long 90000
set feedback off
set echo off
set heading 999
set lines 100

select
   dbms_metadata.GET_DDL(u.object_type,u.object_name,'PUBS')
from
   dba_objects u
where
   owner = 'PUBS';


Save the grants for a schema:

To use this script, just enter the schema owner (username) into the following code to display the grant statements for that schema owner:

connect username/password;


select dbms_metadata.get_granted_ddl('system_grant','') from dual;

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;


You can use dbms_metadata to display the definitions for a single tablespae:

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

And here we can get the DDL for all tablespaces:

set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces


Using dbms_metadata for roles

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;
SELECT dbms_metadata.get_ddl('ROLE','RESOURCE') from dual;

Using the dbms_metadata Package

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.

set pagesize 0
set long 90000

SELECT
DBMS_METADATA.GET_DDL( 'TABLE','BOOK','PUBS')
FROM DUAL;

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:

CREATE TABLE "PUBS"."BOOK"
( "BOOK_KEY" VARCHAR2(6),
"PUB_KEY"
VARCHAR2(4),
"BOOK_TITLE" VARCHAR2(80),

"BOOK_TYPE"
VARCHAR2(30),
"BOOK_RETAIL_PRICE"
VARCHAR2(30),
"BOOK_ADVANCES" VARCHAR2(30),

"BOOK_ROYALTIES"
NUMBER(10,0),
"BOOK_YTD_SALES"
NUMBER(10,0),
"BOOK_COMMENTS" VARCHAR2(200),

"BOOK_DATE_PUBLISHED"
DATE
) 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";

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.

set pagesize 0
set long 90000

exec
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT
DBMS_METADATA.GET_DDL( 'TABLE','BOOK','PUBS')
FROM DUAL;

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.

PL/SQL procedure successfully completed.

CREATE TABLE "PUBS"."BOOK"
( "BOOK_KEY" VARCHAR2(6),
"PUB_KEY"
VARCHAR2(4),
"BOOK_TITLE"
VARCHAR2(80),
"BOOK_TYPE" VARCHAR2(30),

"BOOK_RETAIL_PRICE"
VARCHAR2(30),
"BOOK_ADVANCES"
VARCHAR2(30),
"BOOK_ROYALTIES"
NUMBER(10,0),
"BOOK_YTD_SALES"
NUMBER(10,0),
"BOOK_COMMENTS"
VARCHAR2(200),
"BOOK_DATE_PUBLISHED"
DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

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

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

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.

set pagesize 0
set long 90000

connect pubs/pubs

spool pubs_schema.lst

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

spool off

host notepad pubs_schema.lst

Here we get the DDL for all the tables in the sample database:

CREATE TABLE "PUBS"."AUTHOR"
( "AUTHOR_KEY" VARCHAR2(11),
"AUTHOR_LAST_NAME"
VARCHAR2(40),
"AUTHOR_FIRST_NAME"
VARCHAR2(20),
"AUTHOR_PHONE"
VARCHAR2(12),
"AUTHOR_STREET"
VARCHAR2(40),
"AUTHOR_CITY" VARCHAR2(20),
"AUTHOR_STATE"
VARCHAR2(2),
"AUTHOR_ZIP"
VARCHAR2(5),
"AUTHOR_CONTRACT_NBR" NUMBER(5,0)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0

FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_16K"

CREATE TABLE "PUBS"."BOOK"
( "BOOK_KEY" VARCHAR2(6),
"PUB_KEY"
VARCHAR2(4),
"BOOK_TITLE"
VARCHAR2(80),
"BOOK_TYPE" VARCHAR2(30),

"BOOK_RETAIL_PRICE" VARCHAR2(30),
"BOOK_ADVANCES" VARCHAR2(30),
"BOOK_ROYALTIES"
NUMBER(10,0),
"BOOK_YTD_SALES" NUMBER(10,0),
"BOOK_COMMENTS" VARCHAR2(200),|
"BOOK_DATE_PUBLISHED"
DATE
) 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"

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:

set pagesize 0
set long 90000

connect pubs/pubs

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.

CREATE TABLE "PUBS"."BOOK"
( "BOOK_KEY" VARCHAR2(6),
"PUB_KEY"
VARCHAR2(4),
"BOOK_TITLE"
VARCHAR2(80),
"BOOK_TYPE" VARCHAR2(30),

"BOOK_RETAIL_PRICE"
VARCHAR2(30),
"BOOK_ADVANCES"
VARCHAR2(30),
"BOOK_ROYALTIES" NUMBER(10,0),

"BOOK_YTD_SALES"
NUMBER(10,0),
"BOOK_COMMENTS"
VARCHAR2(200),
"BOOK_DATE_PUBLISHED" DATE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE
ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

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 PCTINCRE
ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"


Issues with dbms_metadata


Like all new Oracle utilities, there are some shortcomings with dbms_metadata. When you have tables with foreign key referential integrity (e.g., Oracle’s references constraints), it would be nice to have dbms_metadata punch (display) the table DDL in its proper order for reloading into another database. If you don't punch the tables in the proper order of foreign key referential integrity, a table may be added that references another table that has not yet been added. Making this change would involve adding a WHERE clause that queries dba_constraints and dba_cons_columns to determine the proper table order.

Despite the immediate shortcomings, the dbms_metadata utility is fantastic for extracting DDL and procedural code from Oracle. I have no doubt a future release of Oracle will make dbms_metadata even more powerful.

Related dbms_metadata Articles:

Using the dbms_metadata package
DBMS_METADATA package tips
Get Oracle schema DDL syntax with dbms_metadata
Get table and index DDL the easy way



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.

 

   old_generate_table_dll.sql script

 

 

set echo off

set heading off

set feedback off

set verify off

set pagesize 0

set linesize 132

 

define schema=&1

define CR=chr(10)

define TAB=chr(9)

col x noprint

col y noprint

select  table_name y,

        0 x,

        'CREATE TABLE ' ||

        rtrim(table_name) ||

        '('

from    dba_tables

where   owner = upper('&schema')

union

select  tc.table_name y,

        column_id x,

        decode(column_id,1,'    ','   ,')||

        rtrim(column_name)|| &TAB || &TAB ||

        rtrim(data_type) ||

        rtrim(decode(data_type,'DATE',null,'LONG',null,

               'NUMBER',decode(to_char(data_precision),null,null,'('),

               '(')) ||

        rtrim(decode(data_type,

               'DATE',null,

               'CHAR',data_length,

               'VARCHAR2',data_length,

               'NUMBER',decode(to_char(data_precision),null,null,

                 to_char(data_precision) || ',' || to_char(data_scale)),

               'LONG',null,

               '******ERROR')) ||

        rtrim(decode(data_type,'DATE',null,'LONG',null,

               'NUMBER',decode(to_char(data_precision),null,null,')'),

               ')')) || &TAB || &TAB ||

        rtrim(decode(nullable,'N','NOT NULL',null))

from    dba_tab_columns tc,

        dba_objects o

where   o.owner = tc.owner

and     o.object_name = tc.table_name

and     o.object_type = 'TABLE'

and     o.owner = upper('&schema')

union

select  table_name y,

        999999 x,

        ')'  || &CR

        ||'  STORAGE('                                   || &CR

        ||'  INITIAL '    || initial_extent              || &CR

        ||'  NEXT '       || next_extent                 || &CR

        ||'  MINEXTENTS ' || min_extents                 || &CR

        ||'  MAXEXTENTS ' || max_extents                 || &CR

        ||'  PCTINCREASE '|| pct_increase                || ')' ||&CR

        ||'  INITRANS '   || ini_trans                   || &CR

        ||'  MAXTRANS '   || max_trans                   || &CR

        ||'  PCTFREE '    || pct_free                    || &CR

        ||'  PCTUSED '    || pct_used                    || &CR

        ||'  PARALLEL (DEGREE ' || rtrim(DEGREE) || ') ' || &CR

        ||'  TABLESPACE ' || rtrim(tablespace_name)      ||&CR

        ||'/'||&CR||&CR

from    dba_tables

where   owner = upper('&schema')

order by 1,2;

 

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.

 

old_generate_table_ddl.sql output

 

 

CREATE TABLE CUSTOMER(

    CUSTOMERID          NUMBER(10,0)            NOT NULL

   ,FIRSTNAME           VARCHAR2(20)            NOT NULL

   ,LASTNAME            VARCHAR2(30)            NOT NULL

   ,PHONE               CHAR(10)                NOT NULL

   ,ADDRESS             VARCHAR2(40)            NOT NULL

   ,CITY                VARCHAR2(30)            NOT NULL

   ,STATE               CHAR(2)                    NOT NULL

   ,ZIP               CHAR(5)                  NOT NULL

   ,BIRTHDATE           DATE

   ,GENDER              CHAR(1)

)

  STORAGE(

  INITIAL 1048576

  NEXT 1048576

  MINEXTENTS 1

  MAXEXTENTS 2147483645

  PCTINCREASE 0)

  INITRANS 1

  MAXTRANS 255

  PCTFREE 10

  PCTUSED

  PARALLEL (DEGREE 1)

  TABLESPACE USERS

/

 

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.

 

Figure 6.4:  Oracle SQL Developer

 

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.
 

 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.