Re: Extent management local problem

From: Peter van Rijn <p.vanrijnRM_at_THISzhew.nl>
Date: Fri, 2 May 2003 12:12:45 +0200
Message-ID: <vb4h08qrm0qn0a@corp.supernews.com>

> >
> >> But the following won't work for locally managed, still don't know
> >> why:
> >>
> >> CREATE TABLESPACE temp
> >> DATAFILE 'c:\oracle\oradata\db2\temp.dbf' SIZE 100M
> >> EXTENT MANAGEMENT local
> >> TEMPORARY;
> >>
> >> My question is that why is a locally managed temporary tablespace
> >> shown as a data file in the dba_data_files table, but not as a temp
> >> file in dba_temp_files table?
> >
> >Datafile specifies a datafile,tempfile specifies a tempfile - clever huh?
>
>
> Why is a temporary file treated as a datafile? Why is it designed this
> way? A temporary file created with a datafile clause, and then listed
> as a datafile. It should be created with a tempfile clause instead and
> listed as a tempfile.
>

First:

SQL> create tablespace temp2
2 datafile '/oradata/eaglet/temp02.dbf' size 10m 3 extent management local
4 temporary;
create tablespace temp2
*
ERROR at line 1:
ORA-25144: invalid option for CREATE TABLESPACE with TEMPORARY contents

Second:

If you want to create a "locally managed" temporary tablespace cannot use this form of tablespace creation. You will have to use the CREATE TEMPORARY TABLESPACE form of the statement.

Third:

If you use this alternate form you will HAVE to use the TEMPFILE clause. I don't know how the tempfiles are implemented on Wondows, but on Unix they are created as so-called sparse files. That's why this sort of tablespace creation is so incredibly fast.

Fourth:

CREATE TABLESPACE ....TEMPORARY uses ordinary datafiles, and are visible through DBA_DATA_FILES

CREATE TEMPORARY TABLESPACE ... uses the 'new' tempfiles and, hence, are visible through DBA_TEMP_FILES

A temp file is not treated as a datafile as you stated, Don't mix up a temporary tablespace (which we have in two flavours) with temporary file (only one flavour).

hth,
Peter Received on Fri May 02 2003 - 05:12:45 CDT