Re: temporary tablespace usage

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 30 Nov 2002 07:07:17 +1100
Message-ID: <mFPF9.86504$g9.243485@newsfeeds.bigpond.com>

You're using a proper datafile as your temporary tablespace?

IE, you did:

create tablespace TEMP datafile 'c:\blah.dbf' size 40m temporary;

...and not...

create temporary tablespace TEMP tempfile 'c:\blah.dbf' size 40m;

To be certain, what is the output from dba_tablespaces for your temp tablespace? If it says 'LOGGING' for it, its a temporary tablespace of the first sort.

Regards
HJR "Marcin Buchwald" <Marcin.Buchwald_at_agora.pl> wrote in message news:3DE74986.B4C9EA1_at_agora.pl...
> Could You explain why 3rd select results with no rows?
>
> 11:58:19 SPA.SPAC.GWSPACER> select * from v$temp_extent_pool;
>
> TABLESPACE_NAME FILE_ID EXTENTS_CACHED EXTENTS_USED
> BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
> ------------------------------ ---------- -------------- ------------
> ------------- ----------- ------------ ---------- ------------
> TEMP 1 20
> 0 5120 0 20971520 0 1
>
> Całkowity: 00:00:00.10
> 11:58:42 SPA.SPAC.GWSPACER> select * from dba_temp_files;
>
> FILE_NAME
> --------------------------------------------------------------------------

---

---

>
> D:\SPAC\TEMP01.DBF
>
> Całkowity: 00:00:00.51
> 11:58:49 SPA.SPAC.GWSPACER> select * from dba_temp_files,
> v$temp_extent_pool;
>
> no rows selected
>
> Całkowity: 00:00:00.00
> 11:58:56 SPA.SPAC.GWSPACER> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> PL/SQL Release 8.1.7.0.0 - Production
> CORE 8.1.7.0.0 Production
> TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
> The backdoor statement is
>
> select * from
> (select bytes from dba_temp_files) f,
> (select bytes_cached,bytes_used from v$temp_extent_pool) p
>
> What's happening?
>
> Regards,
>
> Marcin
>
Received on Fri Nov 29 2002 - 14:07:17 CST