How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters. SQL> … More

Example of using tracefile_identifier

Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be … More

How to use dbms_xplan to display execution plan?

Using dbms_xplan.display_cursor one can see the execution plan of a SQL statement. The function takes following 3 parameters. SQL_ID (VARCHAR2) … More

How to check if auditing is turned on and privilege auditing is turned on?

If AUDIT_TRAIL is not set to NONE then using the following SQL one can find the statements that have auditing … More

How to get current session id, process id, client process id?

SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b where a.addr = b.paddr and b.audsid = … More

Tom Kyte’s utility to collect statistics

Tom Kyte’s following routine is a utility that can be used to to collect statistics of a session at the … More

How to enable auditing?

By setting the parameter “AUDIT_TRAIL” to “DB” or “OS”, one can enable auditing in the database. To change the value … More

How to generate systemdump when the instance is hung?

Using oradebug one can analyze if an instance is hung $ sqlplus “/as sysdba” SQL> oradebug mysetpid Statement processed. — … More

How to find the last time a session performed any activity?

In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any … More

Using oradebug to dump call stack?

Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running … More