2 Replies Latest reply on Apr 26, 2013 2:28 AM by 534627

    Decode of system FLAGS columns?

      I'm looking for a resource/writeup that can tell me what the different values in the FLAGS columns mean. Specifically I'm looking at indpart$.flags and tabpart$.flags and seg$.spare1. If I'm looking in the wrong place please let me know.

      I'm trying to write a (generic) procedure to compress partitions on tables that may or may not have already been compressed. If the table has not been compressed and it has valid bitmap indexes then any attempt to compress will fail with the following error:

      SQL Error: ORA-14646: Specified alter table operation involving compression cannot be performed in the presence of usable bitmap indexes.

      In most cases I can look at the partitions using dba_tab_partitions and check the COMPRESSION column value. However, it's possible to have that column tell you the partition is compressed (COMPRESSION = ENABLED) without the partition actually being compressed. This has been documented elsewhere and I won't go into it.

      Within my procedure I would like to proactively verify the partition(s) to see if they really are compressed. If at least one partition is compressed then there is no need to disable all the bitmap indexes at the table level. I'll disable the bitmap indexes at the table level only if it is absolutely necessary.

      Alternatively, my only other way to make this work is to try the MOVE command and if ORA-14646 is thrown then go back and disable the bitmap indexes an try again.