-
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'
OWNER: Y
TABLE_NAME: X
TABLESPACE_NAME: Z
CLUSTER_NAME:
IOT_NAME:
PCT_FREE: 10
PCT_USED:
INI_TRANS: 1
MAX_TRANS: 255
INITIAL_EXTENT: 20480
NEXT_EXTENT:
MIN_EXTENTS: 1
MAX_EXTENTS: 2147483645
PCT_INCREASE:
FREELISTS:
FREELIST_GROUPS:
LOG: YES
B: N
NUM_ROWS:
BLOCKS:
EMPTY_BLOCKS:
AVG_SPACE:
CHAIN_CNT:
AVG_ROW_LEN:
AVG_SPACE_FREELIST_BLOCKS:
NUM_FREELIST_BLOCKS:
DEGREE: 1
INSTANCES: 1
CACHE: N
TABLE_LO: ENABLED
SAMPLE_SIZE:
LAST_ANA:
PAR: NO
IOT_TYPE:
...
2) SEGMENTS OF THE TABLE:
SELECT *
FROM DBA_SEGMENTS
WHERE segment_name = 'X' and OWNER='Y'
OWNER: Y
SEGMENT_NAME: X
PARTITION_NAME:
SEGMENT_TYPE: TABLE
TABLESPACE_NAME: Z
HEADER_FILE:12
HEADER_BLOCK:24819
BYTES:65536
BLOCKS:16
EXTENTS:1
INITIAL_EXTENT:20480
NEXT_EXTENT:
MIN_EXTENTS:1
MAX_EXTENTS: 2147483645
PCT_INCREASE:
FREELISTS:
FREELIST_GROUPS:
RELATIVE_FNO: 12
BUFFER_: DEFAULT
3) FREE SPACE IN THE TABLESPACE:
select max(bytes)/1048576 from dba_free_space
where tablespace_name = 'Z';
MAX(BYTES)/1048576
------------------
68,25
4) TABLESPACE INFORMATION :
select TABLESPACE_NAME,MAX_EXTENTS
from dba_tablespaces where tablespace_name='Z';
TABLESPACE_NAME: Z
BLOCK_SIZE: 4096
INITIAL_EXTENT: 65536
NEXT_EXTENT:
MIN_EXTENTS: 1
MAX_EXTENTS: 2147483645
PCT_INCREASE:
MIN_EXTLEN: 65536
STATUS: ONLINE
CONTENTS: PERMANENT
LOGGING: LOGGING
FOR: NO
EXTENT_MAN: LOCAL
ALLOCATIO: SYSTEM
PLU: NO
SEGMEN: AUTO
5)DATAFILES OF THE TABLESPACE:
FILE_NAME: C:\ORACLE\Z.ORA
FILE_ID: 12
TABLESPACE_NAME:Z
BYTES: 314572800
BLOCKS: 76800
STATUS: AVAILABLE
RELATIVE_FNO: 12
AUT: NO
MAXBYTES: 0
MAXBLOCKS: 0
INCREMENT_BY: 0
USER_BYTES: 314507264
USER_BLOCKS: 76784
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
Regards
-
ii. You cannot specify NEXT_EXTENT because it's a LMT with auto allocation.
i. Most probably the problem is because of ths not enough space in the tablespace for the next extent. Can you check how much free space left in the tablesapce. Check in DBA_FREE_SPACE.
HTH
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
where tablespace_name='Z'
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.
-
Remember this point:
For autoallocate in locally managed tablespace Oracle manages all extent allocation and sizing automatically, based on whatever value is specified for the initial extent. So the next extent is always the same as initial extent.
Regarding your problem, to allocate the extent to an object, the extents should be adjacent. To acheive this, you can coalesce adjacent free space as follows:
ALTER TABLESPACE Z COALESCE;
Agasimani
OCP(10g/9i/8i/8)
-
Originally posted by agasimani
Remember this point:
For autoallocate in locally managed tablespace Oracle manages all extent allocation and sizing automatically, based on whatever value is specified for the initial extent. So the next extent is always the same as initial extent.
This is not true if you are using AUTOALLOCATE. Oracle allocate extents of size based on grwoth of the table.
Excerpts from Oracle Magazine:
If you create a new table, the first extent will be 64KB in size. Subsequent extents will also be 64KB, until the table reaches 1MB in size. At that point the size of subsequent extents for the table will be increased to 1MB. When the table reaches 64MB in size, the extent size will be increased again to 8MB. Finally, if the table reaches a size of 1GB, the extent size will be increased one last time, to 64MB.
http://www.oracle.com/oramag/oracle/...ml?o60o8i.html
Turin,
Are you sure about the results posted from DBA_SEGMENTS ? I see the table only one extent big.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Thanks all of you again.
Yes, I'm pretty sure that I have only one segment of 64 Kb.
The next extent's size (the 2nd) will have a size of 64 Kb according with the Oracle's storage formula for LMT AUTO ALLOC.
My free space, according white dba_free_space view, is 156,578125 Mb.
So, I think there aren't any reason for expecting lack of space in my LMT.
Regards.
Last edited by Turin; 02-04-2003 at 11:29 AM.
-
[QUOTE]Originally posted by Turin
[B]Thanks all of you again.
Yes, I'm pretty sure that I have only one segment of 64 Kb.
The next extent's size (the 2nd) will have a size of 64 Kb according with the Oracle's storage formula for LMT AUTO ALLOC.
My free space, according white dba_free_space view, is 156,578125 Mb.
So, I think there aren't any reason for expecting lack of space in my LMT.
you may want to check how did you create your tablespace.
I just got exactly the same mesage on testing system - 9.2 gave me the same message during creation of simple table with blob column on the tablspace created as:
CREATE TABLESPACE CMM2_TSP_TBL03 LOGGING DATAFILE '/oracle_dbf1/oradata/ENKLAV/t
bl3_1.dbf' SIZE 1999M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;
Databases`s block size is 16K. It works on tablespace created as just 'MANAGEMENT LOCAL' and 'MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO'.
I did not have time to check the reason of it yet, probably it`s supposed to be so...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|