建立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表空間裡資料檔案所在的空間，故報錯了