HELP: ORA-01653 Unable to extent a table in a tablespace
Hi friends, I have a question that I really need to solve. Please help me, any answer that can explain me what is happening will be very appreciated.
Recently I performed an import operation that succesfully created all the tables and rows of the schema Y. The information was imported using the option compress=n. At the moment I don't know if this is relevant to the problem, but I think is a good idea tell you what I have done. After that, the users have been using the database without any problems (using DML sentences), but in this moment I'm receiving an error from one application that is trying to insert some rows. The error is:
UNABLE TO EXTENT TABLE Y.X BY 5 IN TABLESPACE Z;
I think the problem is the segment of the table cannot extent anymore.
So I get these information from Oracle:
1) BLOCK SIZE:
show parameters db_block_size;
NAME TYPE VALUE
------------------------------------ ------- -----------
db_block_size integer 4096
2) STORAGE OF TABLE:
select * from dba_tables where table_name='X'
2) SEGMENTS OF THE TABLE:
WHERE segment_name = 'X' and OWNER='Y'
3) FREE SPACE IN THE TABLESPACE:
select max(bytes)/1048576 from dba_free_space
where tablespace_name = 'Z';
4) TABLESPACE INFORMATION :
from dba_tablespaces where tablespace_name='Z';
5)DATAFILES OF THE TABLESPACE:
As you can see the NEXT_EXTENT parameter of the table's storage is not present. In DBA-STUDIO the value showed is 0.
(I don't know the reason). But when I try to change this value to 10Mb (a multiple of the block_size) I get the error ORA-25150, telling me that the extent parameter value that I'm trying to use is not permitted. Also I have increased succesfully the size of the tablespace's datafile in 100 Mb. But I have the same bad results trying to modify the NEXT_EXTENT value...
I have concluded that:
* i) I don't know why the table cannot extent in the tablespace (ORA-01653 ).
* ii) I don't know why I cannot change the NEXT_EXTENT parameter of the table's storage in 10 Mb (ORA-25150).
* iii ) I don't know if I solve the point "ii)" will help me to solve the point "i)".
Thanks a lot for all your ansers
How can I calculate the next extent size of a data segment in a LMT-AUTO ALLOC?
Thank you very much for your answer.
I executed the query:
(The results are in MB)
select file_id, block_id, bytes/1024/1024 from dba_free_space
FILE_ID BLOCK_ID BYTES/1024/1024
---------- ---------- ---------------
7 19887 ,01953125
7 19027 ,13671875
7 1307 3,046875
7 20232 11,6992188
7 23917 156,578125
7 19187 ,76171875
I have the doubt:
How can I know that the free space of a auto allocated LMT is enought to support the next extent of a table's segment? How can I calculate the next extent size of a data segment in a LMT AUTO ALLOC?
Regards, and thank you very much again.