DBMS_METADATA.GET_DDL is great function which can help to generate DDL for almost any object in your database. I found it very useful on development servers where you might overrride your older code or/and drop an object by accident. Yes we should check IN/OUT code to repository all the time, but let’s be honest, we are all human.
I run this code nightly to backup pretty much every object in the instance. It saved my skin only few times, but it was worth it for sure. Please note that following code does not include any purging of old data, I use partitions and simply truncate old backups, byt it’s really up to you how much history you want to keep.
DBMS_METADATA.GET_DDL has small catch, if you want to use it for all objects in the instance, you have to run it as SYS or create small wrapper which sits in SYS schema. I go the wrapper route simply because I don’t want to run any automated code as SYS if I don’t have to.
CREATE OR REPLACE FUNCTION SYS.F_GET_DDL (v_object_type varchar2, v_object_name varchar2, v_object_owner varchar2) return CLOB is v_ddl_clob clob; BEGIN v_ddl_clob := DBMS_METADATA.GET_DDL (v_object_type, v_object_name, v_object_owner); RETURN v_ddl_clob; END; GRANT EXECUTE ON SYS.F_GET_DDL TO JIRI;
The table which stores the backup of my DDL is very simple, you can go fancy and add PK, partitions or/and better storage clause.CREATE TABLE JIRI.BACKUP_DDL ( BACKUP_CREATE_DTTM DATE NOT NULL, INSTANCE_NM VARCHAR2(128 BYTE) NOT NULL, OBJECT_ID NUMBER, OBJECT_OWNER VARCHAR2(30 BYTE) NOT NULL, OBJECT_NM VARCHAR2(128 BYTE) NOT NULL, OBJECT_TYPE VARCHAR2(18 BYTE) NOT NULL, OBJECT_STATUS VARCHAR2(60 BYTE) NOT NULL, OBJECT_DDL CLOB, OBJECT_CREATE_DTTM DATE NOT NULL, OBJECT_UPDATE_DTTM DATE )
The wrapper is very simple as well, as you can see I do not backup every object nor every schema. The only tiny catch is that OBJECT_TYPE has to be modified, I wish Oracle would follow the same object type naming in all their procedures and functions.CREATE OR REPLACE PROCEDURE JIRI.SP_BACKUP_DDL AUTHID OWNER is v_ddl_clob clob; v_object_name varchar2(128); v_object_id number; v_object_type varchar2(18); v_object_type_ddl varchar2(128); v_status varchar2(60); v_owner varchar2(30); v_created date; v_last_ddl_time date; v_backup_create_dttm date := sysdate; v_instance_nm varchar2(128) := sys_context ('USERENV', 'DB_UNIQUE_NAME') ; v_sql_to_execute varchar2(32000); TYPE cur_typ IS REF CURSOR; c1 cur_typ; BEGIN v_sql_to_execute := ' SELECT OBJECT_NAME, ' || ' OBJECT_ID, ' || ' OBJECT_TYPE, ' || ' CASE WHEN OBJECT_TYPE = `DATABASE LINK` THEN `DB_LINK` ' || ' WHEN OBJECT_TYPE = `MATERIALIZED VIEW` THEN `MATERIALIZED_VIEW` ' || ' WHEN OBJECT_TYPE = `MATERIALIZED VIEW LOG` THEN `MATERIALIZED_VIEW_LOG` ' || ' WHEN OBJECT_TYPE = `JAVA SOURCE` THEN `JAVA_SOURCE` ' || ' ELSE OBJECT_TYPE END OBJECT_TYPE_DDL, ' || ' STATUS, ' || ' OWNER, ' || ' CREATED, ' || ' LAST_DDL_TIME ' || ' FROM SYS.DBA_OBJECTS ' || ' WHERE (OBJECT_TYPE IN (`TABLE`, `INDEX`, `DATABASE LINK`, `VIEW`, `FUNCTION`, ' || ' `JAVA SOURCE`, `PACKAGE`, `PROCEDURE`, `SEQUENCE`, `SYNONYM`, ' || ' `TRIGGER`, `MATERIALIZED VIEW`, `MATERIALIZED VIEW LOG`, ' || ' `LIBRARY`, `TYPE`, `DIRECTORY`) ' || ' AND OWNER NOT IN (`SYS`,`PUBLIC`,`SYSTEM`,`OUTLN`,`WMSYS`)) ' || ' OR (OBJECT_TYPE IN (`DATABASE LINK`, `DIRECTORY`) AND OWNER IN (`SYS`,`PUBLIC`)) '; v_sql_to_execute := replace(v_sql_to_execute, '`', CHR(39) ); -- loop through recordset and insert to target table (don't fire an error if there is an error; OPEN c1 FOR v_sql_to_execute; LOOP FETCH c1 INTO v_object_name, v_object_id, v_object_type, v_object_type_ddl, v_status, v_owner, v_created, v_last_ddl_time ; EXIT WHEN c1%NOTFOUND; BEGIN v_ddl_clob:= SYS.F_GET_DDL (v_object_type_ddl, v_object_name, CASE WHEN v_object_type_ddl = 'DIRECTORY' then null else v_owner END); EXCEPTION WHEN OTHERS THEN v_ddl_clob:= 'DDL not available'; END; INSERT INTO JIRI.BACKUP_DDL ( BACKUP_CREATE_DTTM, INSTANCE_NM, OBJECT_ID, OBJECT_OWNER, OBJECT_NM, OBJECT_TYPE, OBJECT_STATUS, OBJECT_DDL, OBJECT_CREATE_DTTM, OBJECT_UPDATE_DTTM ) VALUES( v_backup_create_dttm, v_instance_nm, v_object_id, v_owner, v_object_name, v_object_type, v_status, v_ddl_clob, v_created, v_last_ddl_time); COMMIT; END LOOP; END;
As you can see the backup is very simple and the execution pretty fast. I would highly recommend to implement similar procedure to any development environment especially if there is more developers involved in the same project and you don’t follow very strict checkin/checkout policies from code repository.
notes: 1. code above was tested with Oracle 10.2.0.4
2. GET_DDL does not include grants, please use GET_DEPENDENT_DDL if you need it
3. GET_DDL does not cover XML, use GET_XML
4. GET_DDL does not gather all pependent DDL, I recommend to fully test it if it can really recreate everything you need, this is faiirly new package and will improve over timeAdvertisements