|
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
(comments are locked)
|
|
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.
(comments are locked)
|
|
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. 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 '09 at 12:27 PM
BI DWH BALA
(comments are locked)
|
|
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.
(comments are locked)
|
