2

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.

Moudiz
  • 7,211
  • 22
  • 78
  • 156
  • there is no such thing as "the export tool from plsql". PL/SQL is a programming language and does not have "an export tool". –  Jul 03 '13 at 11:42
  • then I have to correct it and say PLS/sql developer ? – Moudiz Jul 03 '13 at 11:45

2 Answers2

6

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');
1

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.

davek
  • 22,499
  • 9
  • 75
  • 95
  • you mean in plsql tool --> export ? If yes I am facing errors as i meantioned above . becase there are table have long and clob. I am not able to do that with anciant way due columns have data type such long and clob – Moudiz Jul 03 '13 at 11:36