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
Printable View
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
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 ...
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.
Look DBMS_ROWID package
select rowid,DBMS_ROWID.ROWID_ROW_NUMBER(rowid) from table_name
Best wishes!
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
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)
);
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
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.Quote:
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.