FreeSpace

[vijay8282]

Hi,

I am running SAP R/3 46B Oracle 816 on NT.

Today on checking free space of tablespaces, i found that lot of free space is shown. And on checking deeply I found that space is left free in the form of few contiguous blocks.

What should I need to do to reorganize this and I fel that this is affecting performance also. Am I right.

eg of free space details


File Block-ID Kbyte free Blocks free

2 392 1,040 130
2 652 1,040 130
2 912 1,040 130
2 1,172 1,040 130
2 1,432 1,040 130
2 1,692 1,040 130
2 1,952 1,040 130
2 2,212 1,040 130
2 10,662 16,640 2,080
92 36,430 1,708,560 213,570


After this again , my tool says that tablespace is 81% full. and i need to add datafile almost every 2 weeks. What would be better to do, keep on adding files or reorganize this.

[samdba]

Hi,

If the free extents are contegeous, Coalease the table spaces.

You can do Export with Compress=Y option then Drop the schema/table and import it back.

Also, you should find out the basic cause of having so many free extents.

Thanks,

[Sureshy]

Investigate deallocate unused command

[svk]

Coalesce will gather all the contiguous free blocks a make one sinlge large free block.

You can automatically accomplish this if you keep the PCTINCREASE value for the tablespace non-zero. In that case, smon will keep on doing the coalesce job.

But, you should do this ONLY if the objects in this tablespace have been created with their own storage parameters and hence will not inherit the tablespace's parameters.

[xsf3190]

If you convert all your tablespaces to Local Managed with uniform extent size, you'll never have to worry about free space/fragmentation ever again. Starting from 8.1.6, no tablespace (other than SYSTEM) should be dictionary managed.

Mark.

[vijay8282]

These tablespaces are in my SAP Database.
Pctfree is 0. So autocoalesce will not work.

How important is that I do coalesce.

SAM , the free extents are not contiguous.

Advise plz.

Regards