• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle SQL Script to Report Tablespace Free and Fragmentation

by admin

This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace.

This can serve as an early warning system to prevent database space allocation crises. Note that no attempt is made to coalesce adjacent fragments. It is thus possible sometimes to obtain an extent larger than the size reported as the Largest Free Frag.

Since a large space-consuming extent may or may not be populated with rows, this script can mislead a DBA into believing that there is much less free space in a database than there actually is.

The script:

ttitle - 
   center  'Database Freespace Summary'  skip 2 
 
comp sum of nfrags totsiz avasiz on report 
break on report 
 
col tsname  format         a16 justify c heading 'Tablespace' 
col nfrags  format     999,990 justify c heading 'Free|Frags' 
col mxfrag  format 999,999,990 justify c heading 'Largest|Frag (KB)' 
col totsiz  format 999,999,990 justify c heading 'Total|(KB)' 
col avasiz  format 999,999,990 justify c heading 'Available|(KB)' 
col pctusd  format         990 justify c heading 'Pct|Used' 
 
select 
  total.tablespace_name                       tsname, 
  count(free.bytes)                           nfrags, 
  nvl(max(free.bytes)/1024,0)                 mxfrag, 
  total.bytes/1024                            totsiz, 
  nvl(sum(free.bytes)/1024,0)                 avasiz, 
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd 
from 
  dba_data_files  total, 
  dba_free_space  free 
where 
  total.tablespace_name = free.tablespace_name(+) 
  and total.file_id=free.file_id(+)
group by 
  total.tablespace_name, 
  total.bytes 
/ 

Here’s a sample output from the above script:

  Database Freespace Summary  
 
 
                   Free     Largest       Total      Available   Pct  
   Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used 
---------------- -------- ------------ ------------ ------------ ----  
DES2                    1       30,210       40,960       30,210   26 
DES2_I                  1       22,848       30,720       22,848   26 
RBS                    16       51,198       59,392       55,748    6 
SYSTEM                  3        4,896       92,160        5,930   94 
TEMP                    5          130          550          548    0  
TOOLS                  10       76,358      117,760       87,402   26 
USERS                   1           46        1,024           46   96 
                 --------              ------------ ------------ 
sum                    37                   342,566      202,732
Oracle SQL Script to Detect Tablespace Fragmentation

Filed Under: oracle

Some more articles you might also be interested in …

  1. MUTATING Table Error and How to Resolve it (ORA-04091)
  2. Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
  3. How To Create An ASM Diskgroup Using XML code in ASMCMD
  4. What are the .lok files used in WebLogic
  5. Oracle Database – How to Recover from a Lost or Deleted Datafile with Different Scenarios
  6. DDL Statement Examples in SQL
  7. How to check which options or features are used in the Oracle database
  8. How to rename a Pluggable Database, along with the respective directories, in Oracle 12c
  9. How to Extend ocfs2 Filesystem with tunefs.ocfs2 Command (Whole device used without partitions)
  10. How to Identify Different File types and space used in Flash Recovery Area

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright