Roll back Segments?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Roll back Segments?

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    For a typical transaction like inserting data from one table to another table, which need not be rolled back at all, Is there any way we can by-pass the entries made by oracle into rollback segments.

    There by the bulk inserts/updates and deletes can be made much faster?

    Badrianth

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    What is the reqt, do u want to create a temp table , work on the data and then copy to real table.If yes, try looking into global temporary tables of 8.1.6.

    Take Care
    GP

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    That's exactly my requirement, but how do I use the "Global Temporary Tables"


  4. #4
    Join Date
    Feb 2001
    Posts
    389
    Be sure to create a temporary tablespace with tempfile(not datafile).
    sql>
    create global temporary table b on commit preserve rows as select * from a;
    sql >(same session) work on b, commit;
    sql>(same session) ,
    create table d nologging as select * from b;
    sql> alter table d logging;

    Take Care
    GP

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    In my requirement, I have a text file containing all the account numbers to be deleted. There are about 6 lac accounts which needs to be deleted.
    I load the text file into a temp table (using sqlldr) and then
    delete from maintable
    where account in (select acc_no from temp);

    This takes a long time. I only want to delete from main for records in temp. Can I use this concept , If so How?

    Hope I am clear
    Badrianth

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Use a loop or a procedure to delete x thousand rows at a time and then commit.
    -
    If the # rows to be deleted>half the # rows in the table, consider creating a temp table and loading it with the data that you want to keep in the db.

  7. #7
    Join Date
    Feb 2001
    Posts
    3
    Don't use temp table. This requirement looks like a typical PL/SQL task. Why don't you use io utilities and read a number of records at once and then use a delete?

    HTH,

    Ravi

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