tablespace reporting issues 1

[ksbrace]

Hello,
I got stung today as Orasnap reporting was falsly indicating that there were no 'Object Extent Warning' or 'Segs >= 50% Of MaxExts' issues.

The no segs > 50% of max extents I can understand as maxextents is huge. And the 'object extent warning' misinterpretation is due I believe to the fact that for locally managed tablespaces next extents is 0 and so there is ALWAYS room for 0 bytes !!!


I found Oracle Note 1020090.6, Script to Report on Space in Tablespaces which is supposed to be good for product versions 7.3 to 9.2. Inside this script, oracle has a query to identify segments where there's not enough room for the next extent.


I've recreated the problem, I believe it is bigger than being and Orasnap issue. I found Oracle Note 1020090.6, Script to Report on Space in Tablespaces which is supposed to be good for product versions 7.3 to 9.2. Inside this script, oracle has a query to identify segments where there's not enough room for the next extent.

I've created a tablespace and table on a test database and it was created as locally managed, autoallocate, initial 512K, tablespace max size is 1MB (maybe 8k bigger than that I manually expanded in 1 time).

Anyway, whenever autoallocate is used, the next extent doesn't ever get taken (if specified on creation) because Oracle uses it's own system generated next extents.

I created a plsql loop to fill the table until ora-01653.

So, now we have a table, that cannot extend 1 more time. Running both oracle's version of the report and orasnap's version of the report, neither one of them report a problem. I believe this is due to the fact that both are quering dba_free_space, and this table/tablespace doesn't even show up in that view since it has no space.

Ideally, I would like to get the warning if a tablespace is near 80% of the amount it could grow to, assuming tablespace autoextend were to function the max. Has anyone else ran into this and if so, do you have a nice fix? Thanks in advance!

[b]***Orasnap's report (adjusted to include just my tablespace to speed up query)***[/b]

SQL> select   ds.OWNER,
  2           ds.SEGMENT_NAME,
  3           ds.SEGMENT_TYPE,
  4           ds.TABLESPACE_NAME,
  5           ds.NEXT_EXTENT,
  6           dfs.MAX,
  7           dfs.SUM,
  8           ds.EXTENTS
  9  from     dba_segments ds,
 10          (select   max(BYTES) max,
 11                    sum(BYTES) sum,
 12                    TABLESPACE_NAME
 13           from     dba_free_space
 14           where tablespace_name='TODDTEST'
 15           group by tablespace_name) dfs
 16  where    ds.NEXT_EXTENT > dfs.MAX
 17  and      ds.TABLESPACE_NAME = dfs.TABLESPACE_NAME
 18  order by ds.OWNER, ds.TABLESPACE_NAME, ds.SEGMENT_NAME;

no rows selected

[b]***Oracle's own query (adjusted to query just my tablespace) ***[/b]

SQL> select  a.owner, a.segment_name, b.tablespace_name,  
  2       decode(ext.extents,1,b.next_extent,  
  3       a.bytes*(1+b.pct_increase/100)) nextext,   
  4       freesp.largest  
  5  from    dba_extents a,  
  6       dba_segments b,  
  7       (select owner, segment_name, max(extent_id) extent_id,  
  8       count(*) extents   
  9       from dba_extents   
 10       group by owner, segment_name  
 11       ) ext,  
 12       (select tablespace_name, max(bytes) largest  
 13       from dba_free_space
 14       where tablespace_name='TODDTEST'   
 15       group by tablespace_name  
 16       ) freesp  
 17  where   a.owner=b.owner and  
 18       a.segment_name=b.segment_name and  
 19       a.owner=ext.owner and   
 20       a.segment_name=ext.segment_name and  
 21       a.extent_id=ext.extent_id and  
 22       b.tablespace_name = freesp.tablespace_name and   
 23       decode(ext.extents,1,b.next_extent,  
 24       a.bytes*(1+b.pct_increase/100)) > freesp.largest  
 25  /  


no rows selected

 

[SantaMufasa]

KS,

Here is my "Freespace.sql" script. It shows the percent free of each file in each tablespace. (I widened the Tek-Tips display to avoid annoying line wraps.) If you want the script to show just the files that are 90% full, then put in a WHERE clause that says "WHERE (nvl(freebytes,0)/d.bytes)*100 < 10":

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      262,144,000      258,080,768    1,839,071,232     87 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA101.DBF'
DATA2              5    2,097,152,000      241,172,480        5,570,560    2,091,581,440     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      159,342,592    1,937,809,408     92 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       10,485,760          458,752    2,096,693,248     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      698,351,616      423,518,208   10,062,241,792


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

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

Let us know if this (perhaps with the WHERE clause) shows what you want.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ksbrace]

Ok, I think we are getting close. I put that where clause in and I don't think it's giving me the correct answer. Maybe you can guide me where I am going wrong. I would only expect to see ToddTest in the results. Did I put the where clause in the wrong location? Thanks again.
Query

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
[b]WHERE (nvl(freebytes,0)/d.bytes)*100 < 10[/b]
and      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time

results:
TS FNUM TB CB USED FREE PERCENTFREE AUTOEXT FNAME
1 TODDTEST 28 1056768 1056768 1056768 0 0 No '/oracle/app/oracle/oradata/test/toddtest01.dbf'
2 USERS 5 34359721984 524288000 518389760 33841332224 98 Yes '/oracle/app/oracle/oradata/test/users01.dbf'
3 XDB 13 34359721984 47185920 46923776 34312798208 99 Yes '/oracle/app/oracle/oradata/test/xdb01.dbf'

 

[SantaMufasa]

KS,

Are you able to run the script in SQL*Plus? That way, the results will be formatted and easier to read.

Another problem I see is that on the "USERS" and "XDB" tablespaces, you created your datafile files with "MAXSIZE UNLIMITED", which translates to 34GB (potential). Some operating systems have problems with file sizes that exceed 2GB. (If your operating system has trouble with 2GB files, the problem reveals itself in this fashion: Whatever table houses the block at the 2GB threshold, all data beyond the "threshold block" in that table becomes inaccessible.)

Since none of your files currently exceed 2GB, you can prevent this problem with the following code (for example, for the USERS tablespace file):

ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/test/users01.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Later, if you sense that you are approaching the 2GB maximum for that file, you can add as many additional 2GB-maximum datafiles as you wish by using this code:

ALTER TABLESPACE USERS ADD DATAFILE '/oracle/app/oracle/oradata/test/users02.dbf' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Now, for the corrected code solution to the query I posted. (The WHERE-clause modification was for an earlier version of my Freespace.sql script.) The amended code segment appears in bold font:

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[b]
  and   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 < 10[/b]
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

If you wish, you can replace the hardcoded "< 10" (percent growth-space remaining), above with some SQL*Plus ACCEPT/PROMPT value so that you can re-run the script to look for any threshold you wish.

Let us know if this is more satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ksbrace]

Ok, so if I want to monitor tablespaces that are 80% or more full now and on the next extent, I would just change the 10 to a 20 in the 'trunc' clause? I apologize in advance for any ignorant questions. I'm a newbie dba, obviously. Thanks in advance!

 

[SantaMufasa]

Yes, if you wish to monitor tablespaces that are 80% or more full, then change "< 10" to "< 20".

But keep in mind, KS, that "Total Potential File Size" presumes that you have enough disk space available at the operating system level to accommodate tablespace-file expansion to that Potential File Size...if you do not have a large enough file system (i.e., available disk space), then you will receive an Oracle error if Oracle is unable to allocate the "NEXT nnM" because of exhausted file-system space.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.