Re: dba_free_space missing a tablespace???

Aaaaaaagh!

What, pray, do you expect? DBA_FREE_SPACE shows only FREE space within a tablespace. If you haven't got any free space, there's nothing to report (presumably, you wouldn't expect the EMP table to report on employees that you haven't hired yet??). If you DO have free space, it reports on the free space. Used space belong somewhere else (try DBA_EXTENTS).

When you first create a database, SYSTEM is highly likely to be chock-a-block full. Your creating a new table would almost certainly have required the system datafile to autoextend, and the new table extents would have been placed in that extra space When you dropped the table, the extra file space is retained, but is now marked free. Hence, SYSTEM suddenly becomes listed in free extents.

It's certainly not a bug (well, almost certainly -with Oracle you can never be quite sure). It's what's supposed to happen, *if* you think about it.

Regards
HJR Gerrit Scholten <ggj.scholten_at_worldonline.nl> wrote in message news:3A706D67.2C45FB9_at_worldonline.nl...
> I had the same problem with Oracle8i 8.1.6 for Windows NT. In
> DBA_FREE_SPACE tablespace SYSTEM was missing :(.
>
> The database had just been created, no user objects had been created
> yet.
> I managed to let SYSTEM show up in DBA_FREE_SPACE by creating a table in
> SYSTEM, and dropping the table thereafter. DBA_FREE_SPACE only showed
> the space that had been occupied by that table, NOT the other 60 MB that
> I added to SYSTEM by enlarging the datafile. Looks like a genuine bug to
> me.
>
> Gerrit
>
> Ed Stevens 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/
Received on Thu Jan 25 2001 - 15:09:12 CST