Oracle Concepts -
Killing From the
Oracle OS Side
Oracle Tips by Burleson Consulting
Methods of Murder
What are the methods of killing these
recalcitrant sessions? Essentially, you can issue an ALTER SYSTEM KILL
SESSION or you can issue a manual process kill command such as the
UNIX ‘kill -9 pid’ from the operating system side. You should do one
or the other of these types of kill operation, but not both. If you
kill both the Oracle process and the operating system process it can
result in database hang situations where you will have to perform a
shutdown abort. If you know the PID and user name it is trivial to
select the required information from V$SESSION, for example from the
serial# from V$SESSION WHERE sid=9 AND username=’LABUSER’;
Killing From the Oracle Side
The DBA can either manually issue an ALTER
SYSTEM command or develop a dynamic SQL script to perform the
operation for him or her. Source 31 shows a PL/SQL procedure to
perform a kill of a process using the dynamic SQL package of
procedures; DBMS_SQL. In Oracle8i a new option is available for the
ALTER SYSTEM which allows disconnection of the user process after a
transaction completes. The remarked section in Source 31 shows this
alternative to a straight kill.
REPLACE PROCEDURE kill_session ( session_id in varchar2,
serial_num in varchar2)
'ALTER SYSTEM KILL SESSION' || CHR(10) ||
-- string :=
-- 'ALTER SYSTEM DISCONNECT SESSION' || CHR(10) ||
cur := dbms_sql.open_cursor;
ret := dbms_sql.execute(cur) ;
WHEN OTHERS THEN
raise_application_error(-20001,'Error in execution',TRUE);
IF dbms_sql.is_open(cur) THEN
Source 31: Example execution of the
Once a session is killed, its status in the
V$SESSION view goes to “KILLED” and users will receive an error if
they try to reactivate the session. The session entry will not be
removed until the user attempts to reconnect. Shutdown immediate and
shutdown normal are supposed to be able to handle killed sessions
properly but there have been some reports of problems up to version
7.3.2 on some platforms.
For the above locking situation the resulting
actions would look like:
sid, serial# from V$SESSION WHERE sid=9 AND username='LABUSER';
procedure successfully completed.
enter/return to continue
Killing From the Operating System Side
The other method of removing these unwanted
sessions is to kill them from the operating system side. In UNIX
environments this is accomplished with the “kill -9’ command executed
from a privileged user. In other operating systems there are similar
commands. Before a session can be killed from the operating side you
must determine which process belongs to the user. In a MTS environment
this may be impossible and thus the Oracle side kill method is
preferred in MTS situations.
Source 32: Example kill command from UNIX
Creating and starting the database
Other important DBA responsibilities are the
creation of databases and the startup and shutdown of databases.
To create a database, the CREATE command is
run under SVRMGR or with 8i, in SQL*Plus.
1. First, the DBA must connect to the Oracle
internal user (or SYS user in 8i) via the command:
INTERNAL AS SYSDBA
2. Next, the instance is started in an
unmounted condition. This is accomplished with the following command.
PFILE=filename refers to the database
initialization file (INIT.ORA) you will be using, unless it is located
in the directory you are currently in; a path must also be provided.
3. Next, the database is created. The format
LOGFILE GROUP n (filespec)
DATAFILE (filespec) autoextend_clause
ON|OFF NEXT n K|M MAXSIZE n|UNLIMITED K|M
file name|logical|system link name' SIZE n K|M REUSE
Database name is the name of the database,
maximum of eight characters long.
File specifications for data files are of the
format: ‘filename’ SIZE integer K or M REUSE. K is for kilobytes, M
is for Megabytes. REUSE specifies that if the file already exists,
reuse it. New with later versions of Oracle7 and all of Oracle8 is the
AUTOEXTEND option, which is used to allow your datafiles to
automatically extend as needed. Be very careful with this command as
it can use up a great deal of disk space rather rapidly if a mistake
is made during table builds or inserts.
File specifications for log files depend on
the operating system.
The MAXLOGFILES, MAXDATAFILES, and
MAXINSTANCES set hard limits for the database; these should be set to
the maximum you ever expect.
MAXLOGMEMBERS and MAXLOGHISTORY are hard
EXTENT MANAGEMENT – This determines if the
extents in the SYSTEM tablespace are managed via the data dictionary
(DICTIONARY) or locally via a bitmap in the tablespace (LOCAL). In
addition the extents can be AUTOALLOCATED letting the system manage
them as to size or the UNIFORM clause with or without a size
specification can be used to force all extents to a uniform size.
For Oracle7 an Oracle8, CHARACTER_SET and
NATIONAL_CHARACTER_SET determine the character set that data will be
stored in; this value is operating system dependent.
If you need archive logging, set ARCHIVELOG;
if you don't need it right away, set NOARCHIVELOG. I suggest using
NOARCHIVELOG to start to avoid creation of multiple archive logs
during database initial creation, you won't recover from a failed
build, you will just rebuild. This is one thing to check if your
latter steps of the build (running catproc.sql for example) seem to
stall, the archive log location may have filled. This is checked using
the alert log stored in the location specified by
Databases are created in EXCLUSIVE mode.
Databases are either EXCLUSIVE or PARALLEL. A database must be altered
to PARALLEL mode after creation.
The CHARACTER_SET is for normal data.
Character specifications JA16SJISFIXED, JA16EUCFIXED and JA16DBCSFIXED
can only be used as the NATIONAL_CHARACTER_SET. The
NATIONAL_CHARACTER_SET specifies the national character set used to
store data in columns specifically defined as NCHAR, NCLOB, or
NVARCHAR2. You cannot change the national character set after creating
the database. If not specified, the national character set defaults to
the database character set.
You must verify that the registry on NT is set
with the same character set as the database or data problems and
performance degradation will occur. This applies to all client
workstations including Windows95, 97 and 98 as well as Windows2000
(NT5) based units.
What the system does when given a CREATE
DATABASE command is easy. First, the system creates control, redo log,
and database files. Next, the system creates the SYSTEM rollback
segment in the SYSTEM tablespace, creates and loads data dictionary
tables, and mounts and opens the database.
On NT and other Windows based installs the
instance manager will help with creation of new instances. To access
it use the START button and then choose the PROGRAMS menu item. From
the list of PROGRAMS choose the Oracle for Windows NT folder. Inside
the Oracle for Windows NT folder will be an icon for NT Instance
Manager v8.0 (or whatever version you are running). Selection of the
NT Instance manager v8.0 icon will start the instance manager, which
will list the current (if any) instances of Oracle running on your
platform. Select the New button from this display. You will be
prompted for all required information to start a base installation of
Oracle. The major limitation of this tool is that it only provides
single line inputs for items that have multiple lines of values.
However, be patient; it can be used once you are used to the way it
works. Another painful part of its use is that if you make a mistake,
it forgets everything you told it and you have to start over. The
newer versions are more reliable.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher