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 the tracefile:

*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1’
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1’ console output:

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 will create a trace file with the user’s OSuser and machine part of the filename for any user who login with “SCOTT”

CREATE OR REPLACE TRIGGER sys.session_trace_on
— to be created by sys user
AFTER LOGON ON database
DECLARE
v_machinename VARCHAR2(64);
v_ora_username VARCHAR2(30) DEFAULT NULL;
v_os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE sid = userenv('sid')
and username = 'SCOTT';
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_os_username, v_machinename, v_program;
IF c1%FOUND THEN
— DBMS_SESSION.set_sql_trace (TRUE);
v_machinename := replace(replace(v_machinename, '\', '_'), '/', '_');
v_os_username := replace(replace(v_os_username, '\', '_'), '/', '_');
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''' || trim(v_os_username) || '''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
CLOSE c1;
END;
/

Trace file created:
ls testdb_ora_5480_CAL-AMIN_Administrator.trc

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, ALL. The default value in TYPICAL

SQL> select count(1) from table1;

COUNT(1)
———-
32

Show execution plan of last SQL statement
SQL> SELECT *FROM TABLE (DBMS_XPLAN.display_cursor());

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

Using sql_id, sql_child_number of a SQL statement that was last executed
SQL> select sql_id, sql_child_number from v$session where sid = 130;

SQL_ID SQL_CHILD_NUMBER
————- —————-
5mf7bd0jnctjv 0

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display_cursor(‘dtzn8k4hzc88d’, 0));
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

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 ACCESS

ALTER DATABASE BY ACCESS BY ACCESS
..

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 windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.

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” is enabled/disabled, Oracle will write OS audit trail at OS audit trail at startup and shutdown of instance, and connections made by SYSOPER and SYSDBA.

If “AUDIT_TRAIL” is set to “OS”, the parameter “AUDIT_FILE_DEST” is the location where audit files are created. The default value of this parameter is “$ORACLE_HOME/rdbms/audit”.

If “AUDIT_TRAIL” is set to “DB”, oracle writes the auditing information in “SYS.AUD$” table. By default the table is created in SYSTEM tablespace.

If one tries to open a database in read only mode and AUDIT_TRAIL is set to DB, Oracle will generate an error stating the audit_trail is incompatible with database open mode.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode

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.

— no limit on trace file
SQL> oradebug unlimit
Statement processed.

SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc

— Wait for couple of minutes
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc

— Wait for couple of minutes and get systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.

— Wait for couple of minutes and run systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.

If one cannot connect using SQL*Plus then using PRELIM option one can connect without allocation of a session. Information on PRELIM

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 26 11:47:18 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.
$ sqlplus /nolog
SQL> set _prelim on
SQL> connect /as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
— Wait for couple of minutes
SQL> oradebug dump systemstate 10
Statement processed.

Attach using debugger and dump systemstate
$ gdb /u01/oracle/product/10.2.0/db_1/bin/oracle 22878
GNU gdb Red Hat Linux (6.3.0.0-1.153.el4_6.2rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type “show copying” to see the conditions.
There is absolutely no warranty for GDB. Type “show warranty” for details.
This GDB was configured as “x86_64-redhat-linux-gnu”…(no debugging symbols found)
Using host libthread_db library “/lib64/tls/libthread_db.so.1”.

Attaching to program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878

(gdb) print ksudss(10)
$1 = -1073762080
(gdb) print ksdhng(3)
$2 = 1
(gdb) detach
Detaching from program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878
(gdb) quit

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 activity within the database.

select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null –– to ignore background process
order by last_call_et;

USERNAME Minutes STATUS
—————————— ———- ——–
SYS 0 ACTIVE
SCOTT 0 INACTIVE
SYSTEM 34 INACTIVE
..

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 session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area

Example:
— Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc

Information in the trace file:

*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3’ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?

..