-
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 ...
Raghu
-
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.
-
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
Rajeev Suri
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|