创建index时候要用到排序空间

今天要创建一个索引，但是等命令一敲完就报错了，如下：

SQL> create table t
  2  as
  3  select object_name unindexed,object_name indexed from all_objects;

Table created.

SQL> create index t_idx on t(indexed);
create index t_idx on t(indexed)
                      *
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

SQL>

 

1、随后去查了临时表空间：

SQL> desc dba_temp_files;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 FILE_NAME                                                                  VARCHAR2(513)
 FILE_ID                                                                    NUMBER
 TABLESPACE_NAME                                                   NOT NULL VARCHAR2(30)
 BYTES                                                                      NUMBER
 BLOCKS                                                                     NUMBER
 STATUS                                                                     CHAR(9)
 RELATIVE_FNO                                                               NUMBER
 AUTOEXTENSIBLE                                                             VARCHAR2(3)
 MAXBYTES                                                                   NUMBER
 MAXBLOCKS                                                                  NUMBER
 INCREMENT_BY                                                               NUMBER
 USER_BYTES                                                                 NUMBER
 USER_BLOCKS                                                                NUMBER

SQL> col file_name format a30
SQL> select file_name,tablespace_name,status from dba_temp_files;

no rows selected

SQL>

空空如也

2、创建临时表空间

SQL> create temporary tablespace temp
  2  tempfile '/u01/oradata/denver/temp02.dbf'
  3  size 10m;

Tablespace created.

SQL>

3、再建索引

SQL> create index t_idx on t(indexed);

 

Index created.

 

SQL>

index创建成功

 

总结：如上在开始创建表的时候没有报表空间错，但是在创建索引时候就报错，可见创建索引要先在内存中排序，当排序空间不够时候就会用到磁盘上的temp表空间里数据文件所在的空间，故报错了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16400082/viewspace-707776/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16400082/viewspace-707776/