Index PCTFREE And INI_TRANS Changed By ALTER TABLE MOVE ONLINE (Doc ID 2673508.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• The PCT_FREE and INI_TRANS of index was changed by ALTER TABLE MOVE ONLINE, which are supposed to be not changed.
  
  
  SQL> grant dba to <USER> identified by <PASSWORD>;
  
  Grant succeeded.
  
  SQL> conn <USER>/<PASSWORD>
  Connected.
  SQL> create table <TABLE NAME>(col1 number, col2 varchar2(30))SEGMENT CREATION
  IMMEDIATE PCTFREE 20 initrans 20;
  
  Table created.
  
  SQL> CREATE INDEX <INDEX NAME> ON <TABLE NAME> (COL1) PCTFREE 20 INITRANS 20;
  
  Index created.
  
  SQL> select PCT_FREE, INI_TRANS from dba_tables where table_name='<TABLE NAME>';
  
    PCT_FREE  INI_TRANS
  ---------- ----------
          20         20
  
  SQL> select PCT_FREE, INI_TRANS from dba_indexes where table_name='<TABLE NAME>';
  
    PCT_FREE  INI_TRANS
  ---------- ----------
          20         20
  
  SQL> alter table <TABLE NAME> move online;
  
  Table altered.
  
  SQL> select PCT_FREE, INI_TRANS from dba_tables where table_name='<TABLE NAME>';
  
    PCT_FREE  INI_TRANS
  ---------- ----------
          20         20
  
  SQL> select PCT_FREE, INI_TRANS from dba_indexes where table_name='<TABLE NAME>';
  
    PCT_FREE  INI_TRANS
  ---------- ----------
          10          2 <<<<<<<<<<<<<<<<<<<<<<<<<<<Changed
  
  SQL>
  
  
  
• This problem does not happen when execute ALTER TABLE MOVE without ONLINE clause.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.