ITPub博客

临时表

原创 作者:不一样的天空w 时间:2017-09-01 16:15:35 0 删除 编辑
1.创建会话级临时表:on commit preserve rows

SQL> create global temporary  table temp_table_test on commit preserve rows as select * from dba_tables where rownum=0 ;

Table created.

SQL> select * from v$tempseg_usage;

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943D1DF8
        189 00000000883CC3E0  531484294 3pm8w2cguvmn6
TEMP02                          TEMPORARY DATA             202        256
         1        128          1

此sql:3pm8w2cguvmn6 是假的,见下面
         
--插入
SQL> insert into temp_table_test select * from dba_tables;

2836 rows created.

---查询临时表使用
SQL> select * from v$tempseg_usage;

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943D1DF8
        189 00000000883CC3E0  531484294 3pm8w2cguvmn6
TEMP02                          TEMPORARY DATA             202        256
         1        128          1


USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943AA298
        409 000000008CFB6D68 3925001498 b5cx7xgnz5j8u
TEMP02                          TEMPORARY DATA             202        384
         1        128          1

多了一个sql:b5cx7xgnz5j8u

--提交
SQL> commit;

Commit complete.

--查询临时表空间:
SQL>  select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='TEMP_TABLE_TEST';

TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
TEMP_TABLE_TEST                                               VALID

--退出会话:
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@slient ~]$

--开启一个新会话:
[oracle@slient ~]$  sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 1 15:59:32 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--查询临时表的数据(已经没有了,因为是会话级别,退出就truncate了)
SQL> select count(*) from TEMP_TABLE_TEST;

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

--查询
SQL> select * from v$tempseg_usage;

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943D1DF8
        189 00000000883CC3E0  531484294 3pm8w2cguvmn6
TEMP02                          TEMPORARY DATA             202        256
         1        128          1
此SQL:3pm8w2cguvmn6还在,假的

--查询临时表对应表空间:
SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='TEMP_TABLE_TEST';

TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
TEMP_TABLE_TEST                                               VALID


2.创建事物级临时表:On Commit Delete Rows

SQL>  create global temporary  table temp on commit delete rows as select * from dba_objects where 1=2;

Table created.

SQL>  select * from v$tempseg_usage;

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943D1DF8
        189 00000000883CC3E0  531484294 3pm8w2cguvmn6
TEMP02                          TEMPORARY DATA             202        256
         1        128          1


SQL> insert into temp select * from dba_objects;

86446 rows created.

SQL>
SQL> select * from v$tempseg_usage;

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943D1DF8
        189 00000000883CC3E0  531484294 3pm8w2cguvmn6
TEMP02                          TEMPORARY DATA             202        256
         1        128          1


USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943AA298
        411 000000008CDEAFE0 3250818709 02yb6fb0w73np
TEMP02                          TEMPORARY DATA             202        384
        10       1280          1
同理、、、、、、、、、、、

SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='TEMP';

TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
TEMP_TABLE_TEST                                               VALID

--提交
SQL> commit;

Commit complete.


--查询因为事物级临时表commit后就truncate了
SQL> select * from temp;

no rows selected

SQL> select * from v$tempseg_usage;

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SYS                            SYS                            00000000943D1DF8
        189 00000000883CC3E0  531484294 3pm8w2cguvmn6
TEMP02                          TEMPORARY DATA             202        256
         1        128          1


SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='TEMP';           

TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
TEMP                                                          VALID

SQL>
请登录后发表评论 登录
全部评论

注册时间:2016-09-22

  • 博文量
    720
  • 访问量
    281387