Re: Temp tbs with no temp file

From: Sinardy <all_about_oracle_at_hotpop.com>
Date: Tue, 5 Jul 2005 10:04:30 +0800
Message-ID: <000e01c58105$e6e90e60$0400a8c0@ncs.corp.intads>

Hi Kirti,

Thanks for the explaination

In addition I also notice that the alert log contain this message

WARNING: The following temporary tablespaces contain no files.

         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:

         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************

Do you guys write script to check the existing of the tempfile? I think I need to add "ADD TEMPFILE" in my startup script.

Regards,
Sinardy

Original Message ----- From: "Kirtikumar Deshpande" <kedeshpande_at_yahoo.com> To: "Oracle-L" <oracle-l_at_freelists.org>; <all_about_oracle_at_hotpop.com> Sent: Tuesday, July 05, 2005 3:52 AM Subject: Re: Temp tbs with no temp file

> Hi,
> Sorry I did not catch all the messages in this thread...
>
> So, is this temporary tablespace the default at database level?
>
> Look in DATABASE_PROPERTIES to check which tablespace is the default
temporary
> tablespace.
>
> select *
> from database_properties
> where property_name = 'DEFAULT_TEMP_TABLESPACE';
>
> If the default temp tablespace is the same that does not have any
tempfiles associated
> with it, then your sorting, so far, did not need to write to sort segments
and was
> completing in memory (same for the hash joins). If those segments needed
to be written to
> temp tablepace without tempfiles you would have seen following error:
>
> ORA-25153: Temporary Tablespace is Empty
>
>
> HTH,
>
> Cheers!
>
> - Kirti
>
>
> --- Sinardy <all_about_oracle_at_hotpop.com> wrote:
>
> > My DB without temp file (tablespace) for like two months during this
time
> > where are the sorting being carried out? I know add temp file will work
but
> > just curious how come I can work without temp file.
> >
> > regards,
> > Sinardy
> >
> >
> >
>
>
>
> __________________________________
> Yahoo! Mail
> Stay connected, organized, and protected. Take the tour:
> http://tour.mail.yahoo.com/mailtour.html
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 04 2005 - 22:51:12 CDT