Tuesday, 21 September 2010

ORA-01653 : unable to extend table

in oracle apps 11i receivables When entering receipt and saving oracle gave this error

ORA-01653 : unable to extend table
AR.AR_CASH_RECEIPT_HISTORY_ALL BY 1024 in tablespace ARD

check for extend management is automatic or manual

dba_data_files

select autoextensible from dba_data_files where tablespace_name='ARD'
it will show yes or no



The table was created with size 400M and extend allocation was not automatic so i manually extended 100MB.

ALTER DATABASE
DATAFILE '/u01/proddata/prod/ard1.dbf'
AUTOEXTEND ON MAXSIZE 500M;


ALTER DATABASE
DATAFILE '/u01/proddata/prod/ard1.dbf'
AUTOEXTEND off;

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','ARD') FROM DUAL;

As this is user managed extend management actually what should have done was either resize or add new datafile.

here is how


ALTER DATABASE
DATAFILE '/u01/proddata/prod/ard1.dbf'
RESIZE 500M;

ALTER DATABASE
ADD DATAFILE '/u01/proddata/prod/ard2.dbf' SIZE 400M
AUTOEXTEND OFF;

No comments:

Post a Comment