Tuesday, October 16, 2012

How To Get Oracle DB Object Definition

Normally we can use dbms_metadata.get_ddl to get most of object definition

examples:

SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI') FROM dual;

SELECT dbms_metadata.get_ddl('TABLESPACE', 'DATA') FROM dual;

select  dbms_metadata.get_ddl('INDEX','REVISIONS_CLASSIDSTATE') from dual;

select  dbms_metadata.get_ddl('DB_LINK','TEST.US.ORACLE.COM') from dual;
select  dbms_metadata.get_ddl('USER','MONITOR') from dual;

select  dbms_metadata.get_ddl('PROCEDURE','COMBINE_FOLDER_STRUCTURE') from dual;

select  dbms_metadata.get_ddl('FUNCTION','TEST') from dual;

select  dbms_metadata.get_ddl('TABLE','MYTEST' ) from dual;

However for Oracle Text , we need to use specific ones:
examples:

set long 500000
set pages 999
select ctx_report.create_index_script('FT_MYTEXT2') from dual;

set long 50000
set pages 9999
select dbms_metadata.get_ddl('TABLE', 'DR$FT_MYTEXT2MI') from dual;
select dbms_metadata.get_ddl('TABLE', 'DR$FT_MYTEXT2$I') from dual;

No comments: