Greetings,
how do I calculate the temp space and undo space required for the "ALTER TABLE $SCHEMA.$table MOVE LOB ($lob) STORE AS (TABLESPACE $NEW_LOB_TABLESPACE)"
We have very large segments, and currently have a small undo
-- show MAX SEGMENT SIZE
SELECT tablespace_name, SEGMENT_TYPE, MAX(CEIL((bytes / 1024 / 1024) / 1024)) AS max_undo_space_estimate_GB
FROM dba_segments
WHERE (SEGMENT_TYPE like '%LOB%' or SEGMENT_TYPE = 'TABLE')
AND tablespace_name like 'ELF%'
group by tablespace_name, SEGMENT_TYPE
/
TABLESPACE_NAME SEGMENT_TYPE MAX_SINGLE_SEGMENT_SIZE_GB
------------------------------ ------------------ --------------------------
S1_DATA TABLE 198
S1_LOB LOBSEGMENT 2350
-- Show UNDO Tablespace
SELECT
tablespace_name,
CEIL(bytes / 1024 / 1024 / 1024) AS total_size_gb
FROM dba_data_files
WHERE tablespace_name like 'UNDO%'
ORDER BY tablespace_name, file_name
/
TABLESPACE_NAME TOTAL_SIZE_GB
------------------------------ -------------
UNDO_1 25
UNDO_2 25
UNDO_3 25
UNDO_4 25
-Tom
An ALTER TABLE MOVE will not require a lot of undo as the original segment is left in place whilst the operation takes place.
There will be *slight* increase in undo consumption if you are doing the operation online for keeping of journal of changes to re-apply once the operation completes.
For example, here's my setup
SQL> create table t ( c1 int, c2 int, c3 clob ) lob ( c3) store as ( disable storage in row );
Table created.
SQL>
SQL> insert into t
2 select rownum, rownum,
3 rpad(rownum,4000)||rpad(rownum,4000)||rpad(rownum,4000)
4 from dual
5 connect by level <= 50000;
50000 rows created.
SQL>
SQL> commit;
Commit complete.
Now I am going to move it , whilst in another session I monitor v$transaction for undo consumption, ie "select sysdate d, USED_UBLK, USED_UREC from v$transaction;" every half a second
SQL> alter table t move lob ( c3 ) store as ( disable storage in row );
Table altered.
08/04/2025 08:11:23 4 81
08/04/2025 08:11:23 5 160
08/04/2025 08:11:24 6 245
08/04/2025 08:11:24 7 320
08/04/2025 08:11:25 8 380
08/04/2025 08:11:25 9 427
08/04/2025 08:11:26 10 486
08/04/2025 08:11:26 11 547
08/04/2025 08:11:27 12 605
08/04/2025 08:11:27 13 658
08/04/2025 08:11:28 14 716
08/04/2025 08:11:28 15 765
08/04/2025 08:11:29 15 810
08/04/2025 08:11:29 16 869
08/04/2025 08:11:30 17 928
08/04/2025 08:11:30 18 986
08/04/2025 08:11:31 19 1022
08/04/2025 08:11:31 19 1068
08/04/2025 08:11:32 20 1120
08/04/2025 08:11:32 21 1170
We moved 500MB of LOB but we never used more than 21 undo blocks
Your mileage can vary based on indexes etc, but you can use the above to do some testing on your own system