Re: Missing Grant on Package Body
Date: Thu, 11 Jul 2024 15:51:14 +0100
Message-ID: <CALe4Hpketw_u1LpnUNQ3O9ARus0bA+vcAid=T4k_RVcNsoeuaA_at_mail.gmail.com>
>
> Yes, my table didn't exist
>
Oracle uses a different code path in this case and it does not produce any
"about to signal/Name" lines in the trace file on 19.23 when an existing
table is used for which a user has no privileges. It could be the same on
19.11 but I don't have any of those.
The error stack approach suggested previously would work in either case.
Sample line, followed by a piece of the SQL that referenced my missing
> object:
>
> 2785 :CALL TYPE: call ERROR SIGNALED: yes COMPONENT: (null)
> 3140 :7F7A2B9C7160 2E317520 6C696166 00000031 00000000 [
> u1.fail1.......]
>
> Do you get anything similar in your level 1 trace?
>
Yes, I do. This excerpt would help if we know what to look for. If we start from scratch having just a package that we cannot compile due to ORA-942, it becomes less helpful.
- VKTM Time Drifts Circular Buffer -----
about to signal 942
Name: FAIL1
- 2024-07-11T15:29:15.476508+01:00 (ORCLPDB(3))
- Error Stack Dump -----
Do you also get two dumps in one?
Yes, I do. Here is an excerpt for an existing table with line numbers:
5276 ----- VKTM Time Drifts Circular Buffer -----
5277
5278 *** 2024-07-11T12:46:05.218938+00:00 (PDB(3))
5279 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0,
level=1, mask=0x0)
5280 ----- Error Stack Dump -----
5281 <error barrier> at 0x7ffd605b9680 placed dbkda.c_at_298
5282 ORA-00942: table or view does not exist
5283 <error barrier> at 0x7ffd605bcc10 placed ph2csql.c_at_765
5284 <error barrier> at 0x7ffd605bf898 placed pci.c_at_897
5285 ----- SQL Statement (None) -----
5286 Current SQL information unavailable - no cursor.
Best regards,
*Mikhail Velikikh*
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-lReceived on Thu Jul 11 2024 - 16:51:14 CEST