|
-
Delete non referenced Records
Hi,
is there any utility function or does one have a script to delete all records of a table which are NOT referenced from other tables?
This is kind of clean-up database of not used records...
delete from all entries not referenced...
thank you,
stefan
-
thats just a join between two or more tables
-
generic way please
Sure i can do something like
delete from where not exists (select id from )
but i want a generic function because i have many of those referenced tables..
i can write it for my own looking in views like user_constraints and so on..
i just wanted to know if there are any utility functions in oracle.
or maybe somebody did the same problem before (generic)
i am looking now for a few hours in google... for
script delete non referenced records of table etc....
-
What's the real purpose?
Is it taking too much space?
After deleting the unwanted rows (reference data), you want to add them if the application needs them.
-
solution
so i did it by myself..
took about 2 hours but it works...
this procedure dynamically executes a generated delete-statement of a table excluded records that ar not referenced by other tables..
PROCEDURE delete_non_ref_of_table (pTabelle IN VARCHAR2)
IS
intCurVar INTEGER;
CURSOR REFERENCED_TABLES IS
SELECT DISTINCT b.table_name table_name, c.column_name column_name,
NVL (d.constraint_type, 0) contraint_type,
g.table_name referenced_table
FROM user_tab_columns c,
user_cons_columns b,
user_constraints d,
user_constraints g
WHERE b.table_name(+) = c.table_name
AND b.column_name(+) = c.column_name
AND b.constraint_name = d.constraint_name(+)
AND d.r_constraint_name = g.constraint_name(+);
CURVAR REFERENCED_TABLES%ROWTYPE;
minusSQL VARCHAR2(2000) := '';
lprimKey VARCHAR2(2000) := '';
strSQL VARCHAR2(2000) := '';
intNoOfRows INTEGER := 0;
BEGIN
SELECT c.column_name INTO lprimKey FROM user_constraints b, user_cons_columns c
WHERE b.constraint_type = 'P'
AND b.constraint_name = c.constraint_name
AND c.table_name = pTabelle;
OPEN REFERENCED_TABLES;
LOOP
FETCH REFERENCED_TABLES INTO CURVAR;
EXIT WHEN REFERENCED_TABLES%NOTFOUND;
IF (CURVAR.referenced_table = pTabelle) THEN
minusSQL := minusSQL || ' MINUS SELECT ' || CURVAR.column_name || ' FROM ' || CURVAR.TABLE_NAME;
END IF;
END LOOP;
CLOSE REFERENCED_TABLES;
strSQL := 'DELETE FROM ' || pTabelle || ' WHERE ' || lprimKey || ' IN ( SELECT ' || lprimKey || ' FROM ' || pTabelle || minusSQL || ' )';
--bm_utils_base.debugKz := 'Y';
--bm_utils_base.put_any_line_old (strSQL);
intCurVar:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCurVar, strSQL ,DBMS_SQL.NATIVE);
intNoOfRows := DBMS_SQL.EXECUTE (intCurVar);
dbms_output.put_line('Gelöscht: ' || intNoOfRows );
DBMS_SQL.CLOSE_CURSOR (intCurVar);
END delete_non_ref_of_table;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|
Senior Advisor
Reply With Quote