[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.