Проблема
На расхожем ныне жаргоне заголовок мог бы звучать так: "Как убрать трупы (убитых сеансов)". Интересно, что такое название звучало бы точно.
Как известно, каждый сеанс связи клиентской программы с СУБД Oracle реализуется посредством "серверного процесса", возникающего на компьютере, где работает СУБД. Но сеанс может прерваться аварийно (например, из-за ненормального завершения работы клиентской программы или в результате выдачи администратором БД команды ALTER SYSTEM KILL SESSION) и тогда, по логике вещей серверный процесс, обслуживавший сеанс, должен пропасть. Иногда, однако, этого не происходит, и администратор наблюдает на сервере все растущий перечень "мертвых" серверных процессов. Почему - отдельный разговор, и у разработчиков Oracle найдется, вероятно, свое оправдание, но так далеко заходить не все администраторы имеют возможность. Важно, что "мертвые" серверные процессы как минимум расходуют оперативную память, которой когда-то может не хватить, и что с этим можно бороться.
Информация о процессах и сеансах
С процедурной точки зрения каждая конкретная программа под названием "экземпляр СУБД Oracle" состоит из "набора взаимодействующих процессов" (документация). Каждый активный сеанс связи клиентской программы с экземпляром СУБД реализуется одним таким процессом (серверным).
Список процессов в составе СУБД можно посмотреть в динамической таблице V$PROCESS:
SELECT addr, spid, program, pga_used_mem
FROM v$process;
Здесь поле ADDR - адрес процесса в оперативной памяти, SPID - номер процесса ОС, реализующего конкретный процесс Oracle (собственно процесса в Unix, или же нити в Windows). Поля PROGRAM и PGA_USED_MEM (объем собственной памяти данных процесса) приведены для наглядности.
Список сеансов связи, установленных клиентскими программами с СУБД можно посмотреть в динамической таблице V$SESSION:
SELECT sid, serial#, paddr, status, terminal, program
FROM v$session;
Здесь ADDR - адрес процесса, реализующего данный сеанс, STATUS - состояние сеанса; остальные поля приведены для наглядности (пара SID, SERIAL# - составной идентификатор сеанса, на который и нужно ссылаться при его уничтожении командой ALTER SYSTEM KILL SESSION). Если STATUS = 'KILLED', сеанс уже "убит", и такие-то сеансы иногда и приходится "зачищать".
Если соединить две таблицы по условию V$PROCESS.ADDR = V$SESSION.PADDR, получим информацию о сеансах, их состояниях и номерах физических процессов СУБД для них:
SELECT p.spid, p.program, s.program, s.terminal, s.status
FROM v$process p, v$session s
WHERE p.addr = s.paddr;
(Желающие могут выдать и другие поля, справившись об их смысле в документации).
Чистка процессов
Освобождение напрасно занятых ресурсов СУБД достигается путем удаления в ОС процесса (нити), реализующего процесс Oracle, соответствующий "убитому" сеансу. Для такого удаления в составе ПО Oracle имеется специальная программа orakill, параметрами которой даются номер процесса (нити) ОС (берем из поля V$PROCESS.SPID) и имя экземпляра СУБД (ORACLE_SID):
orakill ORACLE_SID номер_процесса_(нити)
Как воспользоваться этой программой - дело техники. Например, можно построить запрос типа
SELECT 'HOST orakill ' // i.instance_name // ' ' // p.spid
FROM v$process p, v$session s, v$instance i
WHERE p.addr = s.paddr AND
s.status = 'KILLED';
и окаймить его командами SQL*Plus SPOOL и START (а также отключить выдачу заголовка) и получившийся таким образом сценарий SQL*Plus запускать внешними средствами (cron, или программой at, или с помощью консоли Oracle Enterprise Manager) с желаемой регулярностью. Этим сценарием результат запроса выше будет поступать в файл в виде, примерно таком:
HOST orakill teacher 2056
HOST orakill teacher 1628
HOST orakill teacher 1604
HOST orakill teacher 1556
...
а потом командой START интерпретироваться.
Реализовать эту технику можно самостоятельно в качестве упражнения.