ORA-01652: unable to extend

Thanks in advance

Tablespace is having almost 2.9G free space.

select initial_extent,next_extent from dba_tablespaces where tablespace_name='PRONTOMSP';

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
10485760 10485760

i dont know why it is giving error. Any clues in this. ?

ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

that next_extent is not useful, show the next extent of the segment which is failing

and show the maximum free extent size

pando boss -

first part of question i didnt get -

second part of question i am posting the output
SQL> select INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,STATUS from dba_tablespaces where tablespace_name='PRONTOMSP';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
-------------- ----------- ----------- ----------- ------------ ---------
10485760 10485760 1 249 50 ONLINE

what operation is causing this error

ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

Show which segment is causing that error and show us the next_extent of that segment

objective is huge 10 tables from our database are TO BE partitioned

when an alter statment for adding partition is executed at sql prompt this error comes.

ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

pando boss , i dont know how to find this.
Show which segment is causing that error and show us the next_extent of that segment

can u please tell me.

select the next_extent for the *table* not the tablespace as the table settings can override the tablespace settings. Also check to see if you have enough contiguous free space in a file for that next extent

Max extents on the table?

Quote:

---

Originally Posted by gajananpkini

when an alter statment for adding partition is executed at sql prompt this error comes.

ORA-01652: unable to extend temp segment by 61446 in tablespace PRONTOMSP

---

Which partition is causing the error? Partition is the segment then just look dba_segments for next_extent

I guess this error must be happening when you are trying to "create" an object in the tablespace. Possibly an index. And, the operation is failing as it can not find enough usable free space in the tablespace. Is this a locally managed tablespace ? If not, the free space of 2.9 G may not be entirely usable. Check the extent size specs of the object being created and see how many extents bigger than those sizes are available free in the tablespace.

thanks in advance

ORA-1653: unable to extend table QADB0703.PRONTO_ALERT by 466608 in tablespace OSSBSS

i AM GETTING THIS ERROR

details

free space in this tablespace is 16gb.

SQL> select INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_Segments where SEGMENT_NAME='PRONTO_ALERT';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------- ----------- ----------- -----------
20480 20480 1 249
20480 1911226368 1 249


select INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_Segments where TABLESPACE_NAME='OSSBSS';
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------- ----------- ----------- -----------
20480 20480 1 249
20480 20480 1 249
20480 20480 1 249
20480 20480 1 249
20480 20480 1 249

WHAT SHOULD I DO?

shall i increase the initial & next to 10m each

make the tablespace bigger, also your next extent seems to be 1.9gb is that correct

if your next extent is 1.9gb then even you have 16gb free space in your tablespace but if none of extents has 1.9gb contiguos free space you will see this problem.

1.9gb next extent is way too big IMHO, you can either change next extent to a smaller value or increase your tablespace size but doing so might waste your existing free space

I'm with Pando here.

Big extents make big messes. Smaller, uniformly sized extents will perform well and actually make management easier.

We generally use 1M, 10M and 100M extents.

KenEwald

just for my knowledge

u said We generally use 1M, 10M and 100M extents

what storage value u assign for pctincrease.minextents,maxextents parameters?

0 pctincrease, 1 min, max depends on whether we want to cap growth or autoextend.

ken

Thanks for the answer

just one question -

is there any rule of thumb for pctincrease value.

i mean any percentage ?

I'm not Ken, but pctincrease should always be 0 in Dictionary Managed Tablespaces. It is ignored (except at segment creation if minextents > 1) in Locally Managed Tablespaces.

John

Quote:

---

Originally Posted by gajananpkini

ken

Thanks for the answer

just one question -

is there any rule of thumb for pctincrease value.

i mean any percentage ?

---

yes ZERO

pando boss Thank u very much for the answer.