Oracle unable to extend table in tablespace

Tags:  •    •    •    •  

I've got the next error message from Oracle server, when I try to insert new record:

ORA-01653: unable to extend table OWNER.TABLE_NAME by BYTE in tablespace TABLESPACE

Where TABLESPACE is the name of your tablespace. In the future you need to replace TABLESPACE with name of your tablespace.

If you have enough disk space, you need to check how many bytes are used by Oracle in TABLESPACE:

SELECT *
FROM
   dba_data_files
WHERE
   tablespace_name = 'TABLESPACE';
--Where TABLESPACE is the name of your tablespace.
--Please replace it.

"BYTES" column shows maximum size of this tablespace in bytes. If value of "AUTOEXTENSIBLE" column is NO, then Oracle don't extends the maximum size of tablespace. If this value is YES, then Oracle extends tablespace when no more space in it.

If value of "AUTOEXTENSIBLE" column is NO, check how many bytes are used by Oracle:

SELECT
  tablespace_name,
  SUM(bytes/1024/1024)
FROM
  dba_segments
GROUP BY
  tablespace_name;
--Where TABLESPACE is the name of your tablespace.
--Please replace it.

Now look at your tablespace size. If the different between this value and maximum size is enough small you need to set AUTOEXTENSIBLE to
YES. I have generated a query, what set the YES value with this command:

SELECT
  'alter database datafile '''||
  file_name||
  ''' '||
  ' autoextend on;'
FROM
  dba_data_files
WHERE tablespace_name = 'TABLESPACE';
--Where TABLESPACE is the name of your tablespace.
--Please replace it.

Copy and paste generated query end execute it.

Now your tablespace is AUTOEXTENSIBLE and it will grow if need.

Thanks a lot!! this

Thanks a lot!! this procedure really helps.

Submitted by thanks (not verified) on April 28, 2010 - 9:29am.
Thanks thanks thankssssss

thanks a lot men , you are a GOD!!!!!

Submitted by Visitor (not verified) on February 18, 2010 - 11:06pm.
error while inserting the data from one table to another

when am trying to insert the data of one table to another
insert into TESTDB1 select * from DBTEST;
I will get the following error

insert into TESTDB1 select * from DBTEST
*
ERROR at line 1:
ORA-01653: unable to extend table SYSTEM.TESTDB1 by 8192 in tablespace SYSTEM

I have tried the query posted in this forum to extend the tablespace, still am getting the same error.

Please help me in this

Submitted by Rumana (not verified) on April 24, 2009 - 11:27am.
many thanks

This site has really help me alot.

Submitted by Awotunde Ayodeji (not verified) on March 30, 2009 - 1:24pm.

Post new comment

The content of this field is kept private and will not be shown publicly.