Skip to Main Content
  • Questions

  • dbms_metadata.get_ddl - is there a way to get 'dependent triggers' for tables?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vinay.

Asked: June 26, 2009 - 8:23 am UTC

Last updated: June 04, 2012 - 9:12 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I need to write code to retrieve the DDL of our Production database. (Don't yell at me, I didn't design this application, I inherited it :-)

I need to implement a version control mechanism now, and I need the DDL to create a baseline (version 1.0) in our version control system. I am thinking of using dbms_metadata to get the code out. I have three questions:

1) What is your opinion on dbms_metadata to retrieve the DDL? Is there a better way I can do this?

2) "select dbms_metadata.get_ddl('TABLE','ITEMMASTER') from dual;" will give me the table creation script. Is it possible to get the script for the indexes/triggers associated with this table in a single command? I mean, I can get the index scripts and the table scripts separately, but I would like the indexes and triggers to be created in the same file as the table creation script. Is there any technique I can use to achieve this?

3) Certain object types like Materialized views are not covered by dbms_metadata - I mean, it gives me an "ORA-31600: Invalid input value...". Of course I can get these through other dictionary views such as user_snapshots, but is there any way I can get dbms_metadata to give me this information?

Thanks,
Vinay

and Tom said...

1) that would be the correct approach

2) you definitely have the ability to either a) run more than one query into the same output file or b) run a single query that is a bunch of union ALL statements.


3) you can get materialized views.

ops$tkyte%ORA10GR2> exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create materialized view mv
  2  as
  3  select * from t;

Materialized view created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'MATERIALIZED_VIEW', 'MV' ) from dual
  2  /

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV')
-------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "OPS$TKYTE"."MV"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LO
GGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  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 "USERS"
  REFRESH FORCE ON DEMAND
  WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS SELECT "T"."X" "X","T"."Y" "Y" FROM "T" "T";



see the table

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA

for valid types.

Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Which table!?

Ravi, June 26, 2009 - 1:08 pm UTC

Iin the link, I didn't see any table.
Tom Kyte
June 26, 2009 - 2:28 pm UTC

sorry, see right below, messed up the link

A reader, June 26, 2009 - 2:07 pm UTC

The correct link is:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm

Scroll down to Table 57-11.

Incorrect link

A reader, June 26, 2009 - 2:09 pm UTC

I am assuming that Mr. Kyte was trying to point you to this URL:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA

and in case I goof it up it is Table 57-11 of Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2) chapter 57 DBMS_METADATA. The number sign in the URL is getting messed up in the forum formatting.

Thanks

Vinay, June 27, 2009 - 4:42 am UTC

Hi Tom,

Well, I was hoping that dbms_metadata would have some technique to get all associated triggers and indexes associated with a table and append the DDL to the end of the table creation statement. But of course, that's really not an issue, since I can 'union' different statements.

Thanks for the MV thing - I was trying with a parameter of 'MATERIALIZED VIEW' - was missing the underscore..

Thanks,
Vinay

What about duplicates?

Lucian Lazar, November 18, 2010 - 6:30 am UTC

One problem with DBMS_METADATA is that if I call it three times with parameters TABLE, CONSTRAINT and INDEX in order to retrieve the full table structure and constraints, I get duplicated objects and the ddl crashes. For instance I get the PRIMARY KEY 3 times (once for TABLE, once for CONSTRAINT and once for INDEX).

Is there a way to get a DDL script without duplicates?
Tom Kyte
November 19, 2010 - 7:23 am UTC

get the table ddl without constraints then if you are going to get the constraints later.

ops$tkyte%ORA11GR2> create table t (x int primary key);

Table created.

ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0),
         PRIMARY KEY ("X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"



ops$tkyte%ORA11GR2> execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

Thank you

Lucian Lazar, November 23, 2010 - 9:18 am UTC

Thank you, Tom, your solution works just fine for me.

I tried at first to remove the PK's from the user_constraints but I guess it's better to do it your way and remove the PK from the CREATE TABLE script, isn't it?



select dbms_metadata.get_ddl('TABLE', p_table) as ddl_create
from dual

select dbms_metadata.get_ddl('CONSTRAINT', CONSTRAINT_NAME) as ddl_constraints
from user_constraints
where TABLE_NAME = p_table
and CONSTRAINT_TYPE != 'P'
and get_search_condition(constraint_name) not like
'%NOT NULL%'

select dbms_metadata.get_ddl('INDEX', INDEX_NAME) as ddl_indexes
from user_INDEXES
where TABLE_NAME = p_table
and INDEX_NAME not in
(select CONSTRAINT_NAME
from user_constraints
where TABLE_NAME = p_table
and CONSTRAINT_TYPE = 'P')
Tom Kyte
November 23, 2010 - 1:17 pm UTC

If you want to

a) create tables

and then

b) add constraints


then yes, removing the constraints from the create tables would be the way to go. Else, you don't need (b) at all, just create the table.



Comments

chb, March 28, 2012 - 11:41 am UTC

I'm facing the same challenge to create scripts for a version control procedure.
With your help and reading the documentation a little bit I created a nice script to get all the table structures but I've just realized that I'm missing the comments attached to the tables and I can't find how to do it.

I mean, I would like to add to each table script something like:
COMMENT ON COLUMN "TAB1"."CLIENT_ID" IS 'Bla bla bla';

Sorry if I missed something.

Answering myself

chb, March 28, 2012 - 11:59 am UTC

dbms_metadata.get_dependent_ddl('COMMENT','TAB1',....)
solves the problem.

Thank you anyways...

across all schemas

Hans, April 04, 2012 - 6:13 am UTC

