Datafiles reorganization - Split datafiles Oracle

[Former Member]

Helo,

I have a Datafile to 100GB and it must be split into two Datafile of 50GB. How should I proceed? With exp/imp tablespace is possible?

With Brtools I can do only reorganization of tables?

Thanks

[stefan_koehler]

Hello Leo,

the other thing i am wondering about .. how can your data file get so big?

Refering to metalink note #271482.1:

Max Size of a data file on any operating system:
4,194,303 multiplied by the  value of the DB_BLOCK_SIZE parameter

So in the case of a SAP standard system: 4194303 * 8 kb = 33554424 kb = 32 GB

Regards

Stefan

[Former Member]

Hello Leo,

What is the reason for doing this split? 100 GB is pretty big for a single datafile - but so is 50 GB. Having two datafiles instead of one isn't going to give you much benefit either. The only possible advantage I can see is that if the file gets corrupt and has to be restored, the restore/recovery will take less time.

Regards,

Mark

[anindya_bose]

You can follow the method Stefan described. Or you can do hom system copy. ( SAP standard method)

[stefan_koehler]

Hello Leo,

you can create a seperate tablespace with two data files a 50 GB.

After that you can reorg the whole tablespace online (with BR*Tools) into the new tablespace and after that drop the old one.

The online reorganization depends on some factors (LONG, LOB data tyes).

For more information take a look at sapnote #646681, #541538 and #835552.

Regards

Stefan