I just went through a day or 2 of hell with a partitioned table issue in 9.2.0.3.0 that I would like to share....
We have a table that is partitioned by RANGE (Subject Area) and subpartitioned by LIST (Month-Year). We are building a data warehouse and needed to reload data for one partition only - since this is development it happens a lot.
As we have done in the past, we went to truncate the partition using:
alter table <table> truncate partition <partition>;
Instead of it truncating the partition, we got this error:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
I checked and the partition was there and I could successfully select the count from that partition using:
select count(*) from <table> partition (<partition>);
I also tried adding the "RESUSE STORAGE" to the truncate option but that did'nt change anything. It looked like some sort of dictionary issue. Other tables that are partitioned similarly were not failing, which was troubling. We filed a Tar with ORACLE but didnt expect a fix since this is the latest patch set.
I figured that to work around the problem, I could simply drop the partition I was trying to truncate, then add it back by splitting the partition above it in the range.
alter table <table> drop partition <partition>;
alter table <table> split partition <higher-partition> at (<value>) into (partition <partition>, partition <higher-partition>);
This work-around did work but I didn't like the fact that I was dropping something. Also, this puts any index-partitions in to an UNUSABLE state if there is data in either partition, requiring an index partition rebuild
Next, I figured I would just DELETE the rows in the partition, since there was only 200K, using:
delete <table> partition <partition>;
This, too, failed with:
ORA-12096: error in materialized view log on "<table>"
ORA-00942: table or view does not exist
Ah-ha! Finally some evidence from an ORACLE error message! I had a Materialized View log on this table that must have been corrupted somehow. I dropped the MView log and the original command, and all others that failed, succeeded!
alter table <table> truncate partition <partition>;
So, if your heading to 9.2.0.3, and you see this problem, drop the MView log if you can.
Good luck out there, Tom
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
We have a table that is partitioned by RANGE (Subject Area) and subpartitioned by LIST (Month-Year). We are building a data warehouse and needed to reload data for one partition only - since this is development it happens a lot.
As we have done in the past, we went to truncate the partition using:
alter table <table> truncate partition <partition>;
Instead of it truncating the partition, we got this error:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
I checked and the partition was there and I could successfully select the count from that partition using:
select count(*) from <table> partition (<partition>);
I also tried adding the "RESUSE STORAGE" to the truncate option but that did'nt change anything. It looked like some sort of dictionary issue. Other tables that are partitioned similarly were not failing, which was troubling. We filed a Tar with ORACLE but didnt expect a fix since this is the latest patch set.
I figured that to work around the problem, I could simply drop the partition I was trying to truncate, then add it back by splitting the partition above it in the range.
alter table <table> drop partition <partition>;
alter table <table> split partition <higher-partition> at (<value>) into (partition <partition>, partition <higher-partition>);
This work-around did work but I didn't like the fact that I was dropping something. Also, this puts any index-partitions in to an UNUSABLE state if there is data in either partition, requiring an index partition rebuild
Next, I figured I would just DELETE the rows in the partition, since there was only 200K, using:
delete <table> partition <partition>;
This, too, failed with:
ORA-12096: error in materialized view log on "<table>"
ORA-00942: table or view does not exist
Ah-ha! Finally some evidence from an ORACLE error message! I had a Materialized View log on this table that must have been corrupted somehow. I dropped the MView log and the original command, and all others that failed, succeeded!
alter table <table> truncate partition <partition>;
So, if your heading to 9.2.0.3, and you see this problem, drop the MView log if you can.
Good luck out there, Tom
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.
