Share |

ORA-01653: Unable to extend table %s.%s by %s in tablespace %s

The tablespace the table is residing in is unable to extend.

There is not enough space left either due to the datafiles being full, autoextend which is not set at datafile level or due to a disk which's full.

You'll have to check the size of the datafiles attached to the tablespace and check whether they can autoextend or not.. 
select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name='TABLESPACE_NAME'
Either add more datafiles to the tablespace, set the autoextensible flag or enlarge the datafile(s).

To add more space to a file issue following command: 
alter database datafile 'C:\ORACLE\ORADATA\TOOLS01.DBF' resize 1000m;
To turn on the autoextend feature on a datafile use following command:
alter database datafile 'C:\ORACLE\ORADATA\TOOLS01.DBF' autoextend on next 100m maxsize 2000m;
To add a new datafile to the tablespace use following command:
alter tablespace TOOLS add datafile 'C:\ORACLE\ORADATA\TOOLS02.DBF' size 100m autoextend on next 100m maxsize 2000m;
 Was this information helpful?  Yes No
If it was not helpful, please take some time to explain why. This is not to ask questions, you can do so in the forum.

Welcome to our forum for Oracle error: ORA-01653 Add your own message



reply On 07-FEB-2008 03:46:11 abhilasha asked: unable to extend undo tablesspace ....................
Reply
what should i do when get unable to extend undo tablesspace error
reply On 07-FEB-2008 03:51:07 DbMotive replied
Reply
You either extend the tablespace or add a datafile to the tablespace.
Check the examples above on how to do that.

select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name='UNDOTBS1'

reply On 30-MAY-2008 06:34:33 M V Sreedhar replied
Reply
I also have the same problem, but i am afraid that concerned tablespace is having enough free space , so what could be the problem?
reply On 30-MAY-2008 08:50:04 DbMotive replied
Reply
What exactly was the error message?
It can be a temporary segment in the tablespace (eg. during index creation), of the TEMP tablespace which is full.

reply On 19-MAR-2009 15:53:15 Praveen replied
Reply
I am also getting the following error, any one could plz clarify the solution for this would be of great help ASAP. N ha thre is enough space in my table even then am getting this

