Skip to Main Content
  • Questions

  • Comparison between ALTER TABLE MOVE ONLINE and DBMS_REDEFINITION

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alberto.

Asked: December 01, 2023 - 8:34 am UTC

Last updated: December 17, 2023 - 11:43 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

I need to move online a huge table with BLOBs (around 25 TB) to a new tablespace with bigger block size.

Wich method is recommended? Is DBMS_REDEFINITION still the method recommended by oracle? Or is now ALTER TABLE MOVE ONLINE?

and Connor said...

"ALTER TABLE MOVE ONLINE" is nice and simple to run, and also allows parallel operations.

But in terms of "recommended" its not really as simple as that - because 25TB will take a while, and because the operation is atomic, you could run for hours (days?) and then have it fail (space etc) and you're back to square 1.

Sometimes for *massive* operations, you take advantage of what knowledge *you* have, eg, maybe you can copy a lot of the data manually because its read-only etc.

And the other question is - any reason why you need the bigger block size?

Rating

  (2 ratings)

Comments

Alberto, December 04, 2023 - 3:51 pm UTC

Thank you very much for your answer, Connor.

The bigger block size is because we expect the data will increase even more. With current block size we can "only" have a tablespace of 32 TB and we plan to move to a tablespace with 32K block to be able to grow until 128 TB.

We also plan to compress data in the movement.

This database is for a product not developed by my company, so it's not easy for us to know if we can move the data in parts. At this moment, the requirement is to move everything keeping the application up.
Connor McDonald
December 07, 2023 - 6:24 am UTC

I would checkout BIGFILE tablespaces.

A reader, December 07, 2023 - 9:23 am UTC

Hi Connor,
Yes, we're using BIGFILE tablespaces. According to oracle documentation:

A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.

We want to move the table to a larger block size tablespace so it can grow to 128 TB, as currently this table is using 25 TB and growing. In our current tablespace with blocksize of 8K it only can grow to 32 TB.
Connor McDonald
December 17, 2023 - 11:43 pm UTC

Surely this table is partitioned?

(Because you get a tablespace per partition)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here