CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
Moudiz
Moudiz

Reputation: 7377

A query to export the DDL of all the tables in a schema

I have more then 250+ table in my schema, I want to export them as DDL ( create table ...) and send have them in one script . Useally I used the export tool from PLSQL Developer.

But I am facing errors like sql export cannot take clob or long raw or long or blob so i have to remove manually about 50 table.

Is there a query for that?. Because the other way in PLSQL Developer is really slow.

Upvotes: 2

Views: 8280

Answers (2)

user330315
user330315

Reputation:

Maybe this: 

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables

You can exclude tables if you want to:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables
where table_name not in ('FOO', 'BAR');

Upvotes: 6

davek
davek

Reputation: 22925

You can do this with the ancient IMP/EXP tool, when you specify just schema and no data.

e.g. from Tom Kyte's solution here:

exp userid=/ owner=some_schema
imp userid=/ indexfile=foo.sql

and then get rid of these lines 

REM  ...
CONNECT

and then remove all the other REMs.

Upvotes: 1

Related Questions