Handy collection of SQL's for Oracle
This is a collection of Oracle SQL’s that will come handy when you are in trouble. This includes finding who locked a record, finding SQL which is still executing, getting full SQL executed by application, find long running jobs, get time when the table was created, finding uptime of database. This is gathered from various blogs and stackoverflow.com answers. All these SQL uses V$ views and one will need SELECT_CATALOG_ROLE role to run these queries. Find sessions that blocks each other Find locks on records that are caused by two update operations on same record in different sessions. This often happens when one person updates a record using a SQL client and does not commit the change and the application/batch job tries to updates the same record. Then the second application waits forever hoping to acquire the lock. select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.usernam...