I get this error when I run a query against my Oracle db, which I believe indicates I need to expand my dbf file size:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
Based on that, I found this SO answer, which advises running the following:
select value from v$parameter where name = 'db_block_size';
That returns a value of 8192
. The same SO answer advises running an autoextend if the above returns a 'low max size'. So I decided to try that approach. I first get the location of my dbf file(s):
select name from v$datafile;
Which returns these 6 rows:
/opt/oracle/product/11.2.0/xe/dbs/system.dbf
/opt/oracle/product/11.2.0/xe/dbs/sysaux.dbf
/opt/oracle/product/11.2.0/xe/dbs/undotbs1.dbf
/opt/oracle/product/11.2.0/xe/dbs/users.dbf
/opt/oracle/deploy/oradata/my_db_01.dbf
/opt/oracle/deploy/oradata/my_db_02.dbf
So I try to run an autoextend of my_db_01.dbf
:
alter database datafile /opt/oracle/deploy/oradata/my_db_01.dbf autoextend on maxsize unlimited;
But that gives me:
ERROR at line 1:
ORA-02236: invalid file name
Why is the file name invalid when I'm pulling it directly from my v$datafile query?
CodePudding user response:
The error you're getting is that you've run out of space in TEMP
. If you're running a query, that is the space that Oracle uses for things like sorts and the like. While it is certainly possible that you'd really want to extend the size of your TEMP
tablespace, it is much more common that the issue is that the particular query you're running has a bug that is causing it to use excessive amounts of TEMP
space. If, for example, you've missed a join condition and you're creating a Cartesian product, throwing TEMP
space at the problem is unlikely to fix it-- you'd need to fix the query.
If you're certain that the query is written correctly and you believe it is reasonable for it to need whatever TEMP
space it is consuming, you'd need to look at v$tempfile
or dba_temp_files
to see the files associated with your temporary tablespace.
select file_name,
tablespace_name,
bytes/1024/1024 current_size_gb,
maxbytes/1024/1024 max_size_gb,
autoextensible
from dba_temp_files;
If the TEMP
files aren't already set to autoextend, you could set them to autoextend (note that the file_name
has to be enclosed in single quotes)
alter database tempfile '<<path>>' autoextend on;