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?