The alter table move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.
New features of Oracle 12c
Creating a tablespace and table for the experiment,
SQL> create tablespace tbs_1 datafile 'tbs_1.dbf' size 100m autoextend on next 10m; Tablespace created. SQL> create table table_migration(id int) tablespace tbs_1; Table created.
Scripts to enter bulk values in the table,
SQL> BEGIN FOR v_LoopCounter IN 1..5000000 LOOP INSERT INTO table_migration(id) VALUES (v_LoopCounter); END LOOP; END; / PL/SQL procedure successfully completed.
Command to verify the size of the table ,
SQL> select SEGMENT_NAME,BLOCKS/1024,TABLESPACE_NAME from dba_segments where segment_name='TABLE_MIGRATION'; SEGMENT_NAME BLOCKS/1024 TABLESPACE_NAME -------------------- ----------- ------------------------------ TABLE_MIGRATION 3.875 TBS_1 SQL> select count(*) from TABLE_MIGRATION; COUNT(*) ---------- 500500
Deleting some values to the table ,
SQL> delete from TABLE_MIGRATION where id between 100 and 200000; 200302 rows deleted. SQL> select SEGMENT_NAME,BLOCKS/1024,TABLESPACE_NAME from dba_segments where segment_name='TABLE_MIGRATION'; SEGMENT_NAME BLOCKS/1024 TABLESPACE_NAME -------------------- ----------- ------------------------------ TABLE_MIGRATION 3.875 TBS_1 SQL> select count(*) from TABLE_MIGRATION; COUNT(*) ---------- 300198
Moving table from tablespace tbs_1 to USERS,
SQL> alter table TABLE_MIGRATION move ONLINE tablespace USERS; Table altered. SQL> select SEGMENT_NAME,BLOCKS/1024,TABLESPACE_NAME from dba_segments where segment_name='TABLE_MIGRATION'; SEGMENT_NAME BLOCKS/1024 TABLESPACE_NAME -------------------- ----------- ------------------------------ TABLE_MIGRATION .5 USERS
Recent Posts
