Friday Quiz was Re: DBA_DATA_FILES inconsistency?

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 15 Feb 2013 17:06:55 +0000
Message-ID: <CABe10sbyNiuPuervAknvM+YHWVRVmNCC6_LNgxHWPJd9Es8OvA_at_mail.gmail.com>

You can also achieve this quite easily by *manually* resizing an autoextend datafile. Norm is right correctly deciding how much free space a tablespace has is rather complex - especially when you consider that frequently DBAs don't consider the volume size on the volume on which autoextend datafiles are put.
Consider volume /u01 size 100g (and all figures quoted in GB). There are 2 tablespaces on this storage containing files shown below. What should a free space script report as free space for each tablespace?

tablespace ts1 has datafiles
file current_size maxsize autextend free_extents

f1             10       30       yes              5
f2             20       15       yes              5
f3             20       30       yes              5

tablespace ts2 has datafiles
file current_size maxsize autextend free_extents

f4             10                 no             0
f5             10       30       yes             0

On Fri, Feb 15, 2013 at 4:30 PM, Norman Dunbar <oracle_at_dunbar-it.co.uk>wrote:

> Hi Rich,
>
> 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
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


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

Received on Fri Feb 15 2013 - 18:06:55 CET