生产环境，目前有分区表，存在所有数据落于单个分区的情况。
new   1: SELECT SEGMENT_NAME,SEGMENT_TYPE,partition_name,max_size,BYTES/1024/1024/1024 gb  FROM DBA_SEGMENTS WHERE segment_name='T_QRS_TRANDATA' order by 3

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME                   MAX_SIZE         GB
------------------------------ ------------------ ------------------------------ ---------- ----------
T_QRS_TRANDATA                 TABLE PARTITION    P20250124                     2147483645 202.164063
T_QRS_TRANDATA                 TABLE PARTITION    PMAX                          2147483645   .0078125


想将该分区数据全部split出来。然后进行了相关的一些测试。
在split的过程中进行了
SQL>update T_QRS_TRANDATA set IN_MNO= IN_MNO || '1' where TRAN_DT='20240603' and  rownum<=100;
SQL> /

SID    SERIAL CALL     EVENT                          USERNAME        SQL_ID        CLIENT_INFO                                                      BK     PROGRAM
------ ------ -------- ------------------------------ --------------- ------------- ---------------------------------------------------------------- ------ ----------------------------------------
6651   583    4        enq: IV -  contention          SYS             34dsmdsb6kpgd                                                                         sqlplus@hfdb1 (TNS V1-V3)
4466   6239   2        enq: TM - contention           QRS             0ty3gjytnvj87 172.16.121.200                                                   6651   plsqldev.exe

SQL> /

SID    SERIAL CALL     EVENT                          USERNAME        SQL_ID        CLIENT_INFO                                                      BK     PROGRAM
------ ------ -------- ------------------------------ --------------- ------------- ---------------------------------------------------------------- ------ ----------------------------------------
6651   583    5        enq: IV -  contention          SYS             34dsmdsb6kpgd                                                                         sqlplus@hfdb1 (TNS V1-V3)
4466   6239   3        enq: TM - contention           QRS             0ty3gjytnvj87 172.16.121.200                                                   6651   plsqldev.exe

SQL> select * from table(dbms_xplan.display_cursor('&sql_id',null,'allstats last'));
Enter value for sql_id: 0ty3gjytnvj87
old   1: select * from table(dbms_xplan.display_cursor('&sql_id',null,'allstats last'))
new   1: select * from table(dbms_xplan.display_cursor('0ty3gjytnvj87',null,'allstats last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0ty3gjytnvj87, child number 0
-------------------------------------
 update qrs.T_QRS_TRANDATA set IN_MNO= IN_MNO || '1' where
TRAN_DT='20240603' and  rownum<=100

Plan hash value: 99719499

---------------------------------------------------------------
| Id  | Operation                | Name              | E-Rows |
---------------------------------------------------------------
|   0 | UPDATE STATEMENT         |                   |        |
|   1 |  UPDATE                  | T_QRS_TRANDATA    |        |
|*  2 |   COUNT STOPKEY          |                   |        |
|   3 |    PARTITION RANGE SINGLE|                   |      1 |
|*  4 |     INDEX RANGE SCAN     | IDX5_QRS_TRANDATA |      1 |
---------------------------------------------------------------

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

   2 - filter(ROWNUM<=100)
   4 - access("TRAN_DT"='20240603')

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


29 rows selected.

我们可以观察到，如果split的过程中，对该表进行update操作，是会有TM锁的。
同时如果split是一个完全的空分区。比如当前这个分区表的数据是只到20240601日。那么spfile成一个20240818的日分区中，
那么该分区表的索引不会失效。

split 20250124 分区 到P20240818分区
alter table QRS.T_QRS_TRANDATA split partition P20250124 at ('20240818') into (partition P20240818 tablespace QRS_DATA, partition P20250124 ) parallel 32;
操作后查看表和索引状态
select  distinct  status from dba_indexes;
select  distinct  status from dba_ind_partitions;
select status,table_name from dba_indexes where status='UNUSABLE';
select status,index_owner,index_name,partition_name from dba_ind_partitions where status='UNUSABLE';

实际测试中200G的单个分区，split到一个空分区，1-2秒就完成。
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.