Home » SQL & PL/SQL » SQL & PL/SQL » DDL for whole Database (Oracle 10g)
DDL for whole Database [message #555844] Tue, 29 May 2012 05:58 Go to next message
chrissi
Messages: 10
Registered: May 2012
Junior Member
Hi,
I would like to create a script, that lets me rebuild a database while calling it.
For this, I call :
select dbms_metadata.get_ddl('TABLE','Table-name','Schema') from dual;


Is there a way to call this for the whole Database?

I tried it like that:
SET serveroutput ON format wraped; 
Declare
Ergebnis varchar2(9000);
BEGIN
  FOR Tabelle IN 
      (SELECT table_name FROM all_tables WHERE owner='schema') 
  LOOP
      dbms_output.put_line(Tabelle.table_name);
      select dbms_metadata.get_ddl('TABLE',Tabelle.table_name,'schema') into Ergebnis from dual;
      dbms_output.put_line(Ergebnis);
  END LOOP;
END;

but this won't work.

How can I get this working?

Thx!
Re: DDL for whole Database [message #555846 is a reply to message #555844] Tue, 29 May 2012 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Basically:
select dbms_metadata.get_ddl('TABLE',table_name,owner) 
from dba_tables
/

but you have to exclude SYS and other Oracle accounts and maybe some types of table.

Regards
Michel

Re: DDL for whole Database [message #555847 is a reply to message #555846] Tue, 29 May 2012 06:31 Go to previous messageGo to next message
chrissi
Messages: 10
Registered: May 2012
Junior Member
Yes, thats what I did. But how to create a loop, to get the create-statements of all Tables in a schema?
Re: DDL for whole Database [message #555848 is a reply to message #555847] Tue, 29 May 2012 06:37 Go to previous messageGo to next message
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't need a loop. Michel's query does exactly what you need.
Re: DDL for whole Database [message #555856 is a reply to message #555848] Tue, 29 May 2012 07:55 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
In ORACLE 11.2.0.2 i can't use DBMS_METADATA.get_ddl for nested tables, so I have do write:
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name)
  FROM user_all_tables
 WHERE nested = 'NO';
Re: DDL for whole Database [message #555860 is a reply to message #555856] Tue, 29 May 2012 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Of course, there is no DDL for a nested table (this is why I said you have to exclude some kinds of table).
A nested table is not a standalone object, it is part of another table. It is the same than you cannot have the DDL for a column.

Regards
Michel
Re: DDL for whole Database [message #555930 is a reply to message #555860] Wed, 30 May 2012 00:51 Go to previous messageGo to next message
chrissi
Messages: 10
Registered: May 2012
Junior Member
Ok, Michaels Statement seems to work Smile

Thx!

[Updated on: Wed, 30 May 2012 01:11]

Report message to a moderator

Re: DDL for whole Database [message #555937 is a reply to message #555930] Wed, 30 May 2012 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
is there a way for creating a ddl with the output?


What do you mean?
The SELECT statement generates an output.

Regards
Michel
Re: DDL for whole Database [message #555945 is a reply to message #555937] Wed, 30 May 2012 01:36 Go to previous messageGo to next message
chrissi
Messages: 10
Registered: May 2012
Junior Member
I found it already ... left klick on the output and then 'unload'.

Thx!
Re: DDL for whole Database [message #555956 is a reply to message #555945] Wed, 30 May 2012 02:32 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All ,

Like this is there any way to get DML statements applied on that table ?

Thanks
SaiPradyumn
Re: DDL for whole Database [message #555957 is a reply to message #555956] Wed, 30 May 2012 02:59 Go to previous message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Log Miner.
Or if you often need it, activate audit.

Regards
Michel
Previous Topic: Error message in Execute Immediate Command
Next Topic: Why to use cursors?
Goto Forum:
  


Current Time: Sun Aug 31 01:52:19 CDT 2025