?
Solved

Rollback Control in Oracle

Posted on 2004-11-03
10
Medium Priority
?
1,973 Views
Last Modified: 2008-01-09
I have to write a stored procedure in Oracle 8i to do delete about 4 million records from a table.

But, the rollback segment & archive folder will overflow!

How do I set the commit/rollback control?

Step-by-step please?

Thx.
0
Comment
Question by:ramchi22
7 Comments
 
LVL 9

Accepted Solution

by:
pratikroy earned 336 total points
ID: 12488498
How many records are there in the table ? You should keep the total size of the table and the number of records that you want to delete. At times, it is preferable to use the CTAS method instead of actual deletions.

CTAS Method (Create Table As)
-----------------------------------------
CREATE TABLE TAB_NEW AS (SELECT * FROM TAB_OLD WHERE ....[condition/criterias that should remain in the table. Other tables will not be in the new table, so there will not be any need to delete the records]);

Remember that CREATE table command is the DDL and not a DML, so you will not require any commit to do this, so you will not require rollback segments.

Once the NEW table is created, you may DROP the old table, and rename the NEW table as the Original Table name.

APPEND Method
-------------------
On the same lines of CTAS, but slightly different ...
You could Create the NEW table with the wanted records, and then TRUNCATE the original table. Use the APPEND hint, with NOLOG to have a direct/faster insert into the table. This will be preferable, in case you have a lot of dependent objects, that will become invalid if you DROP the original table, and you don't want the hassle of recompiling them.

COMMIT FREQUENCY
-------------------------
This was a more traditional way to handle this situation. You will perform the delete one-by-one (or in smaller chunks) and perform the commit within the LOOP, so that you don;t ever run out of the rollback segment space ...

Hope the above will make sense, and would be of some help !
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12488532
CTAS Method (Create Table As)
-----------------------------------------
CREATE TABLE TAB_NEW AS (SELECT * FROM TAB_OLD WHERE ....[condition/criterias of the records that should remain in the table. Other records will not be in the new table, so there will not be any need to delete them]);

Remember that CREATE table command is the DDL and not a DML, so you will not require any commit to do this, so you will not require rollback segments.

Once the NEW table is created, you may DROP the old table, and rename the NEW table as the Original Table name. You may have to keep the track of objects (like procedures, packages, synonyms etc etc) that will become invalid when you drop the original table. But you could always recompile them. You might have to recreate the indexes that were on the original table. If you don't want all these, then prefer the second method....
0
 
LVL 7

Assisted Solution

by:bvanderveen
bvanderveen earned 332 total points
ID: 12488680
Interesting method by pratikroy.  Two other possibilities:

 1.  If you are deleting all records, try truncate table command.
 2.  I have used SP's on subsets of the data as follows:

        A.  Cursor to select rowids of rows to be deleted.
        B. Loop through records
        C. Copy rowid to variable.
        C. Delete the row where rrowid = variable (very fast)
        D. use a counter to commit every N records.

You could also use bulk operations to do the second one.  (PLSQL table of rowids, FORALL statement).

Hope this helps.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Expert Comment

by:hanzpk
ID: 12489238
or you could specify first nologging for the table to be deleted, before executing sp to delete

'alter table table1 nologging'
0
 
LVL 11

Assisted Solution

by:cjjclifford
cjjclifford earned 332 total points
ID: 12490994
bvanderveen, there is a better way of deleting with commits every N rows (Oracle provide a PL/SQL procedure through Metalink (DocID: 1020306.6) to do just this) - there is a very good discussion about updating large numbers of rows, which is related closely to deleting: http://www.experts-exchange.com/Databases/Oracle/Q_21128949.html, which provides a link to the following interesting read: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330)

Is your table partitioned? If so, you might be lucky in that all the data to delete is in a single partition (and that partition only contains data to be deleted) - if this is the case "ALTER TABLE DROP PARTITION partition_name" - this is generally a very good way of archiving/deleting data by a regular key (say timestamped data deleted by the day after e.g. 100 days) - each day would have a partition, and then after 100 days the eldest partition is dropped - nice and fast!

0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12491013
btw, when you say "archiving folder" will overflow, you must have a very small volume assigned - I like to see enough space for at least 3-5 days of archive, incase there are backup failures, etc, to allow for long-weekends, etc!

just to extend on the PL/SQL I mentioned above, the code more or less does the following, except it does it generically, appending the necessary ROWNUM code to a standard DELETE statement...

DECLARE
    finished BOOLEAN := FALSE;
BEGIN
    WHILE NOT finished
    LOOP
        -- the ROWNUM clause is how to control the number in each commit...
        EXECUTE IMMEDIATE
            'DELETE FROM tablename WHERE key = 'fordelete'
                                                   AND rownum < 1000';
        IF SQL%ROWCOUNT = 0 THEN
            finished := TRUE;
        END IF;
        COMMIT;
     END LOOP;
END;
/
0
 
LVL 9

Expert Comment

by:konektor
ID: 12491105
if u need to delete the records regulary and u have some date criteria (eg. u want to delete all records, which are older then x months) create partitioned table (divided to partition by month of record creation). u will only need to delete partition each month then ...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

839 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