Oracle Space Advisory and Segment Advisor Tutorial

Leave a reply

How to Reclaim Unused Space in Oracle Database

Oracle Space Advisory and Segment Advisor Tutorial

Managing storage efficiently is one of the most important responsibilities of an Oracle DBA. As databases grow, tablespaces become full, objects consume unnecessary storage, and performance may degrade due to poor space management.

Oracle provides powerful tools such as Space Advisory and Segment Advisor to help DBAs monitor space usage, identify reclaimable space, and optimize storage.

In this tutorial, we will learn:

  • How to monitor tablespace usage
  • How to create a demo object for testing
  • How Oracle handles unused space
  • How to run Segment Advisor
  • How to reclaim unused storage using SHRINK SPACE
  • Common errors and troubleshooting

Step 1: Check Tablespace Usage

The first step is to monitor tablespace utilization.

Command

SET LINES 200
COL tablespace_name FORMAT A25
SELECT
    df.tablespace_name,
    df.total_mb,
    fs.free_mb,
    ROUND((df.total_mb-fs.free_mb)*100/df.total_mb,2) used_percent
FROM
    (SELECT tablespace_name,
            ROUND(SUM(bytes)/1024/1024) total_mb
     FROM dba_data_files
     GROUP BY tablespace_name) df,
    (SELECT tablespace_name,
            ROUND(SUM(bytes)/1024/1024) free_mb
     FROM dba_free_space
     GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;

Step 2: Create Demo Table

Now let us create a sample table for testing space consumption.

Command

CREATE TABLE space_test
TABLESPACE users
AS
SELECT * FROM dba_objects;

Step 3: Insert Large Amount of Data

To simulate real database growth, insert more rows repeatedly.

Command

BEGIN
   FOR i IN 1..10 LOOP
      INSERT INTO space_test
      SELECT * FROM space_test;
      COMMIT;
   END LOOP;
END;
/

Step 4: Check Segment Size

Now verify how much storage the table consumes.

Command

SELECT
    segment_name,
    ROUND(bytes/1024/1024,2) MB
FROM dba_segments
WHERE segment_name='SPACE_TEST';

Step 5: Delete Data

Delete rows from the table.

Command

DELETE FROM space_test
WHERE rownum <= 100000;
COMMIT;

Step 6: Run Segment Advisor

Different Oracle versions may implement the package slightly differently. The following script works across many Oracle environments.

Command

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK2';
l_object_type VARCHAR2(32767) := UPPER('&Object_type');
l_attr1 VARCHAR2(32767) := UPPER('&Owner_name');
l_attr2 VARCHAR2(32767) := UPPER('&object_name');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
o.attr3,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Attr3 : ' || cur_rec.attr3);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
OUTPUT:
Enter value for object_type: TABLE
Enter value for owner_name: SYS
Enter value for object_name: SPACE_TEST
..
Type : TABLE
Attr1 : SYS
Attr2 : SPACE_TEST
Attr3 :
Message : Enable row movement of the table SYS.SPACE_TEST and perform shrink, estimated savings is 1118820385 bytes.
More info : Allocated Space:1280311296: Used Space:161490911: Reclaimable Space :1118820385:

STEP 7: Reclaim Space Using SHRINK SPACE

Enable row movement first.

Command

ALTER TABLE space_test ENABLE ROW MOVEMENT;

Now shrink the table.

Command

ALTER TABLE space_test SHRINK SPACE;

Disable row movement first.

Command

ALTER TABLE space_test DISABLE ROW MOVEMENT;

Step 8: Verify Reduced Space

Command

SELECT
    segment_name,
    ROUND(bytes/1024/1024,2) MB
FROM dba_segments
WHERE segment_name='SPACE_TEST';

This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply