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>