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> alter session set sql_trace=true; Session altered. SQL> alter session set optimizer_mode=first_rows; Session altered. -- connect to session SQL> oradebug setmypid Statement processed. SQL> oradebug dump modified_parameters 1; Statement processed. SQL> oradebug tracefile_name; i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc Contents of…

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 identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user. Example: The following trigger…

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) – If passed NULL, then it would assume SQL_ID of the last SQL statement CURSOR_CHILD_NO – If passed NULL, then it would assume child_number of last SQL Statement FORMAT – Possible values are BASIC, TYPICAL,…

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 turned on. SQL> SELECT * FROM dba_stmt_audit_opts union SELECT * FROM dba_priv_audit_opts; USER_NAME PROXY_NAME —————————— —————————— AUDIT_OPTION SUCCESS FAILURE —————————————- ———- ———- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY…

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 = userenv(‘sessionid’); SID SERIAL# PROCESSID CLIENTPID ———- ———- ——— ——— 43 52612 420734 5852:5460 V$SESSION.SID and V$SESSION.SERIAL# are database process id V$PROCESS.SPID – Shadow process id on the database server V$SESSION.PROCESS – Client process id, on…

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 of the parameter you would need to bounce the database for the new value to take into effect. The default value of the parameter is “NONE” in which database auditing is disabled. Even if “AUDIT_TRAIL”…