deleting last 500 rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: deleting last 500 rows

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    hello

    Yesterday i accidently added 500 rows to one of my databases.by using sql can i delete the last 500 rows from the table ??

    regards
    hrishy

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    We can't use rownum ,the inserted records may or may not get last 500 row numbers . Use your original SQL to delete your records ...
    Raghu

  3. #3
    Join Date
    Sep 2001
    Location
    Dallas, TX
    Posts
    27

    Delete 500 rows

    Well one alternative would be to get the row count on the table. Feed the select statement with the given rownum range (max rownum = max count + 1 and max rownum - 500) to the delete statement and you will have the 500 rows deleted. Ensure you have a backup before you go ahead.

    Hope this helps.

    BK.

  4. #4
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Look DBMS_ROWID package

    select rowid,DBMS_ROWID.ROWID_ROW_NUMBER(rowid) from table_name

    Best wishes!

  5. #5
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Use logminer undo. If you are not a DBA, ask your DBA to do it. Here is a link on oracle logminer:

    http://home.clara.net/dwotton/dba/logminer.htm

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Check that:

    delete from server_status where rowid in
    (select rowid from server_status
    where DBMS_ROWID.ROWID_ROW_NUMBER(rowid) >
    (select max(DBMS_ROWID.ROWID_ROW_NUMBER(rowid)) - 500 from server_status)
    );

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    You have two choices --

    1. If the database is in Archivelog mode and you have sufficient resources somewhere then restore the old backup and do a point-in-time recovery to just before the time you inserted the rows; Export the table and inport into original database.
    2.If you are on 8i then use Log_Miner; It has undo commands for all those inserts.

    ROWID may not work if there were subsequent deletes/inserts.

    - Rajeev
    Rajeev Suri

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Don't even think of using ROWIDs or ROWNUMs for this kind of delete! It is an almost guaranteed way to delet the *wrong* records.

    As advised by some wise people here, you should only rely on a reliable backup from before your insert or use a logminer if you are not able to create the reverse (delete) SQL to revert changes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    Don't even think of using ROWIDs or ROWNUMs for this kind of delete! It is an almost guaranteed way to delet the *wrong* records.

    As advised by some wise people here, you should only rely on a reliable backup from before your insert or use a logminer if you are not able to create the reverse (delete) SQL to revert changes.
    True. I would never rely on ROWID if I have to delete from PROD. I would rather do it "by hand" checking all the rows in the table. Even if they are 500 :-) And moreover, I would export the table before I delete.


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