Rollback Control in Oracle

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.
ramchi22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pratikroyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pratikroyCommented:
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
bvanderveenCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hanzpkCommented:
or you could specify first nologging for the table to be deleted, before executing sp to delete

'alter table table1 nologging'
0
cjjcliffordCommented:
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
cjjcliffordCommented:
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
konektorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.