?
Solved

How can I update or insert  a table  without using rollback?

Posted on 2002-05-09
20
Medium Priority
?
3,295 Views
Last Modified: 2008-01-16
Hello Friends,

Which commands do I have to use to update or insert without using rollback segment temprorily?

Best regards,

Hüseyin

0
Comment
Question by:hbiyik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +3
20 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6998974
There is no way that I know of to bypass creation of rollback information for updates.  Some types of inserts can bypass creation of redo-log information, but whether they bypass creation of rollback information or not, I am not sure.  These inserts are limited to tables that you have altered to "nologging" and they only include "insert into...select... from" statements if you include the hint /*APPEND */ after "insert", or to SQL*Loader in direct path mode, if you specify "unrecoverable" in the control file.  Remember, if you use "nologging" temporarily, you usually will want to take a backup very soon and alter the table back to "logging" for normal use.
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 6999619
You cannot bypass rollback segments.  
0
 
LVL 7

Expert Comment

by:yoren
ID: 7000575
What's the reason you want to bypass rollback segments? Is it the insert size or the performance? Like Bajwa said, you can't bypass rollback segments, but you probably don't need to.

SQL*Loader in direct path mode is the fastest way to insert data into Oracle. If it's already in the database, the insert /* APPEND */ trick is your best bet.

If you need to insert a whole bunch of data and your rollback segment isn't large enough, you can break up your insert by creating a PL/SQL block that does several inserts instead of one big one.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Expert Comment

by:MFK
ID: 7002932
You cannot bypasss....but can use the COPY Command to speed up and control the operation.
0
 

Author Comment

by:hbiyik
ID: 7002960
how can I use it?
0
 

Author Comment

by:hbiyik
ID: 7002966

how can I use the command below?
Could you please give me one example?
insert /* APPEND */

How can I do it for update statements?
I'm already database side.



Hüseyin.
0
 
LVL 7

Expert Comment

by:yoren
ID: 7003217
First you have to set your tables to nologging:

alter table mytable nologging;

Here's an example of the insert /* APPEND */. You'd use it to move data from one table to another:

insert /* APPEND */
into mytable
(col1, col2, col3)
select col1,col2,col3 from othertable;

To my knowledge you can't do anything like that for update statements. However, here's how you can do large updates without running out of rollback space, by committing every 1000 records:

declare
  commit_counter number := 0;
  cursor c is
    select col1,col2,col3,rowid from mytable;
begin
  for rec in c loop
    update mytable
    set col3 = col3 + 1
    where rowid = rec.rowid;
 
    if (commit_counter >= 1000) then
      commit;
      commit_counter := 0;
    end if;
  end loop;
end;
/
0
 
LVL 7

Expert Comment

by:yoren
ID: 7003220
oops! after the update statement, add:

commit_counter := commit_counter + 1;
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7006045
No, you cannot turn off logging for updates.  And there is no way to turn off rollback generation for updates.

Do you have a large update process that you need to do, and you are having a problem with rollback segments?  If you give us some more details about the specific problem you have, we may be able to suggest other ways to get the job done.
0
 
LVL 3

Expert Comment

by:Ron Warshawsky
ID: 7007206

 During DML operations major expense is logging, not use of rollbacks. Rollbacks are mandatory to provide read consistency.

 You can disable logging and updates/inserts will be flashing fast:
 
  alter table <name> nologging;

Thanks,

 rwarsh
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7009578
I'm not sure where rwarsh is getting his information.  Here is an excerpt from the Oracle8.1.7 documentation:

SQL Statements That Can Use No-Logging Mode

Although you can set the NOLOGGING attribute for a table, partition, index, or tablespace, no-logging mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute. Only the following operations can make use of no-logging mode:

direct load (SQL*Loader)

direct-load INSERT

CREATE TABLE ... AS SELECT

CREATE INDEX

ALTER TABLE ... MOVE PARTITION

ALTER TABLE ... SPLIT PARTITION

ALTER INDEX ... SPLIT PARTITION

ALTER INDEX ... REBUILD

ALTER INDEX ... REBUILD PARTITION

INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

All of these SQL statements can be parallelized. They can execute in logging or no-logging mode for both serial and parallel execution.

Other SQL statements are unaffected by the NOLOGGING attribute of the schema object. For example, the following SQL statements are unaffected by NOLOGGING mode: UPDATE and DELETE (except on some LOBs, as noted above), conventional path INSERT, and various DDL statements not listed above.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7009588
If you really need to do a major update without generating rollback activity, the only way I know of is a three step process:

1. write an SQL script that will gather the data you need and write the updated data to a spool file (or use a PL\SQL procedure to gather the data, modify it and write it to an ASCII file with utl_file)

2. truncate the table (this generates no rollback and little or no redo)

3. use SQL*Loader in direct-path mode with "unrecoverable" specified to load the modified data back into your table (this generates very little or no rollback, and very little redo).
0
 
LVL 3

Expert Comment

by:Ron Warshawsky
ID: 7009778

Hello markqeer,

 Statement "insert /*+ append */ ... select ... " on nologging table will do work with the minimal (almost no) redo.

 However, You are correct about updates - they will generate redo.

Thanks,

 rwarsh
0
 
LVL 7

Expert Comment

by:yoren
ID: 7009806
rwarsh was correct about insert /*+ APPEND */ -- I accidentally left out the "+"
0
 

Author Comment

by:hbiyik
ID: 7021075
Thank you rwarsh! I evaluated your answer but sorry yoren's comments was better than yours.

Best regards,

Hüseyin.
0
 
LVL 7

Accepted Solution

by:
yoren earned 300 total points
ID: 7021105
Glad to be of help Huseyin. Here's the code I posted earlier with the corrections, for your convenience:

Inserting:

insert /*+ APPEND */
into mytable
(col1, col2, col3)
select col1,col2,col3 from othertable;

Updating with commits every 1000 records:

declare
 commit_counter number := 0;
 cursor c is
   select col1,col2,col3,rowid from mytable;
begin
 for rec in c loop
   update mytable
   set col3 = col3 + 1
   where rowid = rec.rowid;
 
   commit_counter := commit_counter + 1;
   if (commit_counter >= 1000) then
     commit;
     commit_counter := 0;
   end if;
 end loop;
end;
/
0
 

Author Comment

by:hbiyik
ID: 7021151
Thank you rwarsh! I evaluated your answer but sorry yoren's comments was better than yours.

Best regards,

Hüseyin.
0
 

Author Comment

by:hbiyik
ID: 7023518
Thank you yoren for your help. It was good.

I'm giving my last points.

Yoren I've just one question.
How can I see whether my table is working with nologging mode or not?

Best regards,
Hüseyin.

0
 
LVL 7

Expert Comment

by:yoren
ID: 7023525
Use the "logging" column of the user_tables view:

select table_name,logging
from user_tables;
0
 

Author Comment

by:hbiyik
ID: 7023753

Sorry, I assume that I'll use APPEND trick at insert. IS it safe? When this process interrupted unexpectedly, may I face serious problem? Can I rollback it before commit it?    
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question