DROP COLUMN raises ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist
June 26, 2015 Leave a comment
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;