Skip to Main Content
  • Questions

  • How to split a large (Loaded) Datafile into small ones

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: January 03, 2008 - 5:25 pm UTC

Last updated: January 04, 2008 - 1:12 pm UTC

Version: 9208

Viewed 1000+ times

You Asked

Hi Tom,
Happy New Year.

I have tablespace SAY XYZ_FAT_TABLESPACE which has only one Datafile and it has now grown to 20GB.

I want to split this Loaded Datafile into 4 so that each is 5GB and can then indepandantly expand.

How to do this ?

The Tablespace is the default to only one user say XYZ
and User XYZ has most of its objects in this tablespace , but all its indexes are in the seperate Index Tablespace "XYZ_INDEX_TABLESPACE"

CREATE USER "XYZ" PROFILE "DEFAULT"
IDENTIFIED BY "********" DEFAULT TABLESPACE "XYZ_FAT_TABLESPACE"
TEMPORARY TABLESPACE "TEMP"
QUOTA UNLIMITED
ON "BIG_FAT_TABLESPACE"
QUOTA UNLIMITED
ON "XYZ_INDEX_TABLESPACE"
QUOTA UNLIMITED
ON "TEMP"

Can you please give an example with the steps involved.(Shrink is not possible as it has lots of data and I dont want to shrink, want to split )

Thanx you.

and Tom said...

You would have to totally reorganize this object.

You would typically

a) create a new tablespace, with 4 datafiles
b) alter table T move into_new_tablespace;
c) rebuild the indexes in their tablespaces (since all rowids change)


You could use dbms_redefinition as well - to do this "online" (search on this site, there are examples)

Or you could do what I would do which is.....

nothing. Leave it be. Add another file to the tablespace, do not let the existing file grow anymore and just let it be.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alexander the ok, January 03, 2008 - 10:27 pm UTC

I hit an O/S file process limit for a 12 gig tempfile the other day during an export (AIX 5.3). The ulimit was set to unlimited.

There may be some merit for wanting to shrink it down.
Tom Kyte
January 04, 2008 - 11:38 am UTC

the file is already 20gig and they did not report anything negative. I would leave it entirely and utterly alone.

Can TTS export/import help here

A reader, January 04, 2008 - 8:59 am UTC

Tom,
Can Transportable Tablespace export/import help here.

Tom Kyte
January 04, 2008 - 12:08 pm UTC

no, with a transport, you are explicitly preserving the already formatted datafiles

the only way to turn a 20g datafile into 4x5gb datafiles would be a reorganization of the segment - alter table T move, whatever - but a full reorganization.

sarang, January 04, 2008 - 12:20 pm UTC

Tom, what happened to the lob indexes in case of alter table move?

Tom Kyte
January 04, 2008 - 1:12 pm UTC

if you move the LOB (lob doesn't have to move) the lob indexes are maintained transparently as part of moving the lob.

regardless - you need do nothing for the lob index, it is either not affected or maintained for you.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here