Locally Managed RBS

[Ablakios]

Hi all
Why in the world will a newly created locally managed RBS tablespace of 3000M will use up 2020M of space even before database is populated?
I just think that Locally Managed Temporary tablespace and Locally Managed Rollback segments don't do good as Dictionary Managed?
Any comment and suggestion?

[jmodic]

We would need far more details of what you did and what you didn't to give you any usefull comment or suggestion about the space used in your LMT RBS tablespace. And what do you mean by "even before database is populated?"? You created rollbac segments in that tablespace, right? Well, it is not hard to create a rollback segment that will eat up 2020 MB of tablespace, what is so strange about that?

As a comment to your opinion about LMT versus DMT I don't need any additional information. I would simply say: Your opinion sounds absurd to me. In what respect would DMT be better than LMT, in particular for TEMP and RBS? LMT has only pros, no cons, when compared to DMT.

[Ablakios]

We have cases with LMTT where has reach 18G and you cannot reclaim the wasted space even if no sorting is going on and when the database was shutdown and that does not give you any control in term of coalescing the temp tablespace whereas with the DMT you can simply change the default storage (pctincrease to 1) whenever the temp tablespace is reaching its max

[pando]

are we talking about RBS or Temp? Topic says RBS but your last thread says Temp hehe

[jmodic]

Originally posted by Ablakios
We have cases with LMTT where has reach 18G and you cannot reclaim the wasted space even if no sorting is going on and when the database was shutdown and that does not give you any control in term of coalescing the temp tablespace whereas with the DMT you can simply change the default storage (pctincrease to 1) whenever the temp tablespace is reaching its max

IMHO you have some misunderstanding about the concepts in general. And also I would say some database administration problems in general.

1. If something has eaten up 18 GB of your TEMP (or RBS) tablespace then there are two possibilities:
a) You had a runaway process that consumed it unplanned, in which case its totaly your DBA's fault to give it the ability to consume so much space. He has many means to prevent this, some of them are pretty basic rules that even a junior DBBS should know about and follow them (for example prevent the datafiles to extend beyond some reasonable limit).
b) Something actualy did need to consume so much of the TEMP (RBS) space, it was some controled process. In that case you actually need so much space, don't you? So whay would you want to reclaim that space back if you will probably need it again? You are only wasting your's and computer's time.

2. Coalescing (in all of its simplicity) is one of the most misused and misunderstood concepts in Oracle database. Actually I think it was introduced for two reasons:
a) To enable the database (or its DBBS) to perform some semi-usefull stuff to cover mistakes done at the time of the database physical design. On a properly designed database coalescing should never happen.
b) To keep DBBS bussy performing coalescing on and on, instead of taking the Concepts manual off the shelf, read it and make some changes to the database that will release him/her from performing coalescing ever again.

3. Setting the PCTINCREASE to 1 for any of the tablespaces is one of the most nonsensical thing that can be done with Oracle database. Setting PCTFREE to 1 with TEMP or RBS is probably THE most unsensical thing that can be done with Oracle database. The only more stupid thing would be to manually perform coalescing on TEMP or RBS tablespace.

4. If it realy happens that your TEMP tablespace has grown beyond some acceptable limits and you realy want to shrink it, you can do it very easily. Simply create another TEMP tablespace with proper size, switch your users to that tablespace and drop the old one. You are using temp files for TEMP tablespace, right? If so, the whole thing will be done lightening fast.