3 Replies Latest reply on Jul 10, 2014 4:55 AM by 4997bbec-c4b7-492f-8200-47dd115be3d6

    Enable prompt before deleting all rows in a table

    4997bbec-c4b7-492f-8200-47dd115be3d6

      Hi All,

       

      Could any one of you please let me know if there is an option in SQL Developer 1.2.0 to enable prompting before deleting all rows in a table. This is to avoid accidental delete when "where" clause is missed out by mistake. If not, is there any other way to achieve this. I know triggers can be set but I am looking for a generic solution on all tables in the database. Please advise. Many thanks.

        • 1. Re: Enable prompt before deleting all rows in a table
          thatJeffSmith-Oracle

          v1.2.0?

           

          Sorry, the tool doesn't offer a 'are you sure' prompt for that. And even if it did - it wouldn't help you with people in the database not using SQL Developer. So if you want to really be hands-on, triggers are the way to go.

           

          However, I'd lean towards personal responsibility and removing database access if that's abused rather than babysitting your database users.

          1 person found this helpful
          • 2. Re: Enable prompt before deleting all rows in a table
            Could any one of you please let me know if there is an option in SQL Developer 1.2.0 to enable prompting before deleting all rows in a table. This is to avoid accidental delete when "where" clause is missed out by mistake.

             

            No - And there is really no such thing as 'all delete all rows in a table' since Oracle is a multi-user database that does NOT allow one user to see uncommitted DML executed by other users. So other users could be inserting new rows even as you delete what you think is 'all' rows.

            If not, is there any other way to achieve this. I know triggers can be set

            I don't know what 'trigger' you are thinking about but a trigger on the table itself won't help you. A trigger doesn't know how many rows are in a table or how many you are deleting. It also doesn't know if other users are performing DML on the same table or not.

             

            Oracle has plenty of ways to recover from mistakes like that. The most commonly used one is FLASHBACK.TABLE.

             

            See the SQL Language doc for an explanation and examples

            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm

            Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

            1 person found this helpful