question

BI DWH BALA avatar image
BI DWH BALA asked

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

Hi,

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.

BI DWH BALA

oracletableviewsequencematerialized-view
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

user-74 avatar image
user-74 answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BI DWH BALA avatar image BI DWH BALA commented ·
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.
0 Likes 0 ·
BI DWH BALA avatar image
BI DWH BALA answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA avatar image
KillerDBA answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.