Oracle Scratchpad

December 31, 2009

dbms_metadata

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

select
	dbms_metadata.get_ddl('TABLESPACE',tablespace_name)
from
	dba_ts_quotas
where
	username = 'TEST_USER'
;

3 Comments »

  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 !

    Thanks…

    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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,103 other followers