Re: dba_free_space missing a tablespace???

Ah!

Teach me to be lazy, I guess (though one could have wished for a meaningful error message).

So does this resolve the mystery of why Gerrit's additional 60M didn't show up? His script didn't show how he resized his file, so perhaps it too didn't *really* resize it, and when he created his table, autoextend kicked in ('cos autoextend is on for SYSTEM by default, IIRC).

Cheers Yong!

<yong321_at_yahoo.com> wrote in message news:94s9o3$vgm$1_at_nnrp1.deja.com...
> Howard,
>
> You have a mistake in your ALTER DATABASE DATAFILE statement.
> Unfortunately Oracle silently ignores it. You need to use the filename
> instead of file# per SQL Reference documentation:
>
> SQL> create tablespace test
> datafile '/lurch/disk_4/oradata/TEST/test.dbf' size 1m;
>
> Tablespace created.
>
> SQL> select * from dba_free_space where tablespace_name = 'TEST';
>
> TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
> RELATIVE_FNO
> ------------------------------ --------- --------- --------- --------- -
> -----------
> TEST 4 2 1040384
> 127 4
>
> SQL> alter database datafile 4 resize 2m;
>
> Database altered.
>
> SQL> select * from dba_free_space where tablespace_name = 'TEST';
>
> TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
> RELATIVE_FNO
> ------------------------------ --------- --------- --------- --------- -
> -----------
> TEST 4 2 1040384
> 127 4
>
> SQL> alter database datafile '/lurch/disk_4/oradata/TEST/test.dbf'
> resize 2m;
>
> Database altered.
>
> SQL> select * from dba_free_space where tablespace_name = 'TEST';
>
> TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS
> RELATIVE_FNO
> ------------------------------ --------- --------- --------- --------- -
> -----------
> TEST 4 2 2088960
> 255 4
>
> SQL> alter database datafile 123456 resize 2m;
>
> Database altered.
>
> Note that I do not have a datafile numbered 123456. In fact, I can use
> any number less than or equal to 2000000 (2 million). Above that I get
> ORA-2827. I'm not sure what parameter governs that. My Oracle version
> is 8.1.6.0.0, 32-bit, on Solaris 2.6 Sparc. DB_FILES is set to 200. I
> think MAXDATAFILES is 254 when the database is created (as seen in
> output of alter database backup controlfile to trace).
>
> Yong Huang
> yong321_at_yahoo.com
>
> In article <3a70c7e6_at_news.iprimus.com.au>,
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
> > 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 BLOCKS
> > RELAT
> > IVE_F
> > ------------------------------ ---------- ---------- ---------- ------

• -
  > > ----
  > > -----
  > > 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 BLOCKS
  > > RELAT
  > > IVE_F
  > > ------------------------------ ---------- ---------- ---------- ------
  
• -
  > > ----
  > > -----
  > > 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 BLOCKS
  > > RELAT
  > > IVE_F
  > > ------------------------------ ---------- ---------- ---------- ------
  
• -
  > > ----
  > > -----
  > > 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 BLOCKS
  > > RELAT
  > > IVE_F
  > > ------------------------------ ---------- ---------- ---------- ------
  
• -
  > > ----
  > > -----
  > > 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
  >
  >
  > Sent via Deja.com
  > http://www.deja.com/