Настройка процедуры завершения сессий

Материал из START-IP
Перейти к навигации Перейти к поиску

убедиться что tnsnames.ora содержит строчки:

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

а listener.ora содержит:

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/oracle/app/oracle/product/9.2.0)
      (PROGRAM = extproc)
    )

А также

LISTENER = 
DESCRIPTION_LIST =
    	  (DESCRIPTION =
      	(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    	      )

Пример файла listener.ora:

	
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/oracle/app/oracle/product/9.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /usr/oracle/app/oracle/product/9.2.0)
      (SID_NAME = orcl)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = strtip)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

Пример файла tnsnames.ora:

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
startip=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = strtip)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

из под system выполнить:

grant create library to sip_w; где sip_w – схема Cтарт-IP
grant select on v$process to sip_w;
grant select on v$session to sip_w;

из-под пользователя sip_w выполнить:

Linux RedHat

Скопировать библиотеку killer.so в каталог $ORACLE_HOME/lib

CREATE OR REPLACE LIBRARY killer
AS
   	 '$ORACLE_HOME/lib/killer.so';
/

CREATE OR REPLACE FUNCTION p_killer ( pid Pls_integer)
RETURN pls_integer
AS 
		LANGUAGE C
		LIBRARY killer
		NAME "killer"
		PARAMETERS ( pid int);
/

Sun Solaris9 64 bit

Скопировать библиотеку killer64.so в каталог $ORACLE_HOME/lib

CREATE OR REPLACE LIBRARY killer64
AS
    '$ORACLE_HOME/lib/killer64.so';
/

CREATE OR REPLACE FUNCTION p_killer ( pid Pls_integer)
RETURN pls_integer
AS 
	LANGUAGE C
	LIBRARY killer64
	NAME "killer"
	PARAMETERS ( pid int);
/



Проверка:

set serveroutput on
declare pid pls_integer;
begin
 pid := p_killer(10303);
 DBMS_OUTPUT.PUT_LINE (pid);
 end; 
/

Где 10303 – системный pid процесса который нужно завершить