1 2 Previous Next 19 Replies Latest reply: Feb 12, 2010 7:58 PM by hank_nj_usa RSS

    Unable to shrink undo tablespace... Help!

    447508
      Hi,

      I have problems to shrink the system undo tablespace, which has grown up to 14 GB.

      I use 9.2. Table space owner is 'SYSTEM', undo_management = AUTO.

      I tried to shrink the greatest rollback segments by the commands

      ALTER SESSION SET UNDO_SUPPRESS_ERRORS = TRUE;
      ALTER ROLLBACK SEGMENT "_SYSSMU6$" SHRINK TO 20 M;

      Oracle confirmed these commands, but nothing happened.

      What am I doing wrong?

      Hermann Mueller
        • 1. Re: Unable to shrink undo tablespace... Help!
          Paul M.
          You could try to resize datafiles, but this may not work (maybe you already tried it ?).

          So, assuming your undo tablespace name is UNDOTBS1, you can do the following :

          - Create a new undo tablespace as :
          SQL> create undo tablespace UNDOTBS2 datafile '<complete file path>' size <smaller size>;

          - Change parameter UNDO_TABLESPACE
          SQL> alter system set UNDO_TABLESPACE=UNDOTBS2;

          - Drop UNDOTBS1
          SQL> drop tablespace UNDOTBS1 including contents and datafiles;

          To complete the drop command you may have to wait for some transactions to be committed or rolled back.

          BTW, do not set AUTOEXTEND ON if you don't want your datafiles to grow indefinitely.


          Paul
          • 2. Re: Unable to shrink undo tablespace... Help!
            443560
            Hi,
            You should check your undo_retention parameter(its value in Seconds)
            I think it should be accomodate according to your needs
            • 3. Re: Unable to shrink undo tablespace... Help!
              447508
              Thanks to all!

              I switched the undo tablespaces as you proposed, Paul, and it works.

              Hermann
              • 4. Re: Unable to shrink undo tablespace... Help!
                181444
                But unless you built the new tablespace using fixed files and/or checked the value of your undo_retention parameter against how much undo you were holding as other posters recommended then you will likely redevelop the same problem on the new tablespace.

                HTH -- Mark D Powell --
                • 5. Re: Unable to shrink undo tablespace... Help!
                  user598261
                  Do you know if this would work for the TEMP tablespace? Both of our undo and temp tablespaces are growing too large.

                  Rob
                  • 6. Re: Unable to shrink undo tablespace... Help!
                    Madrid
                    You have seen the discussion about the undo segments, on the temporary tablespaces, you should be aware that the sort segment of a given temporary tablespace is created at the time the first sort operation takes place. The sort segment continues to grow by means of extent allocation until the segment size has reached the total storage demands of all of the active sorts running on the instance. Oracle will keep on allocating temporary space on demand unless the physical limit states otherwise.

                    Temporary segments are produced each time a sort operation (explicit -order by- or implicit -aggregation, reindexing-) requires to sort a set that cannot fit into memory. So if you detect excesive sort usage you should aim your monitors towards the sort operations (reports, reindexing, max, min, aggregations, order by ...). If your system has a DDS behaviour, this kind of operations are frequent as a massive sorting has to be peformed against millions of rows.

                    A Temporary tablespace will almost always appear to be near 100% full, that's because once oracle has allocated temporary space it doesn't release it back to the free space, it keeps it allocated even when the sort operation has finished. Criteria behind this fact is similar to the one oracle used to have when the rollback segments were in use, Oracle only allocated space and the dba should perform manual actions to release space, and the criteria is performance. Once it has allocated space this big, there are possibilities that the same circumstances that raised the temporary usage high water mark to this level are repeated, so if oracle keeps the mamimum allocated space, it won't have to allocate the same storage once more.

                    Main concern with temporary tablespace growth is not free space itself, but the reasons why this amount of space was allocated, so I suggest you to track the sql statements with sort operations. If you are certain the circumstances that motivated this amount of temporary resources to be allocated won't be repeated again, then you could think of resizing down your temporary tablespace. I suggest you to create a new temporary tablespace with the desired size, and alter the default tempoary tablespace to point to this newly crated temporary tablespace, and finally get rid of the original temporary TS.



                    ~ Madrid
                    • 7. Re: Unable to shrink undo tablespace... Help!
                      user598261
                      I agree. I think I know why the temp table space got big and I know how to prevent it from getting two big in the future (Max size).

                      But unfortunately it is already too big and the disk is running out of space becasue of it, so I need to know how to fix the problem before I can apply any corrections.

                      Thanks,
                      Rob
                      • 8. Re: Unable to shrink undo tablespace... Help!
                        585319
                        Hi,

                        1) Create new temporary tablespace with smaller size and make it the 'default'
                        2) Redirect user's default temporary tablespace to new tablespace

                        3) Check if somebody is using the 'problem' tablespace with a query to v$tempseg_usage
                        4) if nobody is using it, drop the temp tablespace.

                        Are you using Linux? if so, maybe you'll need to bounce the DB in order to get the disk space freed.

                        Hope this solves your issue...

                        Regards

                        http://oracledisect.blogspot.com
                        • 9. Re: Unable to shrink undo tablespace... Help!
                          Madrid
                          Just drop the tablespace when there are no users requesting temporary segments, and rebuild it, just as directed.

                          By setting maxsize you don't prevent this tablespace to become big without a possible 'unable to extend ... ' error. So you should better first check the root causes that make you consume too much temporary space.


                          ~ Madrid
                          • 10. Re: Unable to shrink undo tablespace... Help!
                            719872
                            Paraphrasing an earlier post here .. when I try to do this procedure ...

                            SQL> create undo tablespace UNDOTBS2 datafile '<complete file path>' size <smaller size>;
                            SQL> alter system set UNDO_TABLESPACE=UNDOTBS2;
                            SQL> drop tablespace UNDOTBS1 including contents and datafiles;

                            It has the cavaet:
                            "To complete the drop command you may have to wait for some transactions to be committed or rolled back."

                            My problem is that I need this to run turnkey, as part of a script on MANY systems worldwide ...
                            I need a way to guantee the drop command will work ... in a finite, predictable time ...

                            So my first take was to bouce the db after the alter ...
                            but when it is back up again, the drop fails saying the old undo is "in use"...
                            even adding a "alter database backup controlfile to trace;" after the undo set above didnt change that ...

                            we use initfiles (not pfiles) and the only undo parm we have in them is "undo_management = AUTO"

                            What am I not understanding here ?
                            • 11. Re: Unable to shrink undo tablespace... Help!
                              Paul M.
                              we use initfiles (not pfiles)
                              Maybe you meant "not spfiles", pfiles and initfiles are the same.

                              If that means you're using initSID.ora for your DBs, then configurations are static, so when you bounce the DB undo_tablespace reverts to the value specified in init file, or the first available undo tablespace, if omitted. Yout can check that out by
                              SQL> show parameter undo_tablespace
                              Is there a reason that you don't use spfiles ?
                              • 12. Re: Unable to shrink undo tablespace... Help!
                                719872
                                sorry, spfiles (typo) ... mostly this is inertia ...
                                I am a one-man-band for dba work for this project, and I have a "flock" of about 100 systems worldwide in 24/7 use.

                                So now I get why the system reverts to the 1st undo on the bounce.
                                I would rather do it without a bounce but the start/stop seemed the only way to know it wasnt still hanging onto something there.

                                I can do the switch, then wait 2 minutes and drop it and this will probably work for most if not all of them.
                                But what I really need is a way to be certain it will work, and I wont get a 2 am call ...

                                but after switching to the 2nd undo ts, and again when swithcing back to the remade one,
                                it sure would be better if I could force it to clear and drop it reliably.
                                • 13. Re: Unable to shrink undo tablespace... Help!
                                  Paul M.
                                  I repeat my question : why don't you use spfiles ?
                                  • 14. Re: Unable to shrink undo tablespace... Help!
                                    719872
                                    I will shift to spfiles when we bump up to 11g this year. We went from 8 straight to 10g last year, and at that time I didn't see too many advantages to converting. It's easier to have a control file I can directly read and edit, or send out a simple fix for. Spfiles have the advantage of remembering from run to run more information. Our client base changes things very slowly, at most one major release per year, and we need to support generations back for many years too. Changing anything is hard, and slow to deploy.

                                    I take it if I had an spfiile, it would remember this UNDOTBS switch through a bounce ?

                                    What I had been really looking for was a way to make the changeover more deterministic, and less probability based. I can wait a few minutes for the old UNDO to drain off its in-use blocks, but if something is stuck there I dont want to fail to drop and remake it over that. Is there a way to force it to give up anything it might be saving ? Do I just reset max undo retention to a short time and wait at least that long ? I can afford to wait a finite time, but not an indeterminate time.

                                    That is certainly preferable to bouncing the db, and costs us lno outage time against our five-nines availability committments.
                                    1 2 Previous Next