Oracle Table Migration – Free Space Requirements for Alter Table Move

alter-tabledisk-spaceoracleoracle-11g-r2tablespaces

During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move?

exemple:. I have 30gb of free space on that disk, table of 1TB in tablespace_1, i wanna move the table using alter table move to tablespace_2.. can i do it or do i need to have more space available? if so, how much free space available?

Thx a lot 🙂

Best Answer

Table move copies the whole table into a new segment, then drops the old segment. This means you need enough space to store the old segment and the new segment at the same time. For a 1 TB table full of data, you need another 1 TB free space for the move to succeed.

Related Solutions

Postgresql – In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation

Try to recreate the index concurrently, so the original one is still used while creating the new.

CREATE INDEX CONCURRENTLY idx_name 
ON table_name
USING BTREE(col_name)
TABLESPACE new_tblspace

Oracle – How to Reorganize LOB Segment

Can I use the same tablespace the lob is in?

Yes.

The total free space in that tablespace needs to be 200Gb?

No. Depends on the amount of data in your LOB segment. If your segment is 200 GB, but you have only 5 GB data in it (because the rest of the data was deleted), then you do not need 200 GB.

During moving the lob is locked?

Not necessarily. Starting with 12.2, alter table has the move online option:

alter table mytable move online lob (lob_column) store as (tablespace users);

The above option is not available in 12.1.

Best Answer

Related Solutions

Postgresql – In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation

Oracle – How to Reorganize LOB Segment

Related Question