Current Activity Views

Oracle has various system views that provide current session and wait information. These are very helpful for performance tuning and troubleshooting.

Current Sessions

Obviously, when there are performance issues, it is necessary to take a look at the current sessions on the database. There is no sp_who, sp_who2, or sp_lock in Oracle, but there is the v$session view. This view shows which sessions are active. You can join this with another view to see which queries a session is running.

SQLPLUS> select username, schemaname, osuser, lockwait, statusfrom v$sessionwhere status='ACTIVE' and username not in ('SYS','SYSTEM');USERNAME          SCHEMANAME    OSUSER          LOCKWAIT STATUS---------------------------------- ---------------------------- ...

Get Oracle Database Administration for Microsoft SQL Server DBAs now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.