ENABLE ROW MOVEMENT with MSSM

As I demonstrated in my previous blog post "ENABLE ROW MOVEMENT" the ALTER TABLE ... ENABLE ROW MOVEMENT is not just for supporting the ALTER TABLE ... SHRINK SPACE.

Furthermore, unlike ALTER TABLE ... SHRINK SPACE which requires that the Table be created in a Tablespace with Segment Space Management AUTO ("ASSM"), ENABLE ROW MOVEMENT can be done for a table in a Segment Space Management MANUAL ("MSSM") Tablespace as well.

SQL> create tablespace MSSM
  2  datafile '/addtl/oracle/oradata/orcl/MSSM.dbf' size 100M
  3  extent management local segment space management manual
  4  /

Tablespace created.

SQL> 
SQL> 
SQL> -- create a "normal" partitoned table
SQL> --  this time create it in an MSSM tablespace
SQL> drop table my_emp_tbl purge;
drop table my_emp_tbl purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table my_emp_tbl
  2  (emp_id   number not null primary key,
  3   country_id  varchar2(2),
  4   emp_name varchar2(50),
  5   join_date date)
  6  partition by list (country_id)
  7  (
  8  partition my_emp_tbl_in values ('IN'),
  9  partition my_emp_tbl_sg values ('SG'),
 10  partition my_emp_tbl_us values ('US')
 11  )
 12  tablespace MSSM
 13  /

Table created.

SQL> 
SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'MY_EMP_TBL'
  4  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
MY_EMP_TBL_IN                  MSSM
MY_EMP_TBL_SG                  MSSM
MY_EMP_TBL_US                  MSSM

SQL> 
SQL> 
SQL> insert into my_emp_tbl
  2  select rownum,
  3  decode(mod(rownum,3),0,'IN',1,'SG',2,'US'),
  4  dbms_random.string('X',40),
  5  sysdate-365+rownum
  6  from dual connect by level < 100
  7  /

99 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> -- collect statistics on row counts
SQL> exec dbms_stats.gather_table_stats('','MY_EMP_TBL',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'MY_EMP_TBL'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
MY_EMP_TBL_IN                          33
MY_EMP_TBL_SG                          33
MY_EMP_TBL_US                          33

SQL> 
SQL> -- identify employee 4
SQL> select * from my_emp_tbl where emp_id = 4;

    EMP_ID CO EMP_NAME
---------- -- --------------------------------------------------
JOIN_DATE
---------
         4 SG WS46VU0RIJDKOIXXAZSEPWUIVG0QBHTL4FUBNYPY
17-JUL-10


SQL> -- change the employees country
SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;
update my_emp_tbl set country_id = 'IN' where emp_id = 4
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> 
SQL> -- enable row movement
SQL> alter table my_emp_tbl enable row movement;

Table altered.

SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;

1 row updated.

SQL>  

I know for a fact that ALTER TABLE ... ENABLE ROW MOVEMENT appears in the 8i documentation --- predating ASSM Tablespaces.

Thus, ALTER TABLE ... ENABLE ROW MOVEMENT is independent of and significantly predates ALTER TABLE ... SHRINK.

.
.
.