DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: dont archive one table

  1. #1
    Join Date
    Jan 2001
    Posts
    216
    Hi,

    Our database is running in archive log mode.

    We have one table in our database whose data changes at a rapid rate. However, the data in that table is not very critical. Its just temporary data that gets written out there. So, is there a way by which we can specify that table not to be archived ? I am hoping if we can do that, we can eliminate lot of the archive logs, recovery will be faster and performance will be faster.

    Please let me know if there is a way to do that.

    Thanks

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    On that case change the table to be on NOLOGGING mode.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sambavan
    On that case change the table to be on NOLOGGING mode.
    Which almost certain will not make any difference....

    Depending on the version of your database and the nature of the data that runs through this table, you might investigate using GLOBAL TEMPORARY TABLE - check the manuals for the benefits and the behavior of this kind of database structures.

    GLOBAL TEMPORARY TABLE will produce the minimal amount of redo information, but the data is only persistent during the session or even only during the transaction.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Originally posted by jmodic
    Originally posted by sambavan
    On that case change the table to be on NOLOGGING mode.
    Which almost certain will not make any difference....

    In the database version 8 and above, you could significantly see the reduction. This NOLOGGING would not totally remove the log generation, as some are needed to protect the data dictionary. There is no way to avoid this.

    Other than this using this NOLOGGING option in the DML and DDL would also help to reduce some log generation.

    I don't see a way to totally remove the log generation.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sambavan
    In the database version 8 and above, you could significantly see the reduction.
    Khm, only in some *very special cases of inserts*. It will make absolutely no difference during deletes or updates and even with "normal" inserts (which usually make the wast majority of inserts anyway). Appart from DDLs, NOLOGGING applies only to so-called direct path inserts (direct mode of SQL*Loader, CTAS, APPEND hint and few other special cases).

    This isue has been discussed here quite a couple of times, for example in the following two threads:

    http://www.dbasupport.com/forums/sho...9&pagenumber=2
    http://www.dbasupport.com/forums/sho...?threadid=9103
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925


    Another day of my ignorence

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Jan 2001
    Posts
    216
    So, isnt there a way to disable logging for just one table ? Can I put that table in a separate tablespace and disable archiving at tablespace level ?

    What are my options ?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Appart from making this table GLOBAL TEMPORARY, your options are very close to none, I guess....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jan 2001
    Posts
    216
    What is a global temporary table ? where can I find more info on this ?

  10. #10
    Join Date
    Jan 2001
    Posts
    216
    silly question : Can I convert regular DMLS into direct load liknd of DMLS and then use the nologgin option ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width