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

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.

You cannot bypass rollback segments.  

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.

You cannot bypasss....but can use the COPY Command to speed up and control the operation.

how can I use it?

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.

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;
/

oops! after the update statement, add:

commit_counter := commit_counter + 1;

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.

 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

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.

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).

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

rwarsh was correct about insert /*+ APPEND */ -- I accidentally left out the "+"

Thank you rwarsh! I evaluated your answer but sorry yoren's comments was better than yours.

Best regards,

Hüseyin.

Thank you rwarsh! I evaluated your answer but sorry yoren's comments was better than yours.

Best regards,

Hüseyin.

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.

Use the "logging" column of the user_tables view:

select table_name,logging
from user_tables;

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?