Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Some useful Unix Commands Linux/Unix
  • Multiple listeners Oracle
  • ext#.sql Oracle
  • oracle_env_10g_CADEV Linux/Unix
  • Exadata Basics Oracle
  • How to find Unix server conguraiton including Server Model number and underlying disk sub-system ( On Solaris) Linux/Unix
  • online_ts_bkup.sql Oracle
  • cache buffer chain latch Oracle
  • To see mem usage and CPU usage system wide. Linux/Unix
  • Removing Blank lines from file using grep Linux/Unix
  • Oracle Statspack survival Guide Oracle
  • Find total file sizes Linux/Unix
  • How To Transfer Passwords Between Databases (ref note: 199582.1) Oracle
  • In Addition to previous note, following grants needed on PERFSTAT user. Oracle
  • Good Oracle Architecture In Short and point to point 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

  • Formatter Explain plan Output 1 Oracle
  • normal maintenance for exp-imp and renaming table Oracle
  • CPU Core related projections AWS
  • Drop all SPM baselines for SQL handle Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • Goldengate Tutorial 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 (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • 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

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Jai Shree Ram Oracle
  • How to find the real execution plan and binds used in that explain plan in Oracle 10g?? Oracle
  • v$backup.status information Oracle
  • currwait.sql Oracle
  • RAC with RHEL4 and 11g Oracle
  • telnet listening Linux/Unix
  • Important Solaris Commands Linux/Unix
  • Validating ORACLE_SID againt oratab file. Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme