Exclusive Lock on Table Segment Is Required when LOB Partitition Is Being Shrunk with Patch 8538842
(Doc ID 1331755.1)
Last updated on FEBRUARY 03, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.7 and laterInformation 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 Exclusivebut 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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |