Help with a query 1

[DrSeussFreak]

I have a script running on a server that runs Oracle 9i and it looks for auto extensible files that can not extend anymore.

SELECT FILE_NAME||' '||BYTES||' '||MAXBYTES FROM DBA_DATA_FILES WHERE autoextensible = 'YES' and maxbytes-bytes < 2097152 order by bytes;

I need this code to be modified so that is the file that it pulls is extended to its max size, BUT another file exists in the same tablespace, it does not alert me. Currently, I keep getting the following alert every day

FILE_NAME||''||BYTES||''||MAXBYTES
--------------------------------------------------------------------------------
Disk:[Path]Tablespace_Name_01.DBS 4294967296 4294967296

Any thoughts? I wrote

SELECT FILE_NAME, BYTES, MAXBYTES
FROM DBA_DATA_FILES 
WHERE autoextensible = 'YES' 
AND maxbytes-bytes < 2097152
AND file_name in (SELECT FILE_NAME from dba_data_files where autoextensible = 'YES' and maxbytes-bytes < 2097152)
order by bytes;

but I fear it will just suppress all alerts, so I have no implemented it.

Thanks in advance!

 

[DrSeussFreak]

I have also tried joining the table to itself, but I have no idea where to go with that

SELECT a.FILE_NAME, a.BYTES, a.MAXBYTES
FROM DBA_DATA_FILES a, dba_data_files b
WHERE a.FILE_NAME = b.FILE_NAME
AND a.autoextensible = 'YES' 
AND a.maxbytes-a.bytes < 2097152
order by bytes;

 

[SantaMufasa]

DrSeussFreak,

Following is output from my "Freespace.sql" script(appropriately divided to accommodate Tek-Tips screen-width limitations):

                                Total            Total                  
Tablespace                  Potential          Current                  
Name               #        File Size        File Size       Bytes Used
--------------- ---- ---------------- ---------------- ----------------
DATA1              4    2,097,152,000      104,857,600       37,027,840
RBS                2    2,097,152,000       73,400,320       27,394,048
SYSTEM             1    2,097,152,000      142,606,336      135,118,848
TEMP               3    2,097,152,000       10,485,760        1,073,152
TFQLTY             5    2,097,152,000       73,400,320       65,601,536
                     ---------------- ---------------- ----------------
sum                    10,485,760,000      404,750,336      266,215,424

(The output, below, are continuations of the output lines, above):

                 % Free
                     of
                   Pot.
       Potential  Total Auto
      Bytes Free  Bytes Ext. Filename
---------------- ------ ---- -------------------------------------------
   2,060,124,160     98 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTDATA01.DBF'
   2,069,757,952     98 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTRBS01.DBF'
   1,962,033,152     93 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTSYS01.DBF'
   2,096,078,848     99 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTTEMP01.DBF'
   2,031,550,464     96 Yes  'D:\DHUNT\ORACLEDATA\DHUNT\DHUNTTFQLTY.DBF'
----------------
  10,219,544,576

As the value in column "% Free of Potential Total Bytes" approaches zero, then it means that that particular file is approaching its maximum capacity. You are welcome to use the "freespace.sql" script as either an alternative or an adjunct to your current warning mechanisms.

Here is the code for "freespace.sql":

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".

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

Mufasa,

As always your scripts are amazing. This particular script seems to pull A LOT more data than we actually want to see. Could you possible vie wmy original code

SELECT FILE_NAME||' '||BYTES||' '||MAXBYTES 
FROM DBA_DATA_FILES 
WHERE autoextensible = 'YES' 
AND maxbytes-bytes < 2097152 order by bytes;

and fulfill my request? LOL, you always go one step beyond lol (not a bad thing)!

 

[SantaMufasa]

Currently, I keep getting the following alert every day:

Disk:[Path]Tablespace_Name_01.DBS 4294967296 4294967296

This simply means that this file has reached its 4GB capacity. Since you were wanting to receive alerts for files that have less than 2MB available, you will always see this file (unless you expand the file's current autoextending capacity to a larger size.)


So, Please restate what you would like to have happen.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

Ok, What I would like is for the script to see that yes, this file is at it;s capacity, BUT there is a second file that is not at it's capacity (or a 3rd) and so the tablespace can still actually continue to grow.

 

[DrSeussFreak]

Basically, I need the script to tell me when all the files in tablespaces that are autoextensible are full, and a new file needs to be added.

 

[SantaMufasa]

Ah...Sorry for my dimness. Now I understand.

I seems to me that what you want instead of your previous code is:

select tablespace_name||' '||
       sum(bytes)||' '||
       sum(maxbytes) "Tablespace Approaching Full"
from dba_data_files
having sum(maxbytes)-sum(bytes) < 2097152
group by tablespace_name;

Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

that looks great, but every day I am going to have this report run, how could I make it just return the ones that need attention?

 

[SantaMufasa]

It seems to me that with the revised code, any tablespace with less than 2MB available, in fact, needs attention. Remember, the revised code takes into account all of the available space of all files, in combination, for a tablespace.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

SantaMufasa,

I bow to your knowledge. With 1 small change your code is the perfect solution to my problem

select tablespace_name||' '||
       sum(bytes)||' '||
       sum(maxbytes) "Tablespace Approaching Full"
from dba_data_files
where autoextensible = 'YES'
having sum(maxbytes)-sum(bytes) < 2097152
group by tablespace_name;

Thanks-you very much sir!

 

[SantaMufasa]

You are very welcome.

...where autoextensible = 'YES'

Even if "autoextensible = 'NO'", do you not want to see when the available space drops below 2MB?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

We have other scripts that look for non-autoextensible files. This script was specifically meant for autoextensible files. For the non-autoextensible files, I have a script written that looks for how many extents are left, and when there are 4 or less it lets me know.

I will share this script since you helped me so much

set pause off
set linesize 90

create table tspace_extents as select tablespace_name, bytes
from dba_free_space
/
create table table_next_extent
as select tablespace_name, table_name, next_extent from dba_tables
where owner not in ('SYS', 'SYSTEM')
/
create table index_next_extent
as select tablespace_name, index_name, next_extent from dba_indexes
where owner not in ('SYS', 'SYSTEM')
/

select table_name, A.tablespace_name, next_extent/1024/1024 NEXT_MEGS
,sum(trunc(bytes/next_extent)) EXTENTS
from table_next_extent A, tspace_extents B
where A.tablespace_name = B.tablespace_name
having sum(trunc(bytes/next_extent)) < 5
group by table_name, A.tablespace_name, next_extent/1024/1024 
/
select index_name, A.tablespace_name, next_extent/1024/1024 NEXT_MEGS
,sum(trunc(bytes/next_extent)) EXTENTS
from index_next_extent A, tspace_extents B
where A.tablespace_name = B.tablespace_name
having sum(trunc(bytes/next_extent)) < 5
group by index_name, A.tablespace_name, next_extent/1024/1024 
/

drop table tspace_extents 
/
drop table table_next_extent
/
drop table index_next_extent
/
set pause on

 

[DrSeussFreak]

if/when it returns a tablespace name, we either alter the existing datafile, or add a new one