Re: DBA_DATA_FILES inconsistency?

On 15/02/13 16:23, Rich wrote:
> Hi List,
> This is Oracle EE 10.2.0.5 on Solaris 5.10.
>
>
>>From SQL:
> select FILE_NAME, FILE_ID, TABLESPACE_NAME, BLOCKS, STATUS, AUTOEXTENSIBLE,
> MAXBLOCKS, INCREMENT_BY, USER_BLOCKS, ONLINE_STATUS
> from dba_data_files where tablespace_name like 'HUB%';
>
> I see the following:
> FILE_NAME FILE_ID TABLESPACE_NAME BLOCKS
> STATUS AUT MAXBLOCKS INCREMENT_BY USER_BLOCKS ONLINE_
> ----------------------------------- ---------- --------------- ------------
> --------- --- ------------ ------------ ------------ -------
> /u01/insta/data/hubaaaa_01.dbf 10 HUBAAAA 3,840,000
> AVAILABLE YES 2,560,000 1280 3,839,984 ONLINE
> /u01/insta/data/hubaaaaindx_01.dbf 2 HUBAAAAINDX 1,190,400
> AVAILABLE YES 2,560,000 1280 1,190,392 ONLINE
>
> Am I missing something?
>
> Shouldn't MAXBLOCKS be larger than BLOCKS (and USERBLOCKS) for FILE_NAME
> /u01/insta/data/hubaaaa_01.dbf (FILE_ID 10)?
>
> Anyone seen anything like this?

I've not only seen this, I once wrote the SQL from hell to be run as a Nagios tablespace usage check. AQs far as I remember, it was 9.2 on HP-UX and I think the cause was the fact that the data file was put into autoextend mode, with a max size smaller than the current actual size.

My SQL had to consider the fact that in a tablespace not all data files are autoextend, and that the max size might already be smaller than the actual size.

Nightmare!

So, yes I've seen it, and no I wouldn't be too worried, but I suspect that those two data files will not be extending any time soon! The other two will have to cope.

HTH Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l