shrinking or tuning index and RBS segment

[dbajay]

The current size of our database is approximately 35GB. The version of DB is Oracle 8.1.7.

The Rollback segments is currently holding approximately 1.25GB data and INDEX segment is currently holding 2.73 GB data.

How to shrink or tune these segments? How can I know whether this much space is really required to occupy lookig at the application and database perspective?

How to dig a deep to shrink or tune these segments?

Thanks in advance.

[gandolf989]

For 35GB rollback and index don't seem that large. Although, without knowing your database, how would I know if they were to large? You can run this procedure to get an ideas about how much wasted space you might have in your tables/indexes.

SET SERVEROUTPUT ON
DECLARE
   v_schema_owner VARCHAR2(128) := 'MYSCHEMA';

   CURSOR outreach_tables IS
          SELECT segment_name, segment_type, partition_name
            FROM dba_segments
           WHERE owner = v_schema_owner
             AND segment_type NOT IN ('LOBINDEX','LOBSEGMENT')
           ORDER BY segment_type, segment_name;

   v_total_blocks                   NUMBER;
   v_total_bytes                    NUMBER;
   v_unused_blocks                  NUMBER;
   v_unused_bytes                   NUMBER;
   v_last_used_extent_file_id       NUMBER;
   v_last_used_extent_block_id      NUMBER;
   v_last_used_block                NUMBER;

   v_sum_total_bytes                NUMBER := 0;
   v_sum_total_unused_bytes         NUMBER := 0;
BEGIN
   DBMS_OUTPUT.ENABLE(1000000);
   FOR p_row IN outreach_tables
   LOOP
      IF p_row.segment_type LIKE '%PARTITION%'
      THEN
         DBMS_SPACE.UNUSED_SPACE(
            v_schema_owner,
            p_row.segment_name,
            p_row.segment_type,
            v_total_blocks,
            v_total_bytes,
            v_unused_blocks,
            v_unused_bytes,
            v_last_used_extent_file_id,
            v_last_used_extent_block_id,
            v_last_used_block,
            p_row.partition_name );
      ELSE
         DBMS_SPACE.UNUSED_SPACE(
            v_schema_owner,
            p_row.segment_name,
            p_row.segment_type,
            v_total_blocks,
            v_total_bytes,
            v_unused_blocks,
            v_unused_bytes,
            v_last_used_extent_file_id,
            v_last_used_extent_block_id,
            v_last_used_block );
      END IF;

      v_sum_total_bytes          := v_sum_total_bytes 
                                  + NVL(v_total_bytes, 0);
      v_sum_total_unused_bytes   := v_sum_total_unused_bytes
                                  + NVL(v_unused_bytes, 0);

      DBMS_OUTPUT.PUT_LINE(
         RPAD(p_row.segment_type, 20) || ' ' ||
         RPAD(p_row.segment_name, 30) || ' ' ||
         'Total KB' || TO_CHAR(v_total_bytes/1024,  '999,999,990') ||
         TO_CHAR(v_unused_bytes/1024, '999,999,990') ||
         TO_CHAR((v_total_bytes 
                - v_unused_bytes)/1024, '999,999,990'));
   END LOOP;
      DBMS_OUTPUT.PUT_LINE(
         'Total KB alocated: ' ||
         TO_CHAR(v_sum_total_bytes/1024,        '999,999,990') ||
         '      Total KB Free: ' ||
         TO_CHAR(v_sum_total_unused_bytes/1024, '999,999,990') ||
         '      Total KB Used: ' ||
         TO_CHAR((v_sum_total_bytes - 
                  v_sum_total_unused_bytes)/1024, '999,999,990') );

END;
/