• 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. Beginners Guide to Monitoring Oracle Database Restore/Recovery Progress
  2. Oracle SQL Script to Report Tablespace Free and Fragmentation
  3. Database Crashed With ORA-19815, ORA-19809, ORA-16038
  4. Cron Script does not Execute as Expected from crontab – Troubleshoot
  5. Oracle Net New Features in Oracle Database 12c
  6. CentOS/RHEL7 – Tuned Profiles Oracle
  7. Basic SELECT Statement in PL/SQL
  8. How to start up the ASM instance when the spfile is misconstrued or lost
  9. Managing High Availability of Services in Oracle RAC
  10. How To Resize An ACFS Filesystem/ASM Volume (ADVM)

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright