where temp segment of temp table are created

Temporary segment

Temporary segments are created by Oracle when a SQL statement needs a temporary database area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use.

Temporary segment Temporary segments are created by Oracle when a SQL statement needs a temporary database area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use.

这句话的意思是说临时表的段 the temporary segment 分配是在a temporary database area，这个临时数据库区域到底在哪呢，

SQL> select session_addr,blocks from v$sort_usage;

no rows selected

SQL> CREATE GLOBAL TEMPORARY TABLE temp_test(col1 number)  ON COMMIT DELETE ROWS;

Table created.

SQL> insert into temp_test values(10);

1 row created.

SQL> select session_addr,blocks from v$sort_usage;

SESSION_ADDR         BLOCKS
---------------- ----------
00000003E507B8F0        128

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

       SID
----------
       151

SQL> select saddr from v$session where sid=151;

SADDR
----------------
00000003E507B8F0

SQL> commit
  2  /

Commit complete.

SQL> select session_addr,blocks from v$sort_usage;

no rows selected
看来这个 temporary database area就是临时表空间

 

另开一个session，

SQL> insert into temp_test values(10);

1 row created.

SQL> select dbms_rowid.rowid_object(rowid) data_object_id#,
  2         dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3         dbms_rowid.rowid_block_number(rowid) block#,
  4         dbms_rowid.rowid_row_number(rowid) row# from temp_test;

DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
        4194697          1        394          0
返回到第一个窗口

SQL> select dbms_rowid.rowid_object(rowid) data_object_id#,
  2         dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3         dbms_rowid.rowid_block_number(rowid) block#,
  4         dbms_rowid.rowid_row_number(rowid) row# from temp_test;

DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
        4194569          1        266          0

分配了不同的extent，所以也会在sga中有不同的bh，这也是临时表的数据不会相互干扰的处理方式

 

Use the following guidelines to specify DEFAULT STORAGE:

    Set INITIAL=NEXT.Since a process always writes data equal to
    SORT_AREA_SIZE to a temporary segment, a good value for the extent
    size is (n*s + b)

    where:  n is a positive integer
            s is the value of SORT_AREA_SIZE initialization parameter
            b is the value of DB_BLOCK_SIZE initialization parameter

    Using this value optimizes temporary segment usage by allowing
    sufficient space for a header block and multiple sort run data to be
    stored in each extent.

    Specify a PCTINCREASE of zero to ensure that all extents are of the
    same size.

    The MAXEXTENTS parameter only affects a temporary segment if the tablespace
    is a PERMANENT tablespace.

    Proper calculation of the Default Storage clause parameters will increase
    performance and maximize use of storage.

 

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

转载于:http://blog.itpub.net/16158219/viewspace-630677/