Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 511
Eric Wilson Avatar asked Dec 28 '22 17:12

Eric Wilson


2 Answers

Here's what worked for me:

SELECT dbms_metadata.get_ddl('TABLE', table_name)
  FROM user_tables;
like image 67
Eric Wilson Avatar answered Feb 19 '23 12:02

Eric Wilson


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>>
like image 39
Justin Cave Avatar answered Feb 19 '23 12:02

Justin Cave