Backing up DDL in Oracle – the DBA way!!

So the other day I had to back up all database objects for an Oracle instance i manage and I was looking for some tool that would help me generate DDL for each object in a separate SQL file.

A quick Google search revealed I could backup DDL using TOAD (full version), DB Solo and RazorSQL. I use DBArtisan at work and my organization doesn’t want to move to TOAD yet. I have used DB Solo in the past and I have liked the clean and light interface and the quick ability to back up DDL but I just couldn’t use it this time because I had already expired my trial version and didn’t have enough time to get the folks sign off on purchase of a license (which is not very expensive). So I decided to try RazorSQL, but was disappointed because it dumps all objects in one single SQL file. Also, when I tried to back up stored procedures (over 5000 of them), the program refused to copy.

Out of luck and exhausted, I remembered the good old DBMS_METADATA commands. I quickly created a new directory entry in the database for the location where i wanted to dump my DDL, and executed a procedure that I wrote to perform the DDL backup.

Here are the steps I followed: (one can use SQL PLUS or any DB tool he wants)

–create a new directory in all_directories table. This is important to avoid ORA-29280 invalid_path execption.

CREATE OR REPLACE DIRECTORY BACKUP_DIR AS 'E:DATA_BKP';

–also grant permissions to everyone to read, write (maybe restricted based on your requirement).

GRANT READ,WRITE ON BACKUP_DIR TO PUBLIC;

–the stored procedure for DDL backup
–note that we are reading from USER_OBJECTS data dictionary, this way it exports only the current user’s objects.
–to export all objects, execute procedure as sys or sysdba and use the table ALL_OBJECTS instead.

CREATE OR REPLACE PROCEDURE	EXPORT_DDL
AS
	V_DDL_CLOB  CLOB;
	VPATH VARCHAR2(255);
BEGIN
	FOR C IN (SELECT OBJECT_NAME, OBJECT_TYPE
             FROM USER_OBJECTS
             WHERE OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY'))
	LOOP
		V_DDL_CLOB := DBMS_METADATA.GET_DDL(C.OBJECT_TYPE, C.OBJECT_NAME, 'EAGLE');
		DBMS_XSLPROCESSOR.CLOB2FILE(V_DDL_CLOB, 'BACKUP_DIR', C.OBJECT_TYPE || '_' || C.OBJECT_NAME||'.SQL');
	END LOOP;
END;
/

EXEC EXPORT_DDL;

Now that was simple, wasn’t it? :)

2 thoughts on “Backing up DDL in Oracle – the DBA way!!

Leave a Reply