locally managed tablespaces-small tables

[khussain]

I have a schema that has 1800 small tables 40960 bytes.

I had created the tablespace for this schema as 'extent management local autoallocate', since each extent has to be 1M, a lot of space is being wasted.

Is my only choice DICTIONARY managed ?

I know I can make it local uniform, but then there are some other tables in the schema that are sort of large and will have 200 extents - what should I do ?

[sreddy]

Originally posted by khussain
I had created the tablespace for this schema as 'extent management local autoallocate', since each extent has to be 1M, a lot of space is being wasted.

What do you mean by wasted?? this is how AUTOALLOCATE parameter works:

AUTOALLOCATE keyword causes Oracle to take control of extent allocation. Oracle will use extent sizes of 64KB, 1MB, 8MB, and 64MB to manage space in this tablespace.

If you create a new table, the first extent will be 64KB in size. Subsequent extents will also be 64KB, until the table reaches 1MB in size. At that point the size of subsequent extents for the table will be increased to 1MB. When the table reaches 64MB in size, the extent size will be increased again to 8MB. Finally, if the table reaches a size of 1GB, the extent size will be increased one last time, to 64MB.

[khussain]

What I mean by waste is that, the schema that I am importing into the locally management tablespace has the following size:

In DICTIONARY managed: 547 MB
In LOCALLY managed: 2000MB +

Again, this is because this schema has 1800 tables of 40960 bytes...

So, in my mind this is too much wastage of disk space...

I am testing with UNIFORM size of 256K, it seems to be much better.

[marist89]

Of course there will be wasted space. You are specifying uniform extents of 256K but are only using 40K. Either turn on autoallocate which will allocate 64K extents for most of your tables or use smaller UNIFORM size, say 64K. So what if your tables have 200 extents?

[khussain]

I don't really worry about 200 extents either; as long as it is not because of my configuration.

I cannot make it auto allocate, because our block size is 16K, and that triggers Oracle to make the extent size 1M as default in an auto allocate tablespace.
That is why I am using UNIFORM...and it seems like size 256K will work fine for this tablespace.