Re: Missing Grant on Package Body

So I changed my test to access sys.obj$ - and didn't get a trace file at all.
So I guess the OP may be lucky hand find that the code is referencing a table that doesn't exist in the schema where it's supposed to be, rather than a table that does exist but without the necessary privileges granted.

Regards
Jonathan Lewis

On Thu, 11 Jul 2024 at 15:38, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Yes, my table didn't exist
> Test1 - was a non-qualified table name
> Test2 - was a qualified table name that didn't exist in the referenced
> schema
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Thu, 11 Jul 2024 at 15:19, Mikhail Velikikh <mvelikikh_at_gmail.com>
> wrote:
>
>> Ok, I found where it is coming from. It is the function qcsdump942.
>> It didn't produce any output in my case because I used an existing table
>> for which a user has no privileges.
>> Once I started using a table that does not exist at all, I started
>> getting those messages per OERR: ORA-942 "table or view does not exist"
>> Reference Note (Doc ID 18536.1)
>> I assume that you are using a non-existent table rather than an existing
>> one.
>>
>> *-- NO OUTPUT IN THE TRACE FILE WHEN AN EXISTING TABLE IS USED*
>> SQL> select * from tc_table.t_select;
>> select * from tc_table.t_select
>> *
>> ERROR at line 1:
>> ORA-00942: table or view does not exist
>>
>>
>> SQL> !less /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_80560.trc
>> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_80560.trc: No such
>> file or directory
>>
>> *-- THE OUTPUT IS PRODUCED WHEN A NON-EXISTENT TABLE IS USED*
>> SQL> select * from tc_table.no_such_table;
>> select * from tc_table.no_such_table
>> *
>> ERROR at line 1:
>> ORA-00942: table or view does not exist
>>
>>
>> SQL> !less /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_80560.trc
>>
>>
>>
>> On Thu, 11 Jul 2024 at 15:04, Mikhail Velikikh <mvelikikh_at_gmail.com>
>> wrote:
>>
>>> It can be seen from the output I provided previously that I searched for
>>> the word signal too.
>>>
>>>> egrep -iw 'signal|tc_table'
>>>> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58350.trc
>>>
>>>
>>> The `-w` flag was used because there were multiple "ERROR SIGNALLED"
>>> lines such as one below:
>>>
>>>> CALL TYPE: call ERROR SIGNALED: no COMPONENT: PLSQL_Infrastructure
>>>>
>>> Even after removing the `-w` flag or using the exact command you were
>>> using I am not getting any hits on 19.23 with my specific test case.
>>> Which means that there are no such lines in my environment.
>>>
>>> [oracle_at_myhostname ~]$ egrep -i 'signal'
>>> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58350.trc | grep -i
>>> about
>>> [oracle_at_myhostname ~]$ egrep -nT -A+1 "about to signal"
>>> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58350.trc
>>> [oracle_at_myhostname ~]$
>>>
>>> On Thu, 11 Jul 2024 at 14:47, Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>> The "Name" is restricted to the name of the table and doesn't include
>>>> the schema, and I think tc_table is your schema.
>>>> I should have said I was using 19.11; and my choice on egrep was
>>>>
>>>> egrep -nT -A+1 "about to signal" or19_ora_23101_level1.trc
>>>> 5154 :about to signal 942
>>>> 5155 -Name: FAIL1
>>>>
>>>> It would be necessary to search the rest of the file for the object
>>>> name to find the SQL, and that might be messy given the way the text would
>>>> be arbitrarily cut into 16 byte pieces.
>>>>
>>>>
>>>> Regards
>>>> Jonathan Lewis
>>>>
>>>>
>>>>
>>>> On Thu, 11 Jul 2024 at 13:51, Mikhail Velikikh <mvelikikh_at_gmail.com>
>>>> wrote:
>>>>
>>>>> there are no such lines in 19.23 for my sample test case using the
>>>>> default DB parameters.
>>>>> alert log:
>>>>> 2024-07-11T12:46:05.314540+00:00
>>>>> PDB(3):Errors in file
>>>>> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58350.trc:
>>>>> ORA-00942: table or view does not exist
>>>>>
>>>>> egrep -iw 'signal|tc_table'
>>>>> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_58350.trc
>>>>> 7F6E37B25750 4F524620 4354204D 4241545F 542E454C [ FROM TC_TABLE.T]
>>>>> 7F6E37FBEE40 4F524620 4354204D 4241545F 542E454C [ FROM TC_TABLE.T]
>>>>>
>>>>> Best regards,
>>>>> *Mikhail Velikikh*
>>>>>
>>>>>
>>>>>
>>>>> On Thu, 11 Jul 2024 at 13:44, Jonathan Lewis <jlewisoracle_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>>
>>>>>> This may be version-dependent and feature/parameter dependent, but
>>>>>> even dumping an errorstack (on 942) at level 1 I found the following pair
>>>>>> of consecutive lines in my trace file:
>>>>>>
>>>>>> about to signal 942
>>>>>> Name: XXXXX
>>>>>>
>>>>>> (where xxxxx was the name of the missing / unavailable object)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regards
>>>>>> Jonathan Lewis
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Thu, 11 Jul 2024 at 13:01, Mikhail Velikikh <mvelikikh_at_gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>
>>>>>>> If you want to pinpoint the exact object(s) causing ORA-942, then
>>>>>>> you can use the errorstack event as Mladen mentioned.
>>>>>>> Level 2 would be enough to dump the necessary library cache
>>>>>>> structures:
>>>>>>> alter session set events '942 errorstack(2)';
>>>>>>>
>>>>>>>
>>>>>>>>

--
http://www.freelists.org/webpage/oracle-l