DBA Hub

📋Steps in this guide1/5

Move table online in Oracle 12.2 DBACLASS

Move table online in Oracle 12.2. It is a new feature of oracle 12.2. We can move the table online to a different tablespace without impacting DML statements.

oracle clusteringintermediate
by OracleDba
15 views
1

Overview

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
2

Section 2

On session 1( Run the alter table move online command) On session 2( Do some DML activity)

Code/Command (click line numbers to comment):

1
2
3
SQL> alter table DBA_TAB move tablespace TS_TEST online;

SQL>  insert into DBA_TAB select * from DBA_TAB;
3

Section 3

Lets see, whether DML is getting blocked or not. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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

SQL>  insert into DBA_TAB select * from DBA_TAB;

982188 rows created.

SQL> commit;

Commit complete.
4

Section 4

Now only that alter table move statement ins running. I checked the index status: 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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

SQL> alter table DBA_TAB move tablespace TS_TEST online;

Table altered.
5

Section 5

Check the index status: 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!