关于ORA-10922错误和临时表空间组

eagle198699

今天客户在QQ上发了个错误给我看：
ORA-10922: Temporary tablespace group is empty

错误的意思大概是临时表空间组为空导致的。

模拟了一个该错误的情况。
新建了一个临时表空间
create temporary tablespace temp2 tempfile 'E:\oradata\ora10g\dbfile\temp02.dbf' size 2m;
将这个表空间属于group1组。
alter tablespace temp2 tablespace group group1;
修改用户的临时表空间
alter user ldy temporary tablespace group1;

这时做一个需要排序的操作：
SQL> insert into t003 select * from t003 order by 1,2,3,4,5;
insert into t003 select * from t003 order by 1,2,3,4,5
*
第 1 行出现错误:
ORA-01652: 无法通过 128 (在表空间 TEMP2 中) 扩展 temp 段

可以看到这时临时表空间不足是报ORA-01652错误。

将temp2表空间删除，再执行同样的操作，就报了ORA-10922错误
ORA-10922: Temporary tablespace group is empty

关于临时表空间组的内容，可以参考metalink文档ID 245645.1
该文档解释了为什么创建以及如何创建临时表空间组。
临时表空间组是10g引入的新特性。
将临时表空间分组为单个组，可以让用户使用多个表空间的临时空间。

主要内容
------------
1.临时表空间组包含至少1个表空间。没有限制一个组最多可以包含多少表空间。
2.临时表空间组共享表空间的命名空间，所以它不能和任何表空间同名。
3.临时表空间组的名称可以和临时表空间的名称一样，分配给数据库或一个用户。
4.临时表空间组不能直接创建。它通过，将第一个临时表空间指定到该组时创建，将该组最后一个临时表空间删除时删除组。
5.任何一个临时表空间都可以：
 - 从一个组移到另一个组（如果组不存在，将会创建）
 - 从组中移除
 - 如果它是独立的可以增加到一个组
6.临时表空间组的意义：
  - 避免了1个表空间不适当地持有了排序的结果，尤其是分区表的问题。
  - 当一个用户同时连接了多个会话，可以使用不同的临时表空间。
  - 在并行操作中可以并行使用多个临时表空间。

例子
------------
1.SQL> create temporary tablespace LMTEMP 1
       tempfile 'D:\ORACLE10\ORCL\temp1_01.dbf' size 50M
       tablespace group GROUP1;
 在创建LMTEMP1临时表空间之前，GROUP1不存在。LMTEMP1是属于该组的第一个临时表空间。
 SQL> select * from dba_tablespace_groups;

   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   GROUP1                         LMTEMP1

2. SQL> create temporary tablespace lmtemp2
         tempfile 'D:\ORACLE10\ORCL\temp1_02.dbf' size 2M
         tablespace group group1;

   Tablespace created.

   一个新的临时表空间LMTEMP2增加到已存在的组GROUP1中。

   SQL> select * from dba_tablespace_groups;

   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   GROUP1                         LMTEMP1
   GROUP1                         LMTEMP2

3. 临时表空间可以移动到一个新的或另一个组中。
 
   SQL> alter tablespace LMTEMP1  tablespace group GROUP2 ;
   Tablespace altered.

   SQL> select * from dba_tablespace_groups;

   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   GROUP2                         LMTEMP1
   GROUP1                         LMTEMP2

4. 一个临时表空间可以移出组，使之成为孤立的临时表空间。

   SQL> alter tablespace LMTEMP1  tablespace group '';
   Tablespace altered.

   SQL> select * from dba_tablespace_groups;

   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   GROUP1                         LMTEMP2

   SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

   TABLESPACE_NAME              
   ------------------------------
   TEMP                          
   LMTEMP1                       
   LMTEMP2                       

   该孤立的表空间仍然是可以重新合并入一个组中：

   SQL> alter tablespace LMTEMP1  tablespace group GROUP1;
   Tablespace altered.

5. SQL> create temporary tablespace lmtemp5
         tempfile 'D:\ORACLE10\ORCL\temp1_05.dbf' size 50M
         tablespace group lmtemp5;
   create temporary tablespace lmtemp5
   *
   ERROR at line 1:
   ORA-10918: TABLESPACE GROUP name cannot be the same as tablespace name

   由于共享命名空间，所以组名不能和表空间名相同。

6. 如果将GROUP1中的所有临时表空间都删除，该组也会自动移除：
  
   SQL> create temporary tablespace LMTEMP3
         tempfile 'D:\ORACLE10\ORCL\temp1_03.dbf' size 2M
         tablespace group GROUP2;

   Tablespace created.

   SQL> create temporary tablespace LMTEMP4
         tempfile 'D:\ORACLE10\ORCL\temp1_04.dbf' size 2M
         tablespace group GROUP2;

   Tablespace created.

   SQL> select * from dba_tablespace_groups;

   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   GROUP1                         LMTEMP1
   GROUP1                         LMTEMP2
   GROUP2                         LMTEMP3
   GROUP2                         LMTEMP4

   SQL> drop tablespace lmtemp3 including contents and datafiles;
   Tablespace dropped.

   SQL> drop tablespace lmtemp4 including contents and datafiles;
   Tablespace dropped.

   SQL> select * from dba_tablespace_groups;

   GROUP_NAME                     TABLESPACE_NAME
   ------------------------------ ------------------------------
   GROUP1                         LMTEMP1
   GROUP1                         LMTEMP2
 
7. 可以分配给用户一个默认的临时表空间组:

   SQL> alter user scott temporary tablespace GROUP1;
   User altered.

8. 临时表空间组也可以在数据库级别分配默认的临时表空间组：

   SQL> alter database  default temporary tablespace GROUP1;
   Database altered.

   需要注意的，现在任何属于这个分配给数据库默认表空间组的临时表空间，都不能被删除：

   SQL> drop  tablespace LMTEMP2 including contents and datafiles;
   drop  tablespace lmtemp2 including contents and datafiles
   *
   ERROR at line 1:
   ORA-10921: Cannot drop tablespace belonging to default temporary tablespace
   group

9. 该新特性对用户是有好处的，各自会话的并行的几个排序操作，可以使用不同的临时表空间：

   -- DBA 会话 --

      SQL> select username, session_num, tablespace from v$sort_usage;
      no rows selected

   -- SCOTT 会话1 --

      SQL> select a.table_name, b.table_name from dict A, dict B;

   -- SCOTT 同时发生的会话2--

      SQL> select a.table_name, b.table_name from dict A, dict B;

   -- DBA 会话 --

      SQL> select username, session_num, tablespace from v$sort_usage;

      USERNAME                       SESSION_NUM TABLESPACE
      ------------------------------ ----------- -------------------------------
      SCOTT                                   97 LMTEMP2
      SCOTT                                  150 LMTEMP1

      用户SCOTT分别在2个不同的临时表空间进行排序操作。