Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【Ora】ORA-1653 unable to extend

27 May 2019 »

ORA-1653 unable to extend的解决思路

0.场景

由于需要迁移数据,听到同事说报了如下错误:

ORA-1653: unable to extend table APPS.DM_FIN_JOURNAL_HIS by 5314960 in  tablespace APPLSYSD 

通过查看视图,发现表空间还很充足。用了如下方法都不奏效。

1.手动合并相邻的空闲Extents:

ALTER TABLESPACE APPLSYSD COALESCE;

2.添加数据文件:

ALTER TABLESPACE APPLSYSD ADD DATAFILE '/ora1159/prod/proddata/applsysd04.dbf' SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE unlimited;

3.resize数据文件:

alter database datafile '/ora1159/prod/proddata/applsysd01.dbf' resize 16384M;
alter database datafile '/ora1159/prod/proddata/applsysd02.dbf' resize 20480M;
alter database datafile '/ora1159/prod/proddata/applsysd03.dbf' resize 16384M;

4.修改数据文件的自动扩展的属性:

alter database datafile '/ora1159/prod/proddata/applsysd01.dbf' AUTOEXTEND ON NEXT 512M;
alter database datafile '/ora1159/prod/proddata/applsysd02.dbf' AUTOEXTEND ON NEXT 512M;
alter database datafile '/ora1159/prod/proddata/applsysd03.dbf' AUTOEXTEND ON NEXT 512M;

可惜,上述方法都不奏效。

1.大招

开始分析这个表DM_FIN_JOURNAL_HISstorage,如下:

tablespace APPLSYSD
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 40
    next 46519680
    minextents 1
    maxextents 505
    pctincrease 50
  );

发现他的next=46519680相当46GB的空间,加上pctincrease等于50,通过修改上述属性。

修改的命令如下:

SQL> alter table apps.DM_FIN_JOURNAL_HIS storage(next 2480000k pctincrease 0);

问题解决。

2.总结

表空间扩展的解决方法,归纳如下:

- Manually Coalesce Adjacent Free Extents
       ALTER TABLESPACE <tablespace name> COALESCE;
   The extents must be adjacent to each other for this to work.

- Add a Datafile: 
        ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' 
        SIZE <integer> <k|m>; 

- Resize the Datafile: 
        ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>; 

- Enable autoextend: 
       ALTER DATABASE DATAFILE '<full path and file name>' AUTOEXTEND ON 
       MAXSIZE UNLIMITED;

- Defragment the Tablespace: 

- Lower "next_extent" and/or "pct_increase" size:
        ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m> 
        pctincrease <integer>); 

- If the tablespace is being used as a temporary tablespace, temporary segments may
  be still holding the space.