[dbuckingham]

Hi I am curious to find out more information explaining exactly how tablespaces actually grow. I will explain what I understand thus far, and hopefully someone can fill in the gaps:

CREATE TABLESPACE test
DATAFILE 'x:\database\test.dbf' SIZE 50M
DEFAULT STORAGE (
INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 0);

Once this information has been specified, the test.dbf file is created of size 50M. The initial extent inside this table space is 50K. Once this initial extent fills up, then another one is created of size 50K and so on and so on untill either max extents fills up or the physical 50M is reached (size specified during creation). I believe that I understand this part ok.

What I am unsure of is, we have a database here where a number of the table spaces are a lot larger than what they were when created. Is it possible for tablespaces to also autoextend? If so, is this a setting by default or something that needs to be specified during the actual creation of the tablespace?

Basically I want to cap these tables spaces to be of a specific size and not to grow past this size unless I specify so (physically resize them).

Can some one let me know if I am on the right track with my thinking and what the easiest way to do this would be?

Cheers

Dan.

[Hings]

Dear dbuckingham,

I guess you have some misunderstanding about the Oracle Tablespaces.
Let me explain you how tablespace works.y whenever you create a tablespae, the datafile(s) that belongs to it will be allocated a header and Unallocated space (i.e, Free space). Extents will not be allocated here. They will be allocated when you create a segment(s) in this tablespace.
For e.g.
Your code creates a TEST tablespace with only one datafile test.dbf.
The size of the datafile is 50M. Now at this moment No extent is allocated and they will be allocated when you create segments in it.

To manage the tablespace Please read the Oracle Documentation. You will find lot of tricks to manage them.

Thanks

Hings

================

Still Learning...

[RastogiKamesh]

Originally posted by dbuckingham
Hi I am curious to find out more information explaining exactly how tablespaces actually grow. I will explain what I understand thus far, and hopefully someone can fill in the gaps:

CREATE TABLESPACE test
DATAFILE 'x:\database\test.dbf' SIZE 50M
DEFAULT STORAGE (
INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 0);

Once this information has been specified, the test.dbf file is created of size 50M. The initial extent inside this table space is 50K. Once this initial extent fills up, then another one is created of size 50K and so on and so on untill either max extents fills up or the physical 50M is reached (size specified during creation). I believe that I understand this part ok.

What I am unsure of is, we have a database here where a number of the table spaces are a lot larger than what they were when created. Is it possible for tablespaces to also autoextend? If so, is this a setting by default or something that needs to be specified during the actual creation of the tablespace?

Basically I want to cap these tables spaces to be of a specific size and not to grow past this size unless I specify so (physically resize them).

Can some one let me know if I am on the right track with my thinking and what the easiest way to do this would be?

Cheers

Dan.

Hi Dan,
You can have auto-extend feature for tablespace which will manage & grow as per the defined way while creating it.

You may use AUTO EXTEND ON NEXT 50K MAX SIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

This will increase the extent by 50K when tablespace filled-up. I will keep on growing. You have be sure that you are having proper free space on your disk for tablespace datafile. Or yu may even limit the Max Size parameter. Plesae check the Doc. about this command.

May it help you.

Regards,