Inconsistent information in filext$

From: Ravi Madabhushanam <ravi.madabhushanam_at_gmail.com>
Date: Wed, 14 Jul 2010 17:17:18 +0530
Message-ID: <AANLkTikF_Quvy92qwCae0Wp6BacQbqwK3-1m-lqyHNXi_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 14 2010 - 06:47:18 CDT

Original text of this message