HomeOracle

Addressing the Drop Column Bug in Oracle 18c and 19c

David Fitzjarrell
By David Fitzjarrell

The road of progress can be rough at times. Oracle versions 18 and 19 are no exception. Up until version 18.x Oracle had no issues with marking columns as unused and eventually dropping them. Given some interesting circumstances, the latest two Oracle versions can throw ORA-00600 errors when columns are set as unused and then dropped. The conditions that bring about this error may not be common but there are a large number of Oracle installations across the globe and it is very likely someone, somewhere, will encounter this bug.

The tale begins with two tables and a trigger:


create table trg_tst1 (c0 varchar2(30), c1 varchar2(30), c2 varchar2(30), c3 varchar2(30), c4 varchar2(30));
create table trg_tst2 (c_log varchar2(30));

create or replace trigger trg_tst1_cpy_val
after insert or update on trg_tst1
for each row
begin
        IF :new.c3 is not null then
                insert into trg_tst2 values (:new.c3);
        end if;
end;
/

Data is inserted into table TRG_TST1 and, provided the conditions are met, data is replicated to table TRG_TST2. Two rows are inserted into TRG_TST1 so that only one of the inserted rows will be copied to TRG_TST2. After each insert table TRG_TST2 is queried and the results displayed:


SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c3) values ('Inserting c3 - should log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c4) values ('Inserting c4 - should not log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus >

Now the ‘fun’ begins — two columns in TST_TRG1 are marked ‘unused’ and then dropped, and table TST_TRG2 is truncated. The inserts into TST_TRG1 are executed again, but this time the dreaded ORA-00600 errors are produced. To see why these errors occur, the status of the trigger is reported from USER_OBJECTS:


SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > --  ===================================
SMERBLE @ gwunkus > --  Drop some columns in two steps then
SMERBLE @ gwunkus > --  truncate trg_tst2 and repeat the test
SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > --  ORA-00600 errors are raised
SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > --  The trigger is not invalidated and
SMERBLE @ gwunkus > --  thus is not recompiled.
SMERBLE @ gwunkus > --  ===================================
SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > alter table trg_tst1 set unused (c1, c2);

Table altered.

SMERBLE @ gwunkus > alter table trg_tst1 drop unused columns;

Table altered.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > select object_name, status from user_objects where object_name in (select trigger_name from user_triggers);


OBJECT_NAME                         STATUS
----------------------------------- -------
TRG_TST1_CPY_VAL                    VALID

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > truncate table trg_tst2;

Table truncated.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c3) values ('Inserting c3 - should log');

insert into trg_tst1(c3) values ('Inserting c3 - should log')
            *
ERROR at line 1:
ORA-00600: internal error code, arguments: [insChkBuffering_1], [4], [4], [], [], [], [], [], [], [], [], []


SMERBLE @ gwunkus > select * from trg_tst2;

no rows selected

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c4) values ('Inserting c4 - should not log');

insert into trg_tst1(c4) values ('Inserting c4 - should not log')
            *
ERROR at line 1:
ORA-00600: internal error code, arguments: [insChkBuffering_1], [4], [4], [], [], [], [], [], [], [], [], []


SMERBLE @ gwunkus > select * from trg_tst2;

no rows selected

SMERBLE @ gwunkus >

The issue is that, in Oracle 18c and 19c, the ‘drop unused columns’ action does NOT invalidate the trigger leaving it in a ‘VALID’ state and setting the next transactions up for failure. Since the trigger was not recompiled at the next call the original compilation environment is still in effect, an environment that includes the now-dropped columns. Oracle can’t find columns C1 and C2, but the trigger is still expecting them to exist, thus the ORA-00600 error. My Oracle Support reports this as a bug:


Bug 30404639 : TRIGGER DOES NOT WORK CORRECTLY AFTER ALTER TABLE DROP UNUSED COLUMN.

and reports that the cause is, in fact, the failure to invalidate the trigger with the deferred column drop.

So how to get around this issue? One way is to explicitly compile the trigger after the unused columns are dropped:


SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > -- Compile the trigger after column drops
SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > alter trigger trg_tst1_cpy_val compile;

Trigger altered.

SMERBLE @ gwunkus >

With the trigger now using the current environment and table configuration the inserts function correctly and the trigger fires as expected:


SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > -- Attempt inserts again
SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > insert into trg_tst1(c3) values ('Inserting c3 - should log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c4) values ('Inserting c4 - should not log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus >

Another way exists to get around this issue; Don’t mark the columns as unused and simply drop them from the table. Dropping the original tables, recreating them and executing this example with a straight column drop shows no sign of an ORA-00600, and the trigger status after the column drop proves that no such errors will be thrown:


SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > drop table trg_tst1 purge;

Table dropped.

SMERBLE @ gwunkus > drop table trg_tst2 purge;

Table dropped.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > --  ===================================
SMERBLE @ gwunkus > --  Re-run the example without marking
SMERBLE @ gwunkus > --  columns as 'unused'
SMERBLE @ gwunkus > --  ===================================
SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > create table trg_tst1 (c0 varchar2(30), c1 varchar2(30), c2 varchar2(30), c3 varchar2(30), c4 varchar2(30));

Table created.

SMERBLE @ gwunkus > create table trg_tst2 (c_log varchar2(30));

Table created.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > create or replace trigger trg_tst1_cpy_val
  2  after insert or update on trg_tst1
  3  for each row
  4  begin
  5  	     IF :new.c3 is not null then
  6  		     insert into trg_tst2 values (:new.c3);
  7  	     end if;
  8  end;
  9  /

Trigger created.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c3) values ('Inserting c3 - should log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c4) values ('Inserting c4 - should not log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > --  ===================================
SMERBLE @ gwunkus > --  Drop some columns,
SMERBLE @ gwunkus > --  truncate trg_tst2 and repeat the test
SMERBLE @ gwunkus > --
SMERBLE @ gwunkus > --  No ORA-00600 errors are raised as
SMERBLE @ gwunkus > --  the trigger is invalidated by the
SMERBLE @ gwunkus > --  DDL.  Oracle then recompiles the
SMERBLE @ gwunkus > --  invalid trigger.
SMERBLE @ gwunkus > --  ===================================
SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > alter table trg_tst1 drop (c1,c2);

Table altered.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > select object_name, status from user_objects where object_name in (select trigger_name from user_triggers);

OBJECT_NAME                         STATUS
----------------------------------- -------
TRG_TST1_CPY_VAL                    INVALID

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > truncate table trg_tst2;

Table truncated.

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c3) values ('Inserting c3 - should log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus > 
SMERBLE @ gwunkus > insert into trg_tst1(c4) values ('Inserting c4 - should not log');

1 row created.

SMERBLE @ gwunkus > select * from trg_tst2;

C_LOG
------------------------------
Inserting c3 - should log

SMERBLE @ gwunkus >

Oracle versions prior to 18c behave as expected, with the deferred column drop correctly setting the trigger status to ‘INVALID’:


SMARBLE @ gwankus > select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SMARBLE @ gwankus >
SMARBLE @ gwankus > alter table trg_tst1 set unused (c1, c2);

Table altered.

SMARBLE @ gwankus > alter table trg_tst1 drop unused columns;

Table altered.

SMARBLE @ gwankus >
SMARBLE @ gwankus > select object_name, status from user_objects where object_name in (select trigger_name from user_triggers);

OBJECT_NAME			    STATUS
----------------------------------- -------
TRG_TST1_CPY_VAL		    INVALID

SMARBLE @ gwankus >

How the columns are dropped in versions older than 18c makes no difference as any triggers on the affected table will be rendered invalid. The next call to any trigger on that table will result in an ‘automatic’ recompile, setting the execution environment properly (meaning the missing columns in the affected table won’t remain in the execution context).

It isn’t likely that a production database will undergo column drops without first making such changes in a DEV or TST database. Unfortunately testing inserts after columns are dropped may not be a test that is executed after such changes are made and before the code is promoted to PRD. Having more than one person testing the after-effects of dropping columns would seem to be an excellent idea, since, as the old adage attests, ‘Two heads are better than one.’ The more the merrier in a testing situation so that many avenues of possible failure can be presented and executed. The extra time taken to more thoroughly test a change means a less likely chance of unforeseen errors seriously affecting, or stopping, production.

# # #

See articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.
Previous article
Introduction to Azure SQL Database Auto-Failover Groups
Next article
Verifying Object Exists and Dropping Object with One Statement

Related Articles

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles

DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.

Advertisers

Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.

Advertise with Us

Menu

Our Brands

Property of TechnologyAdvice.
© 2024 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.