临时表分为事务级临时表和会话级临时表
---查表空间
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博客

临时表

最新文章