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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Oracle Database interview questions – Privileges And Roles
  2. Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature
  3. Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
  4. Oracle Database: Profile Limits (Resource Parameter(s)) Are Not Enforced / Do Not Work
  5. How to Create the Oracle Password File using orapwd Command
  6. Oracle Database Interview Questions : Redo Logs and Archiving
  7. How to Verify if a Disk/Partition is in Use by Oracle ASM, was used by Oracle ASM or is never used by Oracle ASM
  8. Oracle Solaris Cluster : Understanding quorum votes and quorum devices (How to avoid Failure Fencing and Amnesia)
  9. Oracle 11G RMAN – Understanding UNDO backup optimisation
  10. Dynamic Oracle Net Server Tracing

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary