[20150504]关于drop表后select查询仍有效的问题.txt

--这个是别人问的问题,我自己也做一个测试:

1.建立测试环境:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t as select rownum id , 'test' name,lpad('a',100,'a') pad from dual connect by level Table created.

CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)
RETURN NUMBER AS
BEGIN
  sys.dbms_lock.sleep(seconds);
  RETURN seconds;
END;
/

exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

2.这样如果执行如下:
--session 1:
SCOTT@test> set array 100
SCOTT@test> select t.id ,sleep(0.02) from t;
--基本需要20秒多,总共1000条记录.

--在另外的会话做drop操作.
--session 2:
SCOTT@test> drop table t purge ;
Table dropped.

--如果看session 1,可以发现一直在输出,直到结束.你可以认为表虽然删除了(注意我加了purge参数),但是段头信息还在,通过段头依旧可以定位数据块.

2.再重复测试:

create table t as select rownum id , 'test' name,lpad('a',100,'a') pad from dual connect by level exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)

--session 1:
SCOTT@test> set array 100
SCOTT@test> select t.id ,sleep(0.03) from t;

--session 2:
SCOTT@test> drop table t purge ;
Table dropped.

SCOTT@test> create table tx as select rownum id , 'test' name,lpad('b',100,'b') pad from dual connect by level Table created.

--session 1:

         799          .03
         800          .03
ERROR:
ORA-08103: object no longer exists
800 rows selected.

--可以如果段头信息被覆盖,已经不是原来的对象了,就报错ORA-08103: object no longer exists.