Oracle 19c 本地索引分区变为 UNUSABLE 后的空间占用验证
适用范围
适用于Oracle 19c
场景概述
通过测试说明Oracle 19c中分区表的不可用索引和索引分区是否占用空间。
实施步骤
1、登录到PDB hrpdb中
使用业务用户hr用户连接
[oracle@19cdb01 ~]$ sqlplus hr/<password>@hrpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 13 04:16:28 2026
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
--检查容器名称
HR@hrpdb(HRPDB)> show con_name
CON_NAME
------------------------------
HRPDB
--确认用户
HR@hrpdb(HRPDB)> show user
USER is "HR"
HR@hrpdb(HRPDB)>2、创建一个range分区表
HR@hrpdb(HRPDB)> create table sales1
(sales_amt number
,d_date_id number)
partition by range (d_date_id)(
partition p_2022 values less than (20220101) tablespace XFTBS,
partition p_2023 values less than (20230101) tablespace XFTBS,
partition p_max values less than (maxvalue) tablespace XFTBS); 2 3 4 5 6 7
Table created创建range分区表sales1
3、给分区表创建索引
HR@hrpdb(HRPDB)> create index inx_sales_fk1 on sales1(d_date_id) tablespace INX_XFTBS local;
Index created.
HR@hrpdb(HRPDB)> create index inx_sales_amt on sales1(sales_amt) tablespace INX_XFTBS local;
Index created.
HR@hrpdb(HRPDB)>给分区表创建两个本地索引 inx_sales_fk1 和inx_sales_amt 。
4、为分区表插入测试数据
HR@hrpdb(HRPDB)> INSERT INTO sales1(sales_amt, d_date_id) VALUES (50, 2022);
1 row created.
HR@hrpdb(HRPDB)> INSERT INTO sales1(sales_amt, d_date_id) VALUES (100, 2023);
1 row created.
HR@hrpdb(HRPDB)> commit;
Commit complete.
HR@hrpdb(HRPDB)> select count(*) from sales1;
COUNT(*)
----------
25、查看分区表信息
HR@hrpdb(HRPDB)> col table_name for a15
HR@hrpdb(HRPDB)> col partitioning_type for a10
HR@hrpdb(HRPDB)> col def_tablespace_name for a15
HR@hrpdb(HRPDB)> select table_name, partitioning_type, def_tablespace_name
from user_part_tables
where table_name=’SALES1′; 2 3
TABLE_NAME PARTITIONI DEF_TABLESPACE_
————— ———- —————
SALES1 RANGE XFTBS
HR@hrpdb(HRPDB)>
6、查表中分区的信息
HR@hrpdb(HRPDB)> col table_name for a15
HR@hrpdb(HRPDB)> col partition_name for a15
HR@hrpdb(HRPDB)> col high_value for 9999
HR@hrpdb(HRPDB)> set long 9999999
HR@hrpdb(HRPDB)> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES1'
order by table_name, partition_name; 2 3 4
TABLE_NAME PARTITION_NAME HIGH_VALUE
--------------- --------------- --------------------
SALES1 P_2022 20220101
SALES1 P_2023 20230101
SALES1 P_MAX MAXVALUE
HR@hrpdb(HRPDB)>
HR@hrpdb(HRPDB)> select rpad(segment_name,10),partition_name, blocks, bytes from user_segments
where segment_type='INDEX PARTITION'; 2
RPAD(SEGMENT_NAME,10) PARTITION_NAME BLOCKS BYTES
---------------------------------------- --------------- ---------- ----------
INX_SALES_ P_2022 8 65536
INX_SALES_ P_2022 8 65536
HR@hrpdb(HRPDB)>
SYS@hrpdb(HRPDB)> select rpad(segment_name,10),partition_name, blocks, bytes from dba_segments
where segment_type='INDEX PARTITION' and owner='HR' order by 1,2; 2
RPAD(SEGMENT_NAME,10) PARTITION_NAME BLOCKS BYTES
---------------------------------------- --------------- ---------- ----------
INX_SALES_ P_2022 8 65536
INX_SALES_ P_2022 8 65536
SYS@hrpdb(HRPDB)>
HR@hrpdb(HRPDB)> select index_name,partition_name,status from user_ind_partitions
where partition_name='P_2022' order by 1,2; 2
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
INX_SALES_AMT P_2022 USABLE
INX_SALES_FK1 P_2022 USABLE
HR@hrpdb(HRPDB)>
SYS@hrpdb(HRPDB)> select index_name,partition_name,status from dba_ind_partitions
where index_owner='HR' and partition_name='P_2022' order by 1,2; 2
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
INX_SALES_AMT P_2022 USABLE
INX_SALES_FK1 P_2022 USABLE
SYS@hrpdb(HRPDB)>7、对分区表SALES1中的P_2022分区进行MOVE
HR@hrpdb(HRPDB)> alter table SALES1 move partition P_2022;
Table altered.
检查数据库日志
2026-06-19T05:06:29.934561+08:00
HRPDB(3):Some indexes or index [sub]partitions of table HR.SALES1 have been marked unusableMOVE执行后分区内数据的物理存储位置（ROWID）发生了改变。Oracle 出于数据一致性考虑，会自动将依赖于这些 ROWID 的本地索引（Local Index）分区标记为 UNUSABLE。
8、再次检查表中分区信息
HR@hrpdb(HRPDB)> select rpad(segment_name,10),partition_name, blocks, bytes from user_segments
where segment_type='INDEX PARTITION'; 2
no rows selected
HR@hrpdb(HRPDB)>
SYS@hrpdb(HRPDB)> select rpad(segment_name,10),partition_name, blocks, bytes from dba_segments
where segment_type='INDEX PARTITION' and owner='HR' order by 1,2; 2
no rows selected
SYS@hrpdb(HRPDB)> select index_name,partition_name,status from dba_ind_partitions
where index_owner='HR' and partition_name='P_2022' order by 1,2; 2
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
INX_SALES_AMT P_2022 UNUSABLE
INX_SALES_FK1 P_2022 UNUSABLE
SYS@hrpdb(HRPDB)>
HR@hrpdb(HRPDB)> select index_name,partition_name,status from user_ind_partitions
where partition_name='P_2022' order by 1,2; 2
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
INX_SALES_AMT P_2022 UNUSABLE
INX_SALES_FK1 P_2022 UNUSABLE
HR@hrpdb(HRPDB)>9、恢复索引状态
在日常运维中，如果需要让这些索引重新生效并重新分配空间，必须对其进行重建（Rebuild）
HR@hrpdb(HRPDB)> ALTER INDEX INX_SALES_AMT REBUILD PARTITION P_2022;
Index altered.
HR@hrpdb(HRPDB)> ALTER INDEX INX_SALES_FK1 REBUILD PARTITION P_2022;
Index altered.再次查询分区表中分区segment信息，查询结果与 第6步中一致，索引状态是USABLE，segment也分配了值。
10、MOVE分区表时保持索引USABLE状态
--move分区表时使用UPDATE INDEXES
HR@hrpdb(HRPDB)> HR@hrpdb(HRPDB)> ALTER TABLE sales1 MOVE PARTITION p_2022 tablespace XFTBS UPDATE INDEXES;
Table altered.
Table altered.
HR@hrpdb(HRPDB)> select rpad(segment_name,10),partition_name, blocks, bytes from user_segments
where segment_type='INDEX PARTITION'; 2
RPAD(SEGMENT_NAME,10) PARTITION_NAME BLOCKS BYTES
---------------------------------------- --------------- ---------- ----------
INX_SALES_ P_2022 8 65536
INX_SALES_ P_2022 8 65536
HR@hrpdb(HRPDB)> select index_name,partition_name,status from user_ind_partitions
where partition_name='P_2022' order by 1,2; 2
INDEX_NAME PARTITION_NAME STATUS
-------------------- --------------- --------
INX_SALES_AMT P_2022 USABLE
INX_SALES_FK1 P_2022 USABLE
--从12c开始当移动分区时，可以通过 ONLINE 子句指定更新所有索引
HR@hrpdb(HRPDB)> ALTER TABLE sales1 MOVE PARTITION p_2022 ONLINE TABLESPACE XFTBS;
Table altered.【总结】19c中分区表的不可用索引和索引分区不占用空间。对分区表MOVE后将分区表中索引状态标记为 不可用用UNUSABLE 的同时，Oracle 会直接释放这些索引分区原本占用的段（Segment）空间。
-the end-
笔者文章集合详见：
https://www.myhfxf.com
https://www.xiaofeihuangfu.com
CSDN:https://blog.csdn.net/xfhuangfu
ITPUB:https://blog.itpub.net/28373936/
微信公众号：xfhuangfu