• 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

SQL script to find tables that are fragmented

by admin

This post intends to provide an SQL script to find tables that are fragmented (i.e. Data is much lower than High Water Mark) so that we can target those segments (tables) for recreation.

pre-requisites

Requires dba privileges as the script is to be run as the owner SYS or SYSTEM. It Will not work on Compressed tables, and may return negative numbers. Do an Analyze with compute statistics on all tables present in the Users schema.

The SQL script

The script is as shown below:

REM This is an example SQL*Plus Script to find tables fragmentated below high water mark

set heading off verify off echo off

REM The below queries gives information about the size of the table with respect to the high water mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192. Substitute your DB blocksize.
PROMPT Please enter the schema name
SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='&OWNER';

REM The below queries gives the actual size in MB used by the table in terms of data.
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragmentation below high water mark prompt Enter name(s) of the schema for which you want to find the fragmented object.
PROMPT Please enter the schema name

SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;

How to run the script

1. Connect as user SYS or SYSTEM.

2. Run Analyze on all the tables present in the schema for which you want to find the fragmented table.

SQL> Analyze table [table_name] compute statistics;

3. Execute the script, the script will prompt for Schema name.

Note: You must Run the two statements one by one to be able to supply the substitution variable for the owner.

Filed Under: oracle

Some more articles you might also be interested in …

  1. How To Shrink A Temporary Tablespace in Oracle Database
  2. Oracle Database 12c : Creating a Scheduler Job in a Multitenant Database
  3. How to Manage Oracle database Audit File Directory Growth with cron (ASM Instances Only)
  4. TSPITR fails With RMAN-06553
  5. How to Move/Restore Oracle Database to New Host and File System using RMAN
  6. Oracle SQL Script to Detect Tablespace Fragmentation
  7. How To Create An ASM Diskgroup Using XML code in ASMCMD
  8. Oracle Database 18c new feature – Scalable Sequences
  9. How to Disable os-prober in CentOS/RHEL 7
  10. How To Change Timezone for Oracle Grid Infrastructure

You May Also Like

Primary Sidebar

Recent Posts

  • Chezmoi: A multi-machine dotfile manager, written in Go
  • cheat: Create and view interactive cheat sheets on the command-line
  • chars: Display names and codes for various ASCII and Unicode characters and code points
  • chafa: Image printing in the terminal

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright