Printable View
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.
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;
/