最近有个客户的oracle 11g环境遇到单个表空间临近耗尽的问题(30TB)，同时单个表空间的datafile 个数已经达到了物理上限1023, 且每个datafile已经达到了单个文件的上限30g（8k block size）, 表空间中的对象仅一张历史分区表，仅其中一个字段存储的是XML内容，实际为clob类型，占据了整个表空间的90%以上，业务是7*24小时无间断，如何调整把影响最小？
创建测试表
CREATE TABLE anbob.TAB_XML (
T_ID VARCHAR2(19) NOT NULL,
T_CODE VARCHAR2(8),
T_NUMBER VARCHAR2(10),
T_XML CLOB,
YEAR VARCHAR2(4),
WRITETIME VARCHAR2(20) DEFAULT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
)
LOB (T_XML) STORE AS SECUREFILE (
TABLESPACE TS_SOE
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
CACHE READS
NOLOGGING
)
TABLESPACE TS_SOE
NOCOMPRESS
NOCACHE
PARTITION BY LIST (YEAR)
SUBPARTITION BY HASH (T_NUMBER) SUBPARTITIONS 8
(
PARTITION PAR_18 VALUES ('2018') TABLESPACE TS_SOE,
PARTITION PAR_19 VALUES ('2019') TABLESPACE TS_SOE,
PARTITION PAR_20 VALUES ('2020') TABLESPACE TS_SOE,
PARTITION PAR_21 VALUES ('2021') TABLESPACE TS_SOE,
PARTITION PAR_22 VALUES ('2022') TABLESPACE TS_SOE,
PARTITION PAR_23 VALUES ('2023') TABLESPACE TS_SOE,
PARTITION PAR_24 VALUES ('2024') TABLESPACE TS_SOE,
PARTITION PAR_25 VALUES ('2025') TABLESPACE TS_SOE,
PARTITION PAR_26 VALUES ('2026') TABLESPACE TS_SOE,
PARTITION PAR_27 VALUES ('2027') TABLESPACE TS_SOE
);
CREATE UNIQUE INDEX anbob.UK_TAB_XML ON anbob.TAB_XML(T_ID)TABLESPACE TS_SOE
GLOBAL PARTITION BY HASH (T_ID) PARTITIONS 16;
CREATE UNIQUE INDEX anbob.UK_TAB_XMLNUM ON anbob.TAB_XML(T_NUMBER, T_CODE) TABLESPACE TS_SOE
GLOBAL PARTITION BY HASH (T_NUMBER) PARTITIONS 16;
ALTER TABLE anbob.TAB_XML ADD (
CONSTRAINT PK_T_TAB_XML PRIMARY KEY (T_ID) USING INDEX anbob.UK_TAB_XML ENABLE VALIDATE,
CONSTRAINT PK_T_TAB_XMLNUM UNIQUE (T_NUMBER, T_CODE) USING INDEX anbob.UK_TAB_XMLNUM ENABLE VALIDATE);Note: year字段分区，t_number 字段hash 8个子分区,然后是个全局分区hash主键索引(确实绕)。
难点：
- 1, 环境是oracle 11g， 只有online rebuild index,没有online move table
- 2, 30t空间短时间无法复制做全表在线重定义
- 3，table :index: lob 数据比例20:1:5000
- 4, 表上有全局Hash分区索引
- 5, 7*24小时业务
尝试方法
为了展示细节，附全数据
SQL> @tab anbob.TAB_XML
Show tables matching condition "%anbob.TAB_XML%" (if schema is not specified then current user s tables only are shown)...
OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESSION
-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- -------------------------------------------------------------------------------- ----------------
ANBOB TAB_XML PTAB 1
SQL> @tabpart
TABLE_OWNER TABLE_NAME POS COMPOS PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW HIGH_VALUE_LENGTH COMPRESSION COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------ ------------------------------ ---------- ------------------ ---------------------------------------------------------------------------------------------------- ----------------- ---------------- ------------------------------------------------------------
ANBOB TAB_XML 1 YES PAR_18 8 '2018' 6 DISABLED
ANBOB 2 YES PAR_19 8 '2019' 6 DISABLED
ANBOB 3 YES PAR_20 8 '2020' 6 DISABLED
ANBOB 4 YES PAR_21 8 '2021' 6 DISABLED
ANBOB 5 YES PAR_22 8 '2022' 6 DISABLED
ANBOB 6 YES PAR_23 8 '2023' 6 DISABLED
ANBOB 7 YES PAR_24 8 '2024' 6 DISABLED
ANBOB 8 YES PAR_25 8 '2025' 6 DISABLED
ANBOB 9 YES PAR_26 8 '2026' 6 DISABLED
ANBOB 10 YES PAR_27 8 '2027' 6 DISABLED
10 rows selected.
SQL> @tabsubpart
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS NUM_ROWS HIGH_VALUE_RAW HIGH_VALUE_LENGTH
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------------------------------------------------------------------------------------------------- -----------------
ANBOB TAB_XML PAR_18 SYS_SUBP7000 1 0
ANBOB PAR_18 SYS_SUBP7001 2 0
ANBOB PAR_18 SYS_SUBP7002 3 0
ANBOB PAR_18 SYS_SUBP7023 4 0
ANBOB PAR_18 SYS_SUBP7024 5 0
ANBOB PAR_18 SYS_SUBP7025 6 0
ANBOB PAR_18 SYS_SUBP7026 7 0
ANBOB PAR_18 SYS_SUBP7027 8 0
ANBOB PAR_19 SYS_SUBP7028 1 0
ANBOB PAR_19 SYS_SUBP7029 2 0
ANBOB PAR_19 SYS_SUBP7030 3 0
ANBOB PAR_19 SYS_SUBP7031 4 0
ANBOB PAR_19 SYS_SUBP7032 5 0
ANBOB PAR_19 SYS_SUBP7033 6 0
ANBOB PAR_19 SYS_SUBP7034 7 0
ANBOB PAR_19 SYS_SUBP7035 8 0
ANBOB PAR_20 SYS_SUBP7036 1 0
ANBOB PAR_20 SYS_SUBP7037 2 0
ANBOB PAR_20 SYS_SUBP7038 3 0
ANBOB PAR_20 SYS_SUBP7039 4 0
ANBOB PAR_20 SYS_SUBP7040 5 0
ANBOB PAR_20 SYS_SUBP7041 6 0
ANBOB PAR_20 SYS_SUBP7042 7 0
ANBOB PAR_20 SYS_SUBP7043 8 0
ANBOB PAR_21 SYS_SUBP7044 1 0
ANBOB PAR_21 SYS_SUBP7045 2 0
ANBOB PAR_21 SYS_SUBP7046 3 0
ANBOB PAR_21 SYS_SUBP7047 4 0
ANBOB PAR_21 SYS_SUBP7048 5 0
ANBOB PAR_21 SYS_SUBP7049 6 0
ANBOB PAR_21 SYS_SUBP7050 7 0
ANBOB PAR_21 SYS_SUBP7051 8 0
ANBOB PAR_22 SYS_SUBP7052 1 0
ANBOB PAR_22 SYS_SUBP7053 2 0
ANBOB PAR_22 SYS_SUBP7054 3 0
ANBOB PAR_22 SYS_SUBP7055 4 0
ANBOB PAR_22 SYS_SUBP7056 5 0
ANBOB PAR_22 SYS_SUBP7057 6 0
ANBOB PAR_22 SYS_SUBP7058 7 0
ANBOB PAR_22 SYS_SUBP7059 8 0
ANBOB PAR_23 SYS_SUBP7060 1 0
ANBOB PAR_23 SYS_SUBP7061 2 0
ANBOB PAR_23 SYS_SUBP7062 3 0
ANBOB PAR_23 SYS_SUBP7063 4 0
ANBOB PAR_23 SYS_SUBP7064 5 0
ANBOB PAR_23 SYS_SUBP7065 6 0
ANBOB PAR_23 SYS_SUBP7066 7 0
ANBOB PAR_23 SYS_SUBP7067 8 0
ANBOB PAR_24 SYS_SUBP7068 1 0
ANBOB PAR_24 SYS_SUBP7069 2 0
ANBOB PAR_24 SYS_SUBP7070 3 0
ANBOB PAR_24 SYS_SUBP7071 4 0
ANBOB PAR_24 SYS_SUBP7072 5 0
ANBOB PAR_24 SYS_SUBP7073 6 0
ANBOB PAR_24 SYS_SUBP7074 7 0
ANBOB PAR_24 SYS_SUBP7075 8 0
ANBOB PAR_25 SYS_SUBP7076 1 0
ANBOB PAR_25 SYS_SUBP7077 2 0
ANBOB PAR_25 SYS_SUBP7078 3 0
ANBOB PAR_25 SYS_SUBP7079 4 0
ANBOB PAR_25 SYS_SUBP7080 5 0
ANBOB PAR_25 SYS_SUBP7081 6 0
ANBOB PAR_25 SYS_SUBP7082 7 0
ANBOB PAR_25 SYS_SUBP7083 8 0
ANBOB PAR_26 SYS_SUBP7084 1 0
ANBOB PAR_26 SYS_SUBP7085 2 0
ANBOB PAR_26 SYS_SUBP7086 3 0
ANBOB PAR_26 SYS_SUBP7087 4 0
ANBOB PAR_26 SYS_SUBP7088 5 0
ANBOB PAR_26 SYS_SUBP7089 6 0
ANBOB PAR_26 SYS_SUBP7090 7 0
ANBOB PAR_26 SYS_SUBP7091 8 0
ANBOB PAR_27 SYS_SUBP7092 1 0
ANBOB PAR_27 SYS_SUBP7093 2 0
ANBOB PAR_27 SYS_SUBP7094 3 0
ANBOB PAR_27 SYS_SUBP7095 4 0
ANBOB PAR_27 SYS_SUBP7096 5 0
ANBOB PAR_27 SYS_SUBP7097 6 0
ANBOB PAR_27 SYS_SUBP7098 7 0
ANBOB PAR_27 SYS_SUBP7099 8 0
80 rows selected.
SQL> @lob
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION IN_ROW FORMAT PARTIT SECURE SEGMEN RETENTION_TYPE RETENTION_VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- -------------------- -------------- -------- ------------ ------------------------------ ------ ------------------------------ ------ ------ ------ -------------- ---------------
ANBOB TAB_XML T_XML SYS_LOB0000135942C00004$$ TS_SOE SYS_IL0000135942C00004$$ 8192 10 CACHEREADS NO NO NO NO YES ENDIAN NEUTRAL YES YES N/A DEFAULT
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,PARTITION_NAME,LOB_PARTITION_NAME,LOB_INDPART_NAME,PARTITION_POSITION,TABLESPACE_NAME
from dba_lob_partitions where table_owner='ANBOB'
TABLE_OWNER TABLE_NAME COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME LOB_INDPART_NAME PARTITION_POSITION TABLESPACE_NAME
--------------- -------------------- ------------------------------ ------------------------------ ------------------------------ -------------------- -------------------- ------------------ ------------------------------
ANBOB TAB_XML T_XML SYS_LOB0000135942C00004$$ PAR_18 SYS_LOB_P7100 SYS_IL_P7270 1 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_19 SYS_LOB_P7109 SYS_IL_P7271 2 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_20 SYS_LOB_P7118 SYS_IL_P7272 3 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_21 SYS_LOB_P7127 SYS_IL_P7273 4 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_22 SYS_LOB_P7136 SYS_IL_P7274 5 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_23 SYS_LOB_P7145 SYS_IL_P7275 6 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_24 SYS_LOB_P7154 SYS_IL_P7276 7 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_25 SYS_LOB_P7163 SYS_IL_P7277 8 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_26 SYS_LOB_P7172 SYS_IL_P7278 9 TS_SOE
ANBOB T_XML SYS_LOB0000135942C00004$$ PAR_27 SYS_LOB_P7181 SYS_IL_P7279 10 TS_SOE
10 rows selected.
SQL> select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,LOB_PARTITION_NAME,SUBPARTITION_NAME,LOB_SUBPARTITION_NAME,TABLESPACE_NAME
from DBA_LOB_SUBPARTITIONS where table_owner='ANBOB' and table_name='T_FSEIXML' order by SUBPARTITION_POSITION 2 ;
TABLE_OWNER TABLE_NAME COLUMN_NAME LOB_NAME LOB_PARTITION_NAME SUBPARTITION_NAME LOB_SUBPARTITION_NAME TABLESPACE
--------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
ANBOB T_FSEIXML EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7000 SYS_LOB_SUBP7385 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7028 SYS_LOB_SUBP7110 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7036 SYS_LOB_SUBP7347 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7044 SYS_LOB_SUBP7128 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7052 SYS_LOB_SUBP7137 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7060 SYS_LOB_SUBP7146 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7068 SYS_LOB_SUBP7155 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7076 SYS_LOB_SUBP7164 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7084 SYS_LOB_SUBP7173 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7092 SYS_LOB_SUBP7182 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7001 SYS_LOB_SUBP7388 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7029 SYS_LOB_SUBP7111 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7037 SYS_LOB_SUBP7355 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7045 SYS_LOB_SUBP7129 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7053 SYS_LOB_SUBP7138 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7061 SYS_LOB_SUBP7147 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7069 SYS_LOB_SUBP7156 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7077 SYS_LOB_SUBP7165 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7085 SYS_LOB_SUBP7174 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7093 SYS_LOB_SUBP7183 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7002 SYS_LOB_SUBP7391 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7030 SYS_LOB_SUBP7112 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7038 SYS_LOB_SUBP7358 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7046 SYS_LOB_SUBP7130 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7054 SYS_LOB_SUBP7139 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7062 SYS_LOB_SUBP7148 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7070 SYS_LOB_SUBP7157 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7078 SYS_LOB_SUBP7166 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7086 SYS_LOB_SUBP7175 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7094 SYS_LOB_SUBP7184 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7023 SYS_LOB_SUBP7394 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7031 SYS_LOB_SUBP7113 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7039 SYS_LOB_SUBP7361 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7047 SYS_LOB_SUBP7131 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7055 SYS_LOB_SUBP7140 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7063 SYS_LOB_SUBP7149 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7071 SYS_LOB_SUBP7158 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7079 SYS_LOB_SUBP7167 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7087 SYS_LOB_SUBP7176 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7095 SYS_LOB_SUBP7185 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7024 SYS_LOB_SUBP7397 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7032 SYS_LOB_SUBP7114 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7040 SYS_LOB_SUBP7364 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7048 SYS_LOB_SUBP7132 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7056 SYS_LOB_SUBP7141 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7064 SYS_LOB_SUBP7150 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7072 SYS_LOB_SUBP7159 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7080 SYS_LOB_SUBP7168 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7088 SYS_LOB_SUBP7177 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7096 SYS_LOB_SUBP7186 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7025 SYS_LOB_SUBP7432 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7033 SYS_LOB_SUBP7115 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7041 SYS_LOB_SUBP7367 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7049 SYS_LOB_SUBP7133 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7057 SYS_LOB_SUBP7142 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7065 SYS_LOB_SUBP7151 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7073 SYS_LOB_SUBP7160 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7081 SYS_LOB_SUBP7169 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7089 SYS_LOB_SUBP7178 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7097 SYS_LOB_SUBP7187 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7026 SYS_LOB_SUBP7435 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7034 SYS_LOB_SUBP7116 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7042 SYS_LOB_SUBP7370 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7050 SYS_LOB_SUBP7134 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7058 SYS_LOB_SUBP7143 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7066 SYS_LOB_SUBP7152 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7074 SYS_LOB_SUBP7161 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7082 SYS_LOB_SUBP7170 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7090 SYS_LOB_SUBP7179 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7098 SYS_LOB_SUBP7288 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7100 SYS_SUBP7027 SYS_LOB_SUBP7438 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7109 SYS_SUBP7035 SYS_LOB_SUBP7117 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7118 SYS_SUBP7043 SYS_LOB_SUBP7373 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7127 SYS_SUBP7051 SYS_LOB_SUBP7135 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7136 SYS_SUBP7059 SYS_LOB_SUBP7144 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7145 SYS_SUBP7067 SYS_LOB_SUBP7153 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7154 SYS_SUBP7075 SYS_LOB_SUBP7162 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7163 SYS_SUBP7083 SYS_LOB_SUBP7171 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7172 SYS_SUBP7091 SYS_LOB_SUBP7180 TS_SOE
ANBOB EINVOICEXML SYS_LOB0000135942C00004$$ SYS_LOB_P7181 SYS_SUBP7099 SYS_LOB_SUBP7461 TS_SOE
80 rows selected.
SQL> @seg anbob.SYS_LOB
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- -------------------------- ---------- ---------- ----------
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7189 LOB SUBPARTITION TS_SOE 1024 14 736257
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7188 LOB SUBPARTITION TS_SOE 1024 14 735233
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7187 LOB SUBPARTITION TS_SOE 1024 14 734209
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7186 LOB SUBPARTITION TS_SOE 1024 14 733185
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7185 LOB SUBPARTITION TS_SOE 1024 14 732161
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7184 LOB SUBPARTITION TS_SOE 1024 14 731137
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7183 LOB SUBPARTITION TS_SOE 1024 14 730113
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7182 LOB SUBPARTITION TS_SOE 1024 14 729089
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7180 LOB SUBPARTITION TS_SOE 1024 14 728065
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7179 LOB SUBPARTITION TS_SOE 1024 14 727041
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7178 LOB SUBPARTITION TS_SOE 1024 14 726017
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7177 LOB SUBPARTITION TS_SOE 1024 14 724993
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7176 LOB SUBPARTITION TS_SOE 1024 14 723969
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7175 LOB SUBPARTITION TS_SOE 1024 14 722945
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7174 LOB SUBPARTITION TS_SOE 1024 14 721921
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7173 LOB SUBPARTITION TS_SOE 1024 14 720897
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7171 LOB SUBPARTITION TS_SOE 1024 14 719873
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7170 LOB SUBPARTITION TS_SOE 1024 14 718849
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7169 LOB SUBPARTITION TS_SOE 1024 14 717825
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7168 LOB SUBPARTITION TS_SOE 1024 14 716801
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7167 LOB SUBPARTITION TS_SOE 1024 14 715777
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7166 LOB SUBPARTITION TS_SOE 1024 14 714753
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7165 LOB SUBPARTITION TS_SOE 1024 14 713729
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7164 LOB SUBPARTITION TS_SOE 1024 14 712705
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7111 LOB SUBPARTITION TS_SOE 1024 14 664577
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7110 LOB SUBPARTITION TS_SOE 1024 14 663553
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7108 LOB SUBPARTITION TS_SOE 1024 14 662529
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7107 LOB SUBPARTITION TS_SOE 1024 14 661505
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7106 LOB SUBPARTITION TS_SOE 1024 14 660481
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7105 LOB SUBPARTITION TS_SOE 1024 14 659457
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7104 LOB SUBPARTITION TS_SOE 1024 14 658433
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7103 LOB SUBPARTITION TS_SOE 1024 14 657409
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7102 LOB SUBPARTITION TS_SOE 1024 14 656385
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7101 LOB SUBPARTITION TS_SOE 1024 14 655361
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7162 LOB SUBPARTITION TS_SOE 1024 14 711681
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7161 LOB SUBPARTITION TS_SOE 1024 14 710657
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7160 LOB SUBPARTITION TS_SOE 1024 14 709633
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7159 LOB SUBPARTITION TS_SOE 1024 14 708609
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7158 LOB SUBPARTITION TS_SOE 1024 14 707585
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7157 LOB SUBPARTITION TS_SOE 1024 14 706561
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7156 LOB SUBPARTITION TS_SOE 1024 14 705537
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7155 LOB SUBPARTITION TS_SOE 1024 14 704513
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7153 LOB SUBPARTITION TS_SOE 1024 14 703489
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7152 LOB SUBPARTITION TS_SOE 1024 14 702465
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7151 LOB SUBPARTITION TS_SOE 1024 14 701441
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7150 LOB SUBPARTITION TS_SOE 1024 14 700417
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7149 LOB SUBPARTITION TS_SOE 1024 14 699393
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7148 LOB SUBPARTITION TS_SOE 1024 14 698369
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7147 LOB SUBPARTITION TS_SOE 1024 14 697345
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7146 LOB SUBPARTITION TS_SOE 1024 14 696321
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7144 LOB SUBPARTITION TS_SOE 1024 14 695297
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7143 LOB SUBPARTITION TS_SOE 1024 14 694273
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7142 LOB SUBPARTITION TS_SOE 1024 14 693249
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7141 LOB SUBPARTITION TS_SOE 1024 14 692225
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7140 LOB SUBPARTITION TS_SOE 1024 14 691201
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7139 LOB SUBPARTITION TS_SOE 1024 14 690177
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7138 LOB SUBPARTITION TS_SOE 1024 14 689153
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7137 LOB SUBPARTITION TS_SOE 1024 14 688129
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7135 LOB SUBPARTITION TS_SOE 1024 14 687105
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7134 LOB SUBPARTITION TS_SOE 1024 14 686081
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7133 LOB SUBPARTITION TS_SOE 1024 14 685057
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7132 LOB SUBPARTITION TS_SOE 1024 14 684033
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7131 LOB SUBPARTITION TS_SOE 1024 14 683009
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7130 LOB SUBPARTITION TS_SOE 1024 14 681985
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7129 LOB SUBPARTITION TS_SOE 1024 14 680961
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7128 LOB SUBPARTITION TS_SOE 1024 14 679937
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7126 LOB SUBPARTITION TS_SOE 1024 14 678913
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7125 LOB SUBPARTITION TS_SOE 1024 14 677889
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7124 LOB SUBPARTITION TS_SOE 1024 14 676865
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7123 LOB SUBPARTITION TS_SOE 1024 14 675841
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7122 LOB SUBPARTITION TS_SOE 1024 14 674817
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7121 LOB SUBPARTITION TS_SOE 1024 14 673793
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7120 LOB SUBPARTITION TS_SOE 1024 14 672769
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7119 LOB SUBPARTITION TS_SOE 1024 14 671745
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7112 LOB SUBPARTITION TS_SOE 1024 14 665601
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7117 LOB SUBPARTITION TS_SOE 1024 14 670721
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7116 LOB SUBPARTITION TS_SOE 1024 14 669697
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7115 LOB SUBPARTITION TS_SOE 1024 14 668673
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7114 LOB SUBPARTITION TS_SOE 1024 14 667649
8 ANBOB SYS_LOB0000135942C00004$$ SYS_LOB_SUBP7113 LOB SUBPARTITION TS_SOE 1024 14 666625
80 rows selected.
Note: 10个分区，每个8 hash 子分区，一共80个subpartition, 一对一 对应80个lob subpartition.
生产数据库每个lob子分区大的达600G, 那move 历史年份的lob subpartition到其他tablespace可能最当前适合的方法。
如何移动lob subpartition？
alter table <table name> move partition <table partition name>
lob (<lob column name>) store as <optional lob partition name> (tablespace <lob tablespace name>);
-or-
alter table <table name> move partition <table partition name>
lob (<lob column name>) store as (tablespace <lob tablespace name>);
移动 lob subpartition会导致索引失效吗？
lob 在table行内存的是Location地址，当然希望只移动lob segment，不动table segment，这样能避免index 失效。真能避免吗？选择一个subpartition, table subpartition(SYS_SUBP7000) ==对应== lob subpartition(SYS_LOB_SUBP7101).
SQL> alter table anbob.TAB_XML
move subpartition SYS_LOB_SUBP7189 --lob subpartition name
LOB (T_XML) STORE AS SECUREFILE ( TABLESPACE USERS ) ;
move subpartition SYS_LOB_SUBP7189
*
ERROR at line 2:
ORA-14251: Specified subpartition does not exist
SQL> alter table anbob.TAB_XML
move subpartition SYS_SUBP7099 -- tab subpartition name
LOB (T_XML) STORE AS SECUREFILE ( TABLESPACE USERS ) ;
Table altered.
OWNER INDEX_NAME PARTITION_NAME
-------------------- ---------------------------- ------------------------------
ANBOB UK_TAB_XML SYS_P7399
ANBOB UK_TAB_XML SYS_P7400
ANBOB UK_TAB_XML SYS_P7401
ANBOB UK_TAB_XML SYS_P7402
ANBOB UK_TAB_XML SYS_P7403
ANBOB UK_TAB_XML SYS_P7404
ANBOB UK_TAB_XML SYS_P7405
ANBOB UK_TAB_XML SYS_P7406
ANBOB UK_TAB_XML SYS_P7407
ANBOB UK_TAB_XML SYS_P7408
ANBOB UK_TAB_XML SYS_P7409
ANBOB UK_TAB_XML SYS_P7410
ANBOB UK_TAB_XML SYS_P7411
ANBOB UK_TAB_XML SYS_P7412
ANBOB UK_TAB_XML SYS_P7413
ANBOB UK_TAB_XML SYS_P7414
ANBOB UK_TAB_XMLNUM SYS_P7415
ANBOB UK_TAB_XMLNUM SYS_P7416
ANBOB UK_TAB_XMLNUM SYS_P7417
ANBOB UK_TAB_XMLNUM SYS_P7418
ANBOB UK_TAB_XMLNUM SYS_P7419
ANBOB UK_TAB_XMLNUM SYS_P7420
ANBOB UK_TAB_XMLNUM SYS_P7421
ANBOB UK_TAB_XMLNUM SYS_P7422
ANBOB UK_TAB_XMLNUM SYS_P7423
ANBOB UK_TAB_XMLNUM SYS_P7424
ANBOB UK_TAB_XMLNUM SYS_P7425
ANBOB UK_TAB_XMLNUM SYS_P7426
ANBOB UK_TAB_XMLNUM SYS_P7427
ANBOB UK_TAB_XMLNUM SYS_P7428
ANBOB UK_TAB_XMLNUM SYS_P7429
ANBOB UK_TAB_XMLNUM SYS_P7430
32 rows selected.
Note: move lob subpartition的语法没有，Move tab subpartition的语法后，不及预期，全局分区索引有部分失效了。
SQL> SELECT segment_name
, tablespace_name
, header_file
, header_block
, partition_name
FROM dba_segments
WHERE segment_name LIKE 'TAB_XML%'
and PARTITION_NAME='SYS_SUBP7000'
ORDER BY 1
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK PARTITION_NAME
------------------------------ ------------------------------ ----------- ------------ ------------------------------
TAB_XML TS_SOE 13 1149290 SYS_SUBP7000
SQL> SELECT segment_name
, tablespace_name
, header_file
, header_block
, partition_name
FROM dba_segments
WHERE (owner,segment_name,PARTITION_NAME) in (
select TABLE_OWNER,LOB_NAME,LOB_SUBPARTITION_NAME
from DBA_LOB_SUBPARTITIONS where table_owner='ANBOB' and table_name='TAB_XML' and SUBPARTITION_NAME='SYS_SUBP7000'
);
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK PARTITION_NAME
------------------------------ ------------------------------ ----------- ------------ ------------------------------
SYS_LOB0000135942C00004$$ TS_SOE 13 907265 SYS_LOB_SUBP8258
SQL> alter table anbob.TAB_XML
move subpartition SYS_SUBP7000
LOB (EINVOICEXML) STORE AS SECUREFILE ( TABLESPACE USERS ) ;
Table altered.
SQL> SELECT segment_name
2 , tablespace_name
, header_file
, header_block
, partition_name
3 4 5 6 FROM dba_segments
WHERE (owner,segment_name,PARTITION_NAME) in (
select TABLE_OWNER,LOB_NAME,LOB_SUBPARTITION_NAME
from DBA_LOB_SUBPARTITIONS where table_owner='ANBOB' and table_name='TAB_XML' and SUBPARTITION_NAME='SYS_SUBP7000'
7 8 9 10 );
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK PARTITION_NAME
------------------------------ ------------------------------ ----------- ------------ ------------------------------
SYS_LOB0000135942C00004$$ USERS 5 26625 SYS_LOB_SUBP8261
SQL> SELECT segment_name
, tablespace_name
, header_file
, header_block
, partition_name
FROM dba_segments
WHERE segment_name LIKE 'TAB_XML%'
and PARTITION_NAME='SYS_SUBP7000'
ORDER BY 1
/
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK PARTITION_NAME
------------------------------ ------------------------------ ----------- ------------ ------------------------------
TAB_XML TS_SOE 13 846194 SYS_SUBP7000Note: move lob换到了预定的表空间，同时table subpartition 在原表空间也做了move， 所以才会索引失效。 猜测虽然table 行内存储的是lob seg的指针，但是如果move 了lob，但是原来的地址也要更新，oracle又是row level存储，更新一个字段也要加载所有数据，如果是行级更新可能吞吐量没有重建快。
重建失效索引
SELECT 'alter index '
|| index_owner
|| '.'
|| index_name
|| ' rebuild partition '
|| partition_name
|| ' parallel 8;'
sqls
FROM dba_ind_partitions
WHERE status NOT IN ('N/A', 'USABLE') order by 1; move分区 UPDATE GLOBAL INDEXES
可以在move table时带上UPDATE GLOBAL INDEXES 同时维护索引。
SQL> alter table anbob.T_FSEIXML
move subpartition SYS_SUBP7000
LOB (EINVOICEXML) STORE AS SECUREFILE ( TABLESPACE USERS ) UPDATE GLOBAL INDEXES;
Table altered.
SQL> @inv
Show invalid objects, indexes, index partitions and index subpartitions....
no rows selected
no rows selected
no rows selected
no rows selected
Note: UPDATE GLOBAL INDEXES 同时更新索引不会失效，但是这种会非常的慢，我们尝试一个50g的分区，4个小时没有执行完。即使session级开启了force parallel ddl, force parallel dml，同样并行效果不好。
12c以后可以使用online。
alter table anbob.T_FSEIXML
move subpartition SYS_SUBP7000
LOB (EINVOICEXML) STORE AS SECUREFILE ( TABLESPACE USERS ) online;Note: 同样也不会有索引失效。
创建分区local索引临时过度
当前是global partition index， 可临时增加local partition index，这样仅move的partiton对应的index partition会失效，重建速度快，全局索引失效可以有local索引顶替。
CREATE UNIQUE INDEX ANBOB.UK_TAB_XML1 ON ANBOB.TAB_XML(T_ID,YEAR,T_NUMBER) TABLESPACE USERS local;Note：这种会临时失去唯一约束限制。
应用测调整
快速的方法是修复应用partkey value, 创建新的分区，因为是list 分区，定义新的分区新数据写新的分区到新存储即可，再更新新年度的已入库的少量分区。或应用查询时拼接。如原来是2026, 新分区为2026a， 查询时 like year.
XMLtype 如何move
oracle支持xmltype，同样也是使用clob形式存储，这种type move和lob 有些不同之处。
CREATE TABLE anbob.t_xml2
( c_vc2 VARCHAR2(4000)
, c_clob CLOB
, c_xml XMLTYPE
)
TABLESPACE users
LOB (c_clob) STORE AS SECUREFILE t_test_clob (TABLESPACE users DISABLE STORAGE IN ROW RETENTION CACHE)
XMLTYPE COLUMN c_xml STORE AS SECUREFILE BINARY XML t_test_xml (TABLESPACE users DISABLE STORAGE IN ROW RETENTION CACHE)
/
SQL> desc anbob.t_xml2
Name Null? Type
------------------------------- -------- ----------------------------
1 C_VC2 VARCHAR2(4000)
2 C_CLOB CLOB
3 C_XML XMLTYPE STORAGE BINARY
SQL> @lob anbob.t_xml2
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION IN_ROW FORMAT PARTIT SECURE SEGMEN RETENTION_TYPE RETENTION_VALUE
------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- -------------------- -------------- -------- ------------ ------------------------------ ------ ------------------------------ ------ ------ ------ -------------- ---------------
ANBOB T_XML2 C_CLOB T_TEST_CLOB USERS SYS_IL0000138458C00002$$ 8192 YES YES NO NO NO NO ENDIAN NEUTRAL NO YES YES DEFAULT
ANBOB T_XML2 SYS_NC00004$ T_TEST_XML USERS SYS_IL0000138458C00004$$ 8192 YES YES NO NO NO NO NOT APPLICABLE NO YES YES DEFAULT
xmltype move时，不能使用column名， 而要使用内部的lob名。
SQL> ALTER TABLE anbob.t_xml2 MOVE
TABLESPACE TS_SOE
LOB (c_clob) STORE AS SECUREFILE (TABLESPACE TS_SOE)
LOB (c_xml) STORE AS SECUREFILE (TABLESPACE TS_SOE)
/
LOB (c_xml) STORE AS SECUREFILE (TABLESPACE TS_SOE)
*
ERROR at line 4:
ORA-00904: "C_XML": invalid identifier
SQL> ALTER TABLE anbob.t_xml2 MOVE
TABLESPACE TS_SOE
LOB (c_clob) STORE AS SECUREFILE (TABLESPACE TS_SOE)
LOB (SYS_NC00004$) STORE AS SECUREFILE (TABLESPACE TS_SOE)
/
Table altered.
默认情况下，move 表时，LOB 段不会移动；如果要将其包含在移动中，则需要明确指定。
XML 段在这方面并不一致，默认情况下它们会移动到表数据段的表空间（无论是否指定）；必须明确指定它们将保留在正确的表空间中（但它们总是会移动）。
— over —