OraFAQ Forum: Server Administration » Unable to extend the tablespace ORA-01688

Home » RDBMS Server » Server Administration » Unable to extend the tablespace ORA-01688 (Oracle 10g RAC)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unable to extend the tablespace ORA-01688 [message #458901]

Wed, 02 June 2010 08:11

sainipardeep
Messages: 2
Registered: June 2010
Location: Bangalore

Junior Member

Hi,

I received Unable to extend the tablespace ORA-01688 error. I checked the free space and found that the free space is available then why tablespace is not able to extend.

select segment_name, partition_name, tablespace_name, round(bytes/1024/1024/1024) gb, extents, max_extents from dba_segments.

Anyone please help on this.

1.Is there a chance that a temporary object (table, index, etc) could be created in this tablespace while this process runs and then dropped once it ends.
2.Is this related to max extents that can be allocated say

initial 5M next 5M max 500M and when it reached 500M it is not able to extend and it throws ORA-01688 but the data file space is available.

Thanks n Advance!

Report message to a moderator

Re: Unable to extend the tablespace ORA-01688 [message #458903 is a reply to message #458901]

Wed, 02 June 2010 08:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

01688, 00000, "unable to extend table %s.%s partition %s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

Report message to a moderator

Re: Unable to extend the tablespace ORA-01688 [message #458907 is a reply to message #458903]

Wed, 02 June 2010 09:18

sainipardeep
Messages: 2
Registered: June 2010
Location: Bangalore

Junior Member

Ya that is fine, one more datafile can be added to tablespace.

My question is that if the max extents that can be allocated to tablespace is say 500M, then it will start ORA-01688. Datafile space is available but autoextend can't allocate as max limit is 500M.

select segment_name, partition_name, tablespace_name, round(bytes/1024/1024/1024) gb, extents, max_extents from dba_segments

The above command tells the free space available in datafile but not for tablespace, right?

select round(BYTES/1024/1024) mb, count(*) from dba_free_space where tablespace_name='?

and this command will tell the free space available of tablespace or datafile. If this is the free space available in tablespace then why unable to extend the tablespace?

Thanks!

Report message to a moderator

Re: Unable to extend the tablespace ORA-01688 [message #458908 is a reply to message #458907]

Wed, 02 June 2010 09:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>If this is the free space available in tablespace then why unable to extend the tablespace?

No CONTIGUOUS free space available.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

use COPY & PASTE so wee can see what you do & how Oracle responds

Report message to a moderator

Re: Unable to extend the tablespace ORA-01688 [message #458913 is a reply to message #458907]

Wed, 02 June 2010 10:41

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

The above command tells the free space available in datafile but not for tablespace, right?

No it gives you space used by segments.

Quote:

and this command will tell the free space available of tablespace or datafile.

As it for the tablespace. Of course, if you group by file_id you have it per file.

Quote:

If this is the free space available in tablespace then why unable to extend the tablespace?

1/ A tablespace does extend, a file can.
2/ If there is free space in tablespace, the question why should it extend? As it, your question is meaningless.

As BlackSwan said the key word is CONTIGUOUS.

Regards
Michel

Report message to a moderator

Previous Topic:	Undo management query?
Next Topic:	MAINTAINING PARTITIONS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 09:08:40 CST 2025