CODEWITHSUNDEEP
javasqloracle-database
Sethiel
Sethiel

Reputation: 312

"Table or view does not exist" on create table

I got a very strange exception from my code recently:

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

while executing create table ST_UTEST2_DATE (value varchar(100) not null unique,replacement varchar(100) not null)
            at XXX
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
            at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
            at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
            at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
            at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
            at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
            at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1037)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
            at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
            at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
            at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1088)
            ... 8 more

The table really does not exist (both before and after this launch). That is usually the case when I want to execute "create table". I have no idea how can I get "table or view does not exist" while creating a table.

This exception was thrown exactly once. The same code with the exactly same environment (db state) executed just fine many times. There are multiple threads that execute this code (check the existence of this table and if it doesn't exist, it creates it), each with its own connection. But the fragment where the check and creation occurs is synchronized - the execution of the table certainly doesn't happen simultaneously from multiple threads, and the check and creation are made atomic.

Any ideas what could produce an exception like this (wild guesses including) are welcome.

The database is Oracle 10g.

Upvotes: 2

Views: 5107

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30775

My guess would be a schema-level trigger that tried to perform some kind of auditing operation and failed.

One possible scenario:

  • user1 owns a logging package defined as AUTHID CURRENT_USER (granted to user2) that inserts into a table inside the user1 schema, without explicitly specifying the table schema
  • user2 creates a schema-level trigger for logging all DDL statements inside the user2 schema
  • user2 tries to create a table; this fails, since the trigger fires the logging procedure, which looks for the log table inside the user2 schema and fails (since the table does not exist)

SQL statements for reproducing this (assuming User test1 already exists and has DBA privileges):

-- create the log table
CREATE TABLE ddl_log as 
SELECT ora_sysevent,
             ora_dict_obj_owner,
             ora_dict_obj_name,
             USER as ddl_user,
             SYSDATE as ddl_date
        FROM DUAL
WHERE 1=0;        

-- create the logging package
CREATE OR REPLACE PACKAGE pck_log_ddl AUTHID CURRENT_USER is

  PROCEDURE log_ddl;

END pck_log_ddl;
/
create or replace package body pck_log_ddl is

  procedure log_ddl is
  begin
    INSERT INTO ddl_log
      SELECT ora_sysevent,
             ora_dict_obj_owner,
             ora_dict_obj_name,
             USER,
             SYSDATE
        FROM DUAL;
  end;

end pck_log_ddl;

-- create the second user
create user test2 identified by test2;

grant connect, resource, create table, create trigger to test2;
grant execute on pck_log_ddl to test2;

connect test2@mydb
-- create the schema-level trigger
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
  test1.pkg_log_ddl.log_ddl;
END bcs_trigger;

 -- try creating a table => ORA-00604 / ORA-00942
create table ST_UTEST2_DATE (
   value varchar(100) not null unique,
   replacement varchar(100) not null
);

Upvotes: 0

Srini V
Srini V

Reputation: 11355

This is frequent if the data dictionary is corrupt. Read here more (Not exactly on create table, but this can be the same issue. Try the solution provided there). You may have to execute the catqueue.sql script. Before running any script on the data dictionary, you should have a backup of your database. Refer the same discussion here

Upvotes: 2

Related Questions