Very interesting - your clarifications are GREAT, but I'm not sure why
you started off your email with such a misleading comment as:
>>Temporary tablespace most certainly DOES show up in dba_free_space
Temporary tablespaces do NOT show up in dba_free_space. A temporary
tablespace is the following:
create temporary tablespace ...
Now you can create a tablespace whose contents are temporary as in:
create tablespace foo ...
alter tablespace temporary ...
this is a normal tablespace that contents are considered temporary. Not
the same thing. Creating a temporary tablespace creates the tablespace
as nologging, locally managed and temporary. Altering a tablespace
temporary only changes the content type. It doesn't set the nologging
or locally managed (of course making a data dictionary tbs into a
locally managed would be a pain). Try to create a locally managed
tablespace and alter temporary and you get ora-03218, as you have found.
Best
Kyle
In article <3a709f60_at_news.iprimus.com.au>,
"Howard J. Rogers" <howardjr_at_www.com> wrote:
> Temporary tablespace most certainly DOES show up in dba_free_space,
although
> in ordinary use, you can expect the entire thing to be full of
temporary
> segments, and thus not have any free space (and hence not show). But
when a
> database is freshly started, and no users have started doing sorts,
> temporary tablespace WILL be listed.
>
> The following is off my test system:
>
> SVRMGR> select * from dba_free_space;
> TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
> RELAT
> IVE_F
> ------------------------------ ---------- ---------- ---------- ------
- -
> ----
> -----
> SYSTEM 1 30073
589824 72
> 1
> SYSTEM 1 30049
65536 8
> 1
> RBS 2 8770
1564672 191
> 2
> RBS 2 3650
524288 64
> 2
> RBS 2 2754
524288 64
> 2
> USERS 3 2 20963328
2559
> 3
> TEMP 4 250 18931712
2311
> 4
> TOOLS 5 2 10477568
1279
> 5
> INDX 6 2 20963328
2559
> 6
> DRSYS 7 490 16965632
2071
> 7
> TEMP2 8 9 10420224
1272
> 8
>
> And from DBA_TABLESPACES:
> SVRMGR> select * from dba_tablespaces;
> TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT
MAX_EXTENT
> PCT_I
> NCREA MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO
PLU
> ------------------------------ ---------- ---------- ---------- ------
- -
> ----
> ----- ---------- --------- --------- --------- ---------- --------- --
-
> SYSTEM 65536 65536 1
2147483645
> 50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
> RBS 524288 524288 8
4096
> 50 524288 ONLINE PERMANENT LOGGING DICTIONARY USER NO
> USERS 131072 131072 1
4096
> 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
> TEMP 65536 65536 1
> 0 65536 ONLINE TEMPORARY LOGGING DICTIONARY USER NO
> TOOLS 32768 32768 1
4096
> 0 32768 ONLINE PERMANENT LOGGING DICTIONARY USER NO
> INDX 131072 131072 1
4096
> 0 131072 ONLINE PERMANENT LOGGING DICTIONARY USER NO
> DRSYS 65536 65536 1
2147483645
> 50 65536 ONLINE PERMANENT LOGGING DICTIONARY USER NO
> WHACKO 1048576 1048576 1
> 0 1048576 ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM NO
> TEMP2 65536 1
2147483645
> 65536 ONLINE PERMANENT LOGGING LOCAL SYSTEM NO
>
> Notice that TEMP is proper TEMPORARY tablespace, dictionary managed,
using
> datafiles. TEMP2 is PERMANENT tablespace, locally managed, using
datafiles
> (ie, it's really just normal tablespace). Both are listed in
FREE_SPACE.
>
> WHACKO is proper TEMPORARY tablespace, locally managed, using
tempfiles.
> That ISN'T in FREE_SPACE (but since there are no sorts going on, I
might
> have expected it to be).
>
> So it appears to be true is that temporary tablespace created with the
> "tempfile" keyword instead of the old "datafile" one is not listed,
but good
> old-fashioned temporary tablespace most definitely is.
>
> Incidentally, and something I wasn't aware of, it's fairly easy to
issue the
> command 'alter tablespace blah TEMPORARY' to turn it into proper
temporary
> tablespace -but the command won't work on locally managed tablespaces
that
> were created using the 'datafile' keyword. As ever, the error
message is a
> master of obfuscation: 'invalid option for create/alter tablespace'.
I
> can't think why it should be so hard to turn datafile-locally managed
> tablespace into temporary tablespace.
>
> Regards
> HJR
>
> <hailey_kyle_at_my-deja.com> wrote in message
> news:94ptdc$tv4$1_at_nnrp1.deja.com...
> > Good points, but the tbs could be a temporary type tablespace
> > temporary tablespace don't show up in
> > v$datafile
> > dba_data_files
> > dba_free_space
> > but do show up in
> > dba_tablespaces
> > v$tempfile
> > dba_temp_files
> >
> > I haven't played with the temporary files that much. Finding free
space
> > in them doesn't seem totally straight forward. Currently I'm using
the
> > v$sort_segment, but would be currious as to what other people use to
> > monitor the space in the temporary files.
> >
> > Best
> > Kyle
> >
> >
> > In article <94pr6e$rlv$1_at_nnrp1.deja.com>,
> > yong321_at_yahoo.com wrote:
> > > It simply means you don't have free space in that tablespace.
> > >
> > > It's not true that read only tablespaces or locally managed
tablespaces
> > > will not show in dba_free_space.
> > >
> > > Yong Huang
> > > yong321_at_yahoo.com
> > >
> > > In article <94n56a$g95$1_at_nnrp1.deja.com>,
> > > Ed Stevens <ed.stevens_at_home.com> wrote:
> > > > Here's an odd one. I SELECT DISTINCT TABLSPACE_NAME from 3
different
> > > > views: dba_tablespaces, dba_data_files, and dba_free_space. I
should
> > > > get the same results, but look at the following, and you'll see
that
> > > > dba_free_space returned one less tablespace name . . . .
> > > >
> > > > SQL> select tablespace_name
> > > > 2 from dba_tablespaces
> > > > 3 order by tablespace_name;
> > > >
> > > > TABLESPACE_NAME
> > > > ------------------------------
> > > > APHDTATS
> > > > APHIDXTS
> > > > APHRB1TS
> > > > APHRB2TS
> > > > APHTMPTS
> > > > SYSTEM
> > > >
> > > > 6 rows selected.
> > > >
> > > > SQL> select distinct tablespace_name
> > > > 2 from dba_data_files
> > > > 3 order by tablespace_name;
> > > >
> > > > TABLESPACE_NAME
> > > > ------------------------------
> > > > APHDTATS
> > > > APHIDXTS
> > > > APHRB1TS
> > > > APHRB2TS
> > > > APHTMPTS
> > > > SYSTEM
> > > >
> > > > 6 rows selected.
> > > >
> > > > SQL> select distinct tablespace_name
> > > > 2 from dba_free_space
> > > > 3 order by tablespace_name;
> > > >
> > > > TABLESPACE_NAME
> > > > ------------------------------
> > > > APHDTATS
> > > > APHIDXTS
> > > > APHRB2TS
> > > > APHTMPTS
> > > > SYSTEM
> > > >
> > > > 5 rows selected.
> > > >
> > > > SQL> spool off
> > > >
> > > > --
> > > > - Ed Stevens
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> > >
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>
Sent via Deja.com
http://www.deja.com/
Received on Fri Jan 26 2001 - 00:40:10 CST
