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_dataNow execute 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;
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;
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;