4 Replies Latest reply: Nov 13, 2012 8:54 PM by LiuMaclean(刘相兵) RSS

    SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?

    958083
      T1表200W记录,select * from t1; 马上DROP TABLE T1;
      查询依然在运行中,其数据从何而来?
      求解答!
        • 1. Re: SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?
          不了峰
          不可能吧,,select 时会对表加一个共享锁,在读的时候,表不可能被删除

          楼主贴出你的实验情况
          • 3. Re: SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?
            anbob
            以前记的老白也提过,如果有大数据量的fetch 正在进行中被其它进程中断,select process返回异常会存在延时
            • 4. Re: SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?
              LiuMaclean(刘相兵)
              SQL> create tablespace smallone datafile size 10M;
              
              Tablespace created.
              
              
              
              SQL> create table tvb_2 tablespace smallone as select * from dba_objects;
              
              Table created.
              
              SQL> set serveroutput on
              SQL> set linesize 200 pagesize 1400
              SQL> set time on
              10:38:19 SQL> declare
              10:38:19   2    l_audsid number;
              10:38:19   3    l_sid    number;
              10:38:19   4    l_serial number;
              10:38:19   5    l_module varchar2(128);
              10:38:19   6    l_pid    number;
              10:38:19   7    l_spid   number;
              10:38:19   8    l_trace  varchar2(2000);
              10:38:19   9    l_user   varchar2(128);
              10:38:19  10  begin
              10:38:19  11    DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'Maclean Liu OppO',
              10:38:19  12                                     action_name => 'ACTIVE');
              10:38:19  13    select audsid, sid, SERIAL#, module,username
              10:38:19  14      into l_audsid, l_sid, l_serial, l_module,l_user
              10:38:19  15      from v$session
              10:38:19  16     where sid = (select distinct sid from v$mystat);
              10:38:19  17    select pid, spid
              10:38:19  18      into l_pid, l_spid
              10:38:19  19      from v$process
              10:38:19  20     where addr = (select paddr
              10:38:19  21                     from v$session
              10:38:19  22                    where sid = l_sid
              10:38:19  23                      and serial# = l_serial);
              10:38:19  24    SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
              10:38:19  25           p.spid || '.trc'
              10:38:19  26      into l_trace
              10:38:19  27      FROM (SELECT p.spid
              10:38:19  28              FROM v$mystat m, v$session s, v$process p
              10:38:19  29             WHERE m.statistic# = 1
              10:38:19  30               AND s.SID = m.SID
              10:38:19  31               AND p.addr = s.paddr) p,
              10:38:19  32           (SELECT t.INSTANCE
              10:38:19  33              FROM v$thread t, v$parameter v
              10:38:19  34             WHERE v.NAME = 'thread'
              10:38:19  35               AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
              10:38:19  36           (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
              10:38:19  37
              10:38:19  38    dbms_output.enable(9999999);
              10:38:19  39    dbms_output.put_line('===============================================');
              10:38:19  40    dbms_output.put_line(' USERNAME=' || l_user);
              10:38:19  41    dbms_output.put_line(' SESSION ID=' || l_sid || '  SERIAL#=' || l_serial);
              10:38:19  42    dbms_output.put_line(' AUDSID=' || l_audsid || '      MODULE#=' ||
              10:38:19  43                         l_module);
              10:38:19  44    dbms_output.put_line(' PID=' || l_pid || '          SPID#=' || l_spid);
              10:38:19  45    dbms_output.put_line(' TRACE_FILE_LOCATION=' || l_trace);
              10:38:19  46    dbms_output.put_line('===============================================');
              10:38:19  47    commit;
              10:38:19  48  end;
              10:38:19  49  /
              ===============================================
              USERNAME=SYS
              SESSION ID=585  SERIAL#=1315
              AUDSID=4294967295      MODULE#=Maclean Liu OppO
              PID=39          SPID#=8516
              TRACE_FILE_LOCATION=D:\app\ML\diag\rdbms\testem\testem\trace/testem_ora_8516.trc
              ===============================================
              
              PL/SQL procedure successfully completed.
              
              
              10:38:20 SQL> select * from tvb_2;
              
              
              
              
              
              
              
              
              
              
              session B:
              
              SQL> oradebug setorapid 39
              Oracle pid: 39, Windows thread id: 8516, image: ORACLE.EXE (SHAD)
              SQL> oradebug suspend
              Statement processed.
              
              
              用 suspend 把正在select TVB的 process 中断
              
              
              session C:
              
              
              SQL> drop table tvb_2 purge;
              
              Table dropped.
              
              SQL> create table tvb_3 tablespace smallone as select * from dba_objects;
              
              Table created.
              
              
              SQL>  insert into tvb_3  select * from tvb_3 where rownum<=1000;
              
              1000 rows created.
              
              SQL>  insert into tvb_3  select * from tvb_3 where rownum<=1000;
              
              1000 rows created.
              
              SQL>  insert into tvb_3  select * from tvb_3 where rownum<=1000;
              
              1000 rows created.
              
              SQL>  insert into tvb_3  select * from tvb_3 where rownum<=1000;
               insert into tvb_3  select * from tvb_3 where rownum<=1000
              *
              ERROR at line 1:
              ORA-01653: unable to extend table SYS.TVB_3 by 128 in tablespace SMALLONE
              
              
              SQL> commit;
              
              Commit complete.
              
              SQL> alter system flush buffer_cache;
              
              System altered.
              
              SQL> /
              
              System altered.
              
              
              
              
              
              此时session B resume :
              
              SQL> oradebug resume
              Statement processed.
              
              
              
              session A:
              
              ERROR:
              ORA-08103: object no longer exists
              
              
              
              4560 rows selected.
              如以上演示 SELECT 运行期间DROP TABLE,查询依然在继续运行 , 其数据来源于 源表数据, 若源表数据在SELECT 期间被覆盖 则会报ORA-08103: object no longer exists错误