| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: dba_free_space missing a tablespace???
Because, I suspect, that dba_free_space is only measuring free EXTENTS, not amorphous blobs of space that hasn't been laid out yet. In any event, the view gets utterly confused by re-sizes, regardless of tablespace. Consider this little test:
SVRMGR> create tablespace freespace datafile 'c:\free.dbf' size 1m;
Statement processed.
SVRMGR> select * from dba_free_space where tablespace_name='FREESPACE';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKSRELAT
------------------------------ ---------- ---------- ---------- ---------- -
FREESPACE 11 2 1040384 127
11
1 row selected.
SVRMGR> alter database datafile 11 resize 2m;
Statement processed.
SVRMGR> select * from dba_free_space where tablespace_name='FREESPACE';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKSRELAT
------------------------------ ---------- ---------- ---------- ---------- -
FREESPACE 11 2 1040384 127
11
1 row selected.
So, I create a brand new tablespace, 1m big, and get 1m listed in free_space. I resize the file to 2m, and still have only the original 1m of free space. And a shutdown-startup doesn't make the free_space pick up on the extra 1m, either.
On the other hand, when I add a second datafile into tablespace FREESPACE, DBA_FREE_SPACE immediately reports two lines for that tablespace. Clearly, additions of brand new files are picked up immediately, resizes aren't. Which is a bit quirky, but at least it's consistent.
Except for this next test (where I got a bit confused with my megabytes and kilobytes, and ended up with a 600M SYSTEM tablespace!! Guess I'm ready to upgrade to 9i, huh??!):
SVRMGR> select * from dba_free_space where tablespace_name='SYSTEM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKSRELAT
------------------------------ ---------- ---------- ---------- ---------- -
SYSTEM 1 30073 589824 72
1
1 row selected.
SVRMGR> alter database datafile 'C:\ORACLE\ORADATA\HJR\SYSTEM01.DBF' resize
6000
00k;
Statement processed.
SVRMGR> select * from dba_free_space where tablespace_name='SYSTEM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKSRELAT
------------------------------ ---------- ---------- ---------- ---------- -
SYSTEM 1 30073 368050176 44928
1
1 row selected.
Here, I resize a file, and the increase in space is immediately evident.
On the other hand, when I resized SYSTEM back down to a more reasonable 275M, my BYTES in free_space actually went UP!!!
So God alone knows. Resizes clearly confuse the bugger out of it, and additions of files don't. Clearly, we need to know more about the mechanisms underlying free_space.
Jonathan...??? Anyone....??
Regards
HJR
Gerrit Scholten <ggj.scholten_at_worldonline.nl> wrote in message
news:3A70B26E.4E84992C_at_worldonline.nl...
> 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 - 18:41:00 CST
 |
 |