ORA-1653表空间不足问题

tjpcs2004

我的表空间还剩3.7G，为什么还会报：
ORA-1653: unable to extend table ××× by 8192 in                 tablespace ×××
表空间600G左右，数据库文件101个，每个大小为4G和8G不等！

kevinlin.ora

--
select * from V$version;
select * from dba_tablespaces;
select tablespace_name,max(bytes)/1024/1024 from dba_free_space group by tablespace_name order by 1;
select name,value from V$system_parameter2 where name in ('db_block_size','recyclebin');
select ts_name,count(ts_name) from dba_recyclebin group by ts_name order by 1;
--
upload output.

tjpcs2004

输出结果请看附件：

tjpcs2004

表空间为：TBS_CRM_DEF
目前处理的方式是增加了一个8G的数据文件
alert日志节选：
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:32 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:32 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:32 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:32 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:33 2012
ORA-1653: unable to extend table UCR_IOM.COMBO_ORDER_MSG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:34 2012
ORA-1653: unable to extend table UCR_IOM.COMBO_ORDER_MSG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:34 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:34 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:34 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:34 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:34 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:35 2012
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
ORA-1653: unable to extend table UCR_CEN1.TF_NL_MENU_FUNC_LOG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:36 2012
ORA-1653: unable to extend table UCR_IOM.COMBO_ORDER_MSG by 8192 in                 tablespace TBS_CRM_DEF
Wed Feb 15 12:08:36 2012
Completed: ALTER TABLESPACE TBS_CRM_DEF  ADD DATAFILE '/dev/crmvg9/rcrmvg9_8_076' SIZE 8511M AUTOEXTEND OFF
Wed Feb 15 12:10:56 2012

action825

奇怪，从你的sql结果来看 空间还够啊
TBS_CRM_DEF                                    2287

[ 本帖最后由 action825 于 2012-2-22 13:54 编辑 ]

tjpcs2004

对呀，当时看有3.7G的空间剩余

orafans

向表空间添加数据文件或者resize现有datafile看行不行。

tjpcs2004

目前解决方法就是添加数据文件，只是不明白为什么会报如此错误！

Maclean Liu(刘相兵

跑一下这个脚本：

http://www.oracledatabase12g.com ... lespace-report.html

Maclean Liu(刘相兵

ODM data:

1. Hdr: 11075068 10.2.0.4 RDBMS 10.2.0.4.0 SPACE PRODID-5 PORTID-226
   
2. Abstract: ORA-1653: UNABLE TO EXTEND TABLESPACE FAD
   
3. 
   
4. 
   
5.   
   
6.   SubComponent: Space Management
   
7.   ==============================
   
8.   DETAILED PROBLEM DESCRIPTION
   
9.   ============================
   
10.   CT has a tablespace FAD in which there are 5 datafiles with AUTOEXTEND ON
    
11.   option.
    
12.   Among the 5 datafiles one datafile reached its maxbytes and the rest are
    
13.   having available bytes.
    
14.   But they are receiving "ORA-1653: unable to extend table FA.FA_BOOKS by 130
    
15.   in tablespace FAD"
    
16.   as below:
    
17.   
    
18.   ORA-1653: unable to extend table FA.FA_BOOKS by 130 in tablespace FAD
    
19.   ORA-1653: unable to extend table FA.FA_BOOKS by 130 in tablespace FAD
    
20.   ORA-1653: unable to extend table FA.FA_BOOKS by 130 in tablespace FAD
    
21.   Fri Dec 24 16:23:46 2010
    
22.   Errors in file
    
23.   /apps/oracle/ERPPRD/erpprddb/admin/ERPPRD_orafinprdracdb01/udump/erpprd1_ora_
    
24.   2809.trc:
    
25.   
    
26.   DIAGNOSTIC ANALYSIS
    
27.   ===================
    
28.   SQL> SELECT max(bytes)/1024/1024 FROM dba_free_space WHERE tablespace_name =
    
29.   'FAD';
    
30.   
    
31.   MAX(BYTES)/1024/1024
    
32.   --------------------
    
33.             99.921875
    
34.   
    
35.   SQL> SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE
    
36.   SEGMENT_NAME='FA_BOOKS' AND SEGMENT_TYPE='TABLE' AND OWNER='FA';
    
37.   
    
38.   NEXT_EXTENT PCT_INCREASE
    
39.   ----------- ------------
    
40.      1064960            0
    
41.   
    
42.   SQL> SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME =
    
43.   'FAD';
    
44.   
    
45.   EXTENT_MAN
    
46.   ----------
    
47.   LOCAL
    
48.   
    
49.   SQL> SELECT file_name, bytes/1024/1024, autoextensible, maxbytes/1024/1024
    
50.   FROM dba_data_files WHERE tablespace_name='FAD';
    
51.   
    
52.   FILE_NAME                      BYTES/1024/1024 AUT MAXBYTES/1024/1024
    
53.   ------------------------------ --------------- --- ------------------
    
54.   /oradata/ERPPRD/fad01.dbf           32767.9844 YES         32767.9844
    
55.   /oradata/ERPPRD/fad02.dbf           10300.0391 YES              30000
    
56.   /oradata/ERPPRD/fad03.dbf           7944.53125 YES              10240
    
57.   /oradata/ERPPRD/fad04.dbf           2127.89063 YES              10240
    
58.   /oradata/ERPPRD/fad05.dbf                  100 YES              10000
    
59.   
    
60.   WORKAROUND?
    
61.   ===========
    
62.   No
    
63.   
    
64.   TECHNICAL IMPACT
    
65.   ================
    
66.   Not able to do DML on the table FA.FA_BOOKS resides on the problematic
    
67.   tablespace FAD.
    
68.   
    
69.   RELATED ISSUES (bugs, forums, RFAs)
    
70.   ===================================
    
71.   NA
    
72.   
    
73.   HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
    
74.   ====================================================
    
75.   Always
    
76.   
    
77.   DOES THE ISSUE REPRODUCE INTERNALLY?
    
78.   ====================================
    
79.   Never
    
80.   
    
81.   IS A TESTCASE AVAILABLE?
    
82.   ========================
    
83.   No

复制代码

类似的案例 10.2.0.4 上

  Among the 5 datafiles one datafile reached its maxbytes and the rest are
  having available bytes.
  But they are receiving "ORA-1653: unable to extend table FA.FA_BOOKS by 130
  in tablespace FAD"