Context
Runbook to clean up the AIF data warehouse before a fresh dataload. It applies to any client or implementation: run the steps in order, and after each cleanup run the paired verification query to confirm the tables are empty. It is part of Environment cleanup process.
These steps delete warehouse data
Every step truncates or purges real warehouse tables and cannot be undone. Confirm you are connected to the correct environment before running anything.
Steps
1. Data warehouse cleanup
BEGIN
DBMS_SCHEDULER.CREATE_JOB (job_name => 'radm_cleanup_run1',
job_type => 'PLSQL_BLOCK',
job_action => q'#BEGIN
RI_SUPPORT_UTIL.CLEAR_SELECTED_RI_TABLES(SCHEMANAME => 'RADM01');
END;#',
enabled => TRUE,
auto_drop => TRUE);
END;Ensure the cleanup checking:
SELECT /*+ PARALLEL(8) */ t.table_name,
CASE
WHEN o.table_name IS NOT NULL THEN
TO_NUMBER(
EXTRACTVALUE(
XMLTYPE(
DBMS_XMLGEN.GETXML('SELECT COUNT(*) AS c FROM RADM01.' || t.table_name)
),
'/ROWSET/ROW/C'
)
)
ELSE NULL
END AS row_count
FROM (
SELECT table_name
FROM c_ri_subjectarea
WHERE schema_name = 'RADM01'
) t
LEFT JOIN all_tables o
ON o.owner = 'RADM01'
AND o.table_name = t.table_name
ORDER BY row_count DESC NULLS LAST;2. Calendar removal
BEGIN
DBMS_SCHEDULER.CREATE_JOB (job_name => 'mcal_cleanup_run1',
job_type => 'PLSQL_BLOCK',
job_action => q'#BEGIN
RI_SUPPORT_UTIL.CLEAR_RI_MCAL_TABLES('RADM01');
END;#',
enabled => TRUE,
auto_drop => TRUE);
END;Ensure cleanup checking:
SELECT /*+ PARALLEL(8) */ t.table_name,
CASE
WHEN o.table_name IS NOT NULL THEN
TO_NUMBER(
EXTRACTVALUE(
XMLTYPE(
DBMS_XMLGEN.GETXML('SELECT COUNT(*) AS c FROM RADM01.' || t.table_name)
),
'/ROWSET/ROW/C'
)
)
ELSE NULL
END AS row_count
FROM (
SELECT table_name
FROM c_ri_subjectarea
WHERE schema_name = 'RADM01'
and area_name like '%MCAL%'
) t
LEFT JOIN all_tables o
ON o.owner = 'RADM01'
AND o.table_name = t.table_name
ORDER BY row_count DESC NULLS LAST;3. Integration layer cleanup
DECLARE
TABLE_NAME VARCHAR2(200);
BEGIN
TABLE_NAME := '%';
RAP_SUPPORT_UTIL.PURGE_INTF_RUNS(p_app_code => '%', p_intf_name => TABLE_NAME);
END;Ensure cleanup checking:
SELECT /*+ PARALLEL(8) */ t.table_name,
TO_NUMBER(
EXTRACTVALUE(
XMLTYPE(
DBMS_XMLGEN.GETXML('SELECT COUNT(*) AS c FROM RDX01.' || t.table_name)
),
'/ROWSET/ROW/C'
)
) AS row_count
FROM all_tables t
WHERE t.owner = 'RDX01'
ORDER BY row_count DESC NULLS LAST;