In a datawarehouse environment (11.1.0.7 on RedHat) with compressed tablespaces, tried to drop some columns in the following way:

alter table DWH.TEST_TABLE set unused column COLUMN_SIX;
alter table DWH.TEST_TABLE drop unused columns;

This took a very long time, let it run for 24 hours (!), noticed that the undo-tablespace (80GB…) completely was filled up. Then I killed the command. No penalty, everything was back to normal.
So my intention was to use the addition ‘checkpoint 100000’ , and came across a very old but good explanation at Tom’s site here.

The next sentence I read  too (should have scared the hell out of me..):

if this statement is interrupted after a checkpoint has been applied, the table remains in an unusable state. While the table is unusable, the only operations allowed on it are DROP TABLE, TRUNCATE TABLE, and ALTER TABLE DROP COLUMNS CONTINUE.  Submitting DROP COLUMNS CONTINUE this statement while the table is in a valid state results in an Error.

So I typed in the command…:

alter table DWH.TEST_TABLE drop unused columns checkpoint 100000;

And after a while – several hours I believe:

ERROR at line 1:
ORA-00600: internal error code, arguments: [17016], [0x12CB1C820], [], [], [],

‘Alter table drop columns continue’  gave the same result. Completely stuck. A select of the table : noway.

No happy customer by the way :-(

Gave this problem to Oracle with the following testcase (don’t try the following at home in your production environment on 11.1.0.7, or 11.2 I understood):

CREATE TABLESPACE TS_TEST DATAFILE ‘<directory>/ts_test_data01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 500M MAXSIZE 64000M
DEFAULT COMPRESSION FOR ALL OPERATIONS
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO;
/

create user dwh identified by incident
default tablespace ts_test
temporary tablespace temp
quota unlimited on ts_test
/

grant connect to dwh;
grant resource to dwh;
grant unlimited tablespace to dwh;

CREATE TABLE “DWH”.”TEST_TABLE”
(    <column1>,
<column2>
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE “TS_TEST”
PARTITION BY RANGE (“UMS_ANSDATE”) INTERVAL (NUMTODSINTERVAL(1,’DAY’)) STORE IN (“TS_TEST”)
(PARTITION “DWH_EUR_TEST_PART1”  VALUES LESS THAN (TO_DATE(‘ 2009-02-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “TS_TEST” COMPRESS FOR ALL OPERATIONS ,
PARTITION “DWH_EUR_TEST_PART2”  VALUES LESS THAN (TO_DATE(‘ 2009-02-03 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “TS_TEST” COMPRESS FOR ALL OPERATIONS ,
PARTITION “DWH_EUR_TEST_PART3”  VALUES LESS THAN (TO_DATE(‘ 2009-02-04 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
TABLESPACE “TS_TEST” COMPRESS FOR ALL OPERATIONS )
PARALLEL 6 ;

Imported some data (however not necessary, Oracle could reproduce this with no data).

alter table DWH.TEST_TABLE set unused column COLUMN_SIX;

alter table DWH.TEST_TABLE drop unused columns checkpoint 100000;

alter table DWH.TEST_TABLE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17016], [0x12CB1C820], [], [], [],
[], [], [], [], [], [], []

The addition ‘checkpoint 100000’ is the bad guy by the way.

Oracle added a new bug (with my name on it..) : Bug 9285665: ORA-600 [17016] ON ALTER TABLE DROP UNUSED COLUMNS

After a while Oracle came up with a workaround. However this workaround is merely a repair-action, it  is allright by me. It will set your status to the point where the columns were set as ‘unused’. So you may try another ‘drop unused columns’ again. The workaround/repair Oracle suggested, worked in my case (thank you Ian Jones and development team….):

1. Please ensure you take a full backup of the database before continuing, as making
direct modifications to the data dictionary tables is highly dangerous and if not
performed correctly, can leave the entire database corrupt and beyond repair.

2. Once a backup has been taken, identify the object number of the problem table, e.g.:
connect / as sysdba
select object_id, object_name, owner, object_type from dba_objects
where owner = ‘DWH’ and object_type = ‘TABLE’ and
object_name = ‘TEST_TABLE’;

3. Using the object_id identified for the table above, perform the following to patch the
data dictionary:
connect / as sysdba
shutdown immediate
startup restrict
update tab$ set flags=flags-32768 where obj#=<OBJECT_ID from 2 above>;
— This should update one row only!
— If not, then issue ROLLBACK and abort the process at this stage, otherwise
commit;
shutdown abort;
startup
connect dwh/<password>
alter table dwh.test_table  drop unused columns;
exit

The statement with ‘alter table… drop unused columns’ I did not execute. You need a hell of an undo-tablespace , and I’ve got only 80GB (!) at my disposal at this time. And if there’s no fix or workaround for the ‘checkpoint’ -statement, the repair-action will do…

Does this bug appear in 11g release 2? I ran the testcase in my own test-environment (VMware) : 11.2.0.1  on Oracle Enterprise Linux, and I could not reproduce the error.  But then.. update from oracle in the service-request:

Development have confirmed it still occurs in the main code line, which now relates to version 12.1. Hence the fact that it didn’t occur in your test is probably just luck and so I wouldn’t recommend the customer tries this in their production instance, until such time as development have identified and fixed this problem.

In the mean-time customer queries the table and suddenly:

Open EUR_CURSOR failed with:

ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [],

After this I ran the following command :
– ‘analyze table OPS$DWHPOPR_TEST.DWH_EURTABLE validate structure online;’
Result:
ERROR at line 1:
ORA-01498: block check failure – see trace file

This is a software – block corruption (hardware corruption = ora-1578), you don’t want this….  Oracle can’t confirm that there is a relationship between the ‘drop unused columns’ and this block corruption.

Had an action plan to repair this with DBMS_REPAIR (couldn’t use RMAN to repair blocks), but customer has lost faith in the stability of the table (and a little bit in Oracle) and will probably decide on short notice to drop the table, re-create and populate it. After all it’s a test-table, should be online within a few days.