不一样的天空w

暂无签名

  • 博客访问: 98593
  • 博文数量: 544
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 15:29
  • 认证徽章:
个人简介

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(544)

文章存档

2017年(383)

2016年(159)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
临时表 2017-09-01 16:15:35

分类: Oracle

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>
阅读(2968) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册