1 2 Previous Next 19 Replies Latest reply: Mar 4, 2013 8:53 PM by Aman.... RSS

    Question about Undo Tablespace?

    Ecimen
      Hello;

      The size requirement of the undo tablespace is related to the number and size of transactions that occur on the database.But I dont need to query any of these transaction for a schema or table by using flashback so is there a way of disabling writing any transactions on a table or schema to undo tablespace ?

      Edited by: Ecimen on 04.Mar.2013 03:50

      Edited by: Ecimen on 04.Mar.2013 03:51
        • 1. Re: Question about Undo Tablespace?
          Aman....
          Ecimen wrote:
          Hello;

          The size requirement of the undo tablespace is related to the number and size of transactions that occur on the database.But I dont need to query any of these transaction for a schema or table by using flashback so is there a way of disabling writing any transactions on a table or schema to undo tablespace ?
          No, the only thing that you can disable is whether that table can generate redo or not(that too won't be a completely correct statement to make but anyways) . I am not sure that there is any such option or command that would let you skip generating the Undo, either for a schema or for a table.

          Aman....
          • 2. Re: Question about Undo Tablespace?
            Ecimen
            Hello Aman;

            what do u mean by saying "(that too won't be a completely correct statement to make but anyways)"...

            thanks alot for helping

            Edited by: Ecimen on 04.Mar.2013 04:04
            • 3. Re: Question about Undo Tablespace?
              Aman....
              Ecimen wrote:
              Hello Aman;

              what do u mean by saying "(that too won't be a completely correct statement to make but anyways)"...

              thanks alot for helping
              What I meant with that added note that it's not possible to say that you would not be generating any redo at all , even if with the option like NOLOGGING. Oracle would only minimize the redo generation for your operation but it won't be ever gone to zero! That's why I said that you may say that we can stop the redo generaion but it won't mean completely .

              HTH
              Aman....
              • 4. Re: Question about Undo Tablespace?
                mtefft
                I suggest you do some more reading to get a better understanding of UNDO. It is not just for flashback queries - its most important functions are for transaction recovery and for read-consistency.

                I would strongly recommend Tom Kyte's book "Expert Oracle Database Architecture" , especially chapters 7, 8 and 9.

                If you don't have that book, (1) order it (2) while waiting for its arrival, start with the Concepts Guide: http://docs.oracle.com/cd/E11882_01/server.112/e16508/part_txn.htm#CHDJIGBH
                • 5. Re: Question about Undo Tablespace?
                  Ecimen
                  Hi again thanks for your answer;

                  Actually ; what i am trying is to disable writing anything on undo tablespace for some log tables (which we dont really care about) when there are too many transcations occuring on these tables to prevent getting our undo tablespace bigger..apparently theres no proper way of doing this...

                  Edited by: Ecimen on 04.Mar.2013 04:27

                  Edited by: Ecimen on 04.Mar.2013 04:41
                  • 6. Re: Question about Undo Tablespace?
                    Rob_J
                    Hi,

                    If you're generating a lot of UNDO it must mean that you are logging a lot. Perhaps you could reduce the level of logging or make it so that you can use a sample of logging or so that the logging can be turned on and off if it's for issue resolution.

                    Rob
                    • 7. Re: Question about Undo Tablespace?
                      Mihael
                      is there a way of disabling writing any transactions on a table or schema to undo tablespace ?
                      You can create External tables and write to them using file operations. No undo, no redo.
                      • 8. Re: Question about Undo Tablespace?
                        Ecimen
                        Rob_J wrote:
                        Hi,

                        If you're generating a lot of UNDO it must mean that you are logging a lot. Perhaps you could reduce the level of logging or make it so that you can use a sample of logging or so that the logging can be turned on and off if it's for issue resolution.

                        Rob
                        hello Rob;

                        so altering log tables with nologging clause will help reducing size of undo tablespace?
                        • 9. Re: Question about Undo Tablespace?
                          Ecimen
                          Mihael wrote:
                          is there a way of disabling writing any transactions on a table or schema to undo tablespace ?
                          You can create External tables and write to them using file operations. No undo, no redo.
                          Hello Mihael ;

                          thanks for advice,

                          we might consider using External tables for our log tables...

                          Edited by: Ecimen on 04.Mar.2013 06:15

                          Edited by: Ecimen on 04.Mar.2013 06:15
                          • 10. Re: Question about Undo Tablespace?
                            Girish Sharma
                            so altering log tables with nologging clause will help reducing size of undo tablespace?
                            Is there any relationship between nologging and Undo ?
                            The very very short answer is NO. For long answer see below link :
                            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869#35000844357201

                            Regards
                            Girish Sharma
                            • 11. Re: Question about Undo Tablespace?
                              Rob_J
                              Hi,

                              No, that's not what I meant. What I meant was that if you say your logging tables are creating a lot of UNDO it must mean that you are writing/updating/deleting a lot of information from them. I was just throwing out the thought that if this is the cause of the issue, can you reduce the amount of data you write to these tables? In other words, do you need all this logging data? It's a question for the business and/or designers of your application to answer.

                              Rob
                              • 12. Re: Question about Undo Tablespace?
                                Ecimen
                                Rob_J wrote:
                                Hi,

                                No, that's not what I meant. What I meant was that if you say your logging tables are creating a lot of UNDO it must mean that you are writing/updating/deleting a lot of information from them. I was just throwing out the thought that if this is the cause of the issue, can you reduce the amount of data you write to these tables? In other words, do you need all this logging data? It's a question for the business and/or designers of your application to answer.

                                Rob
                                Hello Rob;

                                i got it now , and yes we need to logging all this data because its production database occuring many transactions always...

                                Edited by: Ecimen on 04.Mar.2013 06:28

                                Edited by: Ecimen on 04.Mar.2013 06:31
                                • 13. Re: Question about Undo Tablespace?
                                  Girish Sharma
                                  NOLOGGING can be used to minimize the amount of redo generated by Oracle. Only the following operations can make use of nologging:

                                  SQL*Loader in direct mode
                                  INSERT /*+APPEND*/ ...
                                  CTAS
                                  ALTER TABLE statements (move/add/split/merge partitions)
                                  CREATE INDEX
                                  ALTER INDEX statements (move/add/split/merge partitions)

                                  So, other than above operations, we should not consider nologging as a wonderful thing or which is something related to Undo/space matters. Even though, with the nologging we can save disk space, reduced I/O on the redologs and faster completion of the task, but only if they are from above.

                                  Regards
                                  Girish Sharma
                                  • 14. Re: Question about Undo Tablespace?
                                    Rob_J
                                    OK, that's fine, so what makes you say that you are generating too much UNDO? What is the problem you are trying to resolve? Or you just wanted to know if you could reduce UNDO writing?
                                    1 2 Previous Next