Tag Archives: reclaim space

Script for Segment Advisory for partition tables in Oracle

Script for Segment Advisory for partition tables in Oracle

Script is used for reclaim activity which object has free space. Need to reclaim space to Operating system.

Copy paste the following script in command shell windows for segment advisory or reclaim the space from the object like table or index.
It asked for Object_type, Owner name and object name which you need to check for space reclaim activity.
Following script is consider for Partition table, it will provide you partition name with table name for help in reclaim acitivities.


--Segment Advisory Script:

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;
/

Advertisements

Reorganization table for remove fragmentation in Oracle

Reorganization Activity for table to released unused space oracle

Due to lot of insert, update & delete operation table is fragmented. To remove the fragmentation at some extent we have following the steps. Released unused space from segments table and index. Following steps let help to analysed and removed fragmentation from the table level.

Remove fragmentation from tables in Oracle

1. Check the size of tables.

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from user_tables where table_name = 'EMP';

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from dba_tables where table_name = 'TRAN' and owner='SCOTT';

2. Check the actual size of tables.

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables  where table_name = 'EMP';

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from dba_tables where table_name = 'TRAN' and owner='SCOTT';

3. Enable the row movement for the table which need to shrink.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing. ROWIDs are normally assigned to a row for the life time of the row at insert time.

ALTER TABLE emp ENABLE ROW MOVEMENT;

4. Now start Shrink Space

We have three Statements for Shrink the table:

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

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

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

This statement will proceed in two steps:
The first step makes the segment compact by moving rows further down to free blocks at the beginning of the segment.The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.

Table shrinking advantage: 
-will adjust the high watermark
-Can be done online
-Will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
-Indexes will be maintained and remain usable
-Can be made in one go
-Can be made in two steps

5. Disable the row movement.

ALTER TABLE emp DISABLE ROW MOVEMENT;

Script to shrink table objects by Owner.

begin
for i in (SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable
FROM dba_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM dba_indexes
WHERE index_type LIKE 'FUN%'
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND obj.Owner = upper('&1') and NVL(idx.cnt,0) < 1)
loop
execute immediate 'alter table '||i.owner||'.'||i.table_name||' enable row movement';
execute immediate 'alter table '||i.owner||'.'||i.table_name||' shrink space';
execute immediate 'alter table '||i.owner||'.'||i.table_name||' disable row movement';
end loop;
end;

Note :This can be useful if you cannot get a full table lock during certain hours. you only make the first step and adjust the high watermark later when it is more convenient

The following restrictions apply to table shrinking:

1. It is only possible in table spaces with ASSM.

2. You cannot shrink:
– UNDO segments
– temporary segments
– clustered tables
– tables with a column of datatype LONG
– LOB indexes
– IOT mapping tables and IOT overflow segments
– tables with MVIEWS with ON COMMIT
– tables with MVIEWS which are based on ROWIDs