This lists some of the tables that are of good use when looking at data at the session level.
You may have to login as the system user to access some of the tables
1. v$session_longops: Has data regarding operations that are running on the database.
2. v$session: Has data about the session.
3. dba_jobs: Has information about the jobs in the database.
4. dict: Has information about the system tables.
5. dba_source : Has the source for all objects. Useful for finding out which line of your procedure/function is causing errors. The line # in the error message is not the same as in your source code.
6. dba_tablespaces : Has information about the tablespaces in the Database.
7. DBA_TAB_PRIVS: Has information about the grants given to all the objects in the database. Can use this to find out grants given to a user for any object in the database.