学步园

http://hi.baidu.com/edeed/item/ae7b3bb5e5b90b75254b099d

Temp Table 的特点：

（1） 多用户操作的独立性：对于使用同一张临时表的不同用户，ORACLE都会分配一个独立的 Temp Segment，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性；
（2） 数据的临时性：既然是临时表，顾名思义，存放在该表中的数据是临时性的。ORACLE根据你创建临时表时指定的参数（On Commit Delete Rows / On Commit Preserve Rows），自动将数据TRUNCATE掉。

Temp Table 数据的时效性：

（1）On Commit Delete Rows： 数据在 Transaction 期间有效，一旦COMMIT后，数据就被自动 TRUNCATE 掉了（当前session）；

（2）On Commit Preserve Rows ：数据在 Session 期间有效，一旦关闭了Session 或 Log Off 后，数据就被 ORACLE 自动 Truncate 掉（当前session）。

备注：“多用户独立性”  无论是commit还是exit，清除的数据只是当前session中修改的数据。同一个临时表Temp_A，在不同的session中，seesion_A中insert50条，Session_B中insert100条，在session_A中查到的是50条，在Session_B中查的的是100条；Session_A中commit或exit只会清空Session_A中的50条记录而不会影响Session_B；同样Session_B中也不会影响Session_A；同样session_C中是查不到数据的。

CREATE GLOBAL TEMPORARY TABLE tt_user_objects1
   ON COMMIT PRESERVE ROWS
   AS SELECT * FROM user_objects;

CREATE GLOBAL TEMPORARY TABLE tt_user_objects2
   ON COMMIT DELETE ROWS
   AS SELECT * FROM user_objects;

（3.）向临时表中插入数据产生的redolog量要比向堆表中插入数据产生的redolog量要少。

SQL> insert into tt_user_objects12 SELECT * FROM user_objects;

1452 rows created.

Statistics
----------------------------------------------------------
          0  recursive calls
        198  db block gets
        825  consistent gets
          0  physical reads
     159928  redo size
        830  bytes sent via SQL*Net to client
        749  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1452  rows processed

SQL> insert into tt_user_objects1 SELECT * FROM user_objects;

1452 rows created.

Statistics
----------------------------------------------------------
          0  recursive calls
        106  db block gets
        817  consistent gets
          0  physical reads
       7984  redo size
        830  bytes sent via SQL*Net to client
        748  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1452  rows processed

如果报错

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view

SP2-0612: Error generating AUTOTRACE EXPLAIN report

需要权限

grant select any dictionary to ydqxn;

问题

1.为什么向临时表插入数据产生的redo size比堆表小？

2.向临时表插入数据不产生redo log吗？

“对临时表的操作可以回滚，所以会有undo;
所有undo都受到redo的保护，就是说假设此时数据库崩溃了，下次启动会利用redo把这些undo再次还原出来，然后利用这些undo进行反操作，撤销上次那个崩溃的事务。这些undo里面可能有普通表的，也有临时表的，反正redo都会把它们恢复出来。”

http://www.itpub.net/thread-1301065-1-1.html

SQL> select count(*) from TT_USER_OBJECTS1;
 
  COUNT(*)
----------
         0
SQL> select * from dba_objects where object_name ='TT_USER_OBJECTS1';
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
YDQXN                          TT_USER_OBJECTS1                                                                                                     88575                TABLE               2012/12/11  2012/12/11 12 2012-12-11:12:37:55 VALID   Y         N         N
SQL> select * from dba_segments where segment_name ='TT_USER_OBJECTS1';
 
OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -----------
SQL> select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  2    from v$sort_usage sort, v$session sess, v$sql sql
  3   where sort.SESSION_ADDR = sess.SADDR
  4     and sql.ADDRESS = sess.SQL_ADDRESS
  5   order by blocks desc
SQL>  select tablespace_name,total_blocks,used_blocks,free_blocks from v$sort_segment;
 
TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                  214272           0      214272
YDQXTEMP                              424576         768      423808
 
SQL> --insert前的零时表空间大小
SQL> 
SQL> insert into tt_user_objects1 SELECT * FROM user_objects;
 
1452 rows inserted
SQL> select count(*) from TT_USER_OBJECTS1;
 
  COUNT(*)
----------
      1452
SQL> select * from dba_objects where object_name ='TT_USER_OBJECTS1';
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
YDQXN                          TT_USER_OBJECTS1                                                                                                     88575                TABLE               2012/12/11  2012/12/11 12 2012-12-11:12:37:55 VALID   Y         N         N
SQL> select * from dba_segments where segment_name ='TT_USER_OBJECTS1';
 
OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -----------
SQL> select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  2    from v$sort_usage sort, v$session sess, v$sql sql
  3   where sort.SESSION_ADDR = sess.SADDR
  4     and sql.ADDRESS = sess.SQL_ADDRESS
  5   order by blocks desc
SQL>  select tablespace_name,total_blocks,used_blocks,free_blocks from v$sort_segment;
 
TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                  214272           0      214272
YDQXTEMP                              424576         896      423680
--insert后的零时表空间大小SQL
> commit; 
Commit complete 
SQL> 
SQL> select count(*) from TT_USER_OBJECTS1;
 COUNT(*)
---------- 
0
SQL> select * from dba_objects where object_name ='TT_USER_OBJECTS1'; 
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------YDQXN TT_USER_OBJECTS1 88575 TABLE 2012/12/11 2012/12/11 12 2012-12-11:12:37:55 VALID Y N NSQL> select * from dba_segments where segment_name ='TT_USER_OBJECTS1'; OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -----------SQL> select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text 2 from v$sort_usage sort, v$session sess, v$sql sql 3 where sort.SESSION_ADDR = sess.SADDR 4 and sql.ADDRESS = sess.SQL_ADDRESS 5 order by blocks descSQL> select tablespace_name,total_blocks,used_blocks,free_blocks from v$sort_segment; TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS------------------------------- ------------ ----------- -----------TEMP 214272 0 214272YDQXTEMP 424576 768 423808 SQL> 
--insert后commit的零时表空间大小