20160822Oracle 11g Temporary Tablespace

[20160822]Oracle 11g Temporary Tablespace.txt

1.11G改進了Temporary Tablespace管理,可以回收臨時表空間.

alter tablespace temp shrink space;
alter tablespace temp shrink space keep 10m;

--也可以單獨回收一個臨時表空間資料檔案.
alter tablespace temp shrink tempfile  '/u01/app/Oracle/oradata/test/temp01.dbf' keep 5m;

2.可以指定臨時表使用那個臨時表空間,這點對於一些應用比較有用.透過例子來說明:

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

--//家裡僅僅12c,應該也能說明問題.
--//預設已經建立了臨時表空間.

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
  'D:\APP\ORACLE\ORADATA\TEST\TEST01P\TEST01P_TEMP01.DBF' SIZE 388M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

--//可以發現UNIFORM SIZE=1M,這樣加入每個session都要使用一些小的臨時表,每個至少需要1M,大量的會話佔用許多臨時表空間.
--//我們可以建立UNIFORM SIZE=64K的臨時表空間,而建立的臨時表指定到這個臨時表空間,這樣空間就沒有這麼浪費了.

CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE
  'D:\APP\ORACLE\ORADATA\TEST\TEST01P\TEST01P_TEMP02.DBF' SIZE 100M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

SCOTT@test01p> SCOTT@test01p> create global temporary table temp_test(id number ,text varchar2(100)) on commit delete rows tablespace temp01;
Table created.

SCOTT@test01p> insert into temp_test values (1,'a');
1 row created.

SCOTT@test01p> @ spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
       242         25 6808        22          5 alter system kill session '242,25' immediate;

--不提交執行如下:
  SELECT s.username
        ,s.sid
        ,u.tablespace
        ,u.contents
        ,u.segtype
        ,ROUND (u.blocks * 8192 / 1024) KB
    FROM v$session s, v$sort_usage u
   WHERE s.saddr = u.session_addr AND u.contents = 'TEMPORARY'
ORDER BY KB DESC;

USERNAME                    SID TABLESPACE                     CONTENTS  SEGTYPE           KB
-------------------- ---------- ------------------------------ --------- --------- ----------
SCOTT                       242 TEMP01                         TEMPORARY DATA              64

--//這樣僅僅需要64K.
SCOTT@test01p> commit ;
Commit complete.

--如果不指定:
SCOTT@test01p> create global temporary table temp_testx(id number ,text varchar2(100)) on commit delete rows;
Table created.

SCOTT@test01p> insert into temp_testx values (1,'a');
1 row created.
USERNAME                    SID TABLESPACE                     CONTENTS  SEGTYPE           KB
-------------------- ---------- ------------------------------ --------- --------- ----------
SCOTT                       242 TEMP                           TEMPORARY DATA            1024

--這樣即使插入很少的資料也使用1M.

3.看看這兩種不同UNIFORM SIZE的是否可以建立臨時表空間組:

SCOTT@test01p> ALTER TABLESPACE TEMP TABLESPACE GROUP ttt;
Tablespace altered.

SCOTT@test01p> ALTER TABLESPACE TEMP01 TABLESPACE GROUP ttt;
Tablespace altered.

--取消.
SCOTT@test01p> ALTER TABLESPACE TEMP TABLESPACE GROUP '';
Tablespace altered.

SCOTT@test01p> ALTER TABLESPACE TEMP01 TABLESPACE GROUP '';
Tablespace altered.