How will oracle fill a datafile added to a tablespace.

[Former Member]

Dear Experts,

In db02 old i found that the tablespace PSAPSR3 is 91 % used, so i added the 3 datafiles of 10G size, initial size is 2 GB, autoextend on ,next file size is 2GB into it ,through with the brtools in each of the filesystem sapdata1 to sapdata6 , so it is now having 6*3*10= 180GB of size.

But it is itself only 86% used, not much improvement that is expected, and in column named free, it shows 80 gb , but initially it shows 49 gb.

There is some changes also recorded in AutoExt (kb) column but we can calculate these many things and unble to create the inter-relation in them.

Can anyone please put some light into it , or we need to perform some other changes .

Tablespace

Size (kb)

Free (kb)

Used (%)   Tab/ind

Extents

AutoExt (kb)   Used (%)   Status

Backup

PSAPSR3	581.632.000	80.233.472	86	141.685	473.588	1.329.152.00	38	ONLINE	ACTIVE
PSAPSR3700	16.486.400	4.738.624	71	767	5.384	81.920.000	14	ONLINE	NOT ACTIVE
PSAPSR3USR	14.336.000	14.335.168	0	10	10	14.336.000	0	ONLINE	NOT ACTIVE
PSAPTEMP	22.528.000	22.528.000	0	0	0	30.720.000	0	ONLINE	NOT ACTIVE
PSAPUNDO2	12.390.400	10.412.160	15	35	1.674	16.384.000	12	ONLINE	NOT ACTIVE
SYSAUX	1.167.360	326.912	71	990	2.871	14.336.000	6	ONLINE	NOT ACTIVE
SYSTEM	1.781.760	424.128	76	1.215	2.879	20.480.000	7	ONLINE	NOT ACTIVE

Total

650.321.920

132.998.464

79

144.702

486.406

[ashish_vikas · Accepted solution]

the filesystem sapdata1 to sapdata6 , so it is now having 6*3*10= 180GB of size.

If you have added 3 files, this assumed calculation is not correct. every datafile will go in only 1 sapdata* directory depending on where you created it.

Also, in tab "Free" is will not take consideration of autoextend part. So if you added 3 files with initial size 2 GB : 2 gb will come in Free and 8 GB in "AutoExt (kb)"

So for doing your calculation, you can add "Free" and "AutoExt (kb)" for planning.

Also, you may not this comparision consistent if DB02 was not refreshed before.

best regards

ashish

[Former Member · Accepted solution]

Hi Ashish,

After Seeing the used % column if it goes beyond 92 % , then we start adding the the datafiles, even if we have ample good amount of space in AutoExt (kb).Is this the right approach that we are currently following, because i have a doubt that if the space we have in autoextend column, it will automatically extended.

Pls. Suggest.

Regards

Rajat Jain

[ashish_vikas · Accepted solution]

Hi Rajat,

No, I will say if you have sufficient space in AutoExt (kb), no need to add more datafiles.

What happens is : after any datafile for example the one you addded of size 10 gb but initial size 2 gb : first it will use up to 2 GB. Once this is used up, oracle will automatically extend this by 2 GB. So 2 gb from AutoExt (kb) will be reduced and it will be available to oracle.

So always check AutoExt for your planning.

Also, you need to make sure, you have sufficient space available in sapdata* directory. This need to be monitor periodically.

best regards

ashish

[Former Member · Accepted solution]

Hi Ashish ,

Thanks for your reply, according to your reply, i am concluding that we need not to worry about the used percentage of the tablespace, means it is allows to go upto 100% and after it reaches to 100% it automatically take the datafile form autoextend space available in the block size of 2Gb.

Regards

Rajat Jain

[ashish_vikas · Accepted solution]

Hi Rajat,

For the table space with AutoExtend mode ON(like PSAPSR3) : It will not reach 100% but before that only it will get extended depending on extents allocated for tables.

No worries if you have free space in AutoExtend and sapdata* filesystems. Oracle will manage this.

you need to make sure, you have sufficient space available in sapdata* directory where datafiles have been created for this tablespace. This need to be monitor periodically.

best regards

ashish

[Former Member · Accepted solution]

Hi Ashish,

Now we added one datafile of 10Gb in PSAPSR3 tablespace,

I made some calculation accordingly by taking before and after adding 10Gb datafile of 2gb initial size autoextend on , 2gb next block size.

the results are as follows

S.NO	PSAPSR3	Before	After	Difference in kb	difference in GB
1	Size (kb)	689,111,040	691,159,040	2,048,000	1.953125
2	Free (kb)	83,659,136	84,970,752	1,311,616	1.250854492
3	AutoExt (kb)	188416000	189440000	1,024,000	0.9765625

according to your previous reply 1.953125 gb initial size is allocated to the PSAPSR3 total size, free size is incremented by 1.250854492 gb, rest of 8gb should be go to the autoextend column.

Thanks in advance.

Regards

Rajat Jain

[Former Member · Accepted solution]

Hi Ashish,

I tried to googled this situation, but i could not able to find any suitables,

If you can please answer this , it will really be appreciated.

Rgds

Rajat Jain