Home » RDBMS Server » Server Administration » Tablespace size
Tablespace size [message #683632] Tue, 09 February 2021 02:05 Go to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
Hello,

Do you usually keep your tablespaces below a given size?

What I mean is that we have some tablespaces that are 5 or 6 Teras in size.

What would you do to avoid this?

Thanks

Report message to a moderator

Re: Tablespace size [message #683634 is a reply to message #683632] Tue, 09 February 2021 02:22 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

I see no problem with a tablespace being of any size: it has be as big as it needs to be. Some DBAs (or SAs) don't like to have files over some size, but nowadays I always use bigfile tablespaces and have not had any problems. In their early days there were bugs when they grew to over 300GB or so, but that was years ago. The filesize limits for smallfile tablespaces are really annoying.

Going back a few decades I remember having to balance IO across numerous 2GB discs, striping tablespaces across zillions of little files. Thank heavens all that is history.

Report message to a moderator

Re: Tablespace size [message #683635 is a reply to message #683632] Tue, 09 February 2021 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

The problem with very big size is with backup and recovery.
For instance, if you backup on tape, you should limit the size of files to the size of tape (minus some overhead for management). Having files spread over several tapes is really painful.

If you lose a file then you have to restore and recover it, the bigger it is the longer it will last.

This is the DBA point of view. Wink

[Updated on: Tue, 09 February 2021 02:37]

Report message to a moderator

Re: Tablespace size [message #683636 is a reply to message #683635] Tue, 09 February 2021 02:52 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
That's exactly the point, backup and recovery.

The files are 131G max in size.

We just do RMAN backups and sometimes they require recovering a table or a couple of tables, so we have to recover the whole tablespace.

So we have to recover really huge tablespaces, and I don't know how to avoid this.

Thanks

Report message to a moderator

Re: Tablespace size [message #683637 is a reply to message #683636] Tue, 09 February 2021 03:34 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
Quote:
We just do RMAN backups and sometimes they require recovering a table or a couple of tables, so we have to recover the whole tablespace.
I don't understand this. What do you mean by "recovering a table"? Surely that would be export/import. Or do you mean the RMAN recover table facility introduced in 12c? Which is in fact export/import anyway.

Surely you are not regularly getting file corruptions that mean you have to restore from backup.

Report message to a moderator

Re: Tablespace size [message #683638 is a reply to message #683637] Tue, 09 February 2021 04:36 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
If the dev team or someone has made a mistake and they ask us to recover a table with the data it had X days before. So we have to do the RMAN recovery.

The new RMAN recover table, also requires recovery for the whole tablespace where the table is located.

Report message to a moderator

Re: Tablespace size [message #683643 is a reply to message #683638] Tue, 09 February 2021 09:43 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
JackBauer wrote on Tue, 09 February 2021 04:36
If the dev team or someone has made a mistake and they ask us to recover a table with the data it had X days before. So we have to do the RMAN recovery.

The new RMAN recover table, also requires recovery for the whole tablespace where the table is located.
If you are able to identify a small group of tables for which this is a request, you could isolate them into their own TS. Nothing says the entire app schema has to be in a single TS. It's simply a manner of managing to fit your needs.

I see it as similar to the concept of keeping indexes in their own TS. Contratry to an old wives tale, it's not about "performance" by "isolating" the indexes. It's about arranging things for easier management and administration. In your case, isolating a few table into a dedicated TS is about admin and management.

Report message to a moderator

Re: Tablespace size [message #683644 is a reply to message #683643] Tue, 09 February 2021 10:17 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
Yes, I suppose any DBA working with big volumes should create many data and index tablespaces for a given schema for administration purposes.

Report message to a moderator

Re: Tablespace size [message #683645 is a reply to message #683644] Tue, 09 February 2021 10:56 Go to previous messageGo to next message
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
Senior Member
For questions like this, I often like to see what EBS does. General principle: EBS is as big and complicated as it gets, so if something is good enough for EBS it should be good enough for anything.

EBS used to have several hundred tablespaces, two per module plus other junk. Now it has twelve:

https://techgoeasy.com/wp-content/uploads/2020/04/OATM-tablespace.png

So everything (umpteen TB perhaps) ends up in those first two tablespaces, which are uniform extent size 128KB (you can migrate to a different uniform size if you really want to). However, in EBS there is no way you would EVER recover one table in the way you are doing - it would destroy the integrity of the system if you tried. I can see why you would like to limit the tablespace sizes in your (rather awful!) circumstances.

Report message to a moderator

Re: Tablespace size [message #683646 is a reply to message #683645] Tue, 09 February 2021 11:39 Go to previous message
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
General principle: EBS is as big and complicated as it gets

Maybe this also means you should not follow what they do. Twisted Evil
(Why they want to rebuild indexes on transaction tables? How separating indexes from tables simplify recovery?)

As Ed suggested and I concur, the only thing to take into account to choose what tablespaces should be (number and size) is maintainability and mainly backup and restore.

If you can buy the Partition option and partition your objects then performances may also come into play.

[Updated on: Tue, 09 February 2021 11:39]

Report message to a moderator

Previous Topic: how to manually remove components in 12.1.0.2 CDB
Next Topic: how many oracle tools do we really need to install for SE2 edition
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jan 18 00:05:42 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.