=======================
General
=======================
Generic delete from a table using Business Logic.
Several million of records are being deleted.
The flow is a three steps process:
Step1.
pre_run_steps.sh
This create
backup table for SOURCE_DATA_SUBSCRIBER (SOURCE_DATA_SUBSCRIBER_BAK)
Creates
additional temporary tables which are to be used during actual delete step.
Step2.
main_delete.sh
The actual DELETE.
Create the PL/SQL Package, which handles the business logic.
Call the PL/SQL package.
Progress is logged into SGA_W_LOG Table.
Step3.
post_run_steps.sh
This drop the SOURCE_DATA_SUBSCRIBER_BAK and drop additional temporary tables created in pre_run_steps.sh
=======================
Code
=======================
Step 0.
set_vipuser.sql
Step1.
pre_run_steps.sh
pre_run_steps.batpre_run_steps.sql
Step2.
main_delete.shmain_delete.bat
main_delete.sql
MULTI_DELETE.sql
Step3.
post_run_steps.shpost_run_steps.batpost_run_steps.sql
Step 0.
set_vipuser.sql
--Set the connection detailsdefine vipuser=my_userdefine vippass=my_passdefine connectStr=MY_INSTANCE
Step 1.
pre_run_steps.sh
sqlplus /nolog @pre_run_steps.sql
pre_run_steps.bat
sqlplus /nolog @pre_run_steps.sql
pre_run_steps.sql
@../set_vipuser.sql
SET SERVEROUT ON
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
PROMPT conn &&vipuser/&&vippass@&&connectStr
conn &&vipuser/&&vippass@&&connectStr
EXEC DBMS_OUTPUT.ENABLE(100000);
PROMPT CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2;
DECLARE
v_sql_str VARCHAR2(2000);
v_counter NUMBER;
BEGIN
SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SOURCE_DATA_SUBSCRIBER_BAK';
IF v_counter = 0 THEN
v_sql_str := 'CREATE TABLE SOURCE_DATA_SUBSCRIBER_BAK AS SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER WHERE 1=2';
ELSE
v_sql_str := 'TRUNCATE TABLE SOURCE_DATA_SUBSCRIBER_BAK';
END IF;
EXECUTE IMMEDIATE v_sql_str;
END;
/
PROMPT ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;
ALTER TABLE SOURCE_DATA_SUBSCRIBER_BAK MOVE TABLESPACE IGT_TEMP_TABLE;
PROMPT INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;
INSERT /*+ APPEND */ INTO SOURCE_DATA_SUBSCRIBER_BAK SELECT * FROM SOURCE_USER.SOURCE_DATA_SUBSCRIBER;
COMMIT;
PROMPT CREATE TABLE SGA_LOC_GRP_OUTBACK_NETWORKS
DECLARE
v_sql_str VARCHAR2(2000);
v_counter NUMBER;
BEGIN
SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'SGA_LOC_GRP_OUTBACK_NETWORKS';
IF v_counter = 0 THEN
v_sql_str := 'CREATE TABLE SGA_LOC_GRP_OUTBACK_NETWORKS (network_id NUMBER(10) ) TABLESPACE IGT_TEMP_TABLE;';
ELSE
v_sql_str := 'TRUNCATE TABLE SGA_LOC_GRP_OUTBACK_NETWORKS';
END IF;
EXECUTE IMMEDIATE v_sql_str;
END;
/
PROMPT CREATE TABLE MSISDN_PROFILE_NOT_PRE
DECLARE
v_sql_str VARCHAR2(2000);
v_counter NUMBER;
BEGIN
SELECT COUNT(*) INTO v_counter FROM USER_TABLES WHERE table_name = 'MSISDN_PROFILE_NOT_PRE';
IF v_counter = 0 THEN
v_sql_str := 'CREATE TABLE MSISDN_PROFILE_NOT_PRE (msisdn VARCHAR2(30) NOT NULL) TABLESPACE IGT_TEMP_TABLE;';
ELSE
v_sql_str := 'TRUNCATE TABLE MSISDN_PROFILE_NOT_PRE';
END IF;
EXECUTE IMMEDIATE v_sql_str;
END;
/
PROMPT INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS
INSERT /*+ APPEND */ INTO SGA_LOC_GRP_OUTBACK_NETWORKS
SELECT DISTINCT network_id
FROM SOURCE_USER.SGA_LOCATION_GROUP_ENTRIES
WHERE location_group_id IN
(SELECT location_group_id
FROM SOURCE_USER.SGA_LOCATION_GROUPS
WHERE LOCATION_GROUP_NAME='Outback');
commit;
PROMPT INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)
INSERT /*+ APPEND */ INTO MSISDN_PROFILE_NOT_PRE(msisdn)
SELECT KEY1 FROM SOURCE_USER.SFI_CUSTOMER_PROFILE WHERE ATTR1 NOT IN ('RogEngPre','RogFrPre','FidoEngPre','FidoFrPre');
commit;
PROMPT CREATE INDEX SFI_CST_PROF_IX
DECLARE
v_sql_str VARCHAR2(2000);
v_counter NUMBER;
BEGIN
SELECT COUNT(*) INTO v_counter FROM USER_INDEXES WHERE index_name = 'SFI_CST_PROF_IX';
IF v_counter > 0 THEN
v_sql_str := 'DROP INDEX SFI_CST_PROF_IX';
END IF;
EXECUTE IMMEDIATE v_sql_str;
END;
/
PROMPT CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;
CREATE INDEX SFI_CST_PROF_IX ON MSISDN_PROFILE_NOT_PRE(msisdn) TABLESPACE IGT_TEMP_INDEX;
BEGIN
DBMS_STATS.gather_table_stats('&&vipuser','MSISDN_PROFILE_NOT_PRE');
END;
/
PROMPT Finished Pre Run step
EXIT;
Step 2.
main_delete.sh
sqlplus /nolog @main_delete.sql
main_delete.bat
sqlplus /nolog @main_delete.sql
main_delete.sql
@../set_vipuser.sql
SET SERVEROUT ON
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 0
SET FEEDBACK OFF
PROMPT conn &&vipuser/&&vippass@&&connectStr
conn &&vipuser/&&vippass@&&connectStr
EXEC DBMS_OUTPUT.ENABLE(100000);
PROMPT CREATE PROCEDURE MULTI_DELETE
@MULTI_DELETE.sql
PROMPT EXECUTE PROCEDURE MULTI_DELETE
PROMPT .............................
PROMPT Running DELETE....
PROMPT Follow Up on process Progress by: SELECT * FROM SGA_W_LOG ORDER BY TS_LAST_MODIFIED;
PROMPT .............................
BEGIN
MULTI_DELETE;
END;
/
PROMPT Finished Run step
EXIT;
MULTI_DELETE.sql
CREATE OR REPLACE PROCEDURE MULTI_DELETE IS
v_effected_rows NUMBER;
v_delete_rows NUMBER;
v_row_counter NUMBER;
v_table_name VARCHAR2(30);
v_sql_str VARCHAR2(1000);
v_module_name VARCHAR2(30);
v_msg_text VARCHAR2(1000);
BEGIN
v_table_name :='SOURCE_DATA_SUBSCRIBER';
v_effected_rows := 1;
v_row_counter := 0;
v_module_name := 'MULTI DELETE';
v_sql_str := 'DELETE FROM SOURCE_DATA_SUBSCRIBER WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS ) AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE) AND ROWNUM < 100001';
v_msg_text := 'Running SQL: '||v_sql_str;
INSERT INTO SGA_W_LOG ( procedure_name, data, ts_last_modified)
VALUES(v_module_name,v_msg_text, SYSDATE);
COMMIT;
v_msg_text := 'Starting at: '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss');
INSERT INTO SGA_W_LOG ( procedure_name, data, ts_last_modified)
VALUES(v_module_name,v_msg_text, SYSDATE);
COMMIT;
WHILE v_effected_rows > 0 LOOP
DELETE FROM SOURCE_DATA_SUBSCRIBER
WHERE current_network_id NOT IN ( SELECT network_id FROM SGA_LOC_GRP_OUTBACK_NETWORKS )
AND msisdn IN( SELECT msisdn FROM MSISDN_PROFILE_NOT_PRE)
AND ROWNUM < 100001;
v_effected_rows := SQL%ROWCOUNT;
v_row_counter := v_row_counter + v_effected_rows;
COMMIT;
v_msg_text := v_row_counter||' rows deleted from table '||v_table_name;
INSERT INTO SGA_W_LOG ( procedure_name, data, ts_last_modified)
VALUES(v_module_name,v_msg_text, SYSDATE);
COMMIT;
END LOOP;
COMMIT;
v_msg_text := 'MANUAL_DELETE FROM '||v_table_name||' Finished Successfully';
INSERT INTO SGA_W_LOG ( procedure_name, data, ts_last_modified)
VALUES(v_module_name,v_msg_text, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);
INSERT INTO SGA_W_LOG ( procedure_name, data, ts_last_modified)
VALUES(v_module_name,v_msg_text, SYSDATE);
COMMIT;
END MULTI_DELETE;
/
Step 3.
post_run_steps.shsqlplus /nolog @post_run_steps.sql
post_run_steps.bat
sqlplus /nolog @post_run_steps.sql
post_run_steps.sql
@../set_vipuser.sqlSET SERVEROUT ON
SET HEADING OFF
SET LINESIZE 400
SET PAGESIZE 0
SET FEEDBACK OFF
PROMPT conn &&vipuser/&&vippass@&&connectStr
conn &&vipuser/&&vippass@&&connectStr
EXEC DBMS_OUTPUT.ENABLE(100000);
PROMPT DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK
DROP TABLE SOURCE_DATA_SUBSCRIBER_BAK;
PROMPT DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS
DROP TABLE SGA_LOC_GRP_OUTBACK_NETWORKS;
PROMPT DROP TABLE MSISDN_PROFILE_NOT_PRE
DROP TABLE MSISDN_PROFILE_NOT_PRE;
PROMPT Finished Post Run step
EXIT;
