删除ArcSDE中的死连接或指定SDE_ID号连接或删除全部直连的连接

在实际操作过程中,我们可能有的时候设及到没有把直连的连接删除在表SDE.process_information中的连接记录,这时建议不能直接删除,正常可以建议使用设置TCPKEEPALIVE的值的进行操作,也可使用过程来进行删除.

 

第一种:删除全部直连

进入sqlplus中以sys用户连接,创建过程如下:

     CREATE OR REPLACE PROCEDURE "SYS"."KILL_ALL_DC"
/*************************************************
*John Karagiannis (ESRI Charlotte) - 2007
*Procedure kills all sessions listed in the sde.process_information table
*************************************************/
AS
SQL_STMT VARCHAR2(200);
CURSOR SDE_USERS IS
SELECT SERVER_ID,OWNER FROM SDE.PROCESS_INFORMATION WHERE DIRECT_CONNECT = 'Y';
NEWC INTEGER;
BEGIN
 FOR SDEREC IN SDE_USERS LOOP
  DECLARE CURSOR KILL_DC IS
  SELECT SID,SERIAL# FROM SYS.V$SESSION
  WHERE PROCESS LIKE SDEREC.SERVER_ID || ':%'
  AND USERNAME = SDEREC.OWNER;
  BEGIN
   FOR KILLREC IN KILL_DC LOOP
    SQL_STMT := 'ALTER SYSTEM KILL SESSION ' || '''' || KILLREC.SID || ',' || KILLREC.SERIAL# || '''';
    EXECUTE IMMEDIATE SQL_STMT;
   END LOOP;
  END;
 END LOOP;
    SDE.PINFO_UTIL.PURGE_UNUSED(NEWC);
END;
/

执行exec kill_all_dc;

 

 

第二种,删除指定的条件的直连

 

CREATE OR REPLACE PROCEDURE "SYS"."KILL_DC"
/*************************************************
* John Karagiannis (ESRI Charlotte) - 2007
* Procedure kills oracle session based on specific
* sde_id obtained from the sde.process_information table
*************************************************/
(
SDEID IN NUMBER
)
AS
SQL_STMT VARCHAR2(200);
CURSOR SDE_USERS IS
SELECT SERVER_ID,OWNER FROM SDE.PROCESS_INFORMATION WHERE DIRECT_CONNECT = 'Y'AND SDE_ID = SDEID;
NEWC INTEGER;
BEGIN
 FOR SDEREC IN SDE_USERS LOOP
  DECLARE CURSOR KILL_DC IS
  SELECT SID,SERIAL# FROM SYS.V$SESSION
  WHERE PROCESS LIKE SDEREC.SERVER_ID || ':%'
  AND USERNAME = SDEREC.OWNER;
  BEGIN
   FOR KILLREC IN KILL_DC LOOP
    SQL_STMT := 'ALTER SYSTEM KILL SESSION ' || '''' || KILLREC.SID || ',' || KILLREC.SERIAL# || '''';
    EXECUTE IMMEDIATE SQL_STMT;
   END LOOP;
  END;
 END LOOP;
    SDE.PINFO_UTIL.PURGE_UNUSED(NEWC);
END;
/

 

执行exec Kill_dc(30);

第三种情况:删除断网或意外中断的连接,创建储存过程并设置定时器的方式解决这个问题

1、进入sqlplus中，以sys用户连接，COPY下面内容并执行（就是按ENTER），就OK了。

 

CREATE OR REPLACE PROCEDURE "SYS"."KILL_ALL_DC2"
/*************************************************
*John Karagiannis (ESRI Charlotte) - 2007
*Procedure kills all sessions listed in the sde.process_information table
*************************************************/
AS
SQL_STMT VARCHAR2(200);
CURSOR SDE_USERS IS
SELECT SERVER_ID,OWNER FROM SDE.PROCESS_INFORMATION WHERE DIRECT_CONNECT = 'Y';
NEWC INTEGER;
BEGIN

    SDE.PINFO_UTIL.PURGE_UNUSED(NEWC);
END;
/

variable jobsde number;
begin
dbms_job.submit(:jobsde,'kill_all_dc2;',sysdate,'sysdate+1/1440');
end;
/

begin
dbms_job.run(:jobsde);
end;
/

 

转载于:https://www.cnblogs.com/gzwain/archive/2008/08/27/1277312.html