Friday, September 2, 2016

How to take the DDL Backup of an object in oracle

How to take the DDL Backup of a object in oracle:
====================================

Execute the below query to get the DDL backup of an object.

SET LONG 9999999
SET HEAD OFF
set lines 300 pages 3000
col aaa for a300
SELECT DBMS_METADATA.GET_ddl('&OBJECT_TYPE','&OBJECT_NAME','OWNER') aaa FROM DUAL;

Object Type: TABLE ,  PACKAGE, PROCEDURE, INDEX, FUNCTION, TRIGGER,
                      SYNONYM, VIEW, DB_LINK, MATERIALIZED_VIEW ......etc

Input: OBJECT_TYPE, OBJECT_NAME, OWNER

Note: It's safe to take DDL backup before modifying the DDL of an object.