oracle alter statement to expand dbf file size

Question

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?

Answer 1

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;