Re: Index nearing maxextents

Hi Steve,

    Assuming you took the defaults, the export/import would likely clear up the number of extents on the table as well. However, I'd be careful, as space is tight, and COMPRESS could cause more trouble than it's worth if you can't fit the entire table into it's new initial extent (look for largest contiguous free space within the tablespace you're going recreate the object(s). But it would be nice to have the logical backup of not only the table and index but all constraints and triggers.

    However, drop and recreating the index would be better if your happy with the growth of the table (ie extents are reasonable) and would rather not take it off the database. Though both are safe, it seems a slightly safer move, since you aren't removing the table, indexes, constraints and triggers from the database to resize one object.

    If you'd simply like to see what makes up your index. Try the following script (though the columns have likely changed in size, I think they are all still there through the revs):

    select index_owner, index_name,

             table_owner, table_name,
             column_name
      from sys.dba_ind_columns
      where index_owner = '<ownername>'
          and index_name = '<indexname>'
      order by column position;

    HTH,
        Greg

Steve Jaspar wrote:

> Greetings -
>
> I have an index that is nearing its maxextents. I'm not sure which is the
> best way to handle this:
>
> 1. export and re-import the table
> 2. drop and re-create the index
>
> I don't have the option of REBUILDing the index - we're running Oracle
> 7.1.xx and REBUILD is 7.3 and later.
>
> On a related note, is there a simple way to see the structure of the index,
> i.e. which columns are being indexed, initial extent and next extents sizes,
> etc.?
>
> Any advice will be most appreciated.
>
> Steve J.
Received on Tue Nov 03 1998 - 17:22:13 CST