Examples To Get Definition of DB Objects

Normal DB objects we can get definition from  dbms_metadata.get_ddl():

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

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

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

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

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

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

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

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


For  Oracle TEXT, we need to use specific package:
set long 500000
set pages 999
select ctx_report.create_index_script('FT_TESTTEXT2') from dual;

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