This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 4, 2013 6:53 PM by Aman.... RSS

Question about Undo Tablespace?

Ecimen Newbie
Currently Being Moderated
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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points