Alter Table nologging still generating archive logs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Alter Table nologging still generating archive logs

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    I am using sqlloader to load millions of records into a table.

    I issued the following
    alter table test nologging;

    and then started the sqlloader session to load the data. But still archive logs are being generated at a very fast pace.

    Whats going on here.

    Please suggest.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are you using the direct=y flag?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    No I am not using the direct load method. But that shouldnt matter as logging is disabled at the table level.

    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    That's a common misconception. NOLOGGING only works with certain operations. Please see http://technet.oracle.com/docs/produ...dlins.htm#4418
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Or check the following thread (long thread, check both pages, not just the first one):
    http://www.dbasupport.com/forums/sho...?threadid=8919
    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
    Sep 2000
    Posts
    362
    Thanks for the reply.

    I tried this but still it is generating logs.

    Below is the sample of my parfile and also I have set the table to nologging.

    userid=ofus/ofus@orcl8i
    control=loaddata.ctl
    errors=25000
    direct=true
    log=loaddata.log



    Also when reading the documentation I saw

    "To use a direct path load (with the exception of parallel loads), SQL*Loader must have exclusive write access to the table and exclusive read/write access to any indexes"

    How do you make sure of this and what are its implications.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  7. #7
    Join Date
    Sep 2000
    Posts
    362
    Still waiting for a response....

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well I havent used sql loader direct load but as far as I know when you do direct load users cannot perform DML on those objects. The way to ensure this? Do it when the database activity is low if it is 24x7 database, otherwise do it at early mornings!

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by anuragmin
    I tried this but still it is generating logs.
    There will always be *some* redo logs generated even during the nologged opereations. But the amount of redo generated should be *much* less then normal.

    During nologged operations undo information is still written into RB segments and this undo entries are still fully logged. Also the data dictionary operations caused by unlogged operations (extent allocation etc) are still logged.

    Also when reading the documentation I saw

    "To use a direct path load (with the exception of parallel loads), SQL*Loader must have exclusive write access to the table and exclusive read/write access to any indexes"
    It means that during direct path load the table is being locked in the exclusive mode, so other session can still perform reads on that table, but can't perform DMLs. Indexes belonging to the table that is being loaded are marked as UNUSABLE during the direct path load, so they can't be used by other session even for reads.

    [Edited by jmodic on 06-11-2001 at 03:54 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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