Re: Extent management local problem

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:grs5bvgtq226cl4vr0316qb8hbvrcjgh7v_at_4ax.com...
> 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
>
>
>
>
>
>

Just THINK: do any other options make sense? No, they don't

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address