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.