Re: DBA_DATA_FILES inconsistency?

Like Norman, I also wrote a script to check tablespace freespace considering all these details: maxblocks of dba_data_files can be larger than blocks, and not all datafiles in a tablespace are set to autoextensible. I define %max_free as percent of free space as if all datafiles in the tablespace were extended to maxsize now. (See "in case some datafiles are autoextensible" at http://yong321.freeshell.org/oranotes/TablespaceFreespace.txt )

Niall's message reminds us that the defined autoextension limit may not be reached due to lower layer space restriction. I would still run a tablespace free space check as usual. The lower layer space check can be done separately, either as ASM or filesystem (using df) free space check.

Somewhat related to this topic. Even if you never allow datafile autoextension, tempfiles need special attention. Since they're sparse files when created, you could create one whose space requirement can't be physically met due to lower layer space restriction and the problem occurs only later when all space is allocated.

Yong Huang

--
http://www.freelists.org/webpage/oracle-l