DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: nologging

  1. #1
    Join Date
    Apr 2007
    Posts
    24

    nologging

    Is this supported in oracle 7.3.4?

    I have to delete and inset more than a million records in a table. I tried to set nologging for the table and I get this error:
    "Alter table ghl nologging;"

    ERROR at line 1:
    ORA-01735: invalid ALTER TABLE option

    If it is not supported, what is the best way to avoid creation of transaction log.

    It's the production database, running in archivelog mode and there is not enough room to handle huge transactions.

    Thanks much in advance!

    VP

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    no its obviously not, but i think you have misunderstood what nologging in - only a few specific operations can not log.

    You need the archive logs for recovery

  3. #3
    Join Date
    Apr 2007
    Posts
    24

    info

    dave, thanks

    We are migrating oracle to IBM and I am sending tables as flat files. Some table with millions of records are
    taking 3-4 hrs.

    In order to reduce the time, I decided to create another table with history data and then delete history from the
    main table.

    This will help move data faster. Once migration is complete, the history file will be migrated too and also
    the history data will have to be inserted back into the main table.

    Eg:

    GLH - 3 million record:

    1. create table glt_2004 as select * from glh where lhyear < 2005;

    2. Delete from glh where lhyear < 2005;

    Migrate GLH to IBM


    At a later stage On oracle:

    Migrate GLH_2004 to IBM

    Insert into glh as select * from glh_2004; (or append it)

    Delete glh_2004.

    I am going to test on standby first. Also I do not care about transaction logs during this time.

    Also this operation (steps 1 and 2) will be done when no users are on the system.
    Once the above is completed, an export followed by filesystem back up will be done.

    Now that nologging is not supported in 7.3.4, please suggest?

    Changing database into noarchivelogmode and back to archive mode will be more time consuming.

  4. #4
    Join Date
    Nov 2001
    Posts
    335
    7.3.4 has unrecoverable option on create table statement.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  5. #5
    Join Date
    Apr 2007
    Posts
    24

    insert

    Thanks BV1963.
    Any suggestions on inserts?

  6. #6
    Join Date
    Nov 2001
    Posts
    335
    1. create table glt_2004 unrecoverable as select * from glh where lhyear > 2005;


    You can manipulate with the create statement or insert /*+ append */ ( not sure if it was available in 7.3.4) to extract data that you need.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

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