Inconsistent information in filext$

From: Ravi Madabhushanam <ravi.madabhushanam@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
Date: Wed, 14 Jul 2010 17:17:18 +0530
Peers,

This is in relation to ongoing discussion on free space monitoring and
autoextensible datafiles.

I do not see much documentation on this table filext$. Its not part of DICT
as well. From http://ss64.com/orad/FILEXT$.html it is supposed to give
information about file extensibility.

There is also a CKE article 428803.1 from MOS, which refers this table to
find out the free space in auto-extensible table spaces. Below is a simple
test why we cannot use this  table for finding out free space in
autoextensible cases.

SQL> select
tablespace_name,file_name,autoextensible,INCREMENT_BY,MAXBLOCKS,maxbytes/(1024*1024),bytes/(1024*1024)
from dba_data_files where tablespace_name='CTXD';

TABLESPACE_NAME                FILE_NAME
     AUT INCREMENT_BY  MAXBLOCKS MAXBYTES/(1024*1024)
------------------------------
-------------------------------------------------- --- ------------
---------- --------------------
BYTES/(1024*1024)
-----------------

CTXD                           /ora_data/oracle/XXXX/ctxd02.dbf
      YES        12800     131072                 1024
              500

SQL> alter database datafile '/ora_data/oracle/XXXX/ctxd02.dbf' autoextend
on maxsize 500M;

Database altered.

SQL> alter database datafile '/ora_data/oracle/XXXX/ctxd02.dbf' resize 550M;

Database altered.

SQL>  select
file_id,tablespace_name,file_name,autoextensible,INCREMENT_BY,MAXBLOCKS,maxbytes/(1024*1024),bytes/(1024*1024)
from dba_data_files where tablespace_name='CTXD';

   FILE_ID TABLESPACE_NAME                FILE_NAME
                 AUT INCREMENT_BY  MAXBLOCKS
---------- ------------------------------
-------------------------------------------------- --- ------------
----------
MAXBYTES/(1024*1024) BYTES/(1024*1024)
-------------------- -----------------
        41 CTXD                           /ora_data/oracle/XXXX/ctxd02.dbf
                YES            1      64000
                 500               550

SQL> select * from filext$ where file#=41;

     FILE#  MAXEXTEND        INC
---------- ---------- ----------
        41      64000          1

I've resized the datafile beyond maxbytes . Still the filext$ shows it can
still grow. I believe filext$ can not be used for auto extensible checks.

Am I interpreting the information wrongly ? Any comments ?
Or this is a known limitation.

Regards,
Ravi.M
Inconsistent information in filext$

Other related posts:
