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 above reports:

SELECT sid, 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.

CREATE OR REPLACE PROCEDURE kill_session ( session_id in varchar2,
serial_num in varchar2)
AS
cur INTEGER;
ret INTEGER;
string VARCHAR2(100);
BEGIN
 string :=
        'ALTER SYSTEM KILL SESSION' || CHR(10) ||
CHR(39)||session_id||','||serial_num||CHR(39);
-- string :=
--          'ALTER SYSTEM DISCONNECT SESSION' || CHR(10) ||
-- CHR(39)||session_id||','||serial_num||CHR(39)||CHR(10)||
--' POST_TRANSACTION';
   cur := dbms_sql.open_cursor;
   dbms_sql.parse(cur,string,dbms_sql.v7);
   ret := dbms_sql.execute(cur)  ;
   dbms_sql.close_cursor(cur);
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'Error in execution',TRUE);
      IF dbms_sql.is_open(cur) THEN
        dbms_sql.close_cursor(cur);
      END IF;
END;
/

Source 31: Example execution of the kill_session script

EXECUTE kill_session('13','1424');                                                                  

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:

SQL> SELECT sid, serial# from V$SESSION WHERE sid=9 AND username='LABUSER'; 

      SID   SERIAL#
--------- ---------
        9       171
 

1 row selected.

SQL> execute kill_session(9,171);

PL/SQL procedure successfully completed.

SQL> @waiters

press 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.

kill -9 11240

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.

Database Creation

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:

    CONNECT INTERNAL  AS SYSDBA

2. Next, the instance is started in an unmounted condition. This is accomplished with the following command.

    STARTUP NOMOUNT PFILE=filename 

    where:

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 would be:

CREATE DATABASE name
      CONTROLFILE REUSE
      LOGFILE GROUP n (filespec)
      MAXLOGFILES n
      MAXLOGMEMBERS     n
      MAXLOGHISTORY     n
      MAXDATAFILES      n
      MAXINSTANCES      n
      ARCHIVELOG|NOARCHIVELOG
      CHARACTER_SET  charset
      NATIONAL_CHARACTER_SET charset
      DATAFILE (filespec) autoextend_clause
     Extent_management_clause 

autoextend_clause:

AUTOEXTEND ON|OFF NEXT n K|M MAXSIZE n|UNLIMITED K|M

Filespec:

'full path file name|logical|system link name' SIZE n K|M REUSE

extent_management_clause:

EXTENT MANAGEMENT
              DIRECTORY
              LOCAL
                AUTOALLOCATE
                UNIFORM
                      SIZE n [K|M]
             

where:

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 limits.

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 user_dump_destination.

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 here.