DROP COLUMN raises ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Today I came across with the strange error on developing environment , not in production fortunately 🙂

I have a table

create table ONLINECREDIT.MERCHANTS
(
id NUMBER(9) not null,
PASSWORD2 VARCHAR2(20)
)

I was trying to drop column:

alter table ONLINECREDIT.MERCHANTS drop  COLUMN PASSWORD2;

got error:

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

1. ONLINECREDIT.MERCHANTS exists ,
2. Table name is not case sensitive
3. There is not trigger on the column, that may cause the problem.. no system trigger, I mean after drop, before drop or after ddl trigger should be causing the problem..

so what else?

I did the following:

alter table ONLINECREDIT.MERCHANTS add (test number);

alter table ONLINECREDIT.MERCHANTS drop column test;

And it works.. so newly added columns can be dropped. So there is some problem with system tables…and YES it is true!

Let’s find:

–We are planning to trace our session. For easily finding trace file , run the following:

ALTER SESSION SET TRACEFILE_IDENTIFIER = “MY_TEST_SESSION”;

–Enable tracing

begin
dbms_session.session_trace_enable;
END;

–Try to run drop statement again, to get error

alter table ONLINECREDIT.MERCHANTS drop COLUMN PASSWORD2;

–Disable tracing

BEGIN
dbms_session.session_trace_disable;
END;

You can also find your trace file by running the following query, from the same session:

SELECT value
FROM v$diag_info
WHERE name = ‘Default Trace File’;

Result
—————-
/u01/app/oracle/diag/rdbms/lbankdev/LBANKDEV/trace/LBANKDEV_ora_6038_MY_TEST_SESSION.trc

–From OS
–Go to the tracing directory

cd /u01/app/oracle/diag/rdbms/lbankdev/LBANKDEV/trace

— And make trace file readable using tkprof

tkprof output=prof_readme.txt trace=LBANKDEV_ora_6038_MY_TEST_SESSION.trc

–Open file using notepad. You will find the error at the beginning:

The following statement encountered a error during parse:

update radm_mc$ set intcol#=intcol#-:1 where (obj#=:2 or obj#=:3) and intcol#>:4

Error encountered: ORA-00942

–Check if the table exists

SELECT * FROM DBA_objects
WHERE object_name=’RADM_MC$’

Nothing is returned.

Now the solution:

I’ve connected to the production server and generated METADATA for this sys.RADM_MC$  [note table doesn’t contain data.. for other tables containing data this method is not preferable :), it depends ]

— Create table
create table SYS.RADM_MC$
(
obj# NUMBER not null,
intcol# NUMBER not null,
mfunc NUMBER not null,
mparams VARCHAR2(1000),
regexp_pattern VARCHAR2(512),
regexp_replace_string VARCHAR2(4000),
regexp_position NUMBER,
regexp_occurrence NUMBER,
regexp_match_parameter VARCHAR2(10),
mp_iformat_start_byte INTEGER,
mp_iformat_end_byte INTEGER,
mp_oformat_start_byte INTEGER,
mp_oformat_end_byte INTEGER,
mp_maskchar_start_byte INTEGER,
mp_maskchar_end_byte INTEGER,
mp_maskfrom INTEGER,
mp_maskto INTEGER,
mp_datmask_mo INTEGER,
mp_datmask_d INTEGER,
mp_datmask_y INTEGER,
mp_datmask_h INTEGER,
mp_datmask_mi INTEGER,
mp_datmask_s INTEGER
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index SYS.I_RADM_MC1 on SYS.RADM_MC$ (OBJ#)
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index SYS.I_RADM_MC2 on SYS.RADM_MC$ (OBJ#, INTCOL#)
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

and run on developing database.. I did it because this table doesn’t contain data. It is empty.

So after creating this system table on developing DB , I was able to drop the column.

alter table ONLINECREDIT.MERCHANTS drop  COLUMN PASSWORD2;