Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Implementing Listener Security Oracle
  • Kill a session dynanically using execute immediate Oracle
  • How to specify 2 arch location to avoid any kind of DB hanging. Oracle
  • avail.sh ( find filesystem spae usage) Linux/Unix
  • sid_wise_cursor.sql find open cursor basis on username or SID Oracle
  • Jai Shree Ram Oracle
  • oracle tips… from http://www.bijoos.com/oracle/douknow.htm Oracle
  • Removing first line Linux/Unix
  • block_ident.sql Oracle
  • SQL_PROFILE – I explaination Oracle
  • Processes Parameter decision Oracle
  • Find total file sizes Linux/Unix
  • Jai Shree Ram Oracle
  • All About Trace Fils Oracle
  • Flowers Resize datafiles Oracle

ORA-8031 issue and solution if it is occuring due to truncate.

Posted on 07-Apr-2008 By Admin No Comments on ORA-8031 issue and solution if it is occuring due to truncate.

After doing further research on Oracle errors from metalink, I found following for ORA-8031.

CAUSE:

The data_object_id (seg/obj) stored in the block is different than the

data_object_id stored in the segment header.

EXPLAINATION:

The expected behavior can be that tables are being truncated while a query from

those tables is still in execution. Look if data_object_id is changing for the involved objects while queries are

being executed.

data_object_id is commonly changed by:

truncate table

alter index .. rebuild

alter table .. move

etc.

I manually checked for data_object_id for TRUN.ATS_CTI_EXT_CHECK and found that after each truncate the DATA_OBJECT_ID of this object is changed. So queries that are running at the time of truncate will receive this error.

Solution:

1. Stop the queries while truncate is going on for same table.

2. If above is not possible or difficult to implement, then replace “truncate” with “delete from”. This will result in segment fragmentation. At night time when no one is running query against this table, also plan to truncate it so its data structure will not be fragmented.

Metalink Reference: Note:268302.1

Oracle, SQL scripts

Post navigation

Previous Post: fdisk -l explaination about Primary-Logical-Extended Partitions
Next Post: good linux notes

Related Posts

  • metalink all dynamic view reference notes. Oracle
  • Standby Database Behavior when a Datafile is Resized on the Primary Database Note:123883.1 Oracle
  • Alter procedure auditing Oracle
  • Oracle 10g for solaris 10 Oracle
  • Physical Standby switchover with session active Oracle
  • V$transaction notes for finding XID composition. Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Changing Instance Name ( No DB_NAME) Oracle
  • Find nth max and min. Oracle
  • Finding locked objects Oracle
  • DBMS_Shared_pool pinning triggers Oracle
  • Search and replace pattern Linux/Unix
  • How to collect CPU usage on Linux using Shell script Linux/Unix
  • PLSQL Table Syntax 2 Oracle
  • Good RAC & Standby Notes Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme