Day: September 30, 2021

SQL>SELECT DBMS_METADATA.GET_DDL('object_type','object_name','object owner') FROM DUAL;

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','OWNER') FROM DUAL; 

SQL> select dbms_metadata.get_ddl('TABLE',t.table_name, t.owner) from dba_tables t where owner='USER_NAME' ;

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME','OWNER') FROM DUAL;

SQL> select dbms_metadata.get_ddl('INDEX',i.index_name, i.owner) from dba_indexes i where table_owner='USER_NAME' ;

SQL> Select Dbms_metadata.get_ddl ('VIEW', 'VIEW_NAME', 'OWNER') from dual;

SQL> SELECT dbms_metadata.GET_DDL ('VIEW', v.view_name,v.owner) from dba_views v;

SQL> SELECT query FROM  DBA_MVIEWS WHERE  MVIEW_NAME='MV_NAME';

SQL> Select Dbms_metadata.get_ddl ('MATERIALIZED_VIEW', 'MVIEW_NAME', 'MV_OWNER') from dual;

SQL> SELECT dbms_metadata.GET_DDL ('MATERIALIZED_VIEW', mv.mview_name,mv.owner) from dba_mviews mv;

SQL>select dbms_metadata.get_ddl ('PROCEDURE','PROCEDURE_NAME','OWNER') from dual;

SQL>select dbms_metadata.get_ddl ('PROCEDURE', o.object_name, o.owner,) from dba_objects o where o.object_type = 'PROCEDURE' and o.owner='USER';

SQL>select dbms_metadata.get_ddl ('FUNCTION', 'FUNCTION_NAME', 'OWNER') from dual;

SQL>select dbms_metadata.get_ddl ('FUNCTION', f.object_name, f.owner,) from dba_objects f where f.object_type = 'FUNCTION' and f.owner='USER';

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','PACKAGE_NAME','OWNER') FROM DUAL;

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','OWNER') FROM DUAL;

SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TABLESPACE_NAME') FROM dual;

SQL> SELECT dbms_metadata.GET_DDL('TABLESPACE', ts.tablespace_name) from dba_tablespaces ts;

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','TABLE_NAME','OWNER') from dual;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USER') from dual;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USER') from dual;

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER') from dual;