This discussion is archived
4 Replies Latest reply: Nov 13, 2012 6:54 PM by LiuMaclean(刘相兵) RSS

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

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

    楼主贴出你的实验情况
  • 2. Re: SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?
    958083 Newbie
    Currently Being Moderated
    SELECT 不是不加锁的吗?
  • 3. Re: SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?
    anbob Newbie
    Currently Being Moderated
    以前记的老白也提过,如果有大数据量的fetch 正在进行中被其它进程中断,select process返回异常会存在延时
  • 4. Re: SELECT 运行期间DROP TABLE,查询依然在继续运行,其数据来源于何方?
    LiuMaclean(刘相兵) Expert
    Currently Being Moderated
    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错误

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points