datafile autoextent

[Former Member]

Hi,

How can I check if the autoextend feature of the datafile is working.Is there any SQL command we can use to compare the actual datafile size and the incremented datafile size.

We have 6 datafile each of 2 GB in PSAPDAT tablespace with utoextend on next 20M maxsize 10G.

Does it mean that that tablespace can grow upto 60G. How can I check this. The issue is that I see the datafile in dba_data_files of the same size (i.e 2G).

Regards

[stefan_koehler]

Hello Mohammed,

at first just a suggestion:

> We have 6 datafile each of 2 GB in PSAPDAT tablespace with utoextend on next 20M maxsize 10G.

Please increase the AUTOEXTEND size up to 1 or 2 GB. Your actual autoextend size is too small to handle a "big" database object extent in a LMT tablespace with one extent. In a DMT tablespace it can be much more worse.

> Does it mean that that tablespace can grow upto 60G

Yes, your tablespace can grow to 6 * 10 GB = 60 GB

> Is there any SQL command we can use to compare the actual datafile size and the incremented datafile size.

You can query DBA_DATA_FILES to get these actual size and the max size, but you can not "check" the AUTOEXTEND feature - the datafiles are extended if needed.

>SQL> SELECT FILE_NAME, BYTES, MAXBYTES FROM DBA_DATA_FILES;

But you can check the initial datafile size after the creation and the actual size:

> SQL> SELECT NAME, BYTES, CREATE_BYTES FROM V$DATAFILE;

Regards

Stefan

[Former Member]

Many thanks for a helpful answer.

Please clarify the point as I dont understand this.

Please increase the AUTOEXTEND size up to 1 or 2 GB.

What is the harm of keeping the next to 20MB only.

Is there any SAP NOTE on calculating the next size of Autoextend.

[Former Member]

Hello Ali,

As you mentioned you have 6 datafiles of 2GB of datafile each.

Since , each tablespace has a limitation of No. of datafile added to it.So, if the autoextend size is set to 20MB , more and more number of datafiles will be added to each tablespace as per the database growth.

which may create problems in future.

So , it is advisible to add datafile of bigger size ,so that the database can sustain for longer time.

Regards

Pratap

[stefan_koehler]

Hello Mohammed,

please forget the answer of Pratap - it is completely wrong.

> What is the harm of keeping the next to 20MB only.

Ok i will try to explain it with a LMT tablespace with AUTO ALLOCATE.

The object extent algorithm is the following (refer to sapnote #214995):

>Up to 1 Mb segment size: 64 Kb Extent size

>Up to 64 Mb segment size: 1 Mb Extent size

>Up to 1 Gb segment size: 8 Mb Extent size

>more than 1 Gb segment size: 64 Mb Extent size

Now lets take a table that is bigger than 1 GB ( for example BSIS). If this table needs to be extended and there is no free space in the tablespace (datafiles), the datafiles need to be extended. The needed extend size is 64 MB. Now if your extend size of the datafiles is only 20 MB, oracle needs the extend the datafiles 4 times to reach the needed free space. If you have an AUTOEXTEND SIZE of 1-2 GB it only need to be extended once.

Regards

Stefan

[Former Member]

thanks Stefan.

I was waiting for you reply.. thats really cleared the doubts..