DEV Community

18

Oracle Database Fragmentation - How to reorg Tables / Indexes

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

Database fragmenmtation from 12.2 onwards is using alter table move ( the main of dbms_redefinition in 11g versions is too miuch to handle )

In simple terms to reorg a table

## For a small Table 
alter table move scott.hr ;

## For a small Index
alter index rebuild scott.idx1 ;
Enter fullscreen mode Exit fullscreen mode

For Tables where you "know" which are the reorg candidates

But what happens in a scenario where you need to reorg a large table in a small downtime ( provided you have sufficient CPU's )

You could easily enable parallelism for the mobe/rebuild operations
and disable parallelism

## For a small Table 
alter table move scott.hr parallel 12;
alter table scott.hr noparallel;

## For a small Index
alter index rebuild scott.idx1 parallel 12;
alter index noparallel;
Enter fullscreen mode Exit fullscreen mode

VLDB's - high fragmentation across schemas

The problem with large databases where they are fragmentation prone due to exessive DML's. This is where the issue comes for DBA's where you don't know how to come up with a reorg startegy.

A DBA is often faced with these for reorg

  1. You have several schemas in several TB's
  2. You are not in a position to decide which tables/index to reorg
  3. You have a reasonably small downtime to do what you can
  4. You want the reorg to be human error free
  5. You also have the headache of having a standby database where the exessive archive logs need to apply.

When there is a problem SIMPLIFY

All DBA's need to do get a head start

  1. Identify which tables/indexes are fragemented
  2. Work with application teams to see which are important ones , try to understand the business need of the tables
  3. Test the fragementation approach in Non Prod's

Use my reorg toolkit

https://github.com/abhilash-8/reorg

Database DeFragmentation > 12c - Use the scripts as needed for your environment . It's higly recommended to test the reorg scripts in Non Prod Environments.

Identify Fragmentation

Use the frag.sql to list the tables which are fragemented

for. e.g. to identify fragmentation > 70% , this script will also identify which partitions are fragmented.

SQL> @frag 70
Enter fullscreen mode Exit fullscreen mode

Generate reorg scripts (for non partitioned tables)

Use the reorg_gen_nopart.sql , the script will spool the DDL reorg scripts for non partitioned tables

for e.g. to generate reorg scripts for SCOTT.HR table

SQL> @reorg_gen_nopart.sql SCOTT HR
Enter fullscreen mode Exit fullscreen mode

Generate reorg scripts (for partitioned tables)

Use the reorg_gen_part.sql , the script will spool the DDL reorg scripts for partitioned tables
Use the reorg_gen_nonpart_ind_part_tab.sql.sql , the script will spool the DDL reorg scripts for global indexes for partitioned tables

for e.g. to generate reorg scripts for SALES.INVOICES table for partitions JAN2018 , FEB2018 , MAR2018

SQL> @reorg_gen_nopart.sql SALES INVOICES JAN2018
SQL> @reorg_gen_nopart.sql SALES INVOICES FEB2018
SQL> @reorg_gen_nopart.sql SALES INVOICES MAR2018
Enter fullscreen mode Exit fullscreen mode

It's highly recommended to reorg global indexes when partitions are reorg'd

SQL> @reorg_gen_nonpart_ind_part_tab.sql SALES INVOICES
Enter fullscreen mode Exit fullscreen mode

Statistics

All reorg DDL scripts come will auto generate the DBMS_STATS scripts , you can customise it to your needs

Finally the reorg

Use the Dynamic DDL scripts for reorg , you could use them manually or run them in batches , decide on what works best for your databases.

Image of Docusign

Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay