tablespace free space 1

[watergrinder]

I have a tablespace that has two data files. The second data file is 2gb. Initially it is 500 mb and is autoextensible. The following script takes the initial 500 mb only to give free space details.

Can somebody give a script which takes the entire 2gb into consideration?

SELECT
fs.tablespace_name TABLESPACE_NAME,
df.totalspace TABLESPACE_TOTAL_SIZE,
(df.totalspace - fs.freespace) MB_USED,
fs.freespace MB_FREE,
round(100 * (fs.freespace / df.totalspace),2) PCT_FREE
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name ) df, (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) FreeSpace FROM dba_free_space GROUP BY tablespace_name ) fs WHERE df.tablespace_name = fs.tablespace_name(+)
order by MB_FREE ASC

 

[SantaMufasa]

Grinder,

If you wish to take into account the potential size of your files when figuring out your "freespace", then you can join the table "sys.filext$". It contains the current settings for each data file's maximum extensibility in units of database blocks. For example, my database also uses 2000M autoextending files. Here are the results from my querying my "sys.filext$" table:

SQL> select * from sys.filext$ order by 1;

     FILE#  MAXEXTEND        INC
---------- ---------- ----------
         1     256000       1280
         2     256000       1280
         3     256000       1280
         4     256000       1280
         5     256000       1280

Here is the query again with results in bytes rather than in my 8,192-byte database blocks:

col "Maxsize" format 9,999,999,999
col "Increment" format 9,999,999,999
select file#, maxextend*8192 "Maxsize", inc*8129 "Increment"
from sys.filext$
order by 1;

     FILE#        Maxsize      Increment
---------- -------------- --------------
         1  2,097,152,000     10,405,120
         2  2,097,152,000     10,405,120
         3  2,097,152,000     10,405,120
         4  2,097,152,000     10,405,120
         5  2,097,152,000     10,405,120

I trust you can incorporate sys.filext$ into your original query, using "FILE#" to match data files. But if you still have followup questions, please post.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:16 (04Jun04) UTC (aka "GMT" and "Zulu"), 18:16 (03Jun04) Mountain Time)

 

[watergrinder]

Thank you. Is there a way to determine the amount of space used, left in/by datafile?

 

[watergrinder]

Santa,

I also found that sys.filext$ gives information only about datafile that have autoextensible="YES"

Thanks again.

 

[SantaMufasa]

Grinder,

You are correct: 'sys.filext$ gives information only about datafile that have autoextensible="YES".' But there are ways to "trick" Oracle into providing "information" when none exists.

To illustrate, the code (below) produces both and on-screen and spooled output that, I believe, does what you want. It produces the following categorical results: File name, File number, Total Potential File Size (regardless of whether the file is in AUTOEXTEND mode or note), Total Current File Size, Number of Bytes Used, Potential Freespace (i.e., potential size minus bytes used), Percent of potential-size that is free, Autoextensibility Flag, and File Name.

Section 1 -- "Freespace.sql" code:

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

Section 2 -- Output from "Freespace.sql" (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

Once you copy, paste, and run the code from Section 1, above, let me know if it resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:33 (04Jun04) UTC (aka "GMT" and "Zulu"), 11:33 (04Jun04) Mountain Time)

 

[SantaMufasa]

Grinder,

Also, I failed to emphasize enough in my post, above, that the code works even for "non-AUTOEXTEND-ing" files, as well. The code that deals with "non-AUTOEXTEND-ers" is any code that reads:

...decode(e.file#,null,d.bytes,(e.maxextend * blksize))...

This means, "If an existing file does not appear in the 'sys.filext$' table, then it means the file is not AUTOEXTEND-ing, so use the file's total current file size instead of the "maximum-extend-blocks" * blksize from the sys.filext$ table." This explains how you can cause Oracle to "provide" information when none exists.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:00 (04Jun04) UTC (aka "GMT" and "Zulu"), 12:00 (04Jun04) Mountain Time)