1 2 Previous Next 20 Replies Latest reply: Feb 15, 2013 2:37 AM by Mihael Go to original post RSS
      • 15. Re: Undo Table Space Issue
        985871
        Mihael ,

        I used the first query which gives 1 usedblk and 1 usedrec for 4 users .

        the next query shows the sessions using undo ... but if i total the value it comes to approx 4GB ... but my undo is 8GB

        so where is the rest 3.5 GB being held ... can you please help here or should oracle take care on its own .. i am on 10.2.0.3 version.

        As of now this is the output

        TABLESPACE_NAME     STATUS     GB
        UNDO_TBS1     UNEXPIRED     3.64129638671875
        UNDO_TBS1     EXPIRED     3.46905517578125
        UNDO_TBS1     ACTIVE     0.05078125

        can you please let me know what does expired mean here ?
        • 16. Re: Undo Table Space Issue
          Mihael
          Mihael ,

          I used the first query which gives 1 usedblk and 1 usedrec for 4 users .

          the next query shows the sessions using undo ... but if i total the value it comes to approx 4GB ... but my undo is 8GB

          so where is the rest 3.5 GB being held ... can you please help here or should oracle take care on its own .. i am on 10.2.0.3 version.

          As of now this is the output

          TABLESPACE_NAME     STATUS     GB
          UNDO_TBS1     UNEXPIRED     3.64129638671875
          UNDO_TBS1     EXPIRED     3.46905517578125
          UNDO_TBS1     ACTIVE     0.05078125

          can you please let me know what does expired mean here ?
          expired according to undo_retention
          • 17. Re: Undo Table Space Issue
            985871
            so will oracle reuse the expired blocks if it gets full ?
            • 18. Re: Undo Table Space Issue
              Mihael
              982868 wrote:
              so will oracle reuse the expired blocks if it gets full ?
              yes, and oracle also can use unexpired blocks if no expired found and if undo retention is not guaranty
              • 19. Re: Undo Table Space Issue
                985871
                Thanks Mihael for your help. The issue has been resolved and the offending query has been killed.

                Now i am asked to tune that query :(
                • 20. Re: Undo Table Space Issue
                  Mihael
                  I suggest you to use query based on v$transaction, it will work in any version. You will get undo blocks (used_ublk). Just multiply it by block size of your undo tablespace :

                  select block_size from dba_tablespaces where tablespace_name=(select value from v$parameter where name='undo_tablespace');
                  1 2 Previous Next