How can Oracle determine who deleted a particular record?

To identify who deleted a record in Oracle, you can follow these steps:

1. First, use the FLASHBACK TABLE statement to restore the table to its state before the deleted records. For example:
2. Roll back the FLASHBACK TABLE to one hour ago using the current system timestamp.
3. The time stamp here can be adjusted as needed to find the appropriate recovery point.
4. After restoring the table, you can use Oracle’s redo log to find relevant information about the delete operation. The redo log in Oracle records all important operations that occur in the database, including delete operations.
5. Use the following query to find information related to delete operations:
6. Retrieve the username, operating system username, machine, program, and timestamp from the v$session view where the status is active, the type is user, and the username is not null.
7. This query will return information about the current running session, including the username that performed the delete operation, the operating system username, machine name, program name, and timestamp.
8. Based on the username and timestamp in the query results, it can be further determined which user deleted the records at what time.

Please note that the above method can only be used shortly after a deletion operation, as Oracle’s Redo Log files have a certain retention period. Additionally, this method also requires sufficient privileges to execute FLASHBACK TABLE and query v$session and other critical operations.