OraFAQ Forum: Server Utilities » how to export table structure as script in oracle in all schemas

Home » RDBMS Server » Server Utilities » how to export table structure as script in oracle in all schemas (windows xp / oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to export table structure as script in oracle in all schemas [message #488546]

Sun, 09 January 2011 04:40

muradjuber
Messages: 60
Registered: August 2010

Member

dear experts

how can i export all table structure as script

note : i have multi schema's not one schema

i use SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM DBA_TABLES u;

but i need it for all schemas

thanks

Report message to a moderator

Re: how to export table structure as script in oracle in all schemas [message #488547 is a reply to message #488546]

Sun, 09 January 2011 04:50

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Just tables or all objects?
You need to write some customized sql for that.
A simpler option is to use exp utility and generate the ddl.

Report message to a moderator

Re: how to export table structure as script in oracle in all schemas [message #488548 is a reply to message #488547]

Sun, 09 January 2011 05:01

muradjuber
Messages: 60
Registered: August 2010

Member

how to do that

tell me please

tables+indexes+constraints

i need full structure

Report message to a moderator

Re: how to export table structure as script in oracle in all schemas [message #488550 is a reply to message #488548]

Sun, 09 January 2011 05:14

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

With dbms_metadata, first hit in google.
See if this suits you ( and I trust the author).
http://www.optimaldba.com/scripts/extract_schema_ddl.sql

with exp/imp tools,
export with rows=n
and
import with show=y logfile=somelogfile.sql
or
import with indexfile=somefile.sql

Both above options will actually import nothing into database but will dump the ddl information in specified file.

Report message to a moderator

Previous Topic:	SQL Loader date mask for the data in the format 02MAR1948:00:00:00.000 OR 24SEP1950:00:00:00.000
Next Topic:	How to Export data based on Join between 3 tables.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 11:11:38 CST 2024