CODEWITHSUNDEEP
oracle-databaseoracle11gddl
Eric Wilson
Eric Wilson

Reputation: 59365

Efficient way to obtain DDL from entire Oracle DB

Currently there are about 30 tables in the Oracle 11.1 database.

Is there a way to generate all ddl with a single command? (Or a few commands?)

Edit: Following a suggestion below, I tried:

SELECT dbms_metadata.get_ddl( 'TABLE', table_name, owner )
  FROM all_tables;

And got:

ORA-31603: object "HS_PARTITION_COL_NAME" of type TABLE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3241
ORA-06512: at "SYS.DBMS_METADATA", line 4812
ORA-06512: at line 1
31603. 00000 -  "object \"%s\" of type %s not found in schema \"%s\""
*Cause:    The specified object was not found in the database.
*Action:   Correct the object specification and try the call again.

It's clear that there is something extremely basic about dbms_metadata that I don't understand.

Upvotes: 4

Views: 8006

Answers (4)

Eric Wilson
Eric Wilson

Reputation: 59365

Here's what worked for me:

SELECT dbms_metadata.get_ddl('TABLE', table_name)
  FROM user_tables;

Upvotes: 5

Gurupreet Singh Bhatia
Gurupreet Singh Bhatia

Reputation: 728

If you want to individually generate ddl for each object,

Queries are:

--GENERATE DDL FOR ALL USER OBJECTS
--1. FOR ALL TABLES

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

--2. FOR ALL INDEXES

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

--3. FOR ALL VIEWS

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME) FROM USER_VIEWS;

OR

SELECT TEXT FROM USER_VIEWS

--4. FOR ALL MATERILIZED VIEWS

SELECT QUERY FROM USER_MVIEWS

--5. FOR ALL FUNCTION

SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'

GET_DDL Function doesnt support for some object_type like LOB,MATERIALIZED VIEW, TABLE PARTITION

SO, Consolidated query for generating DDL will be:

SELECT OBJECT_TYPE, OBJECT_NAME,DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) FROM ALL_OBJECTS WHERE (OWNER = 'XYZ') AND OBJECT_TYPE NOT IN('LOB','MATERIALIZED VIEW', 'TABLE PARTITION') ORDER BY OBJECT_TYPE, OBJECT_NAME;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231691

You can use the DBMS_METADATA package. Something like

SELECT dbms_metadata.get_ddl( 'TABLE', table_name, owner )
  FROM all_tables
 WHERE <<some condition to get the 30 tables in question>>

Upvotes: 3

GolezTrol
GolezTrol

Reputation: 116110

Yes you can pretty easily using the dbms_metadata package. You can write a routine that opens a cursor on the USER_TABLES system table and gets the ddl for each table. An example for that is in the article too.

Upvotes: 2

Related Questions