cancel
Showing results for 
Search instead for 
Did you mean: 

Datafile size Auto extend on

Former Member
0 Kudos

Hi all.

We are using SAP ECC 6.0 with Oracle 10.2.0.2. Dtabase size is 150GB. We kept file & table space setting AUTO Extend on and We have enough space at file system.

Situation:

In our SAP system PSAPSR3 tablespace having 3 files with 30GB size each one. ( file Max size is set to : 32GB ).

My Queries:

1. I will add another file to PSAPSR3 tablespace when all files reach 32GB size or Max filesize as per the settings.

Is above decision correct?

2. Which one is SAP or Oracle suggested? maintaining small & more files or few Big files in DB ?If database size is 150GB,

3.If I keep the system like this next my system will have files with32GB size. will it give any problems in future?

Thanks in advance!!!!

Regards,

Veera

Edited by: Veerab on Dec 28, 2010 2:32 PM

Edited by: Veerab on Dec 28, 2010 2:33 PM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

1) yes you will have to extend the tablespace with an additional file once the defined max size of the existing three has been reached. Also note that 32GB is the upper limit for Oracle data files.

2) I would try to use the space as efficiently and administrator friendly as possible. Use large files if you have engough space because you are on the safe side and do not have to control the growth that often. Otherwise I see no disadvantage from the db point of view whether you use small or large files.

3) There should be no problems with 32 GB data files.

Kind regards,

volker_borowski2
Active Contributor
0 Kudos

Hi,

well, there is a lot of religion involved about a topic like this.

1) No, I would tend to add the datafile right now size 2000M Autoextend up to 32000M (never use 32G)

continously resize the file plus 500M every 14 days, so that additional space requirement is balanced across all datafiles.

(Well if you have a storage system underneath with super software, that does automatic hot spot relocation, skip my comment)

Reasons:

- There have been a couple of bugs in past oracle releases involved with exact binary limit boundaries. Be cautious. 32000M is smaller than 32G, big enough and safely below 32G. I am not saying these bugs are not fixed, I just can tell tales...

- If you have no storage system underneath your datafile, and you create the new datafile when everything else is filled to the top, you might create hot-spots in the new datafile because all new extents will be allocated in that file because nowhere else

is space. This might lead to bad performance. So when you reach 6000M with the new one, create the next datafile.

2) I like smaller datafiles for no specific reason, may be because I have grown up with 2000M datafiles.

Having more files is better for load balancing backups of multiple files across more disks.

3) Well there still can be problems with large files mostly due to the filesystems beeing formatted in a way that do not support

large files. Modern OS do support the big files all, but you need to specify it when formatting these. If it is missed, it can be

annoying, but not really bad. It may be bad, if you already have a 2GB file on a non-lagefiles-filesystem and need to autoextend

above 2GB (bounce). Requires a good guess to pintpoint this early without loss of time.

Do not take this too seriously, I am somewhat oldfashioned. Systems I install freshly do still have hostnames of max. 8 chars

taken from the 7-Bit ascii table without any specialchars (esp. no "-" and no "_"), and I NEVER had problems with hostnames.

Happy extending...

Volker

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

if you maintain autoextend ON, transaction DB16 (database check) will give you warning that autoextend (ON) can cause directory overflow.

your question :

1. I will add another file to PSAPSR3 tablespace when all files reach 32GB size or Max filesize as per the settings. Is above decision correct?

>> yes, but it is better if you turn autoextend off first.

2. Which one is SAP or Oracle suggested? maintaining small & more files or few Big files in DB ?If database size is 150GB.

>>note that Oracle limits the number of datafile only to 1024. say that you have already have 1024 of datafiles with 32 GB each, you cannot add new datafile, but you can resize existing datafiles. We have done with it perfectly. We have almost 10TB database and hundreds of datafiles, what we do is resize existing datafiles to provide sufficient freespace. And based on our experience, provide 10 datafiles with 2 GB each and separate it on different disks (mount point) is far better than provide only one datafile with 20 GB in size. If you cannot separate it, there is no significant differences i think.

3.If I keep the system like this next my system will have files with32GB size. will it give any problems in future?

>>No.

tips : if you can spread your datafiles into separated disks (mount point) it will give you better performance, compared if you are concentrate (centralize) datafiles only on a single disk.

hope it help you.

rgds,

Alfonsus Guritno