ORA-00604/ORA-01655 Error

[gunjan14]

Hi,

While running a create table command I am getting this error.

CREATE TABLE rns_p_meas_srrssp_O2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01655: unable to extend cluster SYS.C_OBJ# by 25 in tablespace SYSTEM

Can anyone tell me why this is happening and also what to do?

Gunjan

 

[sbix]

Well ... it looks like you are experiencing a shortage of space in SYSTEM tablespace.
In which tablespace are you allocating the table?

 

[sem]

I think that this is really due to "shortage of space in SYSTEM tablespace", but not because of creating a new table in that tablespace but rather because of attempt to register it in data dictionary. In any case you may add more files to that tablespace.

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Yes .. indeed the error message says:
ORA-01655: unable to extend cluster SYS.C_OBJ# by 25 in tablespace SYSTEM

 

[gunjan14]

This is the result I am getting

SQL> select sum(bytes) from dba_free_space where tablespace_name = 'SYSTEM';

SUM(BYTES)
----------
90112

I think I got to add another datafile to system tablespace.

 

[avjoshi]

try
select bytes from dba_free_space where tablespace_name = 'SYSTEM';

That will give you individual chunk size. Evenif your total free space looks like it can accomodate new addition to dictionary. Individual chunk size may not be large enough.

Anand.

Anand

 

[sbix]

Try to issue the seguent SQL statement:
select next_extent from dba_clusters where cluster_name='C_OBJ#';
This should be a standard value .... to me returns 204800

 

[gunjan14]

SQL> select next_extent from dba_clusters where cluster_name='C_OBJ#';

NEXT_EXTENT
-----------
204800

SQL> select bytes from dba_free_space where tablespace_name = 'SYSTEM';

BYTES
----------
90112

I am trying to add another datafile to the system tablespace and then create the table. (The table contains 996 columns)

Gunjan

 

[gunjan14]

thanks guys,
this was space problem in system tablespace. I added another datafile and then it worked
Gunjan