In previous Releases, To move a table to a different tablespace or segment, we need to take downtime for this activity And after moving the table, we have to rebuild/recreate the indexes.

Now with Oracle 12.2, We can move the table online to a different segment or tablespace, without impacting the DML activities and without making the indexes unusable. It will take care of the Index maintenance activity.

SYNTAX – ALTER TABLE MOVE ONLINE;

Lets test this feature.

SQL> select table_name,tablespace_name from dba_tables where table_name='DBA_TAB';


TABLE_NAME TABLESPACE_NAME
------------------ ------------------------------
DBA_TAB USERS

set lines 299
col index_name for a23
SQL> select index_name,status,TABLESPACE_NAME from dba_indexes where table_name='DBA_TAB';

INDEX_NAME STATUS TABLESPACE_NAME
----------------------- -------- ------------------------------
DBA_IDX1 VALID USERS
DBA_IDX2 VALID USERS

On session 1( Run the alter table move online command)

 
SQL> alter table DBA_TAB move tablespace TS_TEST online;

On session 2( Do some DML activity)

 
 
SQL>  insert into DBA_TAB select * from DBA_TAB;

Lets see, whether DML is getting blocked or not.

 
 
SQL> blocker.sql
 
   INST_ID BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT
---------- ---------------- ---------- ---------- ---------------
         1              342        401      64675             168
 
 
-- Lets get the sql_text from sql_id
 
SQL> @csql
Enter value for 1: 342
old   2: (select sql_hash_value from gv$session where sid=&1)
new   2: (select sql_hash_value from gv$session where sid=342)
 
SQL_TEXT
--------------------------------------------------------------------------------
 insert into DBA_TAB select * from DBA_TAB
 
SQL> csql
Enter value for 1: 401 ------- >> BLOCKED SESSION
old   2: (select sql_hash_value from gv$session where sid=&1)
new   2: (select sql_hash_value from gv$session where sid=401)
 
SQL_TEXT
--------------------------------------------------------------------------------
alter table DBA_TAB move tablespace TS_TEST online

We can see the that the alter table statement is blocked by the insert statement, Even though we fired the Insert statement after the alter table move command. It shows the DMLs are not getting impacted due to this alter table move command online. However, the time taken to complete the alter table move online may take more time.

After few seconds, the DML statement completed.

 
SQL>  insert into DBA_TAB select * from DBA_TAB;
 
982188 rows created.
 
SQL> commit;
 
Commit complete.

Now only that alter table move statement ins running.

I checked the index status:

 
 
SQL> select index_name,status,TABLESPACE_NAME,TEMPORARY  from dba_indexes where table_name='DBA_TAB';
 
INDEX_NAME                                                                           STATUS   TABLESPACE_NAME                T
-------- ------------------------------       -------    --------------
DBA_IDX1                                                                             VALID    USERS                          N
DBA_IDX2                                                                             VALID    USERS                          N
DBA_IDX1_SYS_95234                                                                   VALID    USERS                          N
DBA_IDX2_SYS_95234                                                                   VALID    USERS                          N

Now we are seeing more 2 indexes DBA_IDX1_SYS_95234,DBA_IDX2_SYS_95234 . It seems these indexes were created for the interim purpose during the move online operation.

After 2 min, the alter statement completed.

 
SQL> alter table DBA_TAB move tablespace TS_TEST online;
 
Table altered.

Check the index status:

 
 
SQL>  select index_name,status,TABLESPACE_NAME  from dba_indexes where table_name='DBA_TAB';
 
INDEX_NAME              STATUS   TABLESPACE_NAME
----------------------- -------- ------------------------------
DBA_IDX1                VALID    USERS
DBA_IDX2                VALID    USERS
 
SQL> select table_name,tablespace_name from dba_tables where table_name='DBA_TAB';
 
TABLE_NAME         TABLESPACE_NAME
------------------ ------------------------------
DBA_TAB            TS_TEST
 

The table has been moved to the new tablespace. All indexes are also valid, so no need to rebuild or recreate any of the indexes. 🙂

Oracle 12.2 is making our job much easier.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.