Re: Q: inc. size of temp tablespace

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 11 Jun 2004 09:13:21 +0200
Message-ID: <cablq1$v15$1@news3.tilbu1.nb.home.nl>

Vince Laurent wrote:
> Why when I do this I get an error:
>
> SQL> alter database datafile '/oracle/SND/sapdata1/temp/temp1.data'
> resize 200M;
> alter database datafile '/oracle/SND/sapdata1/temp/temp1.data' resize
> 200M
> *
> ERROR at line 1:
> ORA-01516: nonexistent log file, datafile, or tempfile
> "/oracle/SND/sapdata1/temp/temp1.data"
>
> when the file and the path exists?
>
> sapsnd:orasnd 32> pwd
> /oracle/SND/sapdata1/temp
> sapsnd:orasnd 33> ls -lt
> total 200848
> -rw-rw-rw- 1 orasnd dba 104865792 Jun 7 15:41 temp1.data
>
> Here is what I am running:
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
> PL/SQL Release 9.2.0.4.0 - Production
> CORE 9.2.0.3.0 Production
> TNS for HPUX: Version 9.2.0.4.0 - Production
> NLSRTL Version 9.2.0.4.0 - Production
>
> In other words, what is the easiest way to increase the size
> of the temporary tablespace?
>
> Thanks!
> Vince
>
> p.s. Tried:
> SQL> alter tablespace psaptemp1
> 2 add datafile '/oracle/SND/sapdata1/temp/temp1.data2' size 200M;
> alter tablespace psaptemp1
> *
> ERROR at line 1:
> ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
>

s/datafile/tempfile/g
or -in plain english- replace the word datafile with tempfile. Try a select name from v$datafile; -you will not see your file. Now try select name from v$tempfile; - there it is!

There's now an option to use temporary files for temp tablespaces. You may want to check your backup strategy (peek in the trace, generated by alter database backup controlfile to trace, it will recreate the tempfile):

SQL> select name from v$datafile;
NAME

---

/opt/oracle10/1001/oradata/O1001/datafile/o1_mf_system_02o782mm_.dbf /opt/oracle10/1001/oradata/O1001/datafile/o1_mf_undotbs1_02o78v69_.dbf [snipped for brevity]

SQL> c/datafile/tempfile

   1* select name from v$tempfile
NAME

---

/opt/oracle10/1001/oradata/O1001/datafile/o1_mf_temp_02o79bmm_.tmp

SQL> alter database backup controlfile to trace Last lines:
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE
'/opt/oracle10/1001/oradata/O1001/datafile/o1 _mf_temp_02o79bmm_.tmp'

      SIZE 23068672 REUSE AUTOEXTEND ON NEXT 2097152 MAXSIZE 2050M; -- End of tempfile additions.

-- 

Regards,
Frank van Bortel

Received on Fri Jun 11 2004 - 02:13:21 CDT