Notification of next extent allocation failure 2

[stephenwales]

We have recently converted our databases from a combination on LMT auto extent allocation and DMT to all being LMT with Uniform Extent Allocation.

One of the things I need to do now, is to set up a monitor that will inform me that I have an object that can't grow to its next extent.

In Oracle 8, I might have done something like this:

select table_name 
from   dba_tables a
where  next extent > 
       (select max(bytes) 
        from   dba_free_space b
        where  a.tablespace_name = b.tablespace_name);

Not sure if that would get me exactly what I want, but it's near enough - the theory behind it being that if at any time for any object in the database, the next required extent is greater than the largest amount of contiguous freespace in the tablespace, then I, as a DBA, need to know about it so that action can be taken before a failure occurs.

One thing that could be done, I suppose, is auto-extendable tablespaces - but we don't use that feature here, so that's out.

Doing some research on uniform extent sizes, it appears that the only time we'll get a problem is when every extent in the tablespace is used, hence there's no space left to grow into a new one - problem with this is that once the last extent is allocated, that there's no longer any records in DBA_FREE_SPACE.

I'm wondering if the solution to my problem here is something as simple as this:

select tablespace_name 
from   dba_tablespaces 
where  tablespace_name not in 
       (select tablespace_name from dba_free_space)
and    contents not in ('TEMPORARY', 'UNDO');

Is it truly this simple, or am I missing something here?

Steve

 

[SantaMufasa]

Stephen,

I am puzzled about your organisation's hesitance to use AUTOEXTEND-mode files. If you run out of space in your existing files, what are your options? They seem to me to be either to manually extend your existing files, or to manually create a new file. Either option involves manually taking up space in your file system that AUTOEXTEND would be much more efficiently allocating on a just-in-time, as-needed basis. What are the justifications for proscribing AUTOEXTEND in your shop?

I, too, use LMT with uniform extent sizes. I believe your best investment of time, however, is to monitor your tablespace availability at the file level rather than attempt to micro manage at the object level. Specifically, monitor the freespace that exists in your tablespace data files.

Here is my "freespace.sql" script and its output, that I use to monitor consumption and to determine if I need to add more datafiles. (I have widened the display to avoid unsightly wraparound of the output.):

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- ---------------------------------------------------
DATA1              4    2,097,152,000       83,886,080       17,629,184    2,079,522,816     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
DATA2              5    2,097,152,000      241,172,480          917,504    2,096,234,496     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF'
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1    2,097,152,000      163,577,856      157,401,088    1,939,750,912     92 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       10,485,760            8,192    2,097,143,808     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      520,093,696      176,021,504   10,309,738,496

Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
********************************************************************************************************************************************************

Let us know if this method sounds useful.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[stephenwales]

Dave,

I think I'm going to use a combination of the two - the datafile level script and the simple table one I did above (it will need a little tweaking for the indexes too, I suppose).

We don't use Autoextend more as a matter of personal preference than anything else. There's 2 of us and we both would rather have the manual control over tablespace growth.

There is a level of manual effort involved in extended your tablespaces as you collect more data no matter which way you go. In an autoextending environment, you need to make sure that you have enough space in your filesystem for Oracle to come along and say "gimme more room". If you don't have that, you need to get an SA to allocate you more space.

In the environment we run, we keep our filesystems at 95%+ utilization and add more space as required. We feel we have more control this way. The amount of effort involved, as far as we see it, is much the same when it comes to the growth of the tablespaces/datafiles/filesystems, it's just a matter of when you do the work.

Of course, I'm also that most dangerous of creatures, a DBA who has root access (I'm also the secondary sysadmin), so adding disks to a filesystem to allow it to extend is not that much as a hassle to begin with

I've yet to see a convincing argument to change my mind over to the autoextend arena other than if you make sure that your filesystems have room to grow (which requires monitoring of filesystem space) then you never get unable to extend errors.

However, if you have proper monitoring of tablespace usage and extend accordingly, then you still should never see the unable to extend error messages.

Sorry for rambling on, but hey, you asked

Steve