Thursday 22 December 2016

ORA-8103 (or ORA-600) Partition table after Truncate table

Hi DBA-Mates,
We faced issue like ORA-8103 (or ORA-600) Partition table after Truncate table.
What is this issue and what is solution for this.
As always I would like to share my experiences in form of blog here. So, I am very eager to share it.
We will discuss about _part_access_version_by_number parameter and details of this parameter in oracle database.

Please find the below details:

"_part_access_version_by_number" = FALSE , what does this command?

ORA-8103 (or ORA-600) -- Partition table after Truncate table . This is bug also Bug 19689979
The release is 11.2.0.4 or above

For example of getting this issue or bug, Please find the below details:
 1-. TRUNCATE TABLE abc;
 2-. Access the table:
        select * from abc;
       --> Error is produced.

Here, we are getting above error as mentioned i.e. ORA-8103 (or ORA-600) -- Partition table after Truncate table

Solutions:
"_part_access_version_by_number "
This is hidden undocumented parameters in 12c which use version numbers to access versioned objects for partitioning.

Using "_part_access_version_by_number" = FALSE will revert back to the 11.2.0.3 partitioned handling behaviour and will avoid the problem.

ORA-00600 [QESMAGETTBLSEG1]
closed as a duplicate of ORA-8103 RUNNING SELECT ON PARTITION TABLE ON RAC NODE
1). Either flush the shared pool.

   - OR -

Restart the instance with the hidden parameter "_part_access_version_by_number" set to FALSE.

(RAC sites should restart *all* instances with this parameter set to the same value).


Some more useful links:
Hope this will be useful and helpful. Please let us know for any concerns.
Thanks,

3 comments:

  1. I am extremely impressed with your writing skills as well as with
    the layout on your weblog. Is this a paid theme or did
    you modify it yourself? Either way keep up the nice quality writing, it's rare to
    see a nice blog like this one nowadays.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you a lots for your comment.
      It is a just simple blog theme.

      Regards,
      ora-data Team.

      Delete
  2. An intriguing discussion is worth comment. I do think that you ought to publish more about this subject,
    it might not be a taboo subject but typically folks don't
    discuss these subjects. To the next! Many thanks!!

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.