Full tablespace SYSTEM

JJ_13

Hi guys, I am concerned about my full system tablespace. Can you advice me which solution would be better one?

I will start from some information:

System tablespace contains only the required system objects. There is no other user's objects than OUTLN or DR. It has 1,6 GB total space and only 3 MB free. Autoextend is on with next 10M setting. But still I think it is reasonable to avoid keeping such a full system tablespace.

I wonder if it is better to just resize it's datafile (for example to 2 GB) with ALTER tablespace datafile ... RESIZE 2G option, or maybe if it is autoextended than changing 'NEXT' value from 10M (let's say to 300M) would be better?

Also, could you confirm if altering anything on tablespace system will not affect working of database? Is it possible to have some breaks on connecting while resizing it?

I would really appreciate some helpful advices, thanks a lot!

EdStevens

OK, first off, Tom Kyte (author of AskTom) is as authoritative about all things oracle as you can get. So good there.

But notice the reasons he gave were due to possible OS limitations on file size. Nothing to do with backup time. Further, that comment about OS not handling files over 2G was made in Dec. 2002 - almost 20 years ago. Why do you suppose that, in 2002, he was concerned about an OS not handling a file over 2gb? Might it be because at that time there were still plenty of 32-bit OS's in use, and that 32-bit address space limits the file size?

EdStevens

1.6 GB is barely a rounding error on today's storage. You can get thumb drives several times that size, given away free as marketing swag.

There is no risk in resizing or making it auto extend. Referencing my opening comment, I wouldn't bother with extending in increments of less than 1 gb.

JJ_13

Hi EdStevens, thank you very much for your comment.

I totally get what you mean with the volume of space, but somewhere on another forum I have read that it is better to not exceed the size of datafile over 2GB (why? probably due to faster backup). So as far as the only system datafile is already autoextensive with increasement 10 MB, I was thinking about:

• resize it to 2 GB,
• additionaly change increasement to 300 MB (it's less than 1 GB but still it's system datafile, maybe it doesn't need to grow as much? I'm not sure).

And since that I hope that size of SYSTEM tablespace will not grow anymore... I believe it shouldn't :) However, I'll feel "safer" with more free space in this sys-datafile.

EdStevens

somewhere on another forum I have read that it is better to not exceed the size of datafile over 2GB

Yes, and on this forum you are getting conflicting advice. So how do you decide who's right?

Rather than just an assertion of something you read "somewhere", I'd like to see a source citation. With that you could get some "peer review". Consider which forum contributors you've learned to trust. There is one very well known and well promoted blog that is famous for being right about half the time. The trick for the readers is to know which half. Consider if the writer's assertion passes the "smell test". Consider if the writer's reasoning is based on the proper priorities . . . (Really, how much is the business impacted by the few seconds of increased backup time created by adding another gig to the file size?)

JJ_13

EdSteven, there is a source: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:59812348055

Ok, I didn't remember it correctly. It is written there:

" i PREFER to keep the file at 2gig or less as all OS utilities will work with 2gig and less files, some will not and not all file systems support files over 2gig.".

EdStevens

OK, first off, Tom Kyte (author of AskTom) is as authoritative about all things oracle as you can get. So good there.

But notice the reasons he gave were due to possible OS limitations on file size. Nothing to do with backup time. Further, that comment about OS not handling files over 2G was made in Dec. 2002 - almost 20 years ago. Why do you suppose that, in 2002, he was concerned about an OS not handling a file over 2gb? Might it be because at that time there were still plenty of 32-bit OS's in use, and that 32-bit address space limits the file size?

JJ_13

EdStevens once again - you are totally right. I think I was too focused on this resizing to realize this advice is a little bit out of date.. :) However, I have already changed the SYSTEM space to 2 GB + change next increasement to 512 K (only 512 K because as I wrote earlier, I do not expect much increasement at least for now). Thanks a million! I really like the way you helped me :)