终止任务不会解锁 SQL Developer 中的表

Question

我正在使用 Oracle SQL Developer 版本 3.0.04 通过 VPN 连接到我公司服务器的 11g 数据库。我注意到，通过“任务进度”窗口终止任务后，它实际上并没有完全“终止”该任务。我在一些论坛上读到，发生的情况是 Oracle 正在尝试“回滚”我所调用的过程所做的更改，但我无法想象回滚到我的程序需要花费超过 10 分钟的时间。运行时间 5 分钟后最后一次提交。这让我相信它无论如何都会继续运行。

我可以向 DBA 发送消息来终止我的会话，这会释放进程正在使用的表上的锁定（以及包的源代码，以便我可以再次编辑它），但这不是最佳解决方案。我没有对数据库的命令行访问权限，也没有通过 SQL Developer 获得管理员权限。有什么方法可以明确终止我启动的任务，例如 kill -9 吗？

Answer 1

我使用这个通用的东西来查看模式中正在运行的内容，并用它来监视
回滚。它使用从网络上各个地方窃取的概念，包括问汤姆。

DECLARE

    SID                 INTEGER;
    LOCKWAIT            VARCHAR2(100);
    OSUSER              VARCHAR2(32);
    PROGRAM             VARCHAR2(48);
    LOGIN_TIME          DATE;
    SQL_TEXT            VARCHAR2(32760);
    estimated_rollback_megs     INTEGER;
    STATUS              VARCHAR2(8);

    OSUSERX VARCHAR2(100);
    PROGRAMX VARCHAR2(100);
    SIDX    INTEGER;
    STATUSX VARCHAR2(100);
    MACHINEX VARCHAR2(100);
    LOGON_TIMEX DATE;
    LOCKED_OBJECT_NAME VARCHAR2(100);

    processcount integer := 0;

    CURSOR raw_data IS
        WITH 
            sessionInfo AS
            (
                SELECT *
                FROM V$SESSION
                WHERE USERNAME = USER   -- Just data for my current schema
                ORDER BY SID
            )
            SELECT si.SID,
                   si.LOCKWAIT,
                   si.OSUSER,
                   si.PROGRAM,
                   si.LOGON_TIME,
                   si.STATUS,
                   (
                       SELECT ROUND(USED_UBLK*8/1024,1) 
                       FROM V$TRANSACTION,
                            sessionInfo
                       WHERE sessionInfo.TADDR = V$TRANSACTION.ADDR
                       AND sessionInfo.SID = si.SID

                    ) estimated_rollback_megs,

                    (
                       SELECT (MAX(DECODE(PIECE,  0,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  1,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  2,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  3,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  4,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  5,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  6,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  7,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  8,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE,  9,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 10,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 11,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 12,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 13,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 14,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 15,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 16,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 17,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 18,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 19,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 20,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 21,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 22,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 23,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 24,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 25,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 26,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 27,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 28,SQL_TEXT,NULL)) ||
                               MAX(DECODE(PIECE, 29,SQL_TEXT,NULL)))    
                        FROM V$SQLTEXT_WITH_NEWLINES
                        WHERE ADDRESS = SI.SQL_ADDRESS AND 
                        PIECE < 30
                    ) SQL_TEXT
            FROM sessionInfo  si;

            CURSOR LONG_OPERATIONS(SIDx number) IS
                SELECT VL.MESSAGE, 
                       VL.START_TIME, 
                       VL.LAST_UPDATE_TIME, 
                       VL.TIME_REMAINING
                FROM V$SESSION_LONGOPS VL,
                     V$SESSION V
                WHERE VL.USERNAME LIKE USER AND
                      V.SID = VL.SID AND
                      V.SERIAL# = VL.SERIAL# AND
                      V.SID = SIDx AND
                      VL.TIME_REMAINING > 0
                ORDER BY LAST_UPDATE_TIME DESC;

message         VARCHAR2(100);
startTime       DATE;
lastUpdateTime  DATE;
timeRemaining   VARCHAR2(100);


