CODEWITHSUNDEEP
sqloracle-databasesysdate
Ast
Ast

Reputation: 85

Delete data uploaded on a certain date but there is no date column in the table

I am trying to delete data from an Oracle table. I have uploaded the data today but there is no date column so I cannot filter by that and delete. I have to find how to get the data that were uploaded today in the table and then filter by that and delete.

I would like something like this:

delete from mytable
where  uploaded_date = trunc(sysdate)

I am using Toad.

Thanks

Upvotes: 0

Views: 263

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

I think you want to go back in time to yesterday for your table's data. If so, you may use Flashback Table operation of Oracle DB, provided

  • Your database's version is at least 10g.
  • You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
  • You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
  • Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation. (For this option, you may consult your DBA).

  • Row movement must be already enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with this statement :

    SQL> ALTER TABLE mytable ENABLE ROW MOVEMENT;

but if not already ENABLED, don't use this sentence to ALTER the table, since your undo data is lost by issuing a DDL. In such a case you may recover by using Flashback Query
select * from mytable as of timestamp timestamp '2018-01-14';

and you can go back to yesterday's data by issuing(today's date is 2018-01-14) :

  SQL> FLASHBACK TABLE mytable TO TIMESTAMP 
      TO_TIMESTAMP('2018-01-14 00:00:00','YYYY-MM-DD HH:MI:SS') 
      ENABLE TRIGGERS;

You may omit ENABLE TRIGGERS part at the end, if your table doesn't have any trigger( The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled ).

Important Note : Before applying Flashback Table operation you should take a backup as CREATE TABLE mytable_ AS SELECT * FROM mytable;

Upvotes: 1

Related Questions