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.
