Free space requirements for ALTER TABLE

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.

2 comments

  1. Hi,

    Does ALTER TABLE MOVE (OFFLINE) generates redo? And what about undo? For both I guess not (minimal). Thanks.

  2. 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

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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