Unrecoverable option in oracle 734
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Unrecoverable option in oracle 734

  1. #1
    Join Date
    Oct 2000
    Posts
    57
    Hi,

    I want to use UNRECOVERABLE option in oracle 734 to reduce log switches (or redolog entry ) .
    Time difference between the log switches is same when i use UNRECOVERABLE option compare to without UNRECOVERABLE option.

    Could anybody able to explain about UNRECOVERABLE option
    while inserting to a table . How will i make sure thet transaction is not entering to redologs when i use UNRECOVERABLE optiom.


    Thanks in advance
    Tom.




  2. #2
    Join Date
    Sep 2000
    Posts
    14
    UNRECOVERABLE



    specifies that the creation of the table (and any indices required because of constraints) will not be logged in the redo log file. As a result, media recovery will not recreate the table (and any indices required because of constraints).



    This keyword can only be specified with the AS subquery clause. Using this keyword makes table creation faster than using the RECOVERABLE option because redo log entries are not written.

  3. #3
    Join Date
    Oct 2000
    Posts
    57

    Tony, i agree with that .

    My query is how will i make sure that transactions are not going to redolog when i use UNRECOVERABLE option .

    My sql statement is like this .

    Insert into <tableA>
    select
    col1,
    col2,
    col3
    from <tableB>@dblink UNRECOVERABLE;

    Thanks
    tom


  4. #4
    Join Date
    Sep 2000
    Posts
    14
    I don't think you can use UNRECOVERABLE with Insert in 7.3.4
    Oracle8i now has Insert NOLOGGING option.

    Can you use the CREATE......AS SELECT ....... UNRECOVERABLE option?

  5. #5
    Join Date
    Oct 2000
    Posts
    57
    Yes, I am able to do insert with UNRECOVERABLE option.
    I just want to make sure that it is not going in to redologs !!!

    Thanks
    Tom

  6. #6
    Join Date
    Nov 2000
    Posts
    9
    as you noted early on... you want to compare the redo-log switching with and without the option.

    So...find a reasonbly large table... copy it....then update some field in each record... note or record time before and after for this operation. Note the number of log switches.
    Do the same w/UNRECOVERABLE option...

    ...
    Michael T Hudacko
    Senior Oracle DBA

  7. #7
    Join Date
    Nov 2000
    Posts
    9
    Hi TOMPK..

    Real life example are better illustrations...

    I have a 5 GB production database (7.3.4). There are several
    other "copies" of this for STAGING, TESTING, DEVELOPING, and just plain screwing with it.! Anyways, the developers
    _sometimes_ want one of these copies refreshed with production data. I will use EXP/IMP but on the IMP side do not allow IMP to rebuild Indexes because it takes hours.

    Instead I extract the index building script from PROD's data dictionary (using fresh extent sizes) AND ALWAYS use UNRECOVERABLE in rebuilding indexes. I cannot quote the time difference due to NOT LOGGING BLOCk changes because I set this up 2 years ago... BUT IT IS SIGNIFICANT!
    Michael T Hudacko
    Senior Oracle DBA

  8. #8
    Join Date
    Oct 2000
    Posts
    57
    Hi Micheal,


    Thanks for the reply , I did insert to a reasonably large
    table with and without UNRECOVERABLE option .
    I couldn't find any difference in time on log switches .
    ( getting almost same time for both)

    So i just want to make sure that this transactions(inserts with UNRECOVERABLE option) are not going in to redologs ...

    Thanks
    Tom.



  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There is an error in 7.3
    Take this example:
    Insert into
    select
    col1,
    col2,
    col3
    from @dblink UNRECOVERABLE;

    Here UNRECOVERABLE is used as Alias for the source table.
    Not we thought.

    The correct syntax is:

    CREATE TABLE1 XXX (col1 , col2) UNRECOVERABLE AS SELECT col1, col2 from TABLE2.


  10. #10
    Join Date
    Oct 2000
    Posts
    57
    Thank you for the info.

    I could able to create a table with UNRECOVERABLE option
    but i am not able to insert records with unrecoverable option .

    I use the below syntax

    Insert into table1
    values(col1,col2)
    Unrecoverable
    select col1,col2 from table2@dblink;

    Is there any way to insert records with unrecoverable option?

    Thanks
    Tom

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