Re: dba_free_space missing a tablespace???

From: Gerrit Scholten <ggj.scholten_at_worldonline.nl>
Date: Fri, 26 Jan 2001 00:22:42 +0100
Message-ID: <3A70B542.79B46BC7@worldonline.nl>

B.t.w., have a look at this (sorry, Dutch language version of SQL*Plus, but I added some translations):

<screencopy>
SQL*Plus: Release 8.0.5.0.0 - Production on Ma Jan 8 10:19:45 2001

Verbonden met:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> connect sys/*********@ofptd2k

Verbonden.                                           // connected

SQL> select * from dba_free_space where tablespace_name = 'SYSTEM';

Geen rijen geselecteerd.                             // no rows selected

SQL> create table test ( tekst varchar2(2000) ) tablespace SYSTEM;

Tabel is aangemaakt.                                 // table created

SQL> select * from dba_free_space where tablespace_name = 'SYSTEM';

Geen rijen geselecteerd.                             // no rows selected

SQL> drop table test;

Tabel is verwijderd.                                 // table dropped

SQL> select * from dba_free_space where tablespace_name = 'SYSTEM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES

BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ----------
---------- ------------
SYSTEM                                  1      31257      65536         
8            1

</screencopy>

I'm sorry Howard, but I've never anything seen this before.

"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:22:42 CST