首页 » ORACLE, ORACLE [C]系列 » Alert: Move partition Update Indexes索引空间可能未释放

Alert: Move partition Update Indexes索引空间可能未释放

在做分区表move维护时,可以使用’UPDATE INDEXES’ 选项更新  global 和 local indexes ,使索引并不失效,11.2.0.4版本前有个小bug,在update indexes期间也有短暂的local index失效,在oracle 12c 后引入新特性move online partition ,可以在DDL期间不锁DML操作,注意在online move 期间也会发生 UPDATE INDEXES or UPDATE GLOBAL INDEXES索引维护, 对于非online move 该update indexes选项同样可用, 所以对于update indexes与否online维护分区没有限制区别。

前几日发现一个现象,好些分区索引比分区表大好多倍,甚至分区表只是initial 大小如8M,对应的索引分区达GB。是因为此业务表存在大量的DELETE, 最近刚做过move partition的维护,释放了TABLE分区的空间,但是index 分区并未释放。之前听说过有客户因为Oracle 12c 的Asynchronous Global Index Maintenance 特性,在做了分区维护后Global Index 空间一直无法reuse的bug(bug# 29814995) 直到19.10 RU才修复. 没想到Local Index也存在空间相关问题,这里演示分区维护有些不同。

–demo version 19.3

# create partition table with global PK
CREATE TABLE anbob.test1_part (product NUMBER(16) PRIMARY KEY,customer VARCHAR2(4000),time_id DATE)
PARTITION BY RANGE (time_id)
(PARTITION test1_PART1 VALUES LESS THAN (TO_DATE('01-JUL-2013','DD-MON-YYYY')),
PARTITION test1_PART2 VALUES LESS THAN (TO_DATE('01-AUG-2013','DD-MON-YYYY')),
PARTITION test1_PART3 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY')),
PARTITION test1_PART4 VALUES LESS THAN (TO_DATE('01-OCT-2013','DD-MON-YYYY')),
PARTITION test1_PART5 VALUES LESS THAN (TO_DATE('01-NOV-2013','DD-MON-YYYY')),
PARTITION test1_PART6 VALUES LESS THAN (TO_DATE('01-DEC-2013','DD-MON-YYYY')),
PARTITION test1_PART7 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')),
PARTITION test1_PART_MAX VALUES LESS THAN (MAXVALUE));

insert into anbob.test1_part select rownum,'anbob'||rownum,TO_DATE('01-SEP-2013','DD-MON-YYYY')+rownum/24/60/60 from dual connect by rownum<=1000000; 

SQL> @seg anbob.test1_p

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        40 ANBOB                TEST1_PART                     TEST1_PART3                    TABLE PARTITION      USERS                                5120         12      17681

SQL> alter index anbob.SYS_C008051 rename to pk_TEST1_PART;
Index altered.
 
SQL> create index anbob.idx_test1_part_cust on anbob.test1_part(customer) local;
Index created.

SQL> @ind anbob.test1_part
Display indexes where table or index name matches %anbob.test1_part%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TEST1_PART                     IDX_TEST1_PART_CUST               1 CUSTOMER
                                                    PK_TEST1_PART                     1 PRODUCT


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANAL DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- --------- ------ ---------
ANBOB                TEST1_PART                     IDX_TEST1_PART_CUST            NORMAL     NO   N/A      YES  N     3       3195       1000000    1000000     193692 27-SEP-21 1      VISIBLE
                     TEST1_PART                     PK_TEST1_PART                  NORMAL     YES  VALID    NO   N     1          0             0          0          0 27-SEP-21 1      VISIBLE
SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        20 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                2560         12     185842

1 row selected.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        26 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                3328         12     185834

1 row selected.


truncate partition update indexes

SQL> alter table anbob.TEST1_PART truncate partition TEST1_PART3 update indexes;

Table truncated.

SQL> @seg anbob.test1_p

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         8 ANBOB                TEST1_PART                     TEST1_PART3                    TABLE PARTITION      USERS                                1024         12      17681

1 row selected.

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        20 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                2560         12     185842

1 row selected.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

1 row selected.

SQL> @printtab 'select * from dba_indexes where index_name="PK_TEST1_PART"';
OWNER                         : ANBOB
INDEX_NAME                    : PK_TEST1_PART
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : ANBOB
TABLE_NAME                    : TEST1_PART
TABLE_TYPE                    : TABLE
UNIQUENESS                    : UNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 0
LEAF_BLOCKS                   : 0
DISTINCT_KEYS                 : 0
AVG_LEAF_BLOCKS_PER_KEY       : 0
AVG_DATA_BLOCKS_PER_KEY       : 0
CLUSTERING_FACTOR             : 0
STATUS                        : VALID
NUM_ROWS                      : 0
SAMPLE_SIZE                   : 0
LAST_ANALYZED                 : 27-sep-2021 22:10:33
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
IOT_REDUNDANT_PKEY_ELIM       : NO
DROPPED                       : NO
VISIBILITY                    : VISIBLE
DOMIDX_MANAGEMENT             :
SEGMENT_CREATED               : YES
ORPHANED_ENTRIES              : YES
INDEXING                      : FULL
AUTO                          : NO
CONSTRAINT_INDEX              : YES
-----------------

PL/SQL procedure successfully completed.

SQL> @printtab 'select * from dba_indexes where index_name="IDX_TEST1_PART_CUST"';
OWNER                         : ANBOB
INDEX_NAME                    : IDX_TEST1_PART_CUST
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : ANBOB
TABLE_NAME                    : TEST1_PART
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               :
INI_TRANS                     :
MAX_TRANS                     :
INITIAL_EXTENT                :
NEXT_EXTENT                   :
MIN_EXTENTS                   :
MAX_EXTENTS                   :
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      :
LOGGING                       :
BLEVEL                        : 2
LEAF_BLOCKS                   : 3195
DISTINCT_KEYS                 : 1000000
AVG_LEAF_BLOCKS_PER_KEY       : 1
AVG_DATA_BLOCKS_PER_KEY       : 1
CLUSTERING_FACTOR             : 193692
STATUS                        : N/A
NUM_ROWS                      : 1000000
SAMPLE_SIZE                   : 1000000
LAST_ANALYZED                 : 27-sep-2021 22:14:20
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : YES
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : NO
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
IOT_REDUNDANT_PKEY_ELIM       : NO
DROPPED                       : NO
VISIBILITY                    : VISIBLE
DOMIDX_MANAGEMENT             :
SEGMENT_CREATED               : N/A
ORPHANED_ENTRIES              : NO
INDEXING                      : FULL
AUTO                          : NO
CONSTRAINT_INDEX              : NO
-----------------

PL/SQL procedure successfully completed.


SQL> ALTER INDEX anbob.PK_TEST1_PART COALESCE CLEANUP;

Index altered.

SQL> @printtab 'select * from dba_indexes where index_name="PK_TEST1_PART"';
OWNER                         : ANBOB
INDEX_NAME                    : PK_TEST1_PART
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : ANBOB
TABLE_NAME                    : TEST1_PART
TABLE_TYPE                    : TABLE
UNIQUENESS                    : UNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 0
LEAF_BLOCKS                   : 0
DISTINCT_KEYS                 : 0
AVG_LEAF_BLOCKS_PER_KEY       : 0
AVG_DATA_BLOCKS_PER_KEY       : 0
CLUSTERING_FACTOR             : 0
STATUS                        : VALID
NUM_ROWS                      : 0
SAMPLE_SIZE                   : 0
LAST_ANALYZED                 : 27-sep-2021 22:10:33
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
IOT_REDUNDANT_PKEY_ELIM       : NO
DROPPED                       : NO
VISIBILITY                    : VISIBLE
DOMIDX_MANAGEMENT             :
SEGMENT_CREATED               : YES
ORPHANED_ENTRIES              : NO
INDEXING                      : FULL
AUTO                          : NO
CONSTRAINT_INDEX              : YES
-----------------

PL/SQL procedure successfully completed.


SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        20 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                2560         12     185842

1 row selected.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

1 row selected.
 
SQL> insert into anbob.test1_part select rownum,'anbob'||rownum,TO_DATE('01-SEP-2013','DD-MON-YYYY')+rownum/24/60/60 from dual connect by rownum<=1000000; 1000000 rows created. SQL> COMMIT;

Commit complete.

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        38 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                4864         12     185842

1 row selected.

SQL>  @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        44 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART4                    INDEX PARTITION      USERS                                5632         12     185850
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

2 rows selected.

SQL>

SQL> alter table anbob.TEST1_PART truncate partition TEST1_PART3 update indexes;

Table truncated.

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        38 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                4864         12     191362

1 row selected.

SQL> insert into anbob.test1_part select 1000000+rownum,'anbob'||rownum,TO_DATE('01-SEP-2013','DD-MON-YYYY')+rownum/24/60/60 from dual connect by rownum<=1000000; 
1000000 rows created. 
SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        58 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                7424         12     191362

1 row selected.

Note:
truncate partition update indexes后,global index 空间无释放,似乎也没有reuse,即使手动执行COALESCE CLEANUP 清理孤立条目后。 local index发生rebuild空间已释放。

move partition with update indexes

SQL> delete anbob.test1_part;

1000000 rows deleted.

SQL> commit;
Commit complete.

SQL> alter table anbob.TEST1_PART move partition TEST1_PART4 update indexes;
Table altered.

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        38 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                4864         12     185842

1 row selected.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        44 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART4                    INDEX PARTITION      USERS                                5632         12     185850
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

2 rows selected.

Note:
move partition update indexes后GLOBAL和local index的索引段空间都未释放。

move partition without update indexes

insert into anbob.test1_part select rownum,'anbob'||rownum,TO_DATE('01-SEP-2013','DD-MON-YYYY')+rownum/24/60/60 from dual connect by rownum<=1000000;
insert into anbob.test1_part select 1000001+rownum,'anbob'||rownum,TO_DATE('01-AUG-2013','DD-MON-YYYY')+rownum/24/60/60 from dual connect by rownum<=10; SQL> @seg anbob.test1_part

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        40 ANBOB                TEST1_PART                     TEST1_PART4                    TABLE PARTITION      USERS                                5120         12      34065
         8 ANBOB                TEST1_PART                     TEST1_PART3                    TABLE PARTITION      USERS                                1024         12      17681

2 rows selected.

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        38 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                4864         12     185842

1 row selected.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        44 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART4                    INDEX PARTITION      USERS                                5632         12     185850
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

2 rows selected.

SQL> delete anbob.TEST1_PART partition(TEST1_PART4);

1000000 rows deleted.

SQL> commit;

SQL> alter table anbob.TEST1_PART move partition TEST1_PART4
  2  ;

Table altered.

SQL> @seg  anbob.TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         8 ANBOB                TEST1_PART                     TEST1_PART4                    TABLE PARTITION      USERS                                1024         12      19217
         8 ANBOB                TEST1_PART                     TEST1_PART3                    TABLE PARTITION      USERS                                1024         12      17681

2 rows selected.

SQL> @ind anbob.TEST1_PART;
Display indexes where table or index name matches %anbob.TEST1_PART%...

TABLE_OWNER          TABLE_NAME                     INDEX_NAME                     POS# COLUMN_NAME                    DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
ANBOB                TEST1_PART                     IDX_TEST1_PART_CUST               1 CUSTOMER
                                                    PK_TEST1_PART                     1 PRODUCT


INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANAL DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- --------- ------ ---------
ANBOB                TEST1_PART                     IDX_TEST1_PART_CUST            NORMAL     NO   N/A      YES  N     3       3195       1000000    1000000     193692 27-SEP-21 1      VISIBLE
                     TEST1_PART                     PK_TEST1_PART                  NORMAL     YES  UNUSABLE NO   N     1          0             0          0          0 27-SEP-21 1      VISIBLE
SQL>

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        38 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                4864         12     191362

1 row selected.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        45 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART4                    INDEX PARTITION      USERS                                5760         12     185850
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

2 rows selected.

NOTE:
Global和local index 的大小均未改变,因为是empty空分区,move 后索引都未失效。

move partition online

SQL> alter table anbob.TEST1_PART move partition TEST1_PART4 online;

Table altered.

SQL> @seg anbob.IDX_TEST1_PART_CUST

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART4                    INDEX PARTITION      USERS                                   8         12     266154
         0 ANBOB                IDX_TEST1_PART_CUST            TEST1_PART3                    INDEX PARTITION      USERS                                   8         12     185834

2 rows selected.

SQL> @seg anbob.PK_TEST1_PART

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
        38 ANBOB                PK_TEST1_PART                                                 INDEX                USERS                                4864         12     191362

1 row selected.

-- 10046 trace 
ALTER INDEX "ANBOB"."IDX_TEST1_PART_CUST" REBUILD  PARTITION "TEST1_PART4"
  NOPARALLEL

NOTE:
move online会递归做索引的rebuild, 所以local索引对应的分区释放了,但global index 空间未改变。

打赏

目前这篇文章还没有评论(Rss)

我要评论