472,374 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,374 developers and data experts.

Script to Rebuild the indexes that really needs to be rebuild .

8,127 Expert 4TB
This code genenates a script to rebuld all the indexes with height greater than 3 for all the users except for SYS user. The script needs to be executed from SYS schema. This can also be executed from individual schemas by minor changes. If you need to exclude indxes of some other users that can also be added/changed in cGetIdx cursor. To execute this code from any schema other than SYS. Execute on DBMS_SQL must be granted from SYS(not system) to the the specified user.

Indexes with height less than three (3) should not be rebuilded. If you want to go for indexes with less heights that can also be customized by changing the height parameter.

Set serveroutput on size 100000
Expand|Select|Wrap|Line Numbers
  2.   vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  3.   vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
  4.   vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
  5.   vCursor  NUMBER;                            /* DBMS_SQL cursor        */
  6.   vNumRows INTEGER;                           /* DBMS_SQL return rows   */
  7.   vHeight  index_stats.height%TYPE;           /* Height of index tree   */
  8.   vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
  9.   vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
  10.   vDLfPerc   NUMBER;                          /* Del lf Percentage      */
  11.   vMaxHeight NUMBER;                          /* Max tree height        */
  12.   vMaxDel    NUMBER;                          /* Max del lf percentage  */
  13.   CURSOR cGetIdx IS SELECT owner,index_name
  14.      FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
  15. BEGIN
  16.   /* Define maximums. This section can be customized. */
  17.   vMaxHeight := 3;
  18.   vMaxDel    := 20;
  20.   /* For every index, validate structure */
  21.   OPEN cGetIdx;
  22.   LOOP
  23.      FETCH cGetIdx INTO vOwner,vIdxName;
  24.      EXIT WHEN cGetIdx%NOTFOUND;
  25.      /* Open DBMS_SQL cursor */
  26.      vCursor := DBMS_SQL.OPEN_CURSOR;
  27.      /* Set up dynamic string to validate structure */
  28.      vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
  29.      DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
  30.      vNumRows := DBMS_SQL.EXECUTE(vCursor);
  31.      /* Close DBMS_SQL cursor */
  32.      DBMS_SQL.CLOSE_CURSOR(vCursor);
  33.      /* Does index need rebuilding?  */
  34.      /* If so, then generate command */
  35.      SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
  36.         FROM INDEX_STATS; 
  37.      IF vDLfRows = 0 THEN         /* handle case where div by zero */
  38.         vDLfPerc := 0;
  39.      ELSE
  40.         vDLfPerc := (vDLfRows / vLfRows) * 100;
  41.      END IF;
  42.      IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN 
  43.         DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
  44.      END IF;
  46.   END LOOP;
  47.   CLOSE cGetIdx;
  48. END;
Aug 1 '08 #1
0 16998

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Don | last post by:
I want the server-side php script to return a browser page that is essentially a copy of the original client page that contained the <form> which referenced the php script in the first place....
by: xo55ox | last post by:
Hi, I want to find out what is a good way to identify indexes that need rebuilding. I tried to run DBCC showcontig and identify them based on the scan density %. And according to a Microsoft...
by: Matt | last post by:
Hello I am trying to rebuild indexes on our DB, but I am running into an error I am not able to solve. Server: Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'ft' in...
by: trint | last post by:
Ok, This script is something I wrote for bringing up a report in reporting services and it is really slow...Is their any problems with it or is their better syntax to speed it up and still provide...
by: wackyphill | last post by:
After rebuilding an index, it still shows as the same amount of fragmentation. ANy ideas what's wrong? I'm determining which indexes to rebuild using the following query: SELECT...
by: Dhungu Dhumbak | last post by:
Dear All, SOS Please Help. I have a MS-SQL DB with 4 .ndf files. One (first) .ndf file is missing. somehow got deleted??. Is there any way can rebuild my DB. The .MDF and .LDF files are in...
by: digitalox | last post by:
All efforts to rebuild indexes fails. Not with an error, but they still show high fragmentation. The environment: SQL Server 2005 in 2000 compatibility mode non-clustered indexes on tables that...
by: dsdevonsomer | last post by:
Hello all, I am new to managing indexes on large tables and need some help. Hopefully, I am not repeating question here. I searched as much as I can, but not finding relatively best answer.. ...
by: Okonita via DBMonster.com | last post by:
Hi all, I am comming along with all this Linus/DB2/scripting business...I am no longer scared of it!! (LOL). But, I need to create a .ksh script that does a REORGCHK and output only tables...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.