[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.
