Oracle Database Discussions

Based on some good feedback last week I was able to migrate a high volume table to a partitioned structure. That seemed to be working until I actually started dropping old partitions. So far I seem to have a situation where the old partition gets dropped at 10:55 pm. Then I have an hourly Job that inserts rows from the intermediate insertion tables.

It ran at 11:00 pm and failed with this error:

Details ORA-01502: index 'ORA-01502: index 'EMS.FLOW_COMPLETION_RECORD_PPK2' or partition of such index is in unusable state ORA-06512: at "EMS.FCR_INSERT_CONTROLLER", line 23 .' or partition of such index is in unusable state

This index: FLOW_COMPLETION_RECORD_PPK2 is the primary key on this partitioned table. The tabled is partitioned by day, based on a column named CREATE_DATE DATE.

I would appreciate any ideas or suggestions as to why the dropping of a partition would be causing the indexes to become unusable. And it is not just this index listed above, it seems all the indexes on this table go bad at that time.

I am not using SQLLoader or IMPDP, and I am not doing a HINT /* +APPEND */ in my inserts to the partitioned table.

Thank you,
M


The structure of the system is as follows:

applications insert records to either of two tables: FCR_A or FCR_B. These are fronted by a synonym named FLOW_COMPLETION_RECORD_INSERT. Each hour a job runs that: swaps the synonym, inserts the records from either A or B into the FCR_PART (partitioned) table. Then truncates the data just inserted.

So: Apps --> FCR_A or FCR_B -->job: FCR_INSERT_CONTROLLER --> FLOW_COMPLETION_RECORD_PART

-- Run Hourly (averge 100K records/hour)
create or replace
PROCEDURE FCR_INSERT_CONTROLLER AS

CURRENT_TABLE VARCHAR2(60);

BEGIN
-- A. Get the TABLE the synonym references, and store that in the variable: CURRENT_TABLE
SELECT TABLE_NAME INTO CURRENT_TABLE FROM ALL_SYNONYMS
WHERE SYNONYM_NAME = 'FLOW_COMPLETION_RECORD_INSERT';

CASE CURRENT_TABLE
WHEN 'FLOW_COMPLETION_RECORD_A' THEN
EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM FLOW_COMPLETION_RECORD_INSERT FOR FLOW_COMPLETION_RECORD_B';

WHEN 'FLOW_COMPLETION_RECORD_B' THEN
EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM FLOW_COMPLETION_RECORD_INSERT FOR FLOW_COMPLETION_RECORD_A';

ELSE
DBMS_OUTPUT.PUT_LINE(' Unable to add find FCR Insert Synonym ' );
END CASE;

-- B. Insert the FCR Data
BEGIN
EXECUTE IMMEDIATE ' INSERT INTO FLOW_COMPLETION_RECORD SELECT * FROM ' || CURRENT_TABLE;
COMMIT;
END;

-- C. Truncate the data in the former table
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || CURRENT_TABLE || ' ';
COMMIT;
END;

END FCR_INSERT_CONTROLLER;


The Drop Partition job runs at 10:55 pm each night. It has run successfully for the past two nights but the index becomes unusable.

--
-- PROCEDURE: DROP_PARTITIONS
-- Deletes specific partitions from a table that are created on some day in the past. This is usually.
-- calculated as SYSDATE - x (days) .
--
-- This PROC was designed to be run from an ORACLE JOB
--
create or replace
procedure drop_partitions (
i_table IN VARCHAR2, i_part_nm IN VARCHAR2,
i_date_cutoff IN DATE)
as

v_drop_sql VARCHAR2(500) := ' ALTER TABLE ' || i_table || ' DROP PARTITION ' ;
v_partition_name VARCHAR2(50);

CURSOR partition_name_cur
IS select a.partition_name from V_EMS_PARTITION a where partition_date <= ( select i_date_cutoff from dual)
AND A.TABLE_NAME = i_table;

BEGIN

BEGIN
OPEN partition_name_cur;
LOOP
FETCH partition_name_cur INTO v_partition_name;
EXIT WHEN partition_name_cur%NOTFOUND;

-- Do stuff
DBMS_OUTPUT.PUT_LINE(' partition to be DROPPED: ' || v_partition_name);

BEGIN
EXECUTE IMMEDIATE v_drop_sql || v_partition_name;
commit;
DBMS_OUTPUT.PUT_LINE(' v_drop_sql: ' || v_drop_sql || v_partition_name);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' Unable to add partition:: ' || v_drop_sql || v_partition_name);
RAISE;
END;


END LOOP;
CLOSE partition_name_cur;
END;

END;
/