Re-indexing by user name in Oracle Database

If you manageOracle DatabaseSome application projects have a large number of modification and deletion operations and dataIndexPeriodic reconstruction is required. It not only improves query performance, but also increases the free space of the index tablespace. After a large number of records are deleted in ORACLE, the data block space occupied by tables and indexes is not released. Re-indexing can free up the data block space occupied by deleted record indexes.

To transfer data, you can rename the data in the table.

The following is an SQL script that generates a re-Indexing Based on the ORACLE user name: SET ECHO OFF;

Set feedback off;

Set verify off;

Set pagesize 0;

Set termout on;

Set heading off;

ACCEPT username char prompt 'Enter the index username :';

Spool/oracle/rebuild _ & username. SQL;

SELECT

'Rem + --------------------------------------------- + '| chr (10) |

'Rem | index name: '| owner |'. '| segment_name

| Lpad ('|', 33-(length (owner) + length (segment_name )))

| Chr (10) |

'Rem | BYTES: '| bytes

| Lpad ('|', 34-(length (bytes) | chr (10) |

'Rem | EXTENTS: '| extents

| Lpad ('|', 34-(length (extents) | chr (10) |

'Rem + --------------------------------------------- + '| chr (10) |

'Alter Index' | owner | '.' | segment_name | chr (10) |

'Rebuilt' | chr (10) |

'Tablespace' | tablespace_name | chr (10) |

'Storage ('| chr (10) |

'Initial' | initial_extent | chr (10) |

'Next' | next_extent | chr (10) |

'Minextents' | min_extents | chr (10) |

'Maxextents' | max_extents | chr (10) |

'Pctincrease' | pct_increase | chr (10) |

');' | Chr (10) | chr (10)

FROM dba_segments

WHERE segment_type = 'index'

AND owner = '& username'

Order by owner, bytes DESC;

Spool off;

If you are using a WINDOWS system and want to change the storage directory of the output file, modify the path after spool:

Spool c: \ oracle \ rebuild _ & username. SQL;

If you only want to re-index An index greater than max_bytes, you can modify the preceding SQL statement:

Add a restriction AND bytes> & max_bytes after AND owner = '& username'

If you want to modify the index storage parameters, you can also modify the index rebuild _ & username. SQL.

For example, change the value of pctincrease not equal to zero.

We need to analyze the generated rebuild _ & username. SQL files to determine whether they need to be rebuilt:

ANALYZE the INDEX to see if the fragmentation is serious. SQL> ANALYZE INDEX & index_name VALIDATE STRUCTURE;

Col name heading 'index name' format a30

Col del_lf_rows heading 'deleted' | Leaf Rows 'format 99999999

Col lf_rows_used heading 'used | Leaf Rows 'format 99999999

Col ratio heading '% Deleted | Leaf Rows' format 999.99999

SELECT name,

Del_lf_rows,

Lf_rows-del_lf_rows lf_rows_used,

To_char (del_lf_rows/(lf_rows) * 100, '2017. 100') ratio

FROM index_stats where name = upper ('& index_name ');

When the deletion ratio is greater than 15-20%, the index must be rebuilt.

The rebuild _ & username. SQL file after deletion can be stored in the scheduled ORACLE job:

For example, a month or two is running during non-busy hours.

If a ORA-00054 error occurs, it indicates that the index has lock information on the table, and the index cannot be rebuilt.

Ignore this error and check whether it is successful next time.

The methods described here are not applicable to tables that are particularly busy,

Delete their indexes from rebuild _ & username. SQL.

The knowledge about rebuilding indexes by user name in the Oracle database is explained here. I hope you will be able to gain some benefits from the content mentioned above.