通常是由于表空间数据文件没有自动扩展或者达到最大限制大小导致的。

--永久表空间
--创建永久表空间
SQL> create tablespace ymh datafile 'd:/ymh.dbf' size 15M reuse;

Tablespace created

SQL> create table ymh_test tablespace ymh as select * from dba_objects;

Table created

SQL> insert into ymh_test select * from dba_objects;

60719 rows inserted

SQL> commit;

Commit complete

--检查空间剩余
SQL> SELECT A.tablespace_name, B.CURRENTBYTES - A.FREE USED, A.FREE,B.CURRENTBYTES, B.MAXBYTE
  2   FROM
  3  (SELECT tablespace_name,SUM(BYTES) CURRENTBYTES,SUM(MAXBYTES) MAXBYTE
  4           FROM dba_data_files
  5           GROUP BY tablespace_name ) B,
  6   (SELECT tablespace_name,SUM(BYTES) FREE
  7           FROM dba_free_space
  8          GROUP BY tablespace_name ) A
  9   WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME and a.TABLESPACE_NAME='YMH'
 10  /

TABLESPACE_NAME                      USED       FREE CURRENTBYTES    MAXBYTE
------------------------------ ---------- ---------- ------------ ----------
YMH                              14745600     983040     15728640          0

SQL> select 983040/1024/1024 from dual;

983040/1024/1024
----------------
          0.9375
         
--报ORA-01652
SQL> create index iymh_test on ymh_test(object_id) tablespace ymh;

create index iymh_test on ymh_test(object_id) tablespace ymh

ORA-01652: unable to extend temp segment by 8 in tablespace YMH

--修改为自动扩展
SQL> alter database datafile 'd:/ymh.dbf'  autoextend on next 10M;

Database altered

--创建成功
SQL> create index iymh_test on ymh_test(object_id) tablespace ymh;

Index created

--临时表空间
--创建临时空间
SQL>  create temporary tablespace temp_ymh tempfile 'D:\temp_ymh' size 2M;

Tablespace created

SQL> alter user dcuser temporary tablespace temp_ymh;

User altered

--报ORA-01652
SQL> select * from dcuser.tfx_khh_y_zhzk order by yf,yyb desc;

select * from dcuser.tfx_khh_y_zhzk order by yf,yyb desc

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_YMH

--修改为自动扩展
SQL> alter database tempfile 'D:\temp_ymh' autoextend next 10M;

alter database tempfile 'D:\temp_ymh' autoextend next 10M

ORA-02491: missing required keyword ON or OFF in AUTOEXTEND clause

SQL> alter database tempfile 'D:\temp_ymh' autoextend on next 10M;

Database altered

SQL> select * from dcuser.tfx_khh_y_zhzk order by yf,yyb desc ;

KHH                 YF YYB  ZRRTS JYRTS           QMZC_RMB 
-------------- ------- ---- ----- ----- ------------------
11919830        200910 1018    31    20          297073.89 
11919830        200911 1018    31    20          297073.89 
11919830        201002 1018    31    20          297073.89 
1100121471      201003 998     31    23               0.00 
1100023768      201003 998     31    23               0.00 
1100121401      201003 998     31    23               0.00 
1100047512      201003 998     31    23               0.00 
1100000281      201003 998     31    23               0.00 
1100048665      201003 998     31    23               0.00 
1100900866      201003 998     31    23               0.00

SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='YMH';

FILE_NAME           TABLESPACE_NAME            AUTOEXTENSIBLE
-------------                ------------------------------           --------------
D:\YMH.DBF           YMH                                      YES

SQL> select file_name,tablespace_name,autoextensible from dba_temp_files where tablespace_name='TEMP_YMH';

FILE_NAME           TABLESPACE_NAME            AUTOEXTENSIBLE
-------------                ------------------------------           --------------
D:\TEMP_YMH          TEMP_YMH                       YES