Re: dba_free_space missing a tablespace???

Aaaaaaagh!^2 ?! :)

As I stated, I extended the SYSTEM tablespace by 60 MB, so it grew from 60 MB to 120 MB. Even Oracle8i 8.1.6 doesn't get _that_ much chock-a-block full. So DBA_FREE_SPACE didn't report free extents in SYSTEM, and it didn't report free extents after I double the datafile size to 120 MB. At least 60 MB fresh free space in SYSTEM, and DBA_FREE_SPACE doesn't see it. Strange.

No, the datafile didn't autoextend when I created the table (I checked that :) ). Only after I created tables in SYSTEM, and dropped them, did I see free space in SYSTEM (using DBA_FREE_SPACE).

"Howard J. Rogers" wrote:
>
> 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 - 17:10:38 CST