shrinking or tuning index and RBS segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: shrinking or tuning index and RBS segment

  1. #1
    Join Date
    Sep 2006
    Location
    London
    Posts
    58

    shrinking or tuning index and RBS segment

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.

    Code:
    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;
    /
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width