Thursday, February 19, 2009

How to Kill Oracle Sessions?

There are two main ways to Kill Oracle User Sessions.
1. Directly the session of User.

ALTER SYSTEM KILL SESSION;

2. Identifying the User Process and kill it.

SELECT b.object_name,c.SERIAL#,a.*
FROM V$LOCKED_OBJECT a,DBA_OBJECTS b,v$session c
WHERE a.object_id = b.object_id
AND a.SESSION_ID = c.SID
order by a.SESSION_ID;


ALTER SYSTEM KILL SESSION 'sid,serial#'

In these cases the session will be "marked for kill". It will then be killed as soon as possible.

Friday, February 6, 2009

Oracle Database Architecture

The Oracle instance have the background processes, memory structures, and disk resources that comprise the Oracle instance, and also of the methods in which they act together to allow users to access information. Several memory structures exist on the Oracle database to improve performance on various areas of the database. The memory structures of an Oracle instance include the System Global Area (SGA) and the Program Global Area (PGA).

System Global Area (SGA)

The SGA, in turn, consists of a minimum of three components: the data block buffer cache, the shared pool, and the redo log buffer. Corresponding to several of these memory areas are certain disk resources. These disk resources are divided into two categories: physical resources and logical resources.

Program Global Area (PGA)

The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling. From the prior discussion of the shared pool, the DBA should know that the database already stores parse trees for recently executed SQL statements in a shared area called the library cache. So, why do the users need their own area to execute? The reason users need their own area in memory to execute is that, even though the parse information for SQL or PL/SQL may already be available, the values that the user wants to execute the search or update upon cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.

Background Processes

In any Oracle instance, there will be user processes accessing information. . Likewise, the Oracle instance will be doing some things behind the scenes, using background processes. There are several background processes in the Oracle instance. It was mentioned in the discussion of the SGA that no user process ever interfaces directly with I/O. This setup is allowed because the Oracle instance has its own background processes that handle everything from writing changed data blocks onto disk to securing locks on remote databases for record changes in situations where the Oracle instance is set up to run in a distributed environment. The following list presents each background process and its role in the Oracle instance.