I
In this article, I will show you a script that deletes all rows from all tables for a specific tenant. You don't need to be familiar with your schema or manually piece together queries to perform the deletion. You can also easily modify this script to delete any entity, not just a tenant. There are several ways to achieve this, and I will show you two of them:
To find (even compound) references to a specific table, you can use this query:
SET DEFINE ON SELECT r.table_name, c.column_name, r.constraint_name, k.column_name AS primary_column FROM user_constraints r JOIN user_constraints p ON p.constraint_name = r.r_constraint_name AND p.constraint_type IN ('P', 'U') JOIN user_cons_columns c ON c.constraint_name = r.constraint_name AND c.table_name = r.table_name AND c.column_name NOT IN ('TENANT_ID') -- we should match on position JOIN user_constraints s ON s.table_name = r.table_name AND s.constraint_type = 'P' JOIN user_cons_columns k ON k.constraint_name = s.constraint_name AND k.table_name = s.table_name AND k.column_name NOT IN ('TENANT_ID') -- we should match on position WHERE r.constraint_type = 'R' --AND r.table_name = p.table_name -- to see references in same table AND p.table_name = '&TABLE_NAME' ORDER BY 1, 2;
When your column name is same in all tables, it is easy. You just need to disable related foreign constraints and maybe triggers (depending on your business case). To simplify this even more, I will disable all constraints and all triggers and enable them at the end.
DECLARE in_column_name CONSTANT VARCHAR2(128) := 'TENANT_ID'; -- tenant, user... in_column_value CONSTANT PLS_INTEGER := 100; -- tenant_id, user_id... -- v_affected PLS_INTEGER := 0; v_affected_loop PLS_INTEGER := 0; BEGIN -- disable all triggers FOR c IN ( SELECT t.trigger_name FROM user_triggers t ) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ' || c.trigger_name || ' DISABLE'; END LOOP; -- disable all foreign keys FOR c IN ( SELECT r.table_name, r.constraint_name FROM user_constraints r WHERE r.constraint_type = 'R' ) LOOP EXECUTE IMMEDIATE 'ALTER TABLE ' || c.table_name || ' DISABLE CONSTRAINT ' || c.constraint_name; END LOOP; -- loop over tables with requested column FOR c IN ( SELECT t.table_name FROM user_tab_cols c JOIN user_tables t ON t.table_name = c.table_name LEFT JOIN user_mviews m ON m.mview_name = c.table_name WHERE c.column_name = in_column_name AND m.mview_name IS NULL ) LOOP -- delete matching rows BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || c.table_name || ' WHERE ' || in_column_name || ' = ' || in_column_value; -- v_affected := v_affected + SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || RPAD(c.table_name, 40) || ' ' || SQLERRM); END; END LOOP; -- COMMIT; -- enable all foreign keys FOR c IN ( SELECT r.table_name, r.constraint_name FROM user_constraints r WHERE r.constraint_type = 'R' ) LOOP EXECUTE IMMEDIATE 'ALTER TABLE ' || c.table_name || ' ENABLE CONSTRAINT ' || c.constraint_name; END LOOP; -- enable all triggers FOR c IN ( SELECT t.trigger_name FROM user_triggers t ) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ' || c.trigger_name || ' ENABLE'; END LOOP; -- show number of deleted rows DBMS_OUTPUT.PUT_LINE('DELETED: ' || v_affected); END; /
In real life, your column names will be most likely different in each table (id, user_id, manager_id, approved_by...), hence you can't rely on the name, you have to use foreign keys. And you can have multicolumn foreign keys referencing not just the primary keys, but also unique constraints. So, if your foreign key strategy is not sound, good luck (check out this article about data integrity).
Another challenge is with the correct order. If you want to delete user, and he is referenced somewhere, you have to delete that row first. And depending on your app, you should just NULL the value and not delete the whole row (user B has a manager A, when you delete user A, you don't want to delete user B, you want just set his manager reference to NULL). So in the script below, references in the same table are set to NULL, the others are deleted.
You define the root table, column name and value/id you would like to remove:
DECLARE in_table_name CONSTANT VARCHAR2(64) := 'APP_USER'; in_column_name CONSTANT VARCHAR2(64) := 'ID'; in_ignore_column CONSTANT VARCHAR2(64) := 'TENANT_ID'; -- for compound keys in_id CONSTANT PLS_INTEGER := 1000; -- TYPE t_tables IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER; v_processed t_tables := t_tables(); -- PROCEDURE cascade_delete ( io_processed IN OUT t_tables, -- in_table_name VARCHAR2, in_column_name VARCHAR2, in_id PLS_INTEGER, in_level PLS_INTEGER ) AS v_affected PLS_INTEGER := 0; v_affected_loop PLS_INTEGER := 0; -- TYPE t_numbers IS TABLE OF PLS_INTEGER; v_id_array t_numbers := t_numbers(); -- v_pad VARCHAR2(30) := LTRIM(RPAD('.', in_level * 4, ' '), '.'); BEGIN DBMS_OUTPUT.PUT_LINE(v_pad || 'START: ' || in_table_name); -- io_processed.EXTEND; io_processed(io_processed.LAST) := in_table_name; -- remove references to the same table FOR c IN ( SELECT r.table_name, c.column_name, r.constraint_name FROM user_constraints r JOIN user_constraints p ON p.constraint_name = r.r_constraint_name AND p.constraint_type = 'P' JOIN user_cons_columns c ON c.constraint_name = r.constraint_name AND c.table_name = r.table_name JOIN user_cons_columns k ON k.constraint_name = p.constraint_name AND k.table_name = p.table_name WHERE r.constraint_type = 'R' AND r.table_name = in_table_name AND p.table_name = in_table_name ORDER BY 1, 2 ) LOOP EXECUTE IMMEDIATE 'UPDATE ' || c.table_name || ' SET ' || c.column_name || ' = NULL' || ' WHERE ' || c.column_name || ' = ' || in_id; END LOOP; -- find all references and delete them in a loop (too lazy for dependency sort) FOR i IN 1 .. 100 LOOP v_affected_loop := 0; -- FOR c IN ( SELECT r.table_name, c.column_name, r.constraint_name, k.column_name AS primary_column FROM user_constraints r JOIN user_constraints s ON s.table_name = in_table_name AND r.constraint_type = 'R' AND r.r_constraint_name = s.constraint_name JOIN user_cons_columns c ON c.constraint_name = r.constraint_name AND c.table_name = r.table_name AND c.column_name NOT IN (in_ignore_column) -- ignore compound key column JOIN user_constraints p ON p.table_name = r.table_name AND p.constraint_type IN ('P', 'U') JOIN user_cons_columns k ON k.constraint_name = p.constraint_name AND k.column_name NOT IN (in_ignore_column) -- ignore compound key column ORDER BY 1, 2 ) LOOP IF c.table_name MEMBER OF io_processed THEN DBMS_OUTPUT.PUT_LINE(v_pad || '- ' || c.table_name || ' > SKIPPING'); CONTINUE; END IF; -- DBMS_OUTPUT.PUT_LINE(v_pad || '- ' || c.table_name || '.' || c.column_name); -- fetch key EXECUTE IMMEDIATE 'SELECT DISTINCT ' || c.column_name || ' FROM ' || c.table_name || ' WHERE ' || c.primary_column || ' = ' || in_id BULK COLLECT INTO v_id_array; -- FOR j IN 1 .. v_id_array.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_pad || 'SUBCONTRACT: ' || c.table_name || '.' || c.primary_column || ' = ' || v_id_array(j)); -- cascade_delete ( io_processed => io_processed, in_table_name => c.table_name, in_column_name => c.primary_column, in_id => v_id_array(j), in_level => in_level + 1 ); END LOOP; -- BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || c.table_name || ' WHERE ' || c.column_name || ' = ' || in_id; -- v_affected := v_affected + SQL%ROWCOUNT; v_affected_loop := v_affected_loop + SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(v_pad || 'ERROR: ' || c.table_name || ' ' || SQLERRM); DBMS_OUTPUT.PUT_LINE(v_pad || 'QUERY: ' || 'DELETE FROM ' || c.table_name || ' WHERE ' || c.column_name || ' = ' || in_id ); END; END LOOP; -- IF v_affected_loop = 0 THEN EXIT; END IF; END LOOP; -- COMMIT; -- delete primary table BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || in_table_name || ' WHERE ' || in_column_name || ' = ' || in_id; v_affected := v_affected + SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(v_pad || 'ERROR: ' || in_table_name || ' ' || SQLERRM); DBMS_OUTPUT.PUT_LINE(v_pad || 'QUERY: ' || 'DELETE FROM ' || in_table_name || ' WHERE ' || in_column_name || ' = ' || in_id ); END; -- COMMIT; -- DBMS_OUTPUT.PUT_LINE(v_pad || 'DELETED: ' || v_affected); END; BEGIN -- disable all triggers FOR c IN ( SELECT t.trigger_name FROM user_triggers t ) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ' || c.trigger_name || ' DISABLE'; END LOOP; -- delete rows, not able to rollback cascade_delete ( io_processed => v_processed, in_table_name => in_table_name, in_column_name => in_column_name, in_id => in_id, in_level => 0 ); -- enable all triggers FOR c IN ( SELECT t.trigger_name FROM user_triggers t ) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ' || c.trigger_name || ' ENABLE'; END LOOP; END; /
If you remove the commit and DDL statement at the end of the script, you should be able to see what gets delete and still be able to ROLLBACK it.
Comments
Post a Comment