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