ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20111228]9i?临时表and truncate table.txt

[20111228]9i?临时表and truncate table.txt

原创 Linux操作系统 作者:lfree 时间:2011-12-28 15:15:10 0 删除 编辑
[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;
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 <=10;
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;
10 rows created.
SQL> commit;
Commit complete.

SQL> truncate table t;
Table truncated.

SQL> select count(*) from t ;
  COUNT(*)
----------
         0

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-714085/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2351
  • 访问量
    6092322