oracle global temporary table全局临时表_测试及v$tempseg_usage

SQL> create global temporary table test_global_temp as select * from t_only where rownum<1000;--采用as创建全局临时表，全局临时表

虽然建表成功，但表中没有数据的

Table created.
SQL> select table_name,temporary,duration from user_tables where table_name=upper('test_global_temp');--默认创建的全局临时表是基

于事务的(就是发布rollback or commit表中就会清除记录）,请注意duration列

TABLE_NAME                     T DURATION
------------------------------ - ---------------
TEST_GLOBAL_TEMP               Y SYS$TRANSACTION

SQL> select count(*) from test_global_temp;
SQL> insert into test_global_temp  select * from t_only where rownum<100000;--建好临时表可以用insert into全局临时表 select 来导入

插入数据

99999 rows created.

SQL> /

99999 rows created.

SQL> select count(*) from test_global_temp; --批量插入2次数据到全局临时表

  COUNT(*)
----------
    199998

SQL> select segment_name,bytes/1024/1024 from user_segments where segment_name='T_TEMP_SIZE';--t_temp_size为全局临时表的备份表，

为了测试全局临时表占用数据的大小

SEGMENT_NAME                                                                      BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
T_TEMP_SIZE                                                                                    12

 

SQL> select * from v$tempseg_usage;--请注意这里的blocks，这是为临时表分配的block个数

SQL> select * from v$tempseg_usage; ##segtype表示临时segment的类型，有sort,hash,data,index,lob_data,lob_index,还有

session_addr,sqladdr,sql_id,sqlhash,可以由此提取v$session或v$sql，从而抽取对应session及sql

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
SCOTT                          SCOTT                          00000000677D9D30
         17 0000000067127648 1029988163 9babjv8yq8ru3
TEMP                            TEMPORARY DATA             201       8457
        11       1408          1

SQL> select 1408*8/1024 from dual;---这里根据以上v$tempseg_usage的blocks换算出来也是近12m

1408*8/1024
-----------
         11

目的：为了测试临时表占用空间的大小

 

 

 

#####学习全局临时表两种类型（基于会话和基于事务）

SQL> create global temporary table test_global_temp(a int,b int) on commit delete rows;--基于事务

Table created.

SQL> select table_name,duration,temporary from user_tables where table_name='TEST_GLOBAL_TEMP';

TABLE_NAME                     DURATION        T
------------------------------ --------------- -
TEST_GLOBAL_TEMP               SYS$TRANSACTION Y

SQL> insert into test_global_temp values(1,1);

1 row created.

SQL> select count(*) from test_global_temp;

  COUNT(*)
----------
         1

SQL> commit;--一提交

Commit complete.

SQL> select count(*) from test_global_temp;--没有记录了

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

SQL>
SQL>
SQL> drop table test_global_temp purge;

Table dropped.

SQL> create global temporary table test_global_temp(a int,b int) on commit preserve rows;-创建基于会话的临时表

Table created.

SQL> select table_name,duration,temporary from user_tables where table_name='TEST_GLOBAL_TEMP';

TABLE_NAME                     DURATION        T
------------------------------ --------------- -
TEST_GLOBAL_TEMP               SYS$SESSION      ---基于会话的   Y

SQL> insert into test_global_temp values(1,1);

1 row created.

SQL> select count(*) from test_global_temp;--未提交前有数据

  COUNT(*)
----------
         1

SQL> commit;---提交

Commit complete.

SQL> select count(*) from test_global_temp;--还有数据

  COUNT(*)
----------
         1

SQL> conn /as sysdba
Connected.
SQL> conn scott/system  --退出会话，再新建一个会话
Connected.
SQL> select count(*) from test_global_temp;--没有数据了

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

SQL>

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

转载于:http://blog.itpub.net/9240380/viewspace-672050/