Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Handy Oracle free space script

Oracle Database Tips by Donald BurlesonApril 21, 2015

 

There are several views in the data dictionary that display database free space.  We have to start by defining free space:

  • Is "free space" the empty space in the tablespace?

  • Is "free space the available space on the data files and filesystems?

There are several salient DBA views for displaying free space in Oracle:

  • dba_free_space

  • dba_data_files

  • dba_tablespaces

For a free poster of these Oracle space views, click here.  Here is a simple script to display Oracle free space within the data file space.  Note the in-line view where dba_data_files is joined into dba_free_space:

SELECT
   a.tablespace_name,
   a.file_name,
   a.bytes allocated_bytes,
   b.free_bytes
FROM
   dba_data_files a,
   (SELECT file_id, SUM(bytes) free_bytes
    FROM dba_free_space b GROUP BY file_id) b
WHERE
   a.file_id=b.file_id
ORDER BY
   a.tablespace_name;


This shows the allocated and free bytes within the data files, but it DOES not show available free space on the OS filesystem:

TABLESPACE_NAME      FILE_NAME                 ALLOCATED_BYTES     FREE_BYTES
-------------------- ------------------------- --------------- --------------
TBS_LOCALS           /u01/app/oradata/devdb/de      20,971,520     20,774,912
                     vdb/devdb_tbs_locals_01.d
                     bf

Note that there are many other ways to create tablespace reports.  This method create an intermediate view to doisplay free space:

Rem free_space.sql

rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;

CREATE VIEW SYS.FREE_SPACE AS
SELECT
       TABLESPACE_NAME TABLESPACE,
       FILE_ID,
       COUNT(*)    PIECES,
       SUM(BYTES)  FREE_BYTES,
       SUM(BLOCKS) FREE_BLOCKS,
       MAX(BYTES)  LARGEST_BYTES,
       MAX(BLOCKS) LARGEST_BLKS
FROM  
       SYS.DBA_FREE_SPACE
GROUP  BY TABLESPACE_NAME, FILE_ID;

This is the next script, whixch show all free space within the tablespace:

rem tsfree.sql - Shows all free space within tablespaces.

Prompt be sure that you have run free_space.sql prior to this script

clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;

column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME        FORMAT A15    HEADING 'Tablespace';
COLUMN PIECES                 FORMAT 9,999  HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES            FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES            FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE               FORMAT 999    HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE    FORMAT 999    HEADING 'Percent|FREE|Contiguous';

ttitle left datevar right sql.pno -
       center ' Instance Data File Storage' SKIP 1 -
       center ' in ORACLE Megabytes (1048576 bytes)' -
       skip skip;

BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT

select to_char(sysdate,'mm/dd/yy') today,
       TABLESPACE_NAME,
       PIECES,
       (D.BYTES/1048576) FILE_MBYTES,
       (F.FREE_BYTES/1048576) FREE_MBYTES,
       ((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
       (F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
       ((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F

See code depot for full script
where D.STATUS = 'AVAILABLE' AND
      D.FILE_ID= F.FILE_ID AND
      D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;

Here is the report from this script.

Tablespace         Pieces     Mbytes  Mbytes  FREE       Mbytes Contiguous
--------------- ---------- ---------- ------- ------- ---------- ----------
MASTER1_DETAILS       1      18         2        10            2   10
MASTER1_DETAILS       1      20        20       100           20  100
MASTER2_DETAILS       1       2         1        65            1   65
MASTER3_DETAILS       1       5         5        95            5   95
MASTER4_DETAILS       2       3         1        36            1   35
RBS_ONE              11     490       380        78          280   57
RBS_TWO              11     490       379        77          279   57
SYSTEM               17      60        45        76           45   75
TEMP                  1     650       650       100          650  100
TOOLS                 2      15         9        61            8   55
USERS                41     100        31        31            4    4
----------
                      13,255

This report is useful for finding the largest sized chunk of free space within a tablespace.

Click here for the complete Oracle script collection.

 

 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational