Hi Team,
I work for 11g database, my requirement is simple to delete a customer ID,transaction id from all the tables which are existing in database..
Say I have customer ID 123 in database and transaction id some XXXX value , now the requirement is to delete this customer ID 123 and transaction id XXXX from all the All the tables which has column called customer ID,Transaction_id.
I wrote the below script which will fetch all tables which has customer_id,transaction_id as field's in the tables and delete using the delete statement and it is working good.
This is a daily process for me to execute for any given customer_id,transaction_id by client. This process on an average takes me 3 hours to finish.
Now the problem most of the time that occours is when I run the script after a hour a or two it throws me a Timeout error as sometimes the same tables are been used by other users and causes timeout issues.
Eg.
ORA-02049: timeout: distributed transaction waiting for lock DELETE FROM SNS.HISTORY_LINE WHERE CUSTOMER_ID = 10195965.
To complete the process i do not have other option, i have to rerun the process again so here again the process starts from beginning , i am looking for a solution where i can resume the process from where it got stcuked so it takes less effort and avoid tables which are already deletetd.
Below is the script which is gave you is not full fledged one just gave you so that you can get what the script is trying to do.
-- For sample i gave you 123 as customer id and 4567123 as trasnaction_id.
create procedure TEST_PROC as
v_cnt NUMBER;
v_sql VARCHAR2(4000);
v_ids VARCHAR2(4000);
v_cust NUMBER;
BEGIN
FOR c IN (SELECT table_name
, column_name
FROM(
SELECT owner||'.'||table_name table_name
,column_name
from ALL_TAB_COLS join ALL_TABLES using (TABLE_NAME,OWNER)
WHERE column_name IN ('CUSTOMER_ID','TRANSACTION_ID')
AND data_type = 'NUMBER' )
ORDER BY table_name)
LOOP
for J in (select 123 CUSTOMER_ID, 4567123 TRANSACTION_ID ---> Actually i pass these two values as input parameters
from DUAL
)
LOOP
v_ids := CASE WHEN c.column_name = 'TRANSACTION_ID' THEN ' IN '|| j.TRANSACTION_ID ELSE ' IN ' || j.customer_id END;
v_sql := ' DELETE FROM '||c.table_name||' WHERE '||c.column_name||' '||v_ids;
to_print(v_sql);
EXECUTE IMMEDIATE v_sql;
v_cnt := sql%rowcount;
COMMIT;
END LOOP;
END LOOP;
EXCEPTION when OTHERS then
null;
END test_proc;
Thanks,
S