Posted by One of the very cool features from 12.2 onwards is that the vast majority of segment operations that require rewrite of the existing data can be done online without interruption to service. But whether an operation is being done online or not, since DDL operations need to be atomic, the general rule is that when an object is being reorganised this work cannot be done in place. The reorganised segment must be written elsewhere in order for the original segment to be left untouched in the event of the operation failing midway. (No-one wants their table to vanish just because someone pulled out the wrong power cable!)
General consensus from the “blog-sphere” is that you thus need enough space to hold the existing segment and space for the new segment to be created, but I thought I’d explore that a little further, and there’s a few surprises.
I’ll start with a simple example where I move an existing table. To gauge how much space is needed even transiently I will do all of the work in a very small tablespace that is set to autoextend. As space is required, the tablespace will grow in size and its final size is a reasonable estimate of how much space was required, even if at the end of the operation the segment sizes are smaller.
Example 1: ALTER TABLE MOVE
My tablespace starts at 10MB but I will loading a table that is approximately 1300MB and the move it with a standard offline (ie, table is locked) MOVE command.
SQL> create tablespace DEMO
2 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 10m autoextend on next 1m;
Tablespace created.
SQL>
SQL> create table t tablespace demo as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 );
Table created.
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
1344
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
1346
SQL>
SQL> alter table t move;
Table altered.
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
2747
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
1344
SQL>
You can see that after segment creation, the tablespace grew to approx 1350MB, matching that of the single segment that is within it. After the ALTER TABLE MOVE command is completed, the segment remains at the same size but the tablespace grew to ~2750MB. This aligns with the expectation that (temporarily) we needed 1350MB for the segment we created, plus another 1350MB for the new segment that is the output of the MOVE command. Once the move is completed, a dictionary update points to the new segment and the task is done.
Example 2: ALTER TABLE MOVE COMPRESS
This time the target segment is a compressed version of the original segment. The original segment is still 1350MB and (as you’ll see shortly) the compressed segment ends up being ~410MB. But what will the tablespace consumption be?
SQL> create tablespace DEMO
2 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 10m autoextend on next 1m;
Tablespace created.
SQL>
SQL> create table t tablespace demo as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 );
Table created.
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
1344
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
1346
SQL>
SQL> alter table t move compress;
Table altered.
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
1883
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
408
Once again, the results align reasonably well with expectation. The two segments existing concurrently needed just under 1900MB of tablespace to handle the operation.
Example 3: ALTER TABLE MOVE ONLINE
Both of the above examples are historically how we had to perform segment reorganisations on heap tables unless we headed down into DBMS_REDEFINITION territory. Space consumption is as anticipated, but the table is unavailable during the operation and that outage could be lengthy if that table is large.
Lets explore the same operations this time taking advantage of the ONLINE capabilities in 12.2 onwards.
SQL> create tablespace DEMO
2 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 10m autoextend on next 1m;
Tablespace created.
SQL>
SQL> create table t tablespace demo as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 );
Table created.
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
1344
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
1346
SQL>
SQL> alter table t move online;
Table altered.
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
3347
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
1344
Notice the jump in transient space required. The tablespace has grown to 3300MB. In another session, while the ALTER TABLE MOVE command was running, I repeatedly queried DBA_SEGMENTS for the DEMO tablespace. You get some interesting results
SQL> select segment_name, bytes
2 from dba_segments
3 where tablespace_name = 'DEMO'
4 /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SYS_RMTAB$_H125849 28311552
SYS_IOT_TOP_125863 65536
135.178 150994944
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SYS_RMTAB$_H125849 38797312
SYS_IOT_TOP_125863 65536
135.178 201326592
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SYS_RMTAB$_H125849 44040192
SYS_IOT_TOP_125863 65536
135.178 226492416
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SYS_RMTAB$_H125849 48234496
SYS_IOT_TOP_125863 65536
135.178 251658240
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SYS_RMTAB$_H125849 52428800
SYS_IOT_TOP_125863 65536
135.178 268435456
SQL> /
SEGMENT_NAME BYTES
------------------------------ ----------
T 1409286144
SYS_RMTAB$_H125849 54525952
SYS_IOT_TOP_125863 65536
135.178 285212672
To perform the operation online, additional structures are needed to track any incoming changes to the data whilst the move is taking place. The segment “135.178” is the new segment that is being built and thus it grows with each execution as more data is written, but notice that the SYS_RMTAB$$_H125849 table also grows during the operation. A describe on that table suggests some sort of ROWID tracking going on during the operation
SQL> desc SYS_RMTAB$_H125849
Name Null? Type
--------------------------------- -------- ------
SRC_ROWID NOT NULL ROWID
TGT_ROWID NOT NULL ROWID
Example 4: ALTER TABLE MOVE COMPRESS ONLINE
As you have probably already guessed, there is a similar additional space requirement when compressing the data because the additional structures are needed more for the “online” element of the operation than the compression part.
SQL> create tablespace DEMO
2 datafile 'X:\ORACLE\ORADATA\DB19\PDB1\DEMO.DBF' size 10m autoextend on next 1m;
Tablespace created.
SQL>
SQL> create table t tablespace demo as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 );
Table created.
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
1344
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
1346
SQL>
SQL> alter table t move compress online;
Table altered.
SQL>
SQL> select bytes/1024/1024 from dba_data_files
2 where tablespace_name = 'DEMO';
BYTES/1024/1024
---------------
2420
SQL>
SQL> select bytes/1024/1024
2 from user_segments
3 where segment_name = 'T';
BYTES/1024/1024
---------------
408
Conclusion
With some detailed tracing and analysis, it would probably be possible to diagnose all of the underlying structures required for an online table move, and come up with an exact estimate of how large each object will grow to. But that seems an effort not worth pursuing given that it could easily be platform, version and even patch level dependent, and because its never a good idea to run that close to the wire on free space when it comes to any form of segment reorganisation anyway. If you are going to the effort of moving a segment, then you want to ensure that the operation is not going fail, especially since it is typically the larger segments that we typically perform such operations on, so having plenty of headroom just makes sense.
But be aware that an ONLINE version of these operations will most likely need more space than its offline equivalent, and the best way to estimate this is always going to be to perform some experiments with subsets of your own data in your testing environments before charging ahead in Production.
Learning is not a spectator sport 
Hi,
Does ALTER TABLE MOVE (OFFLINE) generates redo? And what about undo? For both I guess not (minimal). Thanks.
Yes (by default) because we’re changing blocks and changed blocks are logged
SQL> @mystat
Enter value for statname: redo size
NAME VALUE
———————————————————— ———-
redo size 676
redo size for lost write detection 0
redo size for direct writes 0
SQL> alter table t move;
Table altered.
SQL> @mystat
Enter value for statname: redo size
NAME VALUE
———————————————————— ———-
redo size 12761396
redo size for lost write detection 0
redo size for direct writes 12651156