tablespace

[elaine3839]

I have 2 questions about tablespace:

1. When build new tables, if the sum of the initial space for all tables is 200M (as defined in the storage statement in table build script), how should the users specify the size for the table space? Should it be specified as exactly the same as 200M, or we should add some free space in the tablespace? Assume all tables will be built in that tablespace.

2. I know the difference between drop and truncate the table is that the tablespace assign to the tables won't be taken back by system immediately after drop but it will after truncate. So in case of drop table, when will the tablespace being reusable by other tables again? Is it when user commit the drop?

Thanks a lot!

[rcaballe]

You must leave a little room for the header (64K), I always use 32M,64M,128M,256M,384M etc sizes for tablespaces (not a must, just I use)
A DROP as any DDL has a commit implicit, I guess you are confused about DROP and DELETE, DELETE doesn't free space until you do a ALTER TABLE DEALLOCATE UNUSED SPACE;

[elaine3839]

You are right --- I should ask the difference between truncate and delete

But now I have a new question regarding to the tablespace creation --- how should I define the growth rate of the tablespace when existing one is full? Should the 'NEXT' part always be equal of greater than the 'INITIAL' part OR the 'NEXT' part of tablespace is decided upon the biggest 'NEXT' part of a table? e.g. If I have a table that will grow 100M when the initial extent is full, then my tablespace should grow at least 100M when it gets full? What's the strategy behind?

Thanks!

[rcaballe]

The growth rate is specified in the datafile part not the tablespace, ok?
If you have one datafile in the tbs, what I have seen is instead of the error "unable to allocate..." the DB tries to extend the datafile, so , to make the DB life easy, and you know in how much the segments growth then put that value.
If you don't know, it doesn't matter, Oracle will have to resize the datafile n times to be able to fit the new extent.

[sreddy]

Use the TRUNCATE statement to remove all rows from a table or cluster and reset the STORAGE parameters to the values when the table or cluster was created.

Deleting rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates the table's dependent objects, requires you to regrant object privileges on the table, and requires you to re-create the table's indexes, integrity constraint, and triggers and respecify its storage parameters. Truncating has none of these effects.
I would advise you to have your INITIAL and NEXT extent sizes same to avoid fragmentation of the tablespace.