In Oracle, you can check the last DML (Data Manipulation Language) statement executed on a table by querying the data dictionary view called
DBA_HIST_SQLTEXT. This view contains the history of all SQL statements executed in the database.
Here’s a sample query to retrieve the last DML statement executed on a specific table:
SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE OBJECT_NAME = 'YOUR_TABLE_NAME' AND SQL_TYPE = 'UPDATE' OR SQL_TYPE = 'INSERT' OR SQL_TYPE = 'DELETE' ORDER BY SQL_ID DESC, PIECE DESC FETCH FIRST 1 ROWS ONLY;
Note that you’ll need to replace
YOUR_TABLE_NAME with the name of the table you’re interested in. Also, keep in mind that the
DBA_HIST_SQLTEXT view may contain a large amount of data, so it’s advisable to use it judiciously and in conjunction with other filters to reduce the amount of data returned.
In order to access the
DBA_HIST_SQLTEXT table, you need to have the appropriate database privileges. Typically, only database administrators or users with the
DBA role have the necessary privileges to access this table.
or you need a select grant to another user .