alter table move online - table defragmentation

 

MOVE ONLINE implemented in 12cR2 maintains indexes so they do not 

become UNUSABLE and DML does not have to wait. (Oracle Database)

When you execute an "ALTER TABLE MOVE ONLINE" command in Oracle, the database essentially creates a new copy of the table in the specified target tablespace while maintaining accessibility to the original table for queries and DML operations, effectively "moving" the data without taking the table offline; this process happens in the background by allocating new data blocks, copying data over, and updating internal metadata, all while allowing concurrent user access to the table with minimal performance impact. 

Key points about "ALTER TABLE MOVE ONLINE":

Online operation:

The most important aspect is that the table remains available for reads and writes during the move, unlike a traditional offline table move which would require taking the table offline temporarily.

Data copying:

The database internally copies data from the old table segments to the new ones in the target tablespace, managing the process efficiently to minimize disruption.

Index management:

When moving a table online, Oracle automatically updates associated indexes to point to the new data location, ensuring data consistency.

Parallelism:

Depending on the Oracle version and configuration, the move operation can utilize multiple processes to speed up the data transfer.

Important considerations:

Performance impact:

While the move is designed to be online, heavy concurrent DML operations may still experience some performance degradation during the data copy process. 

Not for all scenarios:

Certain operations like parallel DML or direct path inserts might not be supported during an online table move. 

Availability requirement:

This feature is particularly useful when you need to move a large table to a different tablespace for storage optimization without impacting ongoing application operations. 

Check the size of the table and Indexes:

col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

SEGMENT_NAME    SEGMENT_TYPE            GB
--------------- ------------------ ----------
EMP              TABLE              158.641602
EMP~0            INDEX              124.044922
EMP~001          INDEX              74.7177734
EMP~003          INDEX              60.8652344
EMP~004          INDEX              17.7597656
EMP~002          INDEX              15.6337891

Check Fragmentation in Table:

set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from     dba_tables a,
    dba_segments b
where    a.table_name = b.segment_name
and     a.owner = b.owner
and  a.table_name = '&TABLE_NAME'
order by 6;

OWNER      TABLE_NAME    Size(G)   Actual(G)    LAST ANAL          Diff(G)        % Frag
---------- ---------- ----------   ---------- ---------------      ----------    ----------
SAPDAT     EMP         158.641602 71.7050629    27-NOV-24               86          54.8005932

Check the status of the index, degree and last analyzed :-

col STATUS for a10
select index_name,status,degree from dba_indexes where table_name = '&table_name';

INDEX_NAME           STATUS   DEGREE
-------------------- -------- ----------
EMP~0                VALID        1
EMP~001              VALID        1
EMP~002              VALID        1
EMP~003              VALID        1
EMP~004              VALID        1


col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

TABLE_NAME LOGGING    DEGREE     LAST_ANAL
---------- ---------- ---------- ---------
EMP    YES                 1      27-NOV-24

Check the temporary tablespace size

col Free_Space_GB for 99999999

select TABLESPACE_NAME ,tablespace_size/1024/1024/1024 total_GB ,ALLOCATED_SPACE/1024/1024/1024 Allocate_Space_GB ,free_space/1024/1024/1024 Free_Space_GB

from dba_temp_free_space;

Perform De-fragmentation on table online :-

set time on;
set timing on;

SQL> alter table sapdat.EMP move online parallel 5;

Table altered.

Elapsed: 01:22:54.21

Check the degree of the table:-

col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

TABLE_NAME LOGGING    DEGREE     LAST_ANAL
---------- ---------- ---------- ---------
EMP    YES                 1     05-FEB-25

Check the Status and Degree of the Indexes:-

col STATUS for a10
select index_name,status,degree from dba_indexes where table_name = '&table_name';

INDEX_NAME      STATUS     DEGREE
--------------- ---------- ----------
EMP~0           VALID        1
EMP~001         VALID        1
EMP~002         VALID        1
EMP~003         VALID        1
EMP~004         VALID        1

Note: Index status is still VALID 

Check the table and indexes size:-

select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB from dba_segments where SEGMENT_NAME LIKE 'EMP%' AND OWNER='SAPDAT' order by GB desc;

SEGMENT_NAME         SEGMENT_TYPE               GB
-------------------- ------------------ ----------
EMP                   TABLE              85.6797485
EMP~0                 INDEX              56.4254761
EMP~003               INDEX              31.8675537
EMP~001               INDEX              31.8223877
EMP~004               INDEX              11.7949829

Note: You can see that indexes size also reduced when table is moved online so no need to 'REBUILD INDEX' and status of indexes are also VALID

Perform gather stats on table:-

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SAPDAT','EMP',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto', degree => DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:04:17.70

Check the LAST_ANALYZED for the table :

col degree for a10
col LOGGING for a10
select table_name,LOGGING,degree,LAST_ANALYZED from dba_tables where table_name='EMP';

TABLE_NAME LOGGING    DEGREE     LAST_ANAL
---------- ---------- ---------- ---------
EMP         YES           1      05-FEB-25

Check Fragmentation on Table:

set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from     dba_tables a,
    dba_segments b
where    a.table_name = b.segment_name
and     a.owner = b.owner
and  a.table_name = '&TABLE_NAME'
order by 6;

OWNER      TABLE_NAME    Size(G)  Actual(G)   LAST ANAL          Diff(G)     % Frag
---------- ---------- ----------  ---------- ---------------    ---------- ----------
SAPDAT     EMP         85.6797485 70.8516945  05-FEB-25               14    17.3063697