Check the free size of tables or indexs with segment advisory in Oracle

Leave a reply

Segment Advisory with DBMS ADVISORY Package in Oracle Database

Segment Advisory used to analyze the segments that have space available for reclamation. It determines that an object has a significant amount of free space for released, it recommends online segment shrink.
It also let us know about segment is having row chaining above a threshold.

Run the Segment Advisory on TABLE OR INDEX

Use your schema and table name as mentioned in the script with comment sign. Enter Object type like Tablespace, Table, Index or LOB.

DECLARE
l_object_id  NUMBER;
BEGIN

-- Create a segment advisor task for the SCOTT.EMP table.

DBMS_ADVISOR.create_task (
advisor_name      => 'Segment Advisor',
task_name         => 'FRINDLY_ADVISOR',
task_desc         => 'Segment Advisor For EMP');

DBMS_ADVISOR.create_object (
task_name   => 'FRINDLY_ADVISOR', ---Task Name
object_type => 'TABLE', ---Object Type
attr1       => 'KSD2000',     ---SCHEMA Name
attr2       => 'KSD_PROSJEKT_DIFF',  ---TABLE NAME
attr3       => NULL, ---Partition Name
attr4       => 'null',
attr5       => NULL,
object_id   => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => 'FRINDLY_ADVISOR',
parameter => 'RECOMMEND_ALL',
value     => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'FRINDLY_ADVISOR');
END;
/

For Drop the Segment Advisory

exec dbms_advisor.delete_task(task_name =>’FRINDLY_ADVISOR’);

Check the Segment Advisory is dropped

SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
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 IN ('FRINDLY_ADVISOR')
ORDER BY f.task_name, f.impact DESC;

Check the object is locked:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

Online Segment Shrink with row enable

-- Enable row movement.

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

-- Disable row movement.
ALTER TABLE scott.emp DISABLE ROW MOVEMENT;



Leave a Reply