Loop declare cursor 数字字符隐式转化 commit DDL nowait

Applies to:

Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Update one specific partition's data, then try to truncate a different partition in another session, subsequently the update operation fails with ORA-08103.

Create table tab1 ( col1 CHAR(10), col2 CHAR(10), col3 CHAR(4))
   PARTITION BY LIST (col3)
   (   PARTITION "P0000" VALUES ('0'),
       PARTITION "P0001" VALUES ('1'),
       PARTITION "P0002" VALUES ('2'),
       PARTITION "P0003" VALUES ('3')
   );

set serveroutput on
declare
   i int;
begin
   i:=0;
   while (i<10000) loop
   insert into tab1 values(i,i, trunc(dbms_random.value(0,4)));
   i:=i+1;
   commit;
   end loop;
end;
/

set linesize 400
select col3 ,count(*) from  tab1 group by col3;

COL3   COUNT(*)
---- ----------
0         18465
1         18676
2         18621
3         18764


-- Session 1: update the data in partition P0001.
set serveroutput on
declare
   i int;
begin
   i:=0;
     while (i<100000) loop
     update tab1 set col2=col2-1 where col3=1;
     i:=i+1;
     commit;---如果这里没有commit， trucate语句就是resource busy，说明trucate 会等commit的间隙完成，从而导致这条语句做不下去了。
     end loop;
  end;
  /

-- Session 2: truncate partition P0003.
ALTER table tab1 TRUNCATE partition(P0003);

-- Session 1
SQL> declare
   i int;
begin
   i:=0;
     while (i<100000) loop
     update tab1 set col2=col2-1 where col3=1;
     i:=i+1;
     commit;
     end loop;
  end;
  /SQL>   2    3    4    5    6    7    8    9   10   11
declare
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 6

Changes

 None.

Cause

The partition key is used to compare with a number constant, however the partition key's data type is CHAR instead of NUMBER.
Therefore, data conversion acts, causes partition pruning not to work, then full table scan is executed.

SQL> set autotrace traceonly exp
SQL> update tab1 set col2=col2-1 where col3=1;

18676 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 4220179874

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      | 11418 |   200K|   820   (1)| 00:00:01 |       |       |
|   1 |  UPDATE             | TAB1 |       |       |            |          |       |       |
|   2 |   PARTITION LIST ALL|      | 11418 |   200K|   820   (1)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL| TAB1 | 11418 |   200K|   820   (1)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TO_NUMBER("COL3")=1)

Solution

Using partition key to compare with a CHAR type constant, the update operation will just scan the target partition, thus ORA-8103 error disappears.

SQL> update tab1 set col2=col2-1 where col3='1';

18676 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 563302805

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |      | 32479 |   570K|   279   (2)| 00:00:01 |       |       |
|   1 |  UPDATE                | TAB1 |       |       |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      | 32479 |   570K|   279   (2)| 00:00:01 |     2 |     2 |
|   3 |    TABLE ACCESS FULL   | TAB1 | 32479 |   570K|   279   (2)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------