Tag Archives: SQL

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? :)

Select duplicate attributes rows from a table which has more than one column making the primary key

Now here is something that a programmer would have come across sometime or the other. Let us say you have a table where more than one columns make up the primary key and you need to find out all those rows which have two of these columns (attributes) same but the third attribute is different. You can do this using a simple join like this

SELECT * FROM Table1 A INNER JOIN Table1 B ON A.PK1=B.PK1 AND A.PK3=B.PK3 AND A.PK2 <> B.PK2