Hi Tom,
DBMS_METADATA.GET_DDL will not retrieve scripts for objects across all schema.
tks, Hans
Tom Kyte
April 04, 2012 - 12:15 pm UTC

false, it can and will and does.

here is ops$tkyte getting DDL for SCOTT

ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
-------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EXAMPLE"



you do have to have the appropriate privileges of course, but it in fact does work

dbms_metadata's uses

sadek, April 05, 2012 - 6:38 am UTC 

could you comment this please, Mr Thomas.

I am running a developement 11gr2 database.
If you have access to a privileged user (DBA) you can use "DATA_PUMP" utility.
If not ... well this is how did it of course one needs some specific privileged.

-- in a lowlly privileged user session

SQL> select * from tab;

no rows selected

SQL> create table emp(empno int constraint emp_pk primary key);

Table created.

SQL> -- for demonstration purposes only
   create trigger emptrg
   after update on emp
   begin
   null;
   end;
   /

Trigger created.

SQL> -- Just to examplify.
create index emp_fbidx on emp(empno * 10);

Index created.

In another session (here I used "SYS", it's just a developement database..)

SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);

SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

SQL> select dbms_metadata.get_ddl(object_type, object_name, 'SADEK') from dba_objects where object_type in ('TABLE','INDEX','TRIGGER') and owner ='SADEK';

For "constraints" and "comments" you could use the "dba/all_constraints" and the "dba/all_comments"  views similarly to the way "dba_objects" is used.  
an example:
select 'comment on '||table_name||' is '||comments||';' from all_tab_comments where owner = 'SADEK';
 then combine the resulting script(after spool) with the next to previous query to get the final result.

Tom Kyte
April 06, 2012 - 10:18 am UTC

what would you like me to comment on exactly?

Is this a good aproach

bilal, April 06, 2012 - 4:41 pm UTC

I was not clear (not enough...) sorry. What I meant is: which aproach is better; DBMS_METADATA or DATA_PUMP. Say... raguarding security for example. There might be side effects of using DATA_PUMP.
NB. I missed dbms_metadata.get_dependent_ddl('COMMENT','TAB1',....). We always learn.
Thank you.
Tom Kyte
April 08, 2012 - 5:41 pm UTC

what is it you want to do?

I do not see your name here anywhere else so I have no clue what you are trying to accomplish.

if you want metadata - dbms_metada

if you want the data - data pump

Thank you

bilal, April 10, 2012 - 3:49 am UTC

In fact I am not in on any thing related to this thread. I wanted to know out of curiousity. Thanks a lot.
Tom Kyte
April 10, 2012 - 7:43 am UTC

then I'll just have to go with:

what is it you want to do?


we still don't know

A question on dbms_metadata, select_catalog_role and TOAD by Quest.

Mridul Mathew, April 13, 2012 - 11:43 am UTC

Hi Tom,

I have a read only schema. It has the following privileges -
1. CREATE SESSION
2. SELECT ANY TABLE
3. SELECT ANY DICTIONARY

If I use it to generate DDL using dbms_metadata, I get this -

select dbms_metadata.get_ddl('TABLE','SCHEMA_CONFIG') DDL from dba_tables where table_name='SCHEMA_CONFIG' ;

ORA-31603: object "SCHEMA_CONFIG" of type TABLE not found in schema "VPN2RO"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

I presume this is because I need the select_catalog_role.

Question 1.

Is that presumption right?

Question 2.

I am able to generate any other schema's object DDL through TOAD from Quest Software using this read only schema.

Do you know what TOAD or any such equivalent tool might be querying internally to get to the DDL?

Question 3.

Is there any other way to get to the object metadata DDL through just sqlplus, given I have only the three privileges listed above?

Thanks much in advance.

Regards,
Mridul.

Tom Kyte
April 13, 2012 - 1:27 pm UTC

2. SELECT ANY TABLE

really??? wow.




1)) you are seeing that error message because you are not passing in the owner to dbms_metadata. before you get excited - when you do pass in the schema, you'll get:

ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> grant create session, select any table, select any dictionary to a;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> 
a%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'EMP' ) from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "A"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1



no rows selected

a%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1



no rows selected


as documented here:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#i1016867

you would need select_catalog_role:

a%ORA11GR2> 
a%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> grant select_catalog_role to a;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
-------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EXAMPLE"




2) Do you know what TOAD or any such equivalent tool might be querying internally
to get to the DDL?

trace it, they are probably just querying the dictionary and doing it themselves.

3) see the dbms_metadata documentation, it very clearly listed what privileges you need at a minimum.


A question on dbms_metadata, select_catalog_role and TOAD by Quest.

Mridul Mathew, April 13, 2012 - 11:46 am UTC

Tom,

I apologize, I missed mentioning that my database version is 11.2.0.3 EE.

Thanks again,
Mridul.

A question on dbms_metadata, select_catalog_role and TOAD by Quest.

Mridul Mathew, April 16, 2012 - 1:09 pm UTC

Thanks much Tom!

Regards,
Mridul.

Is it possible to assign select dbms_metadata.get_ddl to local variableual to a local variable

sunny, June 04, 2012 - 4:08 am UTC

Hi Tom,

I have small doubt on the following statement.
I have the statement like below.

Lv_ref varchar2(1000);

Lv_ref := 'select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV' ) from dual';
dbms_output.put_line(Lv_ref);


How to assign the select statement to a local variable.

Thanks,
Sunny


Tom Kyte
June 04, 2012 - 9:12 am UTC

just call the function???? why is sql even involved here???

declare
   x varchar2(1000);
begin
   x := dbms_metadata.get_ddl(.....);
end;
/

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library