Real space avalaible on the DB

[Former Member]

Hi people,

I´m watching the Space overview of dbacockpit and I´ve several doubts about the real space avalaible of the database. The information that appears is the next:

Size:                    88,86 GB                      Total size:                  146,48 GB

Free Space:              13,97 GB                      Total free space:             71,59 GB

Used:                       84 %                       Total used:                      51 %

The 88,86 is the currently size of the database and the 146,48 is the maximu value that the database could reach, but in the partition of there are included the tablespaces have 210GB of free space. Where is the difference between the free space of the partition and the total size of database??

The optimal setting about the Tablespaces is with the mode Autoextend ON or OFF?

If I´ve one datafile with a lot free space I could get this space for another datafile on another tablespace?? What is the process??

More thanks.

[Former Member · Accepted solution]

Hi Sergio,

>> Where is the difference between the free space of the partition and the total size of database?? 

Could you clarify what is the free of the space partition? What values you are comparing?

>> The optimal setting about the Tablespaces is with the mode Autoextend ON or OFF?

There's no best practice about it. It depend to your requirements. If you want to manage datafile management by yourself, you may choose to set "autoextend off". So, the datafiles will not be increased, automatically. By "autoextends on", the datafiles will be incremented, automatically. Please keep in mind that having a small increment make things getting worse. It may cause the performance problem, if you have many inserts into the tables. On the other hand, big freespace make backup up times getting longer.

>> If I´ve one datafile with a lot free space I could get this space for another datafile on another tablespace?? What is the process??

If I understand correctly, you want to use 1 datafile for 2 or more tablespaces. This is not possible.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

The DB actually size 88,86 GB and can grow since 146,48. Then, It has 71,59 GB of space avalaible, but in the physical disk there are 210 GB avalaible.

Why the DB couldn´t to gro since to all space avalaible on the physical disk??

The other question is that on datafile has a lot free space unusable, then I can resize the size reducing its space and this space avalaible it will asign to another datafile in another tablespace. I think that this is possible,not?

More thanks.

[Former Member · Accepted solution]

Hi,

>> Why the DB couldn´t to gro since to all space avalaible on the physical disk??

This is because datafile maxsize value has been set to a value and the size cannot be extended when the value has been reached. In short "total(datafile maxsize)" should be equal to "146,48 GB", on the database. The datafile size may not be planned, by the disk capacity. I don't know, there might be another reason to not utilize all the free space by the database, but the database cannot be grow more than total(datafiles maxsize)

>> The other question is that on datafile has a lot free space unusable, then I can resize the size reducing its space and this space avalaible it will asign to another datafile in another tablespace. I think that this is possible,not?

If the datafile is not used completely, you may resize the datafile. If you can't reduce the space allocated by the datafile online, you should plan a tablespace reorganization. So, the allocated space will be reclaimed to the OS and can be allocated to the new or existing datafile.

Best regards,

Orkun Gedik

[Former Member]

Addition - Database use the disk space without manaul addition, only if you enabled the auto extend ON

Regards,

Nick Loy