RE: Ora-8103 error

DDL is absolutely the prime suspect.
But note bug that we had to patch on 19.6 recently - 31182793 but that is meant to be fixed in 19.8 – ORA-8103 whilst running queries against table with EHCC enabled.

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Sayan Malakshinov<mailto:xt.and.r_at_gmail.com> Sent: 28 June 2021 12:14
To: Andy Sayer<mailto:andysayer_at_gmail.com> Cc: Pap<mailto:oracle.developer35_at_gmail.com>; Oracle L<mailto:oracle-l_at_freelists.org> Subject: Re: Ora-8103 error

Hi Pap,

I would try to get more info about the problem: alter system set events 'kg_event[8103] [sql: ...] errorstack(3) incident(inc8103)'; then you can create and show an incident package using adrci

On Mon, Jun 28, 2021 at 2:00 PM Andy Sayer <andysayer_at_gmail.com<mailto:andysayer_at_gmail.com>> wrote: The additional logging is only going to be a problem if you have extremely frequent DDL going on. If that is the case then you’ll find the problem relatively soon and will be able to turn it off again afterwards. I would be confident that any performance impact is going to be negligible.

Be on the lookout for truncate as well as drops.

The other option is to check the contents of dba_objects, it will have last DDL times at both the table and partition level.

In order to work around it, you may find that you are able to rewrite the failing query so that it is always only targeting specific partitions that will not be dropped/truncated, although this could be tricky.

Hope that helps,
Andrew

On Mon, 28 Jun 2021 at 11:51, Pap <oracle.developer35_at_gmail.com<mailto:oracle.developer35_at_gmail.com>> wrote: This question came from a friend in a forum for version 19.9.0.0.0 of oracle , stating, one of the SELECT query is failing with ora-08103 object no longer exists error intermittently and rerun makes that query finish without any error. And the query does have some partition tables used but they aren't able to find any partition maintenance activities during this period. But there must be some DDL happening and thus the SELECT query is failing. And it might be that the DDL is too quick that it's not logged in ASH/AWR views. So is there any method to get hold of the DDL statement and its source ? I saw enabling DDL_LOGGING at the system level is one approach, but thinking if it's having any negative sides if it's set at system level for a long duration and if any other possible approach exists?

Regards

Pap

--

Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=04%7C01%7C%7Cabb9d52946334cdacc2508d93a25f3cc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637604756949108518%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=C1a4jDejY5pQKlXznE8tyNH9DG23xkFy2aVrHbb8NCE%3D&reserved=0>

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 28 2021 - 13:18:35 CEST