Oracle Database Discussions

Dear all,

10gr2p4 on solaris

when am truncating a table am getting the below error :

SQL> truncate table ccm_load_table;
truncate table ccm_load_table
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 50

I checked the table and it is there in the schema. I tried recreating the table and moved the table to different schema and tried truncating it. but helpless.

The table is empty .. tried loading data and truncate ,, but failed with the same error ..

When I traced the session, i found the below :

The following statement encountered a error during parse:

SELECT COUNT(*) FROM ALL_TABLES  WHERE OWNER = 'MDSYS' AND TABLE_NAME = 'RDF_MODEL$'

Error encountered: ORA-00942

the below content of trace file got some info about this

    loop
      fetch vcur into rdt, rsid;
      exit when vcur%NOTFOUND;
      if instr(rdt, '.') = 0 then
        rdt := dictionary_obj_owner || '.' || rdt;
      end if;
      stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
      begin
        execute immediate stm2 using rsid;
      exception
        when others then
          if SQLCODE <> -942 then
            raise;
          end if;
      end;
    end loop;
    stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE ' ||
            ' WHERE SDO_OWNER = :name AND GEORASTER_TABLE_NAME = :2';
    EXECUTE IMMEDIATE stmt USING dictionary_obj_owner, dictionary_obj_name;
  END IF;
  stmt := 'SELECT COUNT(*) FROM ALL_TABLES ' ||
          ' WHERE OWNER = ''MDSYS'' AND TABLE_NAME = ''RDF_MODEL$'' ';
  EXECUTE IMMEDIATE stmt INTO cnt;
  if (cnt = 1) then
  begin
    cnt := 0;
    stmt := 'SELECT count(*) FROM MDSYS.RDF_MODEL$ ' ||
            ' WHERE OWNER = :1 AND TABLE_NAME = :2';
     EXECUTE IMMEDIATE stmt INTO cnt USING dictionary_obj_owner,
                                           dictionary_obj_name;
    if (cnt > 0)  then
      stmt := 'SELECT model_name, model_id FROM MDSYS.RDF_MODEL$ ' ||
            ' WHERE OWNER = :1 AND TABLE_NAME = :2';
       EXECUTE IMMEDIATE stmt INTO model, m_id USING dictionary_obj_owner,
                                             dictionary_obj_name;
       begin
         EXECUTE IMMEDIATE
            'BEGIN MDSYS.RDF_APIS_INTERNAL.NOTIFY_MODEL_DML('''||model||''', ''DEL''); END;';
       exception
        when others then NULL;
       end;
        stmt := ' DELETE  MDSYS.RDF_LINK$ WHERE  model_id = '||to_char(m_id) ;
        EXECUTE IMMEDIATE stmt;
    end if;
     exception

Any idea what am missing ?.. as this is a normal table with out index,constraints,triggers ?

Kai