Oracle数据库分区表SPLIT操作导致归档疯涨

最新推荐文章于 2025-03-20 18:28:21 发布

chenoracle

最新推荐文章于 2025-03-20 18:28:21 发布

阅读量616

点赞数 20

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/143830349

版权

问题现象：

按时间列范围分区的表，设置有maxvalue，经检查发现99%的数据全都集中在P_MAX分区，数据分布严重不均匀，考虑将P_MAX数据按月插入到新分区，执行下面操作：

ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);

执行后，前台卡住，后台归档疯涨 …
之前考虑到小于2023-11-30的数据很少，只有两条数据，split 操作以为只是将这两条数据插入到新的分区，实际上并不是这样。

环境说明：

DB:Oracle 11.2.0.4.0

问题重现：

创建测试数据

create tablespace cjc datafile '/oradata/test/cjc.dbf' size 10M autoextend on;create user cjc identified by "a" default tablespace cjc;grant dba to cjc;conn cjc/a

开启数据库补充日志，用于后续日志挖掘

select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;alter database add supplemental log data;

创建范围分区表 range_part_cjc

create table range_part_cjc(id int,c_date date,contents varchar2(10))partition by range (c_date)(partition p1 values less than (TO_DATE('2023-10-10 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p2 values less than (TO_DATE('2023-10-11 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p3 values less than (TO_DATE('2023-10-12 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p4 values less than (TO_DATE('2023-10-13 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p5 values less than (TO_DATE('2023-10-14 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p6 values less than (TO_DATE('2023-10-15 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p_max values less than ( maxvalue ));

插入数据：

insert into range_part_cjc values(1,TO_DATE('2023-10-10 01:00:32','YYYY-MM-DD HH24:MI:SS'),'a');insert into range_part_cjc values(2,TO_DATE('2023-10-10 02:00:32','YYYY-MM-DD HH24:MI:SS'),'b');insert into range_part_cjc values(3,TO_DATE('2023-10-13 03:00:32','YYYY-MM-DD HH24:MI:SS'),'c');insert into range_part_cjc values(4,TO_DATE('2023-11-01 04:00:32','YYYY-MM-DD HH24:MI:SS'),'d');insert into range_part_cjc values(5,TO_DATE('2023-11-01 05:00:32','YYYY-MM-DD HH24:MI:SS'),'e');insert into range_part_cjc values(6,TO_DATE('2023-12-10 06:00:32','YYYY-MM-DD HH24:MI:SS'),'f');insert into range_part_cjc values(7,TO_DATE('2023-12-11 07:00:32','YYYY-MM-DD HH24:MI:SS'),'g');insert into range_part_cjc values(8,TO_DATE('2023-12-12 08:00:32','YYYY-MM-DD HH24:MI:SS'),'h');insert into range_part_cjc values(9,TO_DATE('2023-12-13 09:00:32','YYYY-MM-DD HH24:MI:SS'),'i');insert into range_part_cjc values(10,TO_DATE('2023-12-14 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-15 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-16 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-17 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-18 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-19 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-20 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');......commit;

查看数据

set pagesize 100select id,to_char(c_date,'YYYY-MM-DD HH24:MI:SS') c_date,contents from range_part_cjc;ID C_DATE              CONTENTS---------- ------------------- ----------         1 2023-10-10 01:00:32 a         2 2023-10-10 02:00:32 b         3 2023-10-13 03:00:32 c         4 2023-11-01 04:00:32 d         5 2023-11-01 05:00:32 e         6 2023-12-10 06:00:32 f         7 2023-12-11 07:00:32 g         8 2023-12-12 08:00:32 h         9 2023-12-13 09:00:32 i        10 2023-12-14 10:00:32 j......

查看分区

set line 300col HIGH_VALUE for a100select partition_name,HIGH_VALUE from dba_tab_partitions where table_name='RANGE_PART_CJC';PARTITION_NAME                 HIGH_VALUE------------------------------ ----------------------------------------------------------------------------------------------------P1                             TO_DATE(' 2023-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP2                             TO_DATE(' 2023-10-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP3                             TO_DATE(' 2023-10-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP4                             TO_DATE(' 2023-10-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP5                             TO_DATE(' 2023-10-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP6                             TO_DATE(' 2023-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP_MAX                          MAXVALUE7 rows selected.

查看分区数据量

SELECT COUNT(*) FROM CJC.RANGE_PART_CJC;                ---10485776SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P1);  ---0SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P2);  ---2SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P3);  ---0SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P4);  ---0SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P5);  ---1SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P6);  ---0SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P_MAX);  ---10485773

大多数数据集中在P_MAX分区。
添加索引
创建local非前缀索引(分区键不是索引第一列)
主键值不能创建local分区索引，因为local分区索引不能保证全局唯一性，可以创建global分区索引。

create index i_local_01 on RANGE_PART_CJC(ID,C_DATE) local;

创建全局索引

create index i_global_01 on RANGE_PART_CJC(CONTENTS,C_DATE) global;

查看索引信息

set line 300col INDEX_NAME for a30col TABLE_NAME for a25col COLUMN_NAME for a30col INDEX_OWNER for a20SELECT INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='RANGE_PART_CJC' ORDER BY 2,4;

INDEX_OWNER          INDEX_NAME                     TABLE_NAME                COLUMN_NAME-------------------- ------------------------------ ------------------------- ------------------------------CJC                  I_GLOBAL_01                    RANGE_PART_CJC            CONTENTSCJC                  I_GLOBAL_01                    RANGE_PART_CJC            C_DATECJC                  I_LOCAL_01                     RANGE_PART_CJC            C_DATECJC                  I_LOCAL_01                     RANGE_PART_CJC            ID

查看索引状态

SELECT PARTITION_NAME,INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE  INDEX_OWNER='CJC' AND INDEX_NAME IN ('I_GLOBAL_01','I_LOCAL_01');PARTITION_NAME                 INDEX_NAME                     STATUS------------------------------ ------------------------------ --------P1                             I_LOCAL_01                     USABLEP2                             I_LOCAL_01                     USABLEP3                             I_LOCAL_01                     USABLEP4                             I_LOCAL_01                     USABLEP5                             I_LOCAL_01                     USABLEP6                             I_LOCAL_01                     USABLEP_MAX                          I_LOCAL_01                     USABLE7 rows selected.

SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='RANGE_PART_CJC';INDEX_NAME                     STATUS------------------------------ --------I_LOCAL_01                     N/AI_GLOBAL_01                    VALID

SPILT PARTITION操作

使用10046跟踪操作过程

SQLPLUS / AS SYSDBASET TIMING ON;ALTER SYSTEM SWITCH LOGFILE;ALTER SESSION SET nls_date_format='DD-MM-YYYY HH24:MI:SS';ALTER SESSION SET tracefile_identifier='10046B';ALTER SESSION SET max_dump_file_size = unlimited;ALTER SESSION SET timed_statistics = true;ALTER SESSION SET statistics_level=all;ALTER SESSION SET events '10046 trace name context forever, level 12';ALTER SESSION SET "_px_trace" = low , messaging;ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);ALTER SESSION SET events '10046 trace name context off';ALTER SESSION SET "_px_trace" = none;

耗时16分钟

将P_MAX分区中小于2023-11-30的放在part_202311分区，大于等于2023-11-30的放在P_MAX分区。

查看数据量，自己将p_max中两条数据插入到新分区。

SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(P_MAX);  ---10485771SELECT COUNT(*) FROM CJC.RANGE_PART_CJC PARTITION(PART_202311); ---2

查询表监控,统计信息没更新，不准确

SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,INSERTS,UPDATES,DELETES FROM dba_tab_modifications WHERE TABLE_NAME='RANGE_PART_CJC';SELECT * FROM sys.mon_mods$ WHERE obj#=92846; OBJ#    INSERTS    UPDATES    DELETES TIMESTAMP                FLAGS DROP_SEGMENTS---------- ---------- ---------- ---------- ------------------- ---------- -------------     92846   10485773          0          0 25-12-2023 16:05:06          0             0

查看 10046 trace

oracle@SATEST-AW-001:/oracle/app/oracle/diag/rdbms/test/test/trace$ls -lrth *10046B*-rw-r----- 1 oracle oinstall 3.5K Dec 25 15:59 test_ora_2952136_10046B.trm-rw-r----- 1 oracle oinstall 300K Dec 25 15:59 test_ora_2952136_10046B.trc

tkprof test_ora_2952136_10046B.trc 08.trctkprof test_ora_2952136_10046B.trc 09.trc sys=no

vi 08.trc

********************************************************************************SQL ID: fcwtdvc6adr2a Plan Hash: 1554261478ALTER TABLE CJC.RANGE_PARcall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      6.25      16.39          2      26302      28455    10485773Fetch        0      0.00       0.00          0          0          0           0------- ------  -------- ---------- ---------- ---------- ----------  ----------total        2      6.25      16.39          2      26302      28455    10485773Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 86Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         0          0          0  LOAD AS SELECT  (cr=28692 pr=0 pw=26151 time=15471596 us)  10485773   10485773   10485773   PARTITION RANGE SINGLE PARTITION: 7 7 (cr=26210 pr=0 pw=0 time=1977135 us cost=2 size=2378 card=82)  10485773   10485773   10485773    TABLE ACCESS FULL RANGE_PART_CJC PARTITION: 7 7 (cr=26210 pr=0 pw=0 time=794551 us cost=2 size=2378 card=82)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  Disk file operations I/O                       10        0.00          0.00  direct path write                             825        0.00          0.07  control file sequential read                   60        0.00          0.00  db file sequential read                         8        0.00          0.00  Data file init write                           30        0.00          0.00  db file single write                            3        0.00          0.00  control file parallel write                     9        0.00          0.01  rdbms ipc reply                                 3        0.00          0.00  log file switch completion                      5        0.10          0.51  log file switch (checkpoint incomplete)        10        1.88          8.46  direct path sync                                1        0.06          0.06  reliable message                                4        0.00          0.00  enq: RO - fast object reuse                     2        0.91          0.91  enq: CR - block range reuse ckpt                2        0.00          0.00  log file sync                                   1        0.00          0.00  SQL*Net message to client                       1        0.00          0.00  SQL*Net message from client                     1       19.29         19.29********************************************************************************

通过10046 trace可以看到，在进行spilt分区时，Execute 对应rows 10485773，接近全表数据量，大量的direct path write、log file switch (checkpoint incomplete)等待事件，说明并不是只移动了两行数据。

日志挖掘

切换归档

SQL> alter system switch logfile;

查看归档文件，移动两行数据，产生了214MB归档文件。

oracle@cjcdb-001:/redis/oradata/arch$ls -lrthtotal 214M-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1752_1130341000.dbf-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1753_1130341000.dbf-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1754_1130341000.dbf-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1755_1130341000.dbf-rw-r----- 1 oracle oinstall 39M Dec 25 15:59 1_1756_1130341000.dbf-rw-r----- 1 oracle oinstall 20M Dec 25 16:00 1_1757_1130341000.dbf/redis/oradata/arch/1_1752_1130341000.dbf/redis/oradata/arch/1_1753_1130341000.dbf/redis/oradata/arch/1_1754_1130341000.dbf/redis/oradata/arch/1_1755_1130341000.dbf/redis/oradata/arch/1_1756_1130341000.dbf/redis/oradata/arch/1_1757_1130341000.dbf

–11g 开始日志挖掘(和10g语法稍有差别)

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1752_1130341000.dbf',Options=>dbms_logmnr.new);EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1753_1130341000.dbf',Options=>dbms_logmnr.addfile);EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1754_1130341000.dbf',Options=>dbms_logmnr.addfile);EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1755_1130341000.dbf',Options=>dbms_logmnr.addfile);EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1756_1130341000.dbf',Options=>dbms_logmnr.addfile);EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/redis/oradata/arch/1_1757_1130341000.dbf',Options=>dbms_logmnr.addfile);EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);CREATE TABLE T1225C AS SELECT * FROM V$LOGMNR_CONTENTS;EXEC DBMS_LOGMNR.END_LOGMNR;---Elapsed: 00:03:17.40

