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 from (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 p.name = '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