I want to check number of rows affected by update query in oracle without executing the query for production environment, is it possible?

CodePudding user response：

You could run EXPLAIN on your update query. The output from that would include things like row counts at each step of the query pipeline. In addition, EXPLAIN would be making its estimates using table statistics, rather than hitting the actual production table.

CodePudding user response：

Instead of an UPDATE use a SELECT statement with the same WHERE filters and use COUNT(*) to get the number of rows; you will get an exact count of the rows without updating them.

You could also use the table statistics, but if your statistics are stale then the estimate given may not accurately reflect the actual number of rows.

CodePudding user response：

You could refer the v$sql table to view the query history or as one of the above answers as Explain.

Also, You could create a one time manual trigger and set it to on a update of a table to get in action on Update

        Eg. Create Trigger tname 
               After Update
               on
               Table for each row

               ..... SELECT count(:new.id)...etc