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
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.
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.
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.
