0504關於drop表後select查詢仍有效的問題

[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 <=1e3;
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 <=1e3;
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 <=1e3;
Table created.

--session 1:

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

--可以如果段頭資訊被覆蓋,已經不是原來的物件了,就報錯ORA-08103: object no longer exists.