Re: Extent management local problem

On 1 May 2003 05:12:15 -0700, postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote:

>Peter <peter_at_nomorenewsspammin.ca> wrote in message news:<jfu1bv8988c8tvr8hguo5ditv9a2ifnv4d_at_4ax.com>...
>> I ran the folllowing:
>>
>> CREATE TABLESPACE temp
>> DATAFILE ?c:\oracle\oradata\db2\temp.dbf? SIZE 100M
>> EXTENT MANAGEMENT LOCAL
>> TEMPORARY;
>>
>>
>> How come it does not allow me to use "local"?
>> and even when it was created, it is shown as a data file in the
>> dba_data_files, but not not a temp file in dba_temp_files?
>
>Check the syntax to create a temporary tablespace in the sql reference manual.
>You have it all wrong.
>
>Sybrand Bakker
>Senior Oracle DBA

The correct syntax for locally managed temporary should be:

 CREATE TEMPORARY TABLESPACE temp
 TEMPFILE 'c:\oracle\oradata\db2\temp.dbf' SIZE 100M  EXTENT MANAGEMENT LOCAL ; For dictionary managed temporary:

 CREATE TABLESPACE temp
 DATAFILE 'c:\oracle\oradata\db2\temp.dbf' SIZE 100M  EXTENT MANAGEMENT dictionary
 TEMPORARY; 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?

The Default temporary tablespace is shown as a temp file in the dba_temp_files table.

What is the difference between Default temp tablespace and the other temporary tablespace that I created later on?

Thanks Received on Thu May 01 2003 - 14:45:01 CDT