Exclusive Lock on Table Segment Is Required when LOB Partitition Is Being Shrunk with Patch 8538842 (Doc ID 1331755.1)

Last updated on MARCH 01, 2017

Applies to:

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

Symptoms

Concurrent DMLs are stalled longer than needed while shrinking a LOB in a partitioned table even with the fix for Bug 8538842; during the shrinking of a LOB partition table, the TM exclusive lock is held on the table.

The testcase that reproduce the issue is:

SQL> CREATE USER test IDENTIFIED BY test
/

SQL> GRANT dba TO test
/

SQL> CONN test/test

SQL> CREATE TABLE test_clob_shrink (
     mdate DATE,
     a CLOB
)
PARTITION BY RANGE (mdate)
(
     PARTITION test_partition VALUES LESS THAN (TO_DATE('2030-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
/

SQL> INSERT INTO test_clob_shrink
SELECT sysdate + rownum, sql_text from sys.WRH$_SQLTEXT
/

SQL> BEGIN
    FOR i IN 1..10
    LOOP
        EXECUTE IMMEDIATE 'INSERT INTO test_clob_shrink SELECT * FROM test_clob_shrink';
    END LOOP;
    COMMIT;
END;
/

SQL> ALTER TABLE test_clob_shrink
    MODIFY PARTITION test_partition LOB(a) (SHRINK SPACE COMPACT)
/

The locks during the shrink are:

SQL> SELECT name, mode_held FROM dba_dml_locks;

NAME                           MODE_HELD
------------------------------ -------------
TEST_CLOB_SHRINK               Row-X (SX)
TEST_CLOB_SHRINK               Exclusive

but the correct locks should be:

SQL> SELECT name, mode_held FROM dba_dml_locks;

NAME                           MODE_HELD
------------------------------ -------------
TEST_CLOB_SHRINK               Row-X (SX)
TEST_CLOB_SHRINK               Row-X (SX)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms