Script to find the tablespaces within certain extents

Oracle script that finds the tablespaces that can extent to a maximum of "ExtLimit".

The following script finds the tablespaces that can extent to a maximum of "ExtLimit". In this case, I didn't want...

any files to be more than 2 GB so I set MAXSIZE to 2 GB. Also, I did not include temp files in this script.

define 2GB = "2048 * 1024 * 1024" 
define ExtLimit = 5     --report if not enough space for at least these many extents 
set define on 
set trimspool on 
set verify off 
select  ts, to_char(nextB/1024/1024,'999G999G999D99') NextMB, 
        to_char(sum(trunc(FreeB/nextB) * NextB)/1024/1024, '999G999G999D99') maxAvailMB
        --trunc to get the size in Extent Boundary
                (select d.tablespace_name ts, 
                        d.increment_by * p.value NextB, 
                        ((CASE WHEN d.maxbytes >= &2GB THEN &2GB ELSE d.maxbytes END) - d.bytes + NVL(f.freebytes,0)) FreeB
                from    dba_data_files d, v$parameter p, 
                        (SELECT file_id, SUM(bytes) freebytes  FROM sys.dba_free_space  GROUP BY file_id) f 
                where   d.file_id = f.file_id(+) 
                AND = 'db_block_size' 
        ) freeOnTS 
        group by ts, nextB 
        having sum(trunc(FreeB/nextB) * NextB) <= NextB * &ExtLimit 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in March 2003

Dig Deeper on Oracle and SQL



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: