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;

 

Understanding sysobjects table in SQL Server

System tables and views are very useful objects that may be queried in many times by database background processes itself or by DBAs. These objects sometimes contain columns that are not so easy to understand what they are for. Here, in this post I will discuss  mostly used content of SYSOBJECT table.

Take into the consideration, that updating or deleting rows in these tables are not recommended. Background processes do it for you.

The most useful columns in SYSOBJECT table are name, id, xtype, uid, parent_obj, crdate.

As you can guess name column saves the name of the database object. Id column is the unique number for this object. xtype shows the type of this object, where:

  • AF = Aggregate function (CLR)
  • C = CHECK constraint
  • D = Default or DEFAULT constraint
  • F = FOREIGN KEY constraint
  • L = Log
  • FN = Scalar function
  • FS = Assembly (CLR) scalar-function
  • FT = Assembly (CLR) table-valued function
  • IF = In-lined table-function
  • IT = Internal table
  • P = Stored procedure
  • PC = Assembly (CLR) stored-procedure
  • PK = PRIMARY KEY constraint (type is K)
  • RF = Replication filter stored procedure
  • S = System table
  • SN = Synonym
  • SQ = Service queue
  • TA = Assembly (CLR) DML trigger
  • TF = Table function
  • TR = SQL DML Trigger
  • TT = Table type
  • U = User table
  • UQ = UNIQUE constraint (type is K)
  • V = View
  • X = Extended stored procedure

I’ve got this list from the internet,most of them are useful to know.

uid                – saves the user id, which is the owner of this object.
parent_obj – Id of the parent object. For example, for constraint parent_obj will be the ID
                        of the table, on which this constraint was defined.
crdate         -Object creation date.
…

There are several other tables, which shows the user objects:

–Shows the table columns owned by the current user.

select *
from information_schema.columns

–Shows the tables owned by the current user.

select *
from information_schema.tables

–Shows the list of all functions and procedures owned by the current user.

select *
from information_schema.routines

Real World Scenario

One day, the person came to me and asked if it was possible to find the table name when you just know the column name. I told him yes Smile and wrote the following simple query:

select a.name as columnName
           ,b.name as tablename
from SYSCOLUMNS as a,SYSOBJECTS as b
where a.ID=b.ID
and a.name=’column_name’

I hope this post was useful for you…