查看表数据量

SELECT COUNT(*) FROM T1225C; ---10486320

查看归档中执行次数多的SQL

set line 300set pagesize 1000col xx for a100select count(*),substr(sql_redo,1,100) xx from t1225C group by substr(sql_redo,1,100) order by 1 desc;

 COUNT(*) XX---------- ----------------------------------------------------------------------------------------------------  10486163        54 set transaction read write;        44 commit;        10 rollback;         3 insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MT         2 update "SYS"."SCHEDULER$_JOB" set "JOB_STATUS" = '1', "NEXT_RUN_DATE" = TO_TIMESTAMP_TZ('26-DEC-23 1         2 insert into "SYS"."DEFERRED_STG$"("OBJ#","PCTFREE_STG","PCTUSED_STG","SIZE_STG","INITIAL_STG","NEXT_         2 insert into "SYS"."SCHEDULER$_JOB_RUN_DETAILS"("LOG_ID","LOG_DATE","REQ_START_DATE","START_DATE","RU         2 insert into "SYS"."SEG$"("FILE#","BLOCK#","TYPE#","TS#","BLOCKS","EXTENTS","INIEXTS","MINEXTS","MAXE         2 insert into "SYS"."SCHEDULER$_EVENT_LOG"("LOG_ID","LOG_DATE","TYPE#","NAME","OWNER","CLASS_ID","OPER         2 update "SYS"."SEG$" set "TYPE#" = '5', "BLOCKS" = '1024', "EXTENTS" = '1', "INIEXTS" = '1024', "MINE         2 update "SYS"."SCHEDULER$_JOB" set "JOB_STATUS" = '3', "LAST_START_DATE" = TO_TIMESTAMP_TZ('25-DEC-23         2 insert into "SYS"."INDPART$"("OBJ#","DATAOBJ#","BO#","PART#","HIBOUNDLEN","HIBOUNDVAL","FLAGS","TS#"         2 update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '1024', "EXTENTS" = '1', "INIEXTS" = '1024', "MINE         2 update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '32640', "EXTENTS" = '103', "INIEXTS" = '8', "MINE         1 delete from "SYS"."SEG$" where "FILE#" = '8' and "BLOCK#" = '2193' and "TYPE#" = '3' and "TS#" = '8'         1 ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (p         1 update "SYS"."PARTOBJ$" set "PARTCNT" = '8' where "OBJ#" = '92839' and "PARTCNT" = '7' and ROWID = '         1 update "SYS"."TABPART$" set "HIBOUNDVAL" = 'TO_DATE('' 2023-11-30 00:00:00'', ''SYYYY-MM-DD HH24:MI:         1 update "SYS"."AUD$" set "SQLBIND" = NULL, "SQLTEXT" = NULL where "SESSIONID" = '88667' and "ENTRYID"         1 insert into "SYS"."AUD$"("SESSIONID","ENTRYID","STATEMENT","TIMESTAMP#","USERID","USERHOST","TERMINA         1 update "SYS"."INDPART$" set "HIBOUNDVAL" = 'MAXVALUE' where "OBJ#" = '92859' and "DATAOBJ#" = '92859         1 insert into "SYS"."TABPART$"("OBJ#","DATAOBJ#","BO#","PART#","HIBOUNDLEN","HIBOUNDVAL","TS#","FILE#"         1 update "SYS"."OBJ$" set "OBJ#" = '92846', "DATAOBJ#" = '92858', "TYPE#" = '19', "CTIME" = TO_DATE('2         1 update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '26624', "EXTENTS" = '1', "INIEXTS" = '1024', "MIN         1 update "SYS"."TAB$" set "DATAOBJ#" = NULL, "TS#" = '0', "FILE#" = '0', "BLOCK#" = '0', "BOBJ#" = NUL         1 update "SYS"."SEG$" set "TYPE#" = '5', "BLOCKS" = '26624', "EXTENTS" = '97', "INIEXTS" = '8', "MINEX         1 update "SYS"."OBJ$" set "OBJ#" = '13588', "DATAOBJ#" = NULL, "TYPE#" = '66', "CTIME" = TO_DATE('24-0         1 delete from "SYS"."SEG$" where "FILE#" = '8' and "BLOCK#" = '3378' and "TYPE#" = '3' and "TS#" = '8'         1 update "SYS"."IND$" set "DATAOBJ#" = '92856', "TS#" = '8', "FILE#" = '8', "BLOCK#" = '55602', "INDME         1 update "SYS"."TABPART$" set "DATAOBJ#" = '92858', "PART#" = '81', "TS#" = '8', "FILE#" = '8', "BLOCK         1 update "SYS"."PARTOBJ$" set "PARTCNT" = '8' where "OBJ#" = '92848' and "PARTCNT" = '7' and ROWID = '         1 update "SYS"."SCHEDULER$_EVENT_LOG" set "ADDITIONAL_INFO" = NULL where "LOG_ID" = '6935' and "LOG_DA         1 delete from "SYS"."OBJ$" where "OBJ#" = '92855' and "DATAOBJ#" = '92855' and "OWNER#" = '86' and "NA         1 update "SYS"."OBJ$" set "OBJ#" = '92839', "DATAOBJ#" = NULL, "TYPE#" = '2', "CTIME" = TO_DATE('25-12         1 delete from "SYS"."INDPART$" where "OBJ#" = '92855' and "DATAOBJ#" = '92855' and "BO#" = '92848' and         1 update "SYS"."SCHEDULER$_EVENT_LOG" set "ADDITIONAL_INFO" = NULL where "LOG_ID" = '6936' and "LOG_DA         1 Unsupported         1 update "SYS"."INDPART$" set "HIBOUNDVAL" = 'TO_DATE('' 2023-11-30 00:00:00'', ''SYYYY-MM-DD HH24:MI:         1 update "SYS"."OBJ$" set "OBJ#" = '13589', "DATAOBJ#" = NULL, "TYPE#" = '66', "CTIME" = TO_DATE('24-0         1 update "SYS"."OBJ$" set "OBJ#" = '1', "DATAOBJ#" = '92865', "TYPE#" = '0', "CTIME" = TO_DATE('24-08-41 rows selected.

可以看到sql_redo为NULL占比最大，10486163和表数据量相近。

SELECT COUNT(*) FROM CJC.RANGE_PART_CJC; ---10485776

查看sql_redo is null 对应的对象信息

COL USERNAME FOR A15SELECT ROW_ID,USERNAME,SEG_TYPE,DATA_OBJ# FROM T1225C WHERE sql_redo IS NULL AND ROWNUM<=100;ROW_ID             USERNAME          SEG_TYPE  DATA_OBJ#------------------ --------------- ---------- ----------AAAAAAAAAAAAAAAAAA CJC                      0      92839AAAAAAAAAAAAAAAAAB CJC                      0      92839AAAAAAAAAAAAAAAAAB CJC                      0      92839D/AAAAAAAAAAAA CJC                      0          1AAAWq6AAIAAAeySAAA CJC                      0      92846AAAWq6AAIAAAeySAAB CJC                      0      92846AAAWq6AAIAAAeySAAC CJC                      0      92846AAAWq6AAIAAAeySAAD CJC                      0      92846AAAWq6AAIAAAeySAAE CJC                      0      92846AAAWq6AAIAAAeySAAF CJC                      0      92846AAAWq6AAIAAAeySAAG CJC                      0      92846AAAWq6AAIAAAeySAAH CJC                      0      92846AAAWq6AAIAAAeySAAI CJC                      0      92846AAAWq6AAIAAAeySAAJ CJC                      0      92846AAAWq6AAIAAAeySAAK CJC                      0      92846AAAWq6AAIAAAeySAAL CJC                      0      92846AAAWq6AAIAAAeySAAM CJC                      0      92846AAAWq6AAIAAAeySAAN CJC                      0      92846AAAWq6AAIAAAeySAAO CJC                      0      92846AAAWq6AAIAAAeySAAP CJC                      0      92846AAAWq6AAIAAAeySAAQ CJC                      0      92846AAAWq6AAIAAAeySAAR CJC                      0      92846AAAWq6AAIAAAeySAAS CJC                      0      92846AAAWq6AAIAAAeySAAT CJC                      0      92846AAAWq6AAIAAAeySAAU CJC                      0      92846AAAWq6AAIAAAeySAAV CJC                      0      92846AAAWq6AAIAAAeySAAW CJC                      0      92846AAAWq6AAIAAAeySAAX CJC                      0      92846AAAWq6AAIAAAeySAAY CJC                      0      92846AAAWq6AAIAAAeySAAZ CJC                      0      92846AAAWq6AAIAAAeySAAa CJC                      0      92846AAAWq6AAIAAAeySAAb CJC                      0      92846AAAWq6AAIAAAeySAAc CJC                      0      92846AAAWq6AAIAAAeySAAd CJC                      0      92846AAAWq6AAIAAAeySAAe CJC                      0      92846AAAWq6AAIAAAeySAAf CJC                      0      92846AAAWq6AAIAAAeySAAg CJC                      0      92846AAAWq6AAIAAAeySAAh CJC                      0      92846AAAWq6AAIAAAeySAAi CJC                      0      92846AAAWq6AAIAAAeySAAj CJC                      0      92846AAAWq6AAIAAAeySAAk CJC                      0      92846AAAWq6AAIAAAeySAAl CJC                      0      92846......

SELECT DISTINCT DATA_OBJ#,COUNT(*) FROM T1225C WHERE sql_redo IS NULL GROUP BY  DATA_OBJ# ORDER BY 2 DESC; DATA_OBJ#   COUNT(*)---------- ----------     92846   10486035         1         95         0          9     92857          5      6038          4        39          3     92839          3      6035          2       591          1       594          1        37          1       599          1       600          1        40          1        36          115 rows selected.

全集中在P_MAX分区的操作

SET LINE 300COL OBJECT_NAME FOR A20COL SUBOBJECT_NAME  FOR A20COL OWNER FOR A10select OWNER,OBJECT_ID,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where OBJECT_ID='92846';OWNER       OBJECT_ID OBJECT_NAME          SUBOBJECT_NAME       OBJECT_TYPE         CREATED---------- ---------- -------------------- -------------------- ------------------- -------------------CJC             92846 RANGE_PART_CJC       P_MAX                TABLE PARTITION     25-12-2023 15:53:27Elapsed: 00:00:00.01

ROW_ID 似乎发生了变化，重新测试spilt后rowid相关的变化

重新创建范围分区表 range_part_cjc

drop table range_part_cjc purge;create table range_part_cjc(id int,c_date date,contents varchar2(10))partition by range (c_date)(partition p1 values less than (TO_DATE('2023-10-10 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p2 values less than (TO_DATE('2023-10-11 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p3 values less than (TO_DATE('2023-10-12 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p4 values less than (TO_DATE('2023-10-13 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p5 values less than (TO_DATE('2023-10-14 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p6 values less than (TO_DATE('2023-10-15 00:00:00','YYYY-MM-DD HH24:MI:SS')),partition p_max values less than ( maxvalue ));

插入数据：

insert into range_part_cjc values(1,TO_DATE('2023-10-10 01:00:32','YYYY-MM-DD HH24:MI:SS'),'a');insert into range_part_cjc values(2,TO_DATE('2023-10-10 02:00:32','YYYY-MM-DD HH24:MI:SS'),'b');insert into range_part_cjc values(3,TO_DATE('2023-10-13 03:00:32','YYYY-MM-DD HH24:MI:SS'),'c');insert into range_part_cjc values(4,TO_DATE('2023-11-01 04:00:32','YYYY-MM-DD HH24:MI:SS'),'d');insert into range_part_cjc values(5,TO_DATE('2023-11-01 05:00:32','YYYY-MM-DD HH24:MI:SS'),'e');insert into range_part_cjc values(6,TO_DATE('2023-12-10 06:00:32','YYYY-MM-DD HH24:MI:SS'),'f');insert into range_part_cjc values(7,TO_DATE('2023-12-11 07:00:32','YYYY-MM-DD HH24:MI:SS'),'g');insert into range_part_cjc values(8,TO_DATE('2023-12-12 08:00:32','YYYY-MM-DD HH24:MI:SS'),'h');insert into range_part_cjc values(9,TO_DATE('2023-12-13 09:00:32','YYYY-MM-DD HH24:MI:SS'),'i');insert into range_part_cjc values(10,TO_DATE('2023-12-14 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-15 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-16 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-17 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-18 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-19 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');insert into range_part_cjc values(10,TO_DATE('2023-12-20 10:00:32','YYYY-MM-DD HH24:MI:SS'),'j');commit;

查看数据

set pagesize 100select rowid,id,to_char(c_date,'YYYY-MM-DD HH24:MI:SS') c_date,contents from range_part_cjc;ROWID                      ID C_DATE              CONTENTS------------------ ---------- ------------------- ----------AAAWrEAAIAAAACwAAA          1 2023-10-10 01:00:32 aAAAWrEAAIAAAACwAAB          2 2023-10-10 02:00:32 bAAAWrHAAIAAAASwAAA          3 2023-10-13 03:00:32 cAAAWrJAAIAAAViwAAA          4 2023-11-01 04:00:32 dAAAWrJAAIAAAViwAAB          5 2023-11-01 05:00:32 eAAAWrJAAIAAAViwAAC          6 2023-12-10 06:00:32 fAAAWrJAAIAAAViwAAD          7 2023-12-11 07:00:32 gAAAWrJAAIAAAViwAAE          8 2023-12-12 08:00:32 hAAAWrJAAIAAAViwAAF          9 2023-12-13 09:00:32 iAAAWrJAAIAAAViwAAG         10 2023-12-14 10:00:32 jAAAWrJAAIAAAViwAAH         10 2023-12-15 10:00:32 jAAAWrJAAIAAAViwAAI         10 2023-12-16 10:00:32 jAAAWrJAAIAAAViwAAJ         10 2023-12-17 10:00:32 jAAAWrJAAIAAAViwAAK         10 2023-12-18 10:00:32 jAAAWrJAAIAAAViwAAL         10 2023-12-19 10:00:32 jAAAWrJAAIAAAViwAAM         10 2023-12-20 10:00:32 j16 rows selected.

执行spilt操作

ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);

再次查看row_id，P_MAX分区所有数据row_id都发生了变化。

set pagesize 100select rowid,id,to_char(c_date,'YYYY-MM-DD HH24:MI:SS') c_date,contents from range_part_cjc;ROWID                      ID C_DATE              CONTENTS------------------ ---------- ------------------- ----------AAAWrEAAIAAAACwAAA          1 2023-10-10 01:00:32 aAAAWrEAAIAAAACwAAB          2 2023-10-10 02:00:32 bAAAWrHAAIAAAASwAAA          3 2023-10-13 03:00:32 cAAAWrKAAIAAAVySAAA          4 2023-11-01 04:00:32 dAAAWrKAAIAAAVySAAB          5 2023-11-01 05:00:32 eAAAWrLAAIAAAWCSAAA          6 2023-12-10 06:00:32 fAAAWrLAAIAAAWCSAAB          7 2023-12-11 07:00:32 gAAAWrLAAIAAAWCSAAC          8 2023-12-12 08:00:32 hAAAWrLAAIAAAWCSAAD          9 2023-12-13 09:00:32 iAAAWrLAAIAAAWCSAAE         10 2023-12-14 10:00:32 jAAAWrLAAIAAAWCSAAF         10 2023-12-15 10:00:32 jAAAWrLAAIAAAWCSAAG         10 2023-12-16 10:00:32 jAAAWrLAAIAAAWCSAAH         10 2023-12-17 10:00:32 jAAAWrLAAIAAAWCSAAI         10 2023-12-18 10:00:32 jAAAWrLAAIAAAWCSAAJ         10 2023-12-19 10:00:32 jAAAWrLAAIAAAWCSAAK         10 2023-12-20 10:00:32 j16 rows selected.

结论：

执行下面语句是：

ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);

是将小于指定值的数据插入到新分区，大于等于指定值的数据插入到P_MAX分区，比如小于的值有两条数据，大于等于指定值有1千万条数据，那么执行split partition，除了将小于值的两条数据插入到新分区以外，大于值的数据也会执行重新插入p_max分区的操作，导入速度慢，归档疯长，考虑到时间、空间等成本，类似的场景不适合用spilt来添加分区了，可以考虑使用导出导入或其他方式。

spilt后索引有哪些变化？

查看索引

ALTER SESSION SET nls_date_format='DD-MM-YYYY HH24:MI:SS';SET LINE 300COL OBJECT_NAME FOR A20COL SUBOBJECT_NAME  FOR A20COL OWNER FOR A10select OWNER,OBJECT_ID,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where SUBOBJECT_NAME='PART_202311';OWNER       OBJECT_ID OBJECT_NAME          SUBOBJECT_NAME       OBJECT_TYPE         CREATED---------- ---------- -------------------- -------------------- ------------------- -------------------CJC             92857 RANGE_PART_CJC       PART_202311          TABLE PARTITION     25-12-2023 15:59:18CJC             92860 I_LOCAL_01           PART_202311          INDEX PARTITION     25-12-2023 15:59:33

查看索引状态

SELECT PARTITION_NAME,INDEX_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE  INDEX_OWNER='CJC' AND INDEX_NAME IN ('I_GLOBAL_01','I_LOCAL_01');PARTITION_NAME                 INDEX_NAME                     STATUS------------------------------ ------------------------------ --------P1                             I_LOCAL_01                     USABLEP2                             I_LOCAL_01                     USABLEP3                             I_LOCAL_01                     USABLEP4                             I_LOCAL_01                     USABLEP5                             I_LOCAL_01                     USABLEP6                             I_LOCAL_01                     USABLEPART_202311                    I_LOCAL_01                     UNUSABLEP_MAX                          I_LOCAL_01                     UNUSABLE8 rows selected

SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='RANGE_PART_CJC';INDEX_NAME                     STATUS------------------------------ --------I_LOCAL_01                     N/AI_GLOBAL_01                    UNUSABLE

可以看到，参与SPILT分区的索引状态由USABLE变为UNUSABLE。

因为执行split没有指定update indexes，索引导致索引失效。

可以将

ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX);

改成

ALTER TABLE CJC.RANGE_PART_CJC split PARTITION P_MAX at (TO_DATE('2023-11-30','YYYY-MM-DD')) into (partition part_202311,partition P_MAX) update indexes;

rebuild global索引

ALTER INDEX CJC.I_GLOBAL_01 REBUILD;

rebuild local索引

ALTER INDEX CJC.I_LOCAL_01 REBUILD;ORA-14086: a partitioned index may not be rebuilt as a whole

需要指定分区名

ALTER INDEX CJC.I_LOCAL_01 REBUILD PARTITION PART_202311;ALTER INDEX CJC.I_LOCAL_01 REBUILD PARTITION P_MAX;

思考：
为什么sql_redo为空呢，难道不应该显示insert into信息吧？
这是因为sql_redo为空的数据OPERATION值是INTERNAL，数据的移动属于内部的操作，是不会显示具体的SQL的。

详细内容，参见我的微信公众号《IT小Chen》
###chenjuchao 20231227###