Interval Partitions Are Not Created In The Hstprg Rerun (Doc ID 3046796.1)

Last updated on SEPTEMBER 17, 2024

Applies to:

Symptoms

Interval Partitions are not created in the hstprg rerun.

After run "Purge Aged Sales History (hstprg)" and return the following error:

$ cat err.hstprg_1.Aug_28 hstprg_1~20220828034728~process~ITEM_LOC_HIST~-54~ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired~EXECUTE DYNAMIC STATEMENT ALTER TABLE RMS14.ITEM_LOC_HIST SET INTERVAL (NUMTODSINTERVAL(7,'DAY'))

Rerun hstprg and interval partitions are not created.

Customer analysis:

As per lines 314-380 of (hstprg.pc) apparently the code looks like it is turning off automatic partitioning to make the partition drop.

However, if the routine has an error at this point, it cannot turn on automatic partitioning again.

When the routine is re-executed, the code enters the else where another partition is dropped without turning on automatic partitioning again.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.