Pls Suggest on how much space required under SAPDATA1

[Former Member]

Hi Experts,

Our database version is oracle 10.2.0. All the tablespaces and it's datafiles are located under /oracle/(SID)/sapdata1. All the file system and tablespace settings are auto extend on. Free space undea sapdata1 is 30GB.( Temporary tablespace data file present size is 1Gb, Auto extend Max size is 32GB, Undo table space file present size is 2GB, Auto extend Max size is 10GB)

My Query is:

1. If Undo occufies 10GB and Temporary file needs to increase 30GB, In that situatuon what will hapen?

2. Is there any chances to fail database updates If all the space in the sapdata1 directory is using by temporary and undo file?

3. What is the suggestable size to maintain under sapdata1 directory?

4. Undo file max auto size is up to 10Gb and for tempfile it is up to 32Gb, shall I have to maintain more than 42Gb(32+10) under sapdata1?

Thanks in advance!!!!!

Regards

Veera

[former_member213250 · Accepted solution]

Hello Mr.Veera

Regards to temporary tablespace, the size of temporary tablespace must be twice the size of the largest index size on your DB.

The reason for this during re-building of index it used temp tablespace, if there is no sufficient space available for the

index in temp. tablespace it will throw errors and re-build of index won't be possible.

Also for re-organising tables as well temp. tablespace will be used.

With less UNDO TABLESPACE, there is more likely chances of

ORA-1555 : snapshot too old errors that may occur on the system

Hope it helps

Regards

Venkat

[Former Member]

Autoextend for the undo tablespace is a bad thing in my opinion. Because of one silly long running select could cause your undo tablespace to expand way beyond necessary instead of getting a snapshot to old error. And undo datafiles tend to be not very shrinkable after the rollback segments are established once.

In my opinion autoextend makes sense if you have a bunch of tablespaces AND a large sapdata filesystem (>200gb) AND you don't have a clue which tablespace will grow in the future. If i had only 30gb of free space i would like to monitor tablespace usage myself and give more space manually.

Kind regards, Michael

[former_member204746]

disk usage basics for Oracle:

if you fill a disk/partition/file system, your database will get errors and transaction will rollback.

you will need to add disk space to fix this.

if a offline redolog disk/partition/file system gets full, database will hang until you resolve this. No rollbacks will happen, just a hang.