Wednesday, March 07, 2007

Monitoring Oracle Data File For Can't Extend

There are several pitfalls when creating SQL that will monitor for a can't extend condition in the Oracle database.

This appears to show a data file larger than the maximum size.

SQL> select substr(FILE_NAME,22,23) as data_file_name, bytes, maxbytes, maxblocks, USER_BYTES, AUTOEXTENSIBLE from dba_data_files where tablespace_name='CUST_BILL_INV_TAX'

DATA_FILE_NAME BYTES MAXBYTES MAXBLOCKS USER_BYTES AUT
------------------------- ---------------- ---------------- ------------ ---------------- ---
cust_bill_inv_tax02.dbf 34,129,051,648 34,359,721,984 4,194,302 34,078,720,000 YES
cust_bill_inv_tax01.dbf 34,358,689,792 34,358,689,792 4,194,176 34,078,720,000 YES
cust_bill_inv_tax03.dbf 16,532,635,648 2,147,483,648 262,144 16,252,928,000 YES





SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production



Oracle Note:197244.1 explains this situation.
The MAXBYTES column in DBA_DATA_FILES is not updated when the datafile has been resized. The only column that is updated is BYTES. The MAXBYTES is updated by the ALTER DATABASE command with MAXSIZE option as described above.

No comments:

Post a Comment