Question about understanding ORA-01652 error

[eseabrook2008]

This error has started coming up in the last couple days when we run queries that we've run many times in the past. I've googled the error and read many articles about what it means and how to resolve it but I'd like to understand why it's coming up in our particular situation. A quick look at the TEMPDATA01 tablespace in Oracle Storage Manager shows the file size is 63GB with 2GB used. Auto Extend is enabled with an increment of 1GB and a max extent of 63GB. I ran this query:

select file_name, bytes, autoextensible, maxbytes
from dba_data_files
where tablespace_name='TEMPDATA1'

and it returned:

FILE_NAME BYTES AUT MAXBYTES
-------------------------- ---------- --- ----------
D:\...\TEMPDATA01.IBS 6.6060E+10 YES 6.8719E+10

Shouldn't there be enough room to extend?
The acutal error changes depending on the query but this particular error is:
Select error: ORA-01652: unable to extend temp segment by 655360 in tablespace TEMPDATA1.

 

[karluk]

Are you sure that Oracle can even use such a large data file? According to the documentation, Oracle rdbms version 8i through 10g are limited to 4194303 addressable blocks per data file. So the effective limit on file size is dependent on the value of db_block_size. You would need a 16k block size to be able to reference the high end blocks of a 63 GB data file.

 

[eseabrook2008]

All I know is it has been working fine up until recently. With auto extend on, does that mean the datastore can grow (to the size of the datafile) or that the datafile can grow based on the specified increments? Or does that even make sense?

 

[karluk]

Please log into sql*plus as run the command

show parameter db_block_size

 

[eseabrook2008]

The result of:

show parameter db_block_size

is:

NAME TYPE VALUE
--------------- ------- ------
db_block_size integer 16384

 

[karluk]

Ok, your block size checks out. That means Oracle is having real problems finding free space in tablespace TEMPDATA01.

The first thing to understand is calculate how much free space is really needed. According to the documentation on ORA-01652 errors

The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

So, the text of your error indicates that Oracle can't find 655360 contiguous free blocks. Multiply by the 16384 block size, and you actually need 10 GB of free space. You are too close to the max size of your data file to get this amount of space by extending the data file, but you indicate that Storage Manager says that only 2 GB of the tablespace are actually in use.

I infer that the problem is that the existing segments in the tablespace are so fragmented that there are no longer 10 GB of contiguous free space available. You could get around this problem by adding a second data file to the tablespace, but if you don't want to continue throwing disk space at the problem, you will need to reorganize the tablespace to make all the 61 GB of free space contiguous.

 

[eseabrook2008]

So run:

alter tablespace TEMPDATA01 coalesce;

?

I thought SMON took care of this periodically? It should be noted that is a dictionary-managed tablespace.

 

[karluk]

I would go ahead and try to coalesce the free space. There's no guarantee it will work, but it's an easy fix if it does.

 

[eseabrook2008]

Before I go any further, I guess I should clarify something. I just went through the Storage Manager again and I mentioned the wrong numbers. The Datafile is still 63GB and there is still only 2GB used but the extent on the Datastore is:
Initial size: 304 KB
Next size: 160 KB
Min size: 0 KB
Increase by 100%
Min Number: 1
Max Number: 1017

So what's the difference between the Extend on the Datastore and the Extend on the Datafile?

 

[eseabrook2008]

Also, this is a prod DB. What are the implications of running

alter tablespace TEMPDATA01 coalesce;

while users are accessing the system?

 

[carp]

I would recommend taking a look at dba_free_space for that tablespace. Looking at the count(*) and max(bytes) should tell you if coalescing is going to help or not. It is doubtful that users will notice anything while you are coalescing; however, it might be prudent to do this during a slack period if possible.

 

[eseabrook2008]

I've been advised that I need to re-create that tablespace. The temp tablespace is set to permanent so I've been advised to recreate it as temporary.