PROCEDURE print_sql_statement
(
    printThis IN VARCHAR2,
    Plen  IN NUMBER DEFAULT 132,
    Pwhsp IN VARCHAR2 DEFAULT  -- newline+space+tab+comma
             CHR(10) || CHR(32) || CHR(9) || ','
) 
IS
    NL CONSTANT VARCHAR2(1) := CHR(10);  -- newline character (OS-independent)
    SP CONSTANT VARCHAR2(1) := CHR(32);  -- space character
    TB CONSTANT VARCHAR2(1) := CHR(9);   -- tab character
    CM CONSTANT VARCHAR2(1) := ',';      -- comma
    substringStart INTEGER := 1;  -- start of string to print
    substringEnd  INTEGER;       -- end of substring to print
    endOfString  INTEGER := LENGTH(printThis);  -- end of string to print
    newLinePosition    INTEGER;       -- point where newline found
    stringLength       INTEGER := GREATEST(LEAST(Plen, 255), 10);  -- 10 <= len <= 255!
BEGIN

NULL;

END print_sql_statement;


BEGIN

    DBMS_OUTPUT.ENABLE(1000000);

    DBMS_OUTPUT.PUT_LINE('Monitor for ' || USER);

    OPEN raw_data;


    LOOP

        FETCH raw_data INTO
            SID,
            LOCKWAIT,
            OSUSER,
            PROGRAM,
            LOGIN_TIME,
            STATUS,
            estimated_rollback_megs,
            SQL_TEXT;

        EXIT WHEN raw_data%NOTFOUND;

        processcount := processcount + 1;

        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE(OSUSER || ' Owns Session ' || SID || ' STATUS ' || STATUS);
        DBMS_OUTPUT.PUT_LINE('Running ' || PROGRAM || ' Since ' || TO_CHAR(LOGIN_TIME,'MM/DD HH24:MI:SS'));

        OPEN LONG_OPERATIONS(SID);

        LOOP
            FETCH LONG_OPERATIONS INTO
                message,
                startTime,
                lastUpdateTime,
                timeRemaining;

            EXIT WHEN LONG_OPERATIONS%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE('    ' || message);
            DBMS_OUTPUT.PUT_LINE('    START: ' || TO_CHAR(startTime,'DD HH24:MI:SS')  || 
                                   ' UPDATE: ' || TO_CHAR(lastUpdateTime, 'DD HH24:MI:SS') || 
                                ' REMAINING: ' || timeRemaining);
            DBMS_OUTPUT.PUT_LINE('    ');

        END LOOP;

        CLOSE LONG_OPERATIONS;

        IF estimated_rollback_megs > 0  then
            DBMS_OUTPUT.PUT_LINE('Estimated megs of rollback ' || estimated_rollback_megs);
        END IF;

        IF LOCKWAIT IS NOT NULL THEN

            SELECT SID
            INTO SIDX
            FROM V$LOCK
            WHERE ID1 = 
            (
                SELECT ID1
                FROM V$LOCK
                WHERE KADDR = LOCKWAIT
            ) AND BLOCK = 1;

            DBMS_OUTPUT.PUT_LINE('The session is waiting on a lock held by session ' || SIDX);


            select  OBJECT_TYPE || ':' || OBJECT_NAME || ':' || SUBOBJECT_NAME THINGY
            INTO LOCKED_OBJECT_NAME
            from dba_objects
            where object_id =
            (
                select object_id
                from v$locked_object
                where session_id = SIDX
            );

            DBMS_OUTPUT.PUT_LINE('The locked object is ' || LOCKED_OBJECT_NAME);

            SELECT OSUSER,  PROGRAM,  SID,  STATUS,  MACHINE,  LOGON_TIME
            INTO   OSUSERX, PROGRAMX, SIDX, STATUSX, MACHINEX, LOGON_TIMEX
            FROM V$SESSION
            WHERE SID = SIDX;

            DBMS_OUTPUT.PUT_LINE(SIDX || ': ' || OSUSER || ' Using ' || PROGRAMX || ' STATUS ' || STATUSX);
            DBMS_OUTPUT.PUT_LINE(SIDX || ': LOGGED IN ON ' || MACHINEX || ' SINCE ' || 
            TO_CHAR(LOGON_TIMEX, 'MM/DD HH24:MI:SS'));

        END IF;

        IF SUBSTR(SQL_TEXT, 1, 16) <> 'WITH SESSIONINFO' THEN

            print_sql_statement(SQL_TEXT, 132);
            DBMS_OUTPUT.PUT_LINE('');
        ELSE
            NULL;
        END IF;

    END LOOP;

    CLOSE raw_data;

    DBMS_OUTPUT.PUT_LINE(processcount || ' sessions found');

END;