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，这也是临时表的数据不会相互干扰的处理方式