JAVA How to JAVA Development tutorials and how to

How to monitor your Oracle DB (Part 1)

Sometimes, you are developing some application that connects to Oracle database and you need to monitor the database to know something about processes, open sessions/cursors and so on, so is necessary to know a little bit about Oracle administration.

Basically, Oracle provides us with a set of fixed tables that allow us to check database settings, processes, parameters, open sessions and so on that gives us important information. Some of these tables are the next:

V$SESSION /*Allows us to see open sessions and details about them*/
V$DATABASE /*Show us database information*/
V$FIXED_TABLE /*IMPORTANT because this shows a list of all fixed tables, learn that and you can get access all*/
V$LOCKED_OBJECT /*Shows us locked objects on the database*/
V$SQLAREA /*Show sql queries executed related to v$session*/

You can check a list of those views in the next link:
http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html
Now, how to use that information to do different stuff on the database?

First situation: Suppose that you're developing an application and you want to checking out what's going on. You suppose that is something related to database. Well, in this case, whe are going to check the open sessions. To do that we can use the next query:

SELECT * FROM V$SESSION WHERE  MACHINE = 'MACHINE_NAME';
--OR TO BE MORE SPECIFIC, JUST THE COLUMNS THAT WE'RE INTERESTED
SELECT SID, SERIAL#,  command, SCHEMANAME, PROGRAM, MODULE, STATUS,
FROM V$SESSION WHERE MACHINE = 'MY_MACHINE' ORDER BY MODULE, status;
/*This show us something like this:*/
SID   SERIAL# COMMAND  SCHEMANAME    PROGRAM            MODULE          STATUS
-------------------------------------------------------------------------------
99    3436    3        WBK            SQL Developer    SQL Developer    ACTIVE
77    8968    0        WBK            SQL Developer    SQL Developer    SNIPED
39    7610    0        WBK            AppOne           AppOne           INACTIVE
136    301    0        WBK            AppOne           AppOne           INACTIVE
98    2574    0        WBK            AppOne           AppOne           SNIPED
47    7179    0        WBK            JAVAJob          JAVAJob          INACTIVE
78    8462    0        WBK            JAVAJob          JAVAJob          INACTIVE

Now you can see your connections (filtered by machine in case that more than one machine are connected to database) and see the connection status or the program that opened that session (in my case you can see SQL Developer that i use  to query database and other applications).

I wanna close some some of those sessions, what i have to do? To do that you have to kill the sessions. To do that you can use the next statement:

ALTER SYSTEM KILL SESSION 'SESSION NUMBER,SERIAL# NUMBER';
/*For example, if i wanna kill the ACTIVE session of my SQL Developer,
you can see that SID is 99 and SERIAL# is 3436, so the statement will look like this */
ALTER SYSTEM KILL SESSION '99,3436';

You can check more information about kill Oracle sessions here:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/manproc.htm#1960
Another situation: Let's suppose that you have a statement that takes a looong time to run or something like that, or a query that leaves inactive the session for any reason, or a procedure call that freezes. Well, in this case we have to see the last query that executed in session, so we use V$SQLAREA joining with V$SESSION using sql_address field, in the next way:

select a.sid, a.serial#, b.sql_text from v$session a, v$sqlarea b
where a.sql_address=b.address and a.MACHINE = 'MY_MACHINE';
/*You will see something like this*/
SID    SERIAL#    SQL_TEXT
---------------------------------
37    10446    select wbkopcione0_.IDOPC_PADRE as IDOPC9_1_, wbkopcione0_.IDOPC as IDOPC1_,...
39    7610     select wbkpermiso0_.IDUSROPC as IDUSROPC44_, wbkpermiso0_.ACCESOS as ...
75    404      BEGIN    LBACSYS.lbac_events.logon(dbms_standard.login_user);   END;
77    8968     BEGIN    LBACSYS.lbac_events.logon(dbms_standard.login_user);   END;
102   8890     BEGIN    LBACSYS.lbac_events.logon(dbms_standard.login_user);   END;
99    3436     select a.sid, a.serial#, b.sql_text from v$session a, v$sqlarea b ...

Now you can see the last query that was executed on the session. It's important to clarify that you need to have privileges over V$ fixed tables in order to query them and also ALTER SYSTEM privilege to kill sessions. There's a lot of stuff that you can do in Oracle administration side, so we are going to write another post talking about this. Right now, i hope that you enjoy this post and find useful