Re: Weird procedure access problem (ORA-00942)

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 4 May 2013 21:46:22 +0000 (UTC)
Message-ID: <pan.2013.05.04.21.46.22_at_gmail.com>

On Sat, 04 May 2013 00:23:43 -0700, neilsolent wrote:

> Hi
>
> User SSF can see and execute procedure SP_GET_SERVER, and has granted
> execute access to user SSF_ADMIN1. However (sqlplus session as
> SSF_ADMIN1):
>
>
> SQL> select * from all_objects where object_name = 'SP_GET_SERVER' and
> object_type = 'PROCEDURE';
>
> OWNER OBJECT_NAME
> ------------------------------ ------------------------------
> SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
> ------------------------------ ---------- --------------
> -------------------
> CREATED LAST_DDL_ TIMESTAMP STATUS T G S ---------
> --------- ------------------- ------- - - -
> SSF SP_GET_SERVER
> 12266 PROCEDURE
> 03-MAY-13 04-MAY-13 2013-05-04:07:57:06 VALID N N N
>
>
> SQL> var c refcursor;
> SQL> exec SSF.SP_GET_SERVER(:c);
> BEGIN SSF.SP_GET_SERVER(:c); END;
>
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist ORA-06512: at
> "SSF.SP_GET_SERVER", line 8 ORA-06512: at line 1
>
>
> What could be wrong here?
>
> thanks - Neil

I would do something like this:

ALTER SESSION SET EVENTS='942 TRACE NAME ERRORSTACK FOREVER, LEVEL 16' and then re-execute procedure. A trace file will be written when the error is encountered. System process ID can be extracted using the following query:
SQL> show parameter user_dump_dest

NAME TYPE VALUE

------------------------------------ ----------- 
------------------------------
user_dump_dest			     string	 /oracle/diag/rdbms/o11/
O11/tra
						 ce

SQL> select p.spid
  2 from v$process p,v$session s
  3 where p.addr=s.paddr and
  4 s.sid=(select sys_context('USERENV','SID') from dual);

SPID

---

2250

With that information, you can quickly locate the desired trace file:

[root_at_medo mgogala]# ls -l /oracle/diag/rdbms/o11/O11/trace/*.trc|grep 2250
-rw-r----- 1 oracle oinstall 7087375 May 4 17:28 /oracle/diag/rdbms/o11/ O11/trace/O11_ora_2250.trc
[root_at_medo mgogala]#

The problem can be located by inspecting the trace:

Machine: x86_64
Instance name: O11
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 2250, image: oracle_at_medo.home.com

• 2013-05-04 17:28:05.983
• SESSION ID:(67.15) 2013-05-04 17:28:05.983
• CLIENT ID:() 2013-05-04 17:28:05.983
• SERVICE NAME:(O11.home.com) 2013-05-04 17:28:05.983
• MODULE NAME:(SQL*Plus) 2013-05-04 17:28:05.983
• ACTION NAME:() 2013-05-04 17:28:05.983

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=16, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=ffuh3a5qharqh) ----- select * from ttt

And here it is: the SQL that has caused the ruckus. You can get trace for almost any oracle errors. You will automatically get trace file for 0060, 0600 and 7445.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

Received on Sat May 04 2013 - 23:46:22 CEST