Oracle error: “data got committed in another/same session, cannot update row.”

Oracle error: “data got committed in another/same session, cannot update row.”

When this error continued to pop up, the only solution that worked for me was this from OTN Discussion Forums:

Just a heads up, if you really need to get that column updated:
1. Copy and edit the value to a safe place such as notepad or another 
 sql developer worksheet, don't try to save in the table.
2. In a sql developer worksheet, make an update query to set the 
 BLOB/CLOB field to NULL
3. Run the update query and commit
4. refresh your table view, the BLOB/CLOB should be NULL now
5. Copy the value back in and commit
This worked very well for me but it must be done in the same sql developer 
instance that is throwing the error. I tried to have a coworker null the 
field and it did not affect my session at all even after refreshing the 
table. Also my coworker's sql developer showed the field to be NULL even 
before I asked them to try to edit it.

Source

Heap Size Exceeds Notification Threshold

Memory Notification: Library Cache Object loaded into SGA
Heap size ** K exceeds notification threshold (8192K)

These are warning messages only which should not cause any problems. These warning messages appear due to new event messaging mechanism and memory manager in 10g Release 2.

In 10g, there is a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

To find out the hidden ‘_kgl_large_heap_warning_threshold’ parameter value in the database:

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%kgl_large_heap%';
Parameter Session Value Instance Value
--------------------------------- ------- -------
_kgl_large_heap_warning_threshold 2097152 2097152

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as “/ as sysdba”)

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
SQL> shutdown immediate
SQL> startup
If using an "old-style" init parameter,edit the init parameter file and add
_kgl_large_heap_warning_threshold=8388608

The default threshold in 10.2.0.1 is 2M. So these messages could show up frequently in some application environments. In 10.2.0.2, the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.

Check your product version:

select   BANNER
from     sys.v_$version;

Source / Reference:

Oracle KEEP and Recycle Cache

Data required by oracle user process is loaded into buffer cache, if it is not already present in cache. Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory. Oracle allows us to use multiple buffers. Using multiple buffers we can control that how long objects should be kept in memory.

Note that in Oracle 10g, the terms are changed to the keep cache and the recycle cache. However, many people still refer to these two as pools. Some of the Oracle documentation still uses the term pool when referring to these caches.

Keep Buffer Pool

Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer. DB_KEEP_CACHE_SIZE initialization parameter is used to create Keep buffer Pool. If DB_KEEP_CACHE_SIZE is not used then no Keep buffer is created. Use following syntax to create a Keep buffer pool of 40 MB.

DB_KEEP_CACHE_SIZE=40M

To pin an object in Keep buffer pool use DBMS_SHARED_POOL.KEEP method.

Recycle Buffer Pool

Blocks loaded in Recycle Buffer pool are immediate removed when they are not being used. It is useful for those objects which are accessed rarely. As there is no more need of these blocks so memory occupied by such blocks is made available for others data. For example if ASM is enabled then available memory can be assigned to other SGA components . Use following syntax to create a Recycle Buffer Pool

DB_RECYCLE_CACHE_SIZE=20M

Default Pool

If an object is not assigned a specific buffer pool then its blocks are loaded in default pool DB_CACHE_SIZE initialization parameter is used to create Default Pool. For more information on Default Pool visit following link,

Database Buffer Cache


BUFFER_POOL value in storage clause of schema objects lets you to assign an object to a specific Buffer pool. Value of BUFFER_POOL can be KEEP,RECYCLE or DEFAULT.

buf_keep_pool.sql

-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
 o.owner owner,
 o.object_name object_name,
 o.subobject_name subobject_name,
 o.object_type object_type,
 count(distinct file# || block#) num_blocks
from
 dba_objects o,
 v$bh bh
where
 o.data_object_id = bh.objd
and
 o.owner not in ('SYS','SYSTEM')
and
 bh.status != 'free'
group by
 o.owner,
 o.object_name,
 o.subobject_name,
 o.object_type
order by
 count(distinct file# || block#) desc
;

select
 'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
 t1,
 dba_segments s
where
 s.segment_name = t1.object_name
and
 s.owner = t1.owner
and
 s.segment_type = t1.object_type
and
 nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
 buffer_pool <> 'KEEP'
and
 object_type in ('TABLE','INDEX')
group by
 s.segment_type,
 t1.owner,
 s.segment_name
having
 (sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;

spool off;

Source / Reference Links:

Indexes on Foreign Keys

Foreign keys produce potentially damaging locking problems if the foreign key columns on the child table are not indexed. Below query lists all of the foreign keys that do not have the appropriate indexes in place on the child table. It shows the foreign key constraints that cause locking problems.

SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
 ||'['||acc.position||'])'||' ***** Missing Index'
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 AND (acc.owner, acc.table_name, acc.column_name, acc.position)
 IN
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position
 FROM all_cons_columns acc, all_constraints ac
 WHERE ac.constraint_name = acc.constraint_name
 AND ac.constraint_type = 'R'
 MINUS
 SELECT table_owner, table_name, column_name, column_position
 FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
 acc.column_name, acc.position;

By creating an index on the foreign key of the child table,  “table-level” locks can be avoided.

Keep in mind that you will often be creating an index on the foreign keys in order to optimize join and queries. However, if you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention. If ever in doubt, it’s often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.

Source / Reference links:

Quick TKPROF

1. Find the session to be monitored

SELECT username, sid, serial#, program
FROM v$session
WHERE username = <User_Name>;

2. Enable SQL Tracing

a, Current Session:

ALTER SESSION SET sql_trace = TRUE;
or
execute dbms_session.set_sql_trace(true);

b, Different Session (as SYSDBA):

execute dbms_system.set_sql_trace_in_session(sid, serial#, sql_trace);
e.g.
execute dbms_system.set_sql_trace_in_session(114, 4667, TRUE);

3. Enable Oracle database to gather statistics

ALTER SYSTEM SET timed_statistics = true;  -- at system level
ALTER SESSION SET timed_statistics = true; -- at session level

4. Formatting the output with TKPROF 

TKPROF inputfile outputfile [OPTIONS]
e.g.
tkprof mydb_ora_11915.trc /tmp/tkprof1.txt SYS=NO

5. Find directory where trace file is generated

SELECT value
FROM v$parameter
WHERE name='user_dump_dest';

6. Identify trace file generated

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid
FROM v$session s,
 v$process p
WHERE p.addr = s.paddr and s.username = <User_Name>;

7. Disable tracing for the session

ALTER SESSION SET sql_trace = TRUE;
OR
EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);