Need DBA's help

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: 2000/09/21
Message-ID: <39cad895.3810048@172.16.7.5>#1/1

Hello all,

We are running Oracle Applications R10.7 and it has an Alert system for checking status of Oracle database and Oracle Applications.

The alert system mails to me some information, but I don't know how to deal with it.

There is a script :

select s.owner, segment_name, blocks, extents, i.pct_increase,

             :maxblocks, :maxextents
into &OWNER, &INDEX_NAME, &BLOCKS, &EXTENTS, &EXTPCT,

           &MAXBLOCKS, &MAXEXTENTS from dba_segments s, dba_indexes i
where segment_type = 'INDEX'

and     segment_name like
               decode(upper(:index_name),'ALL','%',upper(:index_name))
and     s.segment_name = i.index_name
and     s.tablespace_name = i.tablespace_name
and     s.owner like decode(upper(:user),'ALL','%',upper(:user))
and     s.owner = i.owner
and     ((upper(:check_blocks) = 'YES' and blocks > :maxblocks)
          or (upper(:check_extents) = 'YES' and extents > :maxextents))

order by s.owner, segment_name
/

It is for detecting indexes exceeding a 100000 blocks or 20 extents and return some indexes with owner and index_name. (:maxblocks = 100000 and maxextents = 20)

I try to issue ALTER INDEX xxx REBUILD command for each index. After rebuilded successfully,I select dba_extents again and each index is less than 20 extents.
Almost every index rebuilds successfully except for SYS.I_SOURCE1 when i tried to :
ALTER INDEX SYS.I_SOURCE1 REBUILD An error happens :

ORA-01467 : sort key too long

Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a sort key longer than that supported by ORACLE. Either too many columns or too many group functions were specified in the SELECT statement.

Action: Reduce the number of columns or group functions involved in the operation.

Does anyone know how to correct the error??

And second question is:

There is another script :

SELECT di.tablespace_name


     , di.index_name

     , di.next_extent*power(1.+di.pct_increase/100.,ds.extents-1)

  INTO &TABLESPACE


     , &INDEX_NAME

     , &NEXT_EXTENT

  FROM dba_indexes di


     , dba_segments ds

     , dba_free_space df

 WHERE di.tablespace_name = ds.tablespace_name
   and ds.segment_type = 'INDEX'
   and di.owner = ds.owner
   and di.index_name = ds.segment_name
   and df.tablespace_name = di.tablespace_name

   and ds.owner = decode(upper(:user),'ALL',ds.owner,upper(:user))    and ds.segment_name like

       decode(upper(:index_name),'ALL','%',upper(:index_name))  GROUP BY

       di.tablespace_name,di.index_name,
       di.next_extent*power(1.+di.pct_increase/100.,ds.extents-1)

HAVING max(df.bytes) <
di.next_extent*power(1.+di.pct_increase/100.,ds.extents-1)  ORDER BY di.tablespace_name

        , di.index_name

It is for detecting indexes whose next extent is larger than the largest free extent and of course return some indexes with owner and index_name
such like:

 Tablespace          Index Name             Next Extent
  ----------               ----------                    ----------   
  SYSTEM              I_ARGUMENT1     4837375272.375 

The index is belonged to SYS account
but I select dba_indexes and find next_extent is 7364608 and select dba_free_space where tablespace_name ='SYSTEM'. SYSTEM contains 257230848 only.

I'm not sure if the next extent of SYS.I_ARGUMENT1 will become 4837375272.375 and if so,SYSTEM tablespace will run out of all space What could I do for it??

Sorry for the long,long questions and my poor English. But I really need someone's help.

Best Regards.

Violin.
violin.hsiao_at_mail.pouchen.com.tw Received on Thu Sep 21 2000 - 00:00:00 CDT