DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 36

Thread: Ora-01555 Snapshot Too Old

  1. #11
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by ixion
    CTAS
    Then, Instead of insert, drop the original and rename the newly created table with less data! He'll also need to recreate triggers, indexes and FK's etc...
    Dropping the original table is not a wise decision, if the data is huge after purging with too many columns and constraints.
    "What is past is PROLOGUE"

  2. #12
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Do you have a test environment?

    There are tools and/or scripts to reverse engineer all the dependencies. It's not that hard.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #13
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    How about using a PL/SQL collection and then doing the delete with a forall????

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by gandolf989
    How about using a PL/SQL collection and then doing the delete with a forall????
    Still you may get 1555 error if you use LIMIT in the collection.

    Tamil

  5. #15
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by dbasan
    Dropping the original table is not a wise decision, if the data is huge after purging with too many columns and constraints.
    Then how do you suggest the OP reclaim the space? Which is the reason for the deletes, and 1555 in the first place.

    Best option is to add another data file or better yet resize the existing one.

    Well, there is the new 10g commands that can lower the HWM. But he did not indicate the release.

  6. #16
    Join Date
    Nov 2001
    Posts
    335
    SEARCH FOR DELETE _COMMIT PROCEDURE on metalink . It will do the trick.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  7. #17
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You need to externalize the loop from the database. For example, if you need to delete 5 years of data, you can delete day by day with something like:

    Code:
    declare
       start_dt DATE;
    
    begin
       start_dt = to_date('01/01/2000','mm/dd/yyyy');
    
       while start_dt < trunc(sysdate-365) loop
          delete from yourTab where myDate = start_dt;
          commit;
       end loop;
    end;
    Jeff Hunter

  8. #18
    Join Date
    Sep 2005
    Posts
    278
    How about partitioning the table and removing the partitions with old data.

    Just a thought, let me know whether it will work fine..

  9. #19
    Join Date
    Jun 2006
    Posts
    40
    ITS URGENT!!!
    Earlier i was trying to delete the records with the following.
    DECLARE
    row_num NUMBER:=0;
    total_num NUMBER:=0;
    CURSOR del_record_cur IS
    SELECT ROWID
    FROM table_name
    WHERE DATE='01-JAN-2002';
    BEGIN
    FOR rec IN del_record_cur LOOP
    DELETE FROM table_name
    WHERE ROWID=rec.ROWID;
    total_num:=total_num+1;
    row_num:=row_num+1;
    IF(row_num>=300) THEN
    COMMIT;
    row_num:=0;
    END IF;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Deleted ' || total_num ||'records from table_name ');
    END;
    /

    Then with this also i was getting the same error. so i thought because of frequent commit there might be this problem. so i have tried another one

    DELETE FROM TABLE_NAME
    WHERE DATE='01-JAN-2002'
    AND ROWNUM<5;

    but same error was thr.
    Also i 've made undo_retention=1600.
    The free size in undotbs is about 3 GB.
    What should be the size of rollback segments ,if i have to delete about 4000 rows for single date.
    Please provide the probable solution considering above clarifications.
    Last edited by minal_yawale; 07-11-2006 at 02:18 AM.

  10. #20
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I would not suggest for deleting few rows and commit frequently.
    How ever, if you like it, Change the cursor:

    CURSOR del_record_cur IS
    SELECT ROWID
    FROM table_name
    WHERE DATE='01-JAN-2002'
    order by rowid;

    This will reduce chances of getting 1555 error, but will not totally eliminate.

    Tamil
    Last edited by tamilselvan; 07-11-2006 at 09:36 AM.

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