[20111228]9i?临时表and truncate table.txt
这个问题以前遇到，今天再一次遭遇，把它写下来。
1.测试环境:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> CREATE GLOBAL TEMPORARY TABLE T ( id number, name varchar2(10)) ON COMMIT PRESERVE ROWS;
Table created.
2.插入数据:
SQL> insert into t select rownum id ,'test' from dual connect by level 10 rows created.
SQL> commit ;
SQL> select count(*) from t;
COUNT(*)
----------
10
SQL> truncate table t REUSE STORAGE ;
Table truncated.
SQL> select count(*) from t;
COUNT(*)
----------
10
--并没有删除记录。
3.如果这样，正常:
SQL> truncate table t ;
Table truncated.
SQL> select count(*) from t;
COUNT(*)
----------
0
4.在11GR2下结果如何呢?
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> CREATE GLOBAL TEMPORARY TABLE T ( id number, name varchar2(10)) ON COMMIT PRESERVE ROWS;
SQL> insert into t select rownum id ,'test' from dual connect by level SQL> commit ;
SQL> select count(*) from t ;
COUNT(*)
----------
10
SQL> truncate table t REUSE STORAGE ;
truncate table t REUSE STORAGE
*
ERROR at line 1:
ORA-14461: cannot REUSE STORAGE on a temporary table TRUNCATE
SQL> truncate table t drop STORAGE ;
Table truncated.
SQL> select count(*) from t ;
COUNT(*)
----------
0
SQL> insert into t select rownum id ,'test' from dual connect by level 10 rows created.
SQL> commit;
Commit complete.
SQL> truncate table t;
Table truncated.
SQL> select count(*) from t ;
COUNT(*)
----------
0