转载

临时表

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>

正文到此结束