Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲
問題現象：
按時間列範圍分割槽的表，設定有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###
來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/29785807/viewspace-3001770/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- split分割槽操作導致的librarycachelock
- 資料庫分割槽表分割槽未分配導致的一些問題資料庫
- Oracle資料庫中分割槽表的操作方法Oracle資料庫
- oracle 交換分割槽歷史資料歸檔Oracle
- 分割槽表split操作及maxvalue處理
- Oracle資料庫中分割槽表的操作方法（轉）Oracle資料庫
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- 使用split對分割槽表再分割槽
- Oracle資料庫中分割槽表的操作方法詳解Oracle資料庫
- 海量資料處理_表分割槽(分割槽自動維護與歷史分割槽歸檔)
- 詳解ORACLE資料庫的分割槽表Oracle資料庫
- 分割槽表入無分割槽的資料庫資料庫
- 歸檔日誌無法歸檔導致資料庫hang住資料庫
- Oracle資料庫開發——瞭解分割槽表Oracle資料庫
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- MySql資料分割槽操作之新增分割槽操作MySql
- 分割槽表匯入資料庫資料庫
- ddl 導致分割槽表全域性索引unusable索引
- INTERVAL分割槽表鎖分割槽操作
- ORACLE 分割槽索引UNUSABLE導致的DML操作失敗引起的血案Oracle索引
- 【北亞資料恢復】誤操作分割槽損壞導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- oracle 分割槽表進行shrink操作Oracle
- ORACLE分割槽表的概念及操作Oracle
- ORACLE分割槽表的操作應用Oracle
- oracle 分割槽表 概念以及常用操作Oracle
- 資料庫分割槽表 什麼情況下需要分割槽資料庫
- Oracle 12cr2 資料庫之間傳輸表，分割槽或子分割槽Oracle資料庫
- ORACLE刪除-表分割槽和資料Oracle
- oracle分割槽表和分割槽表exchangeOracle
- mysql分割槽表佔用大量容量處理（最佳化）及歸檔分割槽表MySql
- 歸檔問題導致的資料庫無法啟動資料庫
- Oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle vs PostgreSQL DBA（13）- 拆分(split)分割槽OracleSQL
- Oracle資料庫表範圍分割槽策略測試過程Oracle資料庫
- 匯入匯出 Oracle 分割槽表資料Oracle
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle