临时表

临时表分为事务级临时表和会话级临时表
---查表空间
SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 USERS                          YES NO  YES
         6 EXAMPLE                        YES NO  YES
         3 TEMP                           NO  NO  YES

6 rows selected.
---查的数据文件
SQL> select name from v$datafile;         

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ENMOEDU/system01.dbf
/u01/app/oracle/oradata/ENMOEDU/sysaux01.dbf
/u01/app/oracle/oradata/ENMOEDU/undotbs01.dbf
/u01/app/oracle/oradata/ENMOEDU/users01.dbf
/u01/app/oracle/oradata/ENMOEDU/example01.dbf
---临时表添加
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ENMOEDU/temp01.dbf' ;

Tablespace altered.



---会话级临时表
--创建会话临时表
SQL>create global temporary table tmpp_dept on commit preserve rows as select * from dept;

Table created.

SQL>
 ----查询数据
 SQL>  select count(*) from tmpp_dept;

  COUNT(*)
----------
         4

SQL>
----提交 、还有数据
SQL> commit
  2  ;

Commit complete.

SQL> select count(*) from tmpp_dept;

  COUNT(*)
----------
         4

SQL>
-----退出在登录、数据在查询（因为是会话级)
SQL> select count(*) from tmpp_dept;

  COUNT(*)
----------
         0

SQL>

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

转载于:http://blog.itpub.net/31399171/viewspace-2130305/