Unable to extend the tablespace ORA-01688 [message #458901] |
Wed, 02 June 2010 08:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #458907 is a reply to message #458903] |
Wed, 02 June 2010 09:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #458913 is a reply to message #458907] |
Wed, 02 June 2010 10:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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
|
|
|