Home » RDBMS Server » Server Administration » Unable to extend the tablespace ORA-01688 (Oracle 10g RAC)
Unable to extend the tablespace ORA-01688 [message #458901] Wed, 02 June 2010 08:11 Go to next message
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!
Re: Unable to extend the tablespace ORA-01688 [message #458903 is a reply to message #458901] Wed, 02 June 2010 08:42 Go to previous messageGo to next message
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.
Re: Unable to extend the tablespace ORA-01688 [message #458907 is a reply to message #458903] Wed, 02 June 2010 09:18 Go to previous messageGo to next message
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!
Re: Unable to extend the tablespace ORA-01688 [message #458908 is a reply to message #458907] Wed, 02 June 2010 09:25 Go to previous messageGo to next message
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
Re: Unable to extend the tablespace ORA-01688 [message #458913 is a reply to message #458907] Wed, 02 June 2010 10:41 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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

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


Current Time: Thu Mar 28 03:29:47 CDT 2024