Re: Fixing temp tablespace

Sean Garagan (garagan_at_ug.cs.dal.ca) wrote:

<cut>

: However, when I run a large update, it now looks at this file and
: sees it in recover status and refuses to continue. The database is
: not in archive mode nor are there any backups for this file. Is
: there a way to remove all pointers to this file or to get it into
: the database without having to do a recovery (which cannot be done)?

Since this is only temporary tablespace with nothing else in it (ie, no live data), you can just drop the tablespace, rm all the files and create the tablespace again. Would be faster than a restore from a backup.

Sequence would be similar to the following:

sql> alter tablesapce temp offline;
sql> drop tablespace temp;
% rm temp1.dbs temp2.dbs
sql> create tablespace temp ...

HTH,
Leng.

--
Leng Kaing
DBA - Directory Technology P/L
Email: lkaing_at_ventnds1.telecom.com.au
Ph: +61-3-9892-9081  Fax: +61-3-9892-9344
WWW: http://www.vut.edu.au/voug