How to extract table, view, sequence and other objects definitions from Oracle?


I have many tables, views, sequences, functions in multiple schemas in my database. I have to migrate the entire database meta data to production environment. So, I would like to prepare objects (table, view..) scripts.

It would be difficult to open each and every object and get the definition.

Is there any way to extract the definitons of oracle objects all at a once in a file? I have the list of object names, for which I need the definitions.

Thanks in advance.


more ▼

asked Nov 24, 2009 at 12:32 AM in Default

BI DWH BALA gravatar image

606 43 60 62

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The best way is to probably use the data dump tool in schema mode. Depending if also the data or only the metadata definitions need to be exported the content parameter needs to be specified. If the data needs to be also migrated to other tablespaces as part of the loading process the remap_tablespace option needs to be specified.

more ▼

answered Nov 24, 2009 at 04:05 AM

user-74 gravatar image


Thanks for your reply Stefan-roesch. Actually, I don't need to load entire schema. There are specified lists of objects and tables for each schema. Only those objects should get migrated. Any suggestions are welcome.
Nov 24, 2009 at 12:27 PM BI DWH BALA
(comments are locked)
10|1200 characters needed characters left

Try SQL Tools 1.5 from sqltools.net. I've encountered bugs when trying to dump all the SQL from schema but for extracting the DDL for one item at a time, it's quite good. And it's free. In fact, I like it for editing procs and functions that I'm working on.

more ▼

answered Dec 01, 2009 at 07:00 PM

KillerDBA gravatar image

1.5k 8 9 10

(comments are locked)
10|1200 characters needed characters left

Hi ,

I found the solution for this problem. There is one function dbms_metadata.get_ddl() available to get definition of oracle objects.

I used as follows.

set heading off; set echo off; set pages 999; set long 50000;

spool ddl_list.sql select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual; spool off;

It worked out.

Thanks a lot for everyone.

more ▼

answered Nov 25, 2009 at 01:21 PM

BI DWH BALA gravatar image

606 43 60 62

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 24, 2009 at 12:32 AM

Seen: 10933 times

Last Updated: Aug 12 at 11:19 AM