Re: dba_free_space missing a tablespace???

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 26 Jan 2001 08:30:26 +1100
Message-ID: <3a709f60@news.iprimus.com.au>

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

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/
Received on Thu Jan 25 2001 - 15:30:26 CST