2009-03-19 09:29:40.129 | Exception while saving the Recipe Ingredient Details : < Recipe Code , Vendor code , Item Code > :
org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into CUST_RECIPE_INGREDIENT (CUST_ITEM_ID, CHILD_CUST_RECIPE_ID, CHILD_GFS_RECIPE_ID, OFFERING_PREP_ID, CUST_ITEM_PREP_ID, CUST_RECIPE_ID, CUST_OFFERING_PREP_ID, INGREDIENT_TYPE_ID, DISPLAY_SEQ, INGREDIENT_DESC, INGREDIENT_NOTE_TXT, PRINT_IND, INVENTORY_RELEVANCE_IND, INGREDIENT_QTY, PURCHASE_PRICE_AMT, OFFERING_ID, OFFERING_GROUP_ID, INGREDIENT_UOM_CODE, PREP_METHOD_TXT, PREP_YIELD_PCT, ANALYSIS_REF_CODE, ANALYSIS_SOURCE_CODE, ANALYSIS_TYPE_CODE, LAST_UPDATE_TMSTMP, LAST_UPDATE_USER_ID, CUST_RECIPE_INGREDIENT_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [1653]; ORA-01653: unable to extend table RCPMGR_ADMIN.CUST_RECIPE_INGREDIENT by 128 in tablespace RCPMGR_DATA
; nested exception is java.sql.SQLException: ORA-01653: unable to extend table RCPMGR_ADMIN.CUST_RECIPE_INGREDIENT by 128 in tablespace RCPMGR_DATA

reply On 19-MAR-2009 16:04:39 DbMotive replied
Reply
Can you please check the free space in the following tablespace: RCPMGR_DATA 

select sum(bytes) from user_free_space where tablespace_name='RCPMGR_DATA'


If the value is less than 128, or NULL, you need to extend the tablespace
reply On 10-APR-2009 06:47:58 replied
Reply
which ways do not affect other database users?
 
reply On 10-APR-2009 06:46:34 ash asked: question about extending the tablespace.....
Reply
which ways do not affect other database users
reply On 10-APR-2009 09:19:02 DbMotive replied
Reply
You can extend tablespaces without a problem, or set autoextend on, so users will not see this error message
 
reply On 24-APR-2009 11:12:36 Rumana asked: Error while copying the data from one table to another
Reply
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

Please help me in this
reply On 24-APR-2009 11:19:28 DbMotive replied
Reply
First of all, it is bad practice to create objects in the SYSTEM tablespace.

But you've run out of space, you need to extend the tablespace by either adding a new datafile or extending the current datafile.

You can follow the examples above in order to extend the datafile.
reply On 24-APR-2009 11:56:54 rumana replied
Reply
Ya, I have already used the above examples.It tells that database has been altered.
I have done like this
alter database datafile '/u02/oradata/sales/system01.dbf'
autoextend on next 100m maxsize 1000m;

"Database altered."

select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name=
'SYSTEM';

Got the answer like this
/u02/oradata/sales/system01.dbf
3.4360E+10 YES 1048576000

Even after that if i go to insert the data it shows the following eror

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

Please help...
reply On 24-APR-2009 12:03:06 DbMotive replied
Reply
You need to check the size of the segment our are copying, and size the tablespace accordingly.

select sum(bytes/1024/1024) from dba_segments where owner=user and segment_name='DBTEST'


Also check how much free space you have in your SYSTEM tablespace:
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='SYSTEM'


Make sure the free space is larger than the sizze of the segment your are trying to copy.

Also, it might be that - even autoextend is turned on - the filesystem is full, and the datafile is unable to extend, even it didn't reach the maxbytes.

 
reply On 24-APR-2009 12:19:40 rumana asked: Error while copying the data from one table to another
Reply
I have tried this query
select sum(bytes/1024/1024) from dba_segments where owner=user and segment_name='DBTEST'

SUM(BYTES/1024/1024)
--------------------
435

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='SYSTEM'

SUM(BYTES/1024/1024)
--------------------
1.3125

Can you please tel me is it possible for me to insert the data now or what will be the solution for this.

And the maxbytes is

SELECT *
FROM
dba_data_files
WHERE
tablespace_name = 'SYSTEM';



FILE_NAME
-------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u02/oradata/sales/system01.dbf
1 SYSTEM 3.4360E+10 4194302 AVAILABLE
1 YES 3145728000 384000 128000 3.4360E+10 4194288
SYSTEM
reply On 24-APR-2009 12:25:38 DbMotive replied
Reply
You have no space left in your SYSTEM tablespace (Only around 1mb).

You need to extend the datafile to at least 1,5Gb.

alter database datafile '/u02/oradata/sales/system01.dbf'
autoextend on next 100m maxsize 1500m;
 
reply On 24-APR-2009 12:32:31 rumana asked: error No space left
Reply
Hi,

I did the following thing
alter database datafile '/u02/oradata/sales/system01.dbf'
autoextend on next 100m maxsize 1500m;

Database altered.

select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name=
'SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
BYTES AUT MAXBYTES
---------- --- ----------
/u02/oradata/sales/system01.dbf
3.4360E+10 YES 1572864000

After all these things if i try to insert the data of 435MB
I will get the same 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


Please give me some solution for this...
And please tel how to create a new tablespace as you told that its a bad practice to work with
SYSTEM tablespace.
 
reply On 30-APR-2009 15:00:53 rumana asked: Error while copying the data from one table to another
Reply
Can somebody reply(provide solution) for the above query(error No space left).
Please....
 
reply On 17-JUN-2009 06:07:31 Bhaskar asked: ORA-01653
Reply
Hi
When i try to insert data in atable i am getting the following error code ORA-01653 with description unable to extend table 123(Table Name) by 128 in tablespace USERS.

sO GIVE ME A SOLUTION FOR THIS AS EARLY AS POSSIBLE......

Thanks in Advance............
reply On 17-JUN-2009 06:37:30 DbMotive replied
Reply
You can either add a new datafile to the USERS tablespace, or extend the current datafile.

You can use the examples above
select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name='USERS'

alter database datafile 'datafile found' resize 1000m;
 
reply On 26-JUL-2009 07:05:24 zak asked: error
Reply
PU = regenerer ORA-01653:unible to extend .by in tablespace

there is message in my dbase can u help me thx
 
reply On 21-OCT-2009 12:40:05 Gopi B asked: how to increasing table space Oracle10g
Reply
I am execute this quiery, below exception is came.



Alter database datafile 'D:\oracle\product\10.2.0\oradata\mobee\USERS01.DBF' resize 1000m;


----------------------


Error starting at line 1 in command:
Alter database datafile 'D:\oracle\product\10.2.0\oradata\mobee\USERS01.DBF' resize 1000m
Error report:
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

----------------------------


I am trying to open the my Database Control, below exception is came.


Server not found.


             Please help me, how to increase the my tablespace.







reply On 21-OCT-2009 12:42:48 DbMotive replied
Reply

In order to extend your datafile, you need to be connected with a user who has DBA privileges.

So connect to the database using the SYSTEM user or any other user who has dba privileges.

 
reply On 18-MAY-2010 11:45:15 Vicky asked: ORA-01653: unable to extend table by 1024 in tablespace
Reply

Hey can anyone help how to free temp folder on oracle server

reply On 18-MAY-2010 12:27:53 DbMotive replied
Reply

What tablespace was mentioned in the error message?

If it was TEMP tablespace, you will need to extend this tablespace by either add extra space to the temporary file, or add an extra temporary file.

Add your message

 Please provide your personal info 



 Please ask your message as briefly and clear as possible 

 Spam Protection 
Validation Code: dqeuzbkytaa8vtyte


Ask Your Question

If you need more information about this particular error message, you can leave a forum message.

We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.

Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.


Spam Protection

In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.

If you do not enter the validation correctly, your message will not be recorded.


Forum Rules

Please be polite, do not USE ALL UPPERCASE, no insults, violance or any other threats.