ORA-28665 occured when table single partitioning reorg (2TO) with brspace

[Former Member]

Hello all,

we have a big table that size over than 2 To, partitionned into 12 parts. we want to do an online reorg for a single partition.

at the end of operation we get:

ORA-42012: error occurred while completing the redefinition

ORA-28665: table and partition must have same compression attribute

so i have checked partition configuration and i can see that compress is not activate but is activated for index partition.

i think about one solution, but i don't know if is the good way:

1- delete primary index

2- reorg

3- recreate index

did you think that is a good solution, is not dangerous to delete primary index?

or

is there any option to activate in brspace to skeep compression check?

thank you for your help

[Former Member]

Can you verify the compression settings on the table and partitions?

SQL> select partition_name, table_name, compression, compress_for
     from dba_tab_partitions where table_name = '<table_name>'
SQL> select table_name, compression, compress_for
     from dba_tables where table_name = '<table_name>';

If this looks ok, and you only want to compress the indexes, then you should try to choose cind_only in the compression options for brspace.

Cheers Michael

[Former Member]

Hello Michael,

Thank you for your response.

first i try you query but compress_for not exist in dba_tab_partitions !!!

so i run query without this field

SQL> select partition_name, table_name, compression

2 from dba_tab_partitions where table_name = 'VBOX';

PARTITION_NAME TABLE_NAME COMPRESS

-

---

-

---

-

---

VBOX_201012_PART VBOX DISABLED

VBOX_201101_PART VBOX DISABLED

for the second query

SQL> select table_name, compression

2 from dba_tables where table_name ='VBOX';

TABLE_NAME COMPRESS

-

---

-

---

VBOX

result is compress field blanc

my problem is:

when i do an online reorg i got ORA-28665 because of compress option. so i want to know:

is there any option in brspace to avoid this error or to skeep compress check?

or

if i drop primary index before and create it affter reorg solve the issue?

[Former Member]

It's time to tell us what your Oracle version is.

Column compress_for is there for newer versions only.

And the answer to your question probably also will depend on your version of Oracle and Brtools.

regards

[Former Member]

hello Jeo

i am under oracle Release 10.2.0.2.0 and BRTOOLS 7.10 (41).

Thank you for your help

[Former Member]

Ok, my commands were indeed only for Oracle 11g as this is the supported release for SAP at the moment.

Dropping the primary index while the system is running is not a good idea. Sometimes the unique key constraint is depending on it, and it could be possible that duplicate values are generated. Secondary indexes can be dropped, but if they are heavily used you should be careful as well.

A much better solution would be to temporarily uncompress that index, do the reorg and recompress the index afterwards if needed.

Still it would be interesting if the global and partition setting for compression on the index are consistent. Maybe you can adapt my statements for the table to the index. Use dba_indexes and dba_ind_partitions instead. Only query the compression column.

Cheers Michael