Friday, October 15, 2010

Delete all records of all tables of a database schema in Oracle

0 comments
Once I found a question in a forum where an user, who was using Oracle, needed to delete all records from all tables in a schema and he had only DML privileges on that schema. Even he was not able to execute expdp in the database. I wrote the following two procedures for him and he was quite happy.

CREATE OR REPLACE PROCEDURE p_del_tab
 ( prm_table VARCHAR2 )

IS
    /*
    This procedure deletes all record from a table and if ORA-02292: occurs then recursively call the procedure again.
    */
    v_str VARCHAR2(500);

BEGIN

    EXECUTE IMMEDIATE 'DELETE FROM '||prm_table;

EXCEPTION

    WHEN OTHERS THEN

        --ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
        v_str := SQLERRM;
        v_str := SUBSTR(v_str,INSTR(v_str,'.')+1, INSTR(v_str,')')  -INSTR(v_str,'.')-1  );

    DECLARE

        CURSOR cur IS
        SELECT table_name
        FROM user_constraints
        WHERE constraint_name = v_str;

    BEGIN

        FOR rec IN cur LOOP
            P_DEL_tab(rec.table_name);
        END LOOP;
    END;

    p_del_tab(prm_table);

END p_del_tab;

CREATE OR REPLACE PROCEDURE p_del_all_data
IS
/* This procedure calls p_del_tab */

    CURSOR cur IS
    SELECT table_name
    FROM user_tables;

BEGIN

   FOR rec IN cur LOOP
       p_del_tab(rec.table_name);
   END LOOP;

EXCEPTION

    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('p_del_all_data: '||SQLERRM);

END
p_del_all_data
Now execute p_del_all_data:
BEGIN

    p_del_all_data;

END;

Remember you will be in trouble if you have many-to-many relationship like
DEPT has a column reference to COUNTRY and COUNTRY has a column reference to DEPT at that time you will be in infinite recursive call loop;

Leave a Reply

Labels