Step 1: Take Table Counts Before Purging
Before performing any purge, get the record counts of the
affected tables to ensure you can verify the archival later.
Affected Tables:
- WSH_TRIPS
- WSH_TRIP_STOPS
- WSH_DELIVERY_LEGS
- WSH_NEW_DELIVERIES
- WSH_DELIVERY_DETAILS
- WSH_DELIVERY_ASSIGNMENTS
SQL statement:
- SELECT COUNT(*) FROM WSH_TRIPS;
- SELECT COUNT(*) FROM WSH_TRIP_STOPS;
- SELECT COUNT(*) FROM WSH_DELIVERY_LEGS;
- SELECT COUNT(*) FROM WSH_NEW_DELIVERIES;
- SELECT COUNT(*) FROM WSH_DELIVERY_DETAILS;
- SELECT COUNT(*) FROM WSH_DELIVERY_ASSIGNMENTS;
Step 2: Configure Archival Setup
Insert configuration metadata into a custom configuration table
XXXX ARCHIVE_CONFIG_TBL.
SQL statement:
INSERT INTO XXXX_ARCHIVE_CONFIG_TBL
(SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON)
VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_TRIP_STOPS’, ‘XXXX_WSH_TRIP_STOPS_ARCHIVE’, SYSDATE)
(SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON)
VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_TRIPS’, ‘XXXX_WSH_TRIPS_ARCHIVE’, SYSDATE)
(SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON)
VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_DELIVERY_LEGS’, ‘XXXX_WSH_’WSH_DELIVERY_LEGS _ARCHIVE’, SYSDATE)
(SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON)
VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_DELIVERY_DETAILS’, ‘XXXX_WSH_’WSH_DELIVERY_DETAILS’_ARCHIVE’, SYSDATE)
(SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON)
VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_DELIVERY_ASSIGNMENTS’, ‘XXXX_WSH_’WSH_DELIVERY_ASSIGNMENTS’_ARCHIVE’, SYSDATE)
(SNO, MODULE_SHORT_NAME, MODULE_LONG_NAME, ARCHIVE_REQUIRED, ORIG_TABLE_NAME, ARCHIVE_TABLE_NAME, LAST_ARCHIVED_ON)
VALUES (XXXX_ARCHIVE_SEQ.NEXTVAL, ‘WSH’, ‘SHIPPING’, ‘Y’, ‘WSH_NEW_DELIVERIES’, ‘XXXX_WSH_’WSH_NEW_DELIVERIES’_ARCHIVE’, SYSDATE)
Step 3: Create Archive Tables
Create custom archive tables with the same structure as the original shipping tables.
SQL statement:
- CREATE TABLE XXXX_WSH_TRIPS_ARCHIVE AS SELECT * FROM WSH_TRIPS WHERE 1=2;
- CREATE TABLE XXXX_WSH_DELIVERY_ASSIGNMENTS_ARCHIVE AS SELECT * FROM WSH_DELIVERY_ASSIGNMENTS WHERE 1=2;
- CREATE TABLE XXXX_WSH_TRIP_STOPS_ARCHIVE AS SELECT * FROM WSH_TRIP_STOPS WHERE 1=2;
- CREATE TABLE XXXX_WSH_DELIVERY_LEGS_ARCHIVE AS SELECT * FROM WSH_DELIVERY_LEGS WHERE 1=2;
- CREATE TABLE XXXX_WSH_NEW_DELIVERIES_ARCHIVE AS SELECT * FROM WSH_NEW_DELIVERIES WHERE 1=2;
- CREATE TABLE XXXX_WSH_DELIVERY_DETAILS_ARCHIVE AS SELECT * FROM WSH_DELIVERY_DETAILS WHERE 1=2;
Step 4: Create and Run Archival Package
- Develop a PL/SQL package (XXXX_WSH_DATA_ARCHIVE)
- Accepts a date parameter.
- Moves data older than the given date from base tables to archive tables.
Step 5: Shipping backup script
create or replace PACKAGE BODY xxxx_wsh_data_archive AS
PROCEDURE wsh_main_archive (
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_module_name IN VARCHAR2,
p_date_archive DATE
)
IS
BEGIN
g_module_name := p_module_name;
IF p_date_archive IS NULL
THEN
–g_date_archive := add_months(SYSDATE,-84);
select ’31-DEC-‘||to_char(extract(year from sysdate)-8)into g_date_archive from dual;
ELSE
g_date_archive := p_date_archive;
END IF;
dbms_output.put_line(‘G_DATE_ARCHIVE ‘ || g_date_archive);
UPDATE xdmc_archive_config_tbl
SET last_archived_date = g_date_archive,
last_archived_on = SYSDATE
WHERE MODULE_SHORT_NAME = ‘WSH’;
COMMIT;
wsh_archival_process;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END wsh_main_archive;
–END wsh_main_archive;
PROCEDURE wsh_archival_process IS
l_last_archived_date DATE;
BEGIN
FOR c_archive_process IN (SELECT *
FROM xxxx_archive_config_tbl
WHERE archive_required = ‘Y’
AND MODULE_SHORT_NAME = ‘WSH’) LOOP
IF
c_archive_process.module_short_name = ‘WSH’ AND c_archive_process.orig_table_name = ‘WSH_TRIPS’ THEN—4
l_last_archived_date := NULL;
SELECT last_archived_date
INTO l_last_archived_date
FROM xxxx_archive_config_tbl
WHERE orig_table_name = ‘WSH_TRIPS’;
–IF l_last_archived_date < G_DATE_ARCHIVE THEN
INSERT INTO XDMC_WSH_TRIPS_ARCHIVE
SELECT *
FROM WSH_TRIPS
Where creation_date <= g_date_archive;
–END IF;
ELSIF c_archive_process.module_short_name = ‘WSH’ AND c_archive_process.orig_table_name = ‘WSH_TRIP_STOPS’ THEN–5
l_last_archived_date := NULL;
SELECT last_archived_date
INTO l_last_archived_date
FROM xxxx_archive_config_tbl
WHERE orig_table_name = ‘WSH_TRIP_STOPS’;
–IF l_last_archived_date < G_DATE_ARCHIVE THEN
INSERT INTO XXXX_WSH_TRIP_STOPS_ARCHIVE
SELECT *
FROM WSH_TRIP_STOPS
WHERE Creation_date<= g_date_archive;
–END IF;
ELSIF c_archive_process.module_short_name = ‘WSH’ AND c_archive_process.orig_table_name = ‘WSH_DELIVERY_LEGS’ THEN–6
l_last_archived_date := NULL;
SELECT last_archived_date
INTO l_last_archived_date
FROM xxxx_archive_config_tbl
WHERE orig_table_name = ‘WSH_DELIVERY_LEGS’;
–IF l_last_archived_date < G_DATE_ARCHIVE THEN
INSERT INTO XXXX_WSH_DELIVERY_LEGS_ARCHIVE
SELECT *
FROM WSH_DELIVERY_LEGS
WHERE creation_date <= g_date_archive;
–END IF;
ELSIF c_archive_process.module_short_name = ‘WSH’ AND c_archive_process.orig_table_name = ‘WSH_DELIVERY_DETAILS’ THEN–7
l_last_archived_date := NULL;
SELECT last_archived_date
INTO l_last_archived_date
FROM xxxx_archive_config_tbl
WHERE orig_table_name = ‘WSH_DELIVERY_DETAILS’;
–IF l_last_archived_date < G_DATE_ARCHIVE THEN
INSERT INTO XXXX_WSH_DELIVERY_DETAILS_ARCHIVE
SELECT *
FROM WSH_DELIVERY_DETAILS
WHERE Creation_date <= g_date_archive;
–END IF;
ELSIF c_archive_process.module_short_name = ‘WSH’ AND c_archive_process.orig_table_name = ‘WSH_NEW_DELIVERIES’ THEN–8
l_last_archived_date := NULL;
SELECT last_archived_date
INTO l_last_archived_date
FROM xxxx_archive_config_tbl
WHERE orig_table_name = ‘WSH_NEW_DELIVERIES’;
–IF l_last_archived_date < G_DATE_ARCHIVE THEN
INSERT INTO XXXX_WSH_NEW_DELIVERIES_ARCHIVE
SELECT *
FROM WSH_NEW_DELIVERIES
WHERE Creation_date <= g_date_archive;
–END IF;
ELSIF c_archive_process.module_short_name = ‘WSH’ AND c_archive_process.orig_table_name = ‘WSH_DELIVERY_ASSIGNMENTS’ THEN—9
l_last_archived_date := NULL;
SELECT last_archived_date
INTO l_last_archived_date
FROM xxxx_archive_config_tbl
WHERE orig_table_name = ‘WSH_DELIVERY_ASSIGNMENTS’;
–IF l_last_archived_date < G_DATE_ARCHIVE THEN
INSERT INTO XXXX_WSH_DELIVERY_ASSIGNMENTS_ARCHIVE
SELECT *
FROM WSH_DELIVERY_ASSIGNMENTS
WHERE Creation_date<= g_date_archive;
–END IF;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END wsh_archival_process;
END xxxx_wsh_data_archive;
/
PACKAGE CALLING
set serveroutput on;
DECLARE
ERRBUFF VARCHAR2(200);
RETCODE VARCHAR2(200);
P_MODULE_NAME VARCHAR2(200);
P_DATE_ARCHIVE DATE;
BEGIN
P_MODULE_NAME := ‘WSH’;
P_DATE_ARCHIVE := NULL;
XXXX_WSH_DATA_ARCHIVE.WSH_MAIN_ARCHIVE(
ERRBUFF => ERRBUFF,
RETCODE => RETCODE,
P_MODULE_NAME => P_MODULE_NAME,
P_DATE_ARCHIVE => P_DATE_ARCHIVE
);
END;
/
