ORACLE 体系 - 12(下)（oracle体系结构思维导图）

12.5 临时表空间

12.5.1用途:

用于缓存排序的数据（中间结果），可以建立多个临时表空间，但默认的临时表空间只能有一个且不能offline和drop，temp表空间是nologing的（不记日志）。

SQL> select file_id,file_name,tablespace_name from dba_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
1 /u01/oradata/prod/temp01.dbf TEMP

SQL> col name for a60;
SQL> select file#,name ,bytes/1024/1024 from v$tempfile;
FILE# NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
1 /u01/oradata/prod/temp01.dbf 100

12.5.2 基本操作

1）建立临时表空间temp2，增加或删除tempfile。

SQL> create temporary tablespace temp2 tempfile '/u01/oradata/prod/temp02.dbf' size 10m;
SQL> alter tablespace temp2 add tempfile '/u01/oradata/prod/temp03.dbf' size 5m;
SQL> select file_id,file_name,tablespace_name from dba_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------------------------------------- -----------------------
1 /u01/oradata/prod/temp01.dbf TEMP
2 /u01/oradata/prod/temp02.dbf TEMP2
3 /u01/oradata/prod/temp03.dbf TEMP2

将temp2里删掉一个tempfile。

SQL> alter tablespace temp2 drop tempfile '/u01/oradata/prod/temp03.dbf';
SQL> select file_id,file_name,tablespace_name from dba_temp_files;

2） 查看默认的临时表空间

SQL> select * from database_properties where rownum<=5;

3） 指定用户使用临时表空间

SQL> alter user scott temporary tablespace temp2;

4） 切换默认的临时表空间

SQL> alter database default temporary tablespace temp2;

12.5.3 临时表空间组 (10g新特性）

在很多情况下，会有多个session 使用同一个用户名去访问Oracle，而临时表空间又是基于用户的，那么可以建立一个临时表空间组，组中由若干临时表空间成员构成，从而可以提高单个用户多个会话使用临时表空间的效率。

1）临时表空间组无法显式创建，组是通过第一个临时表空间分配时自动创建。

SQL> alter tablespace temp1 tablespace group tmpgrp;
SQL> alter tablespace temp2 tablespace group tmpgrp;
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMPGRP TEMP1
TMPGRP TEMP2

2）将临时表空间组设成默认临时表空间，实现负载均衡。

SQL> alter database default temporary tablespace tmpgrp;

3）要移除表空间组时，该组不能是缺省的临时表空间。

SQL> alter database default temporary tablespace temp;
SQL> alter tablespace temp1 tablespace group '';
SQL> alter tablespace temp2 tablespace group '';

4）当组内所有临时表空间被移除时，组也被自动删除。

SQL> select * from dba_tablespace_groups;
no rows selected
SQL> drop tablespace temp2 including contents and datafiles;

某个tempfile坏掉使得default temporary tablespace不能正常工作，数据库不会crash, 解决的办法是add一个新的tempfile，然后再drop掉坏的tempfile。（default temporary tablespace不能offline，但temporary file可以offline）

12.6 如何调整表空间的尺寸

表空间的大小等同它下的数据文件大小之和，当发生表空间不足的问题时常用的3个解决办法：

1）增加原有数据文件大小（resize）

2）增加一个数据文件（add datafile）

3）设置表空间自动增长（autoextend)

示例:

SQL> create tablespace prod datafile '/u01/oradata/prod/prod01.dbf' size 5m;
SQL> create table scott.test1 (id int) tablespace prod;
SQL> insert into scott.test1 values(1);
SQL> insert into scott.test1 select * from scott.test1;
SQL> /

报错：ORA-01653: unable to extend table SCOTT.TEST1 by 8 in tablespace prod

1）用第一种方法扩充表空间

SQL> alter database datafile '/u01/oradata/prod/prod01.dbf' resize 10m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> /

报错：ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace prod

2）用第二种方法扩充表空间：

SQL> alter tablespace prod add datafile '/u01/oradata/prod/prod02.dbf' size 20m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> /

报错：ORA-01653: unable to extend table SCOTT.TEST1 by 128 in tablespace prod

3）用第三种方法扩充表空间：

SQL> alter database datafile '/u01/oradata/prod/prod01.dbf' autoextend on next 10m maxsize 500m;
SQL> insert into scott.test1 select * from scott.test1;
SQL> drop tablespace prod including contents and datafiles;

12.7 可恢复空间分配Resumable

当我们往一个表里面插入大量数据时，如果某条insert语句因表空间的空间不足（没有开启自动扩展），会报 ORA-01653:无法扩展空间的错误，该条SQL语句会中断，浪费了时间及数据库资源。为防范这个问题，Oracle设计了resumable。

1）功能：

在resumable开启的情况下，如果Oracle执行某条SQL申请不到空间了，比如数据表空间，undo表空间，temporary空间等，则会将该事务的语句挂起（suspended)，等你把空间扩展后，Oracle又会使该insert语句继续进行。

2）设置方法

可以通过两个级别设置resumable：

①system级别：初始化参数RESUMABLE_TIMEOUT非0，这将使数据库中所有session使用可恢复的空间分配

②session级别：alter session enable|disable resumable [TIMEOUT]; 这将为当前session设置可恢复的空间分配。因为resumable是有资源消耗代价的, 所以session级的resumable是比较实用的。

3）参数RESUMABLE_TIMEOUT的用法（单位为秒）

RESUMABLE_TIMEOUT=0, enable session时应该指定TIMEOUT。否则使用缺省值7200秒。
RESUMABLE_TIMEOUT<>0，enable session时可以省略TIMEOUT，此时指定TIMEOUT会覆盖掉参数RESUMABLE_TIMEOUT值。

示例：

session 1:

1）建个小表空间，固定2m大小，然后建个表属于这个表空间

SQL> create tablespace small datafile '/u01/oradata/prod/small01.dbf' size 2m;
SQL> create table scott.test(n1 char(1000)) tablespace small;

2）向这个表插入数据，表空间满了，使for语句没有完成循环，2000条语句整体失败。

SQL> begin
for i in 1..2000 loop
insert into scott.test values('this is test');
end loop;
commit;
end;
/

报错：ORA-01653: 表 SCOTT.TEST 无法通过 128 (在表空间 SMALL 中) 扩展

这个128是块数，表明最后一次需要1M的extent，没有成功.

SQL> select count(*) from scott.test;
COUNT(*)
----------
0

因为没有在循环体内commit，所以插入的记录全部回滚了。

3）使用resumable功能

SQL> alter session enable resumable;

4）再重复第2）步，会话被挂起；

session 2:

5）查看视图的有关信息

SQL> select session_id,sql_text,error_number from dba_resumable;
SESSION_ID SQL_TEXT ERROR_NUMBER
---------- -------------------------------------------------- ------------
136 INSERT INTO SCOTT.TEST VALUES('this is test') 1653
SQL> select sid,event,seconds_in_wait from v$session_wait where sid=136;
SID EVENT SECONDS_IN_WAIT
---------- ---------------------------------------------------------------- ---------------
136 statement suspended, wait error to be cleared 1

6）加扩表空间，看到session1里挂起的会话得以继续并成功完成了2000条语句的插入。

SQL> alter tablespace small add datafile '/u01/oradata/prod/small02.dbf' size 4m;
SQL> select count(*) from scott.test;
COUNT(*)
----------
2000

7）查看EM告警日志报告了以上信息。验证结束后可以disable resumable, 并删除small表空间及数据文件。

session 1:

SQL> alter session disable resumable;
SQL> drop tablespace small including contents and datafiles;

the end ！！！

@jackman 共筑美好！