Datafile space added

[Former Member]

   Dear Experts,

   If we add, say 20 GB space in tablespace at a time, will complete 20 GB be added in system or less.

  We are using oracle 10g with SUSE linux environment. storage technology is SAN.

[former_member189725 · Accepted solution]

When you add space to a tablespace , the space no more appears to be free in the filesystem in which the oracle datafile resides.

Say you have a filesystem /oracle/<SID>/sapdata1 which has a total of 100GB of space out of which you see 40 GB is free when you execute df -h /oracle/<SID>/sapdata1.

Now you add a 20 GB datafile of a tablespace which is under /oracle/<SID>/sapdata1 , then you do

df -h /oracle/<SID>/sapdata1 , you see only 20 GB free space .

Now this 20 GB space which is added appears as additional free space in the database till segments are allocated extents out of this space .

Say your database has been allocated 100 GB of space and 95 GB is allocated or used. Now add 20 GB , database would be allocated 120GB and free space would be 25 GB.

Hope this helps.

Regards

Ratnajit

[Former Member · Accepted solution]

    Hi Ratnajit,

!. Now, when we add datafile, we'll do analysis & comeback about how much free/allocated etc...

2. My actual query is that whether system assign complete say 20 GB or not. When we execute df-h, it shows 20 GB  has been added, that is figure only, but actually, how much space being added

Or else df-h calculate is correct and reliable

Let me clear, I m comparing this scenario with Window HDD. Suppose, we have 80 GB HDD in desktop, but we know that is only market gimmick. In real we got approx 74 GB space.

So, same concept apply here at database storage level

[former_member189725 · Accepted solution]

2. It is 20 GB only that has been added .

Verify using the following sql statement

Get the filename and the corresponding file id of the datafile you have added.

SQL> select FILE_ID,FILE_NAME from dba_data_files;

Then check the size of the datafile

SQL> select BYTES/1024/1024/1024 from dba_data_files where file_id=X;

where X is the file id number . This would give datafile size in GB.

df -h is correct and reliable . You cannot compare everything between Linux and windows.

Regards

Ratnajit

[Former Member · Accepted solution]

    Dear Ratnajit,

It's okay.... we'll add space in some days, then i take a look on all aspects & commands and revert.

Till then this thread is open...

[Former Member · Accepted solution]

   Hi Ratnajit,

  Today, we got created FS , it's new & we haven't start using it. see below:

Size  =  use + avail.

  247 = 234.1.83

Where has 13GB (approx) space has gone???

Filesystem

Size  Used Avail Use% Mounted on

/dev/mapper/SAPDB-SAPDATA8

247G  188M  234G   1% /oracle/PRD/sapdata8

SID<adm> cd /oracle/PRD/sapdata8

SID<adm> ls -l

total 16

drwx------ 2 root root 16384 2012-05-26 22:23 lost+found

[former_member189725 · Accepted solution]

This is so because 5%(which is the default value) of the space is reserved for root/superuser for writing logs etc.

Execute the command

# tune2fs -l </dev/XXXX>

Check the following in the output

Reserved block count(in 4 KB size)


These many blocks are reserved for super user.

You can also change it to 0% by the following command

# tune2fs -m 0 </dev/xxx>      

if the superuser is not going to use space from the filesystems for writing logs . For sapdata filesystem , you can put it 1% ,

# tune2fs -m 1 </dev/xxx>

Regards

Ratnajit 


[Former Member · Accepted solution]

Hi Ratnajit,

See, We asked 250GB space for new FS from storage team. Finally Unix team created FS of  247 GB with availability of 234GB. our storage system is SAN

250->247->234-----------------------> You got me understand phase of 247->234. What about 250->247.

[former_member189725 · Accepted solution]

Can you check with the Linux/Storage team , how much of data they actually added .

Regards

Ratnajit