create table進階學習(二)_dba_free_temp_space_v$tempseg_usage_v$sort_segment

 在http://space.itpub.net/9240380/viewspace-752388一文中,主要測試了全域性臨時表的語法及維護相關知識;
既然全域性臨時表資料不持續性儲存在segment,哪麼到底它儲存在哪兒呢,經查閱官方手冊,源文如下:
   Allocation of Temporary Segments for Temporary Tables and Indexes
 
 --oracle僅為當前會話的全域性臨時表自temporary tablespace分配segment
Oracle Database can also allocate temporary segments for temporary tables and their indexes. Temporary tables hold data that exists only
for the duration of a transaction or session. Each session accesses only the extents allocated for the session and cannot access extents allocated for other sessions.
 
Oracle Database allocates segments for a temporary table when the first INSERT into that table occurs. The insertion can occur explicitly or because of CREATE T
ABLE AS SELECT. The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes,
and allocates any LOB segments.
 
Segments for a temporary table are allocated in a temporary tablespace of the current user. Assume that the temporary tablespace assigned to
user1 is temp1 and the temporary tablespace assigned to user2 is temp2. In this case, user1 stores temporary data in the temp1 segments,
while user2 stores temporary data in the temp2 segments.

--測試建立全域性臨時表時臨時表空間的佔用情況
 涉及到如下幾個方面的內容:
 1,臨時表空間儲存什麼內容:
 2,臨時表空間的管理和維護
 
 先說臨時表空間儲存什麼內容
 
    --中間狀態的排序結果,例如建索引
    Intermediate sort results
 
    --建立全域性臨時表和其上的索引,即我們要討論的問題
    Temporary tables and temporary indexes
   
    --臨時lob
    Temporary LOBs
 
     --臨時b樹
    Temporary B-trees

  再來說臨時表空間的管理和維護,oracle提供幾個檢視和字典
 
  ----v$sort_segment記錄臨時表空間排序段的分配與回收資訊
  You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view.
 
  --- v$tempseg_usage記錄臨時段中當前執行排序操作的相關資訊
The V$TEMPSEG_USAGE view identifies the current sort users in those segments.
 
 ---如下簡述了排序工作機制,排序一開始,則使用臨時表空間的臨時段,如果使用完畢,不會釋放它,而是標記為free,便於下次重用;只有關閉庫才會釋放臨時表空間的臨時段空間
When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just

-----dba_temp_free_space記錄總的分配和空閒的臨時段資訊
marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace.

--當然臨時段可以收縮,請參閱官方手冊
See "Viewing Space Usage for Temporary Tablespaces" for more information. You can manually shrink a locally managed temporary tablespace that
has a large amount of unused space. See "Shrinking a Locally Managed Temporary Tablespace" for details.

---如下開始測試:

---沿用上文全域性臨時表t_global_temp
SQL> insert into t_global_temp select level,level+2 from dual connect by level<3
e5;

299999 rows created.

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        63

---開啟另一會話檢視

sysdba>select tablespace_name,segment_file from v$sort_segment;

TABLESPACE_NAME                                                SEGMENT_FILE
-------------------------------------------------------------- ------------
TEMP                                                                      0

---v$tempseg_usage請參考:
sysdba>select username,user from v$tempseg_usage;

USERNAME
------------------------------------------------------------
USER
------------------------------------------------------------
SCOTT
SYS

SCOTT
SYS

sysdba>select session_num,sql_id,tablespace from v$tempseg_usage;

SESSION_NUM SQL_ID
----------- --------------------------
TABLESPACE
--------------------------------------------------------------
         13 44rj7z9m5z42y
TEMP

         13 44rj7z9m5z42y
TEMP

sysdba>select sql_text from v$sql where sql_id='44rj7z9m5z42y';

SQL_TEXT
-----------------------------------------------------------------------------

insert into t_global_temp select level,level+2 from dual connect by level<3e5

---交替執行,如下列值在遞增
--各列含義參閱:官方手冊
sysdba>select segfile#,segblk#,extents,blocks from v$tempseg_usage;

  SEGFILE#    SEGBLK#    EXTENTS     BLOCKS
---------- ---------- ---------- ----------
       201      70272          5        640
       201      70656          5        640

sysdba>/

  SEGFILE#    SEGBLK#    EXTENTS     BLOCKS
---------- ---------- ---------- ----------
       201      70272         15       1920
       201      70656         19       2432

sysdba>/

  SEGFILE#    SEGBLK#    EXTENTS     BLOCKS
---------- ---------- ---------- ----------
       201      70272         15       1920
       201      70656         19       2432
      
--如你退出操作臨時排序段的會話,排序段會自動釋放
sysdba>select segfile#,segblk#,extents,blocks from v$tempseg_usage;

no rows selected      

--- v$sort_segment診斷排序段相當有用,請參考:http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3041.htm#i1416861     
sysdba>desc v$sort_segment;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 TABLESPACE_NAME                                    VARCHAR2(31)
 SEGMENT_FILE                                       NUMBER
 SEGMENT_BLOCK                                      NUMBER
 EXTENT_SIZE                                        NUMBER
 CURRENT_USERS                                      NUMBER  --當前使用排序段使用者數
 TOTAL_EXTENTS                                      NUMBER  --總排序段的區數
 TOTAL_BLOCKS                                       NUMBER
 USED_EXTENTS                                       NUMBER  --已使用的排序段區數
 USED_BLOCKS                                        NUMBER
 FREE_EXTENTS                                       NUMBER  ---未使用的排序段區數
 FREE_BLOCKS                                        NUMBER
 ADDED_EXTENTS                                      NUMBER  --新增的排序段區數
 EXTENT_HITS                                        NUMBER
 FREED_EXTENTS                                      NUMBER  --釋放的排序段區數
 FREE_REQUESTS                                      NUMBER  --請求空閒排序段的個數
 MAX_SIZE                                           NUMBER
 MAX_BLOCKS                                         NUMBER
 MAX_USED_SIZE                                      NUMBER
 MAX_USED_BLOCKS                                    NUMBER
 MAX_SORT_SIZE                                      NUMBER
 MAX_SORT_BLOCKS                                    NUMBER
 RELATIVE_FNO                                       NUMBER

sysdba>select tablespace_name from v$sort_segment;

TABLESPACE_NAME
--------------------------------------------------------------
TEMP

sysdba>select segment_file from v$sort_segment;

SEGMENT_FILE
------------
           0

sysdba>select segment_block from v$sort_segment;

SEGMENT_BLOCK
-------------
            0

sysdba>select extent_size from v$sort_segment;

EXTENT_SIZE
-----------
        128

sysdba>select current_users from v$sort_segment;

CURRENT_USERS
-------------
            2

sysdba>select total_extents from v$sort_segment;

TOTAL_EXTENTS
-------------
          570

sysdba>select used_extents from v$sort_segment;

USED_EXTENTS
------------
          34

sysdba>select free_extents from v$sort_segment;

FREE_EXTENTS
------------
         536

sysdba>select freed_extents from v$sort_segment;

FREED_EXTENTS
-------------
            0

sysdba>/

FREED_EXTENTS
-------------
            0

sysdba>

---dba_temp_free_space請參閱
--它記錄:DBA_TEMP_FREE_SPACE displays temporary space usage information at tablespace level.