Re: dba_free_space missing a tablespace???

From: <yong321_at_yahoo.com>
Date: Fri, 26 Jan 2001 16:48:06 GMT
Message-ID: <94s9o3$vgm$1@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/ Received on Fri Jan 26 2001 - 10:48:06 CST