Re: Extent management local problem

On Fri, 2 May 2003 12:12:45 +0200, "Peter van Rijn" <p.vanrijnRM_at_THISzhew.nl> wrote:

>> >
>> >> 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
>

So essentially we have
1) locally managed temporary tablespace contaning tempfiles

and

2) dictionary managed temporary tablespace containing datafiles

When we add files to a locally managed temporary tablespace, we can only add tempfiles?
Similarily, when we add files to a dict managed temporary tablespace, we can only add datafiles?

Thanks Received on Fri May 02 2003 - 17:40:47 CDT