How to maintain an Oracle index

First, view the user index in the system table

In Oracle, the system table is created automatically when the database is installed, containing all of the database's data dictionaries, the definitions of stored procedures, packages, functions, and triggers, and the system rollback segment.

In general, you should try to avoid storing objects in the system table that are not system users. This can lead to many problems with database maintenance and management. Once the system table is corrupted, only the database can be rebuilt. We can use the following statement to check that there are no other user's indexes in the system table.

/* Formatted on 2010/6/19 13:22:46 (QP5 v5.115.810.9015) * *

SELECT *

From Dba_indexes

WHERE tablespace_name = ' System ' and owner not in (' SYS ', ' system ')

Second, the storage of the index check

Oracle allocates logical structural space for all data in the database. The units of the database space are block, extent, and segment.

Block: is the smallest storage unit used and allocated by Oracle. It is determined by the Db_block_size set when the database was established. Once the database is generated, the size of the data block cannot be changed. To change, you can only re-establish the database.

Extent: is made up of a set of contiguous blocks. One or more extent form a segment. When all the space in a segment is exhausted, Oracle assigns it a new extent.

Segment: is made up of one or more extent. It contains all the data for a particular logical storage structure in a table space. The extent in one segment can be discontinuous, even in different data files.

An object can only correspond to the segment of a logical store, we can see the storage of the corresponding object by looking at the extent in the segment.

(1) View the number of extent in the index segment:

/* Formatted on 2010/6/19 13:47:34 (QP5 v5.115.810.9015) * *

SELECT segment_name, COUNT (*)

From Dba_extents

WHERE segment_type = ' INDEX ' and owner = UPPER (' Newccs ')

GROUP by Segment_name

(2) View the expansion of the index within the tablespace:

/* Formatted on 2010/6/19 14:05:23 (QP5 v5.115.810.9015) * *

Select SUBSTR (segment_name, 1) "segment name", Bytes, COUNT (bytes)

From Dba_extents

WHERE segment_name in (SELECT index_name

From Dba_indexes

WHERE tablespace_name = UPPER (' Newccs '))

GROUP by Segment_name, bytes

ORDER BY Segment_name