Today we had observe very weird issue.
Application team were facing an issue of auto data update causing all login to failed.
On analysis application team found out , some master set up use to get deleted and all application login was getting failed.
App team wanted us to identified, who was invoking such SQL causing application login to failed.
Step by Step analysis carry out.
1. Query GV$SQL to get Sql_id and other details based on SQL Text
(DELETE FROM <Table_name>)
2. Based on SQL ID , Query GV$ACTIVE_SESSION_HISTORY to get machine, module ,program and session details.
SQL was getting invoked manually using SQL Developer.
3. Based on session details Query GV$Session or GV$open_cursors.
Identified inactive session with necessary OSUSER and Machine details.
Had passed Necessary SQL metadata to Application team to check why Developer was manually deleting master setup.
When Developer was confront, he mentioned no such scripts was manually run by him.
But based on Oracle Snapshot, it was sure such sql was invoke by same user using SQL developer.
On further analysis, we found out purging scripts was by default updated in SQL Developer start up scripts.
So Whenever user was login into SQL Developer, it caused purging scripts to run and delete some master setup necessary for logins.