SAPDBA

[Former Member]

Hi all,

If i want to extend a tablespace using SAPDBA, I have two options- either to add a datafile to it or reorganize the tablespace. I want to know what strategy to adapt in extending tablespaces. Which option should be selected and what are conditions on which it should be based.

Thanks

Cyrus

[Former Member · Accepted solution]

Hi Cyrus.

Reorganize is like "defragment" your tablespace. If you have added several datafiles maybe your tablespace needs reorganization. There are different forms to reorganize a tablespace, but the common one is to use a temporal tablespace.

I would recommend you that if your database is a newly installed, just extend the tablespace, but if you have more than 3 months in production operation maybe you need a reorganization.

Reorganize needs an in-depth analysis of your database. Be careful when extending tablespaces because you could be waisting disk space and affecting performance in a negative way.

The easiest way to obtain space is to extend the tablespace.

[Former Member · Accepted solution]

By the way, take a look in the following link.

http://help.sap.com/saphelp_nw04s/helpdata/en/08/5742084ae611d1894f0000e829fbbd/frameset.htm

Best regards,

JAvier

[Former Member]

"If i want to extend a tablespace using SAPDBA, I have two options- either to add a datafile to it or reorganize the tablespace."

NO!! If you want to extend a tablespace you can 1) add a datafile or 2) resize (increase) an existing datafile.

don't just add datafiles, man it drives me nuts when I see customer sites with thousands of small little datafiles especially when the disk is SAN storage. Check your operating system notes, but go for large file sizes if you're using SAN disk. There's rarely a need to segregate file I/O on a big properly configured SAN, so make datafiles 2 or 4 or even 10 gb in size. And resizing during production online use is A-Okay (with Oracle).

And use LMT to significantly reduce the need for database reorgs.