Oracle Scratchpad

December 31, 2009


Filed under: Infrastructure — Jonathan Lewis @ 6:36 pm BST Dec 31,2009

The dbms_metadata package has a few convenient functions and procedures that allow you to generate the SQL to recreate parts of your database. The best-known function is probably dbms_metadata.get_ddl(), but every example of its use that I’ve seen on the Internet seems to use a “select from dual”, e.g.

select dbms_metadata.get_ddl('TABLE','T1','TEST_USER') from dual;

As a consequence of the available examples, I think it’s been commonly assumed that this is the only way to use the get_ddl function – but it’s just a pl/sql function returning a CLOB, and you can use it on any data set. For example, if you want to generate the DDL for all the tablespaces on which a particular user (“test_user”, in my example) has a quota, you could run the following:

set long 20000
set pagesize 0
set linesize 180
set trimspool on
break on row skip 1

	username = 'TEST_USER'


  1. Thank you for Good example to use dbms_metadata with dictionary views.

    by the way, Happy New Year 2010 – Wish You and Families: Healthy & Happy & Prosperous.

    Comment by Surachart Opun — December 31, 2009 @ 7:54 pm BST Dec 31,2009 | Reply

  2. Smart use !


    Comment by Amardeep Sidhu — January 7, 2010 @ 5:46 am BST Jan 7,2010 | Reply

  3. […] 13-How to use dbms_metadata on objects other than dual? Jonathan Lewis-DBMS_METADATA […]

    Pingback by Blogroll Report 25/12/2009 – 01/01/2010 « Coskan’s Approach to Oracle — January 20, 2010 @ 3:46 pm BST Jan 20,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by