Releasing Space

[msuresh]

I have deleted millions of rows from a table and it didnt release the space occupied by it.When will be the space released???.After the Database reboot???.Pls. let me know.

[khussain]

Reboot will not release the space.

How many rows do you have left in the table ? Can you truncate it ?
That will definitely release the space.

[abhaysk]

Some of the methods you can try to free up unused space
1) Alter table move
2) Exp/Imp ( I personally dont go for it ).

Abhay.

[msuresh]

I had 25 million rows and i deleted 10 million rows and the other 15 million rows are needed.I cannot truncate the table.Is there a solution for this other trhan truncating???

[gbrabham]

Here's a script to find out the used space in the table ...
Once you determine what you have left, and how much you may
need in the future, you can

sql> alter table xxx deallocated unused keep x;

xxx is the table name
keep x is meg to keep ie; 5m

Change the v_user variable to the owner of the table
Change v_object_name to the table name you are looking at ...


set serveroutput on

declare
v_user varchar2(50) := 'OWNER';
v_object_name varchar2(50) := 'PART';
v_object_type varchar2(15) := 'TABLE';
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_partition_name varchar2(50);
v_initial number;
v_next number;
v_number_extents number;
v_num_rows number;
v_avg_row_len number;
v_tablespace_name varchar2(30);

begin

IF v_object_type = 'TABLE'
THEN
SELECT initial_extent,next_extent,num_rows,avg_row_len, tablespace_name
INTO v_initial, v_next, v_num_rows, v_avg_row_len, v_tablespace_name
FROM all_tables
WHERE owner = v_user
AND table_name = v_object_name;
ELSE
SELECT initial_extent,next_extent, tablespace_name
INTO v_initial, v_next, v_tablespace_name
FROM all_indexes
WHERE owner = v_user
AND index_name = v_object_name;
END IF;

SELECT count(*)
INTO v_number_extents
FROM dba_extents
WHERE owner = v_user
AND segment_name = v_object_name;

DBMS_SPACE.UNUSED_SPACE( v_user
,v_object_name
,v_object_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
,v_partition_name );

dbms_output.put_line('Object Name: '||v_object_name||' - '||v_object_type||' Tablespace: '||v_tablespace_name);
dbms_output.put_line('. ');
dbms_output.put_line('Number of Extents: '||v_number_extents||' Initial: '||v_initial||' Next: '||v_next);
dbms_output.put_line('......Approx Rows: '||v_num_rows||' Avg Row Len: '||v_avg_row_len);
dbms_output.put_line('Blocks in segment: '||v_total_blocks);
dbms_output.put_line('Bytes in segment(Size): '||v_total_bytes);
dbms_output.put_line('MegaBytes in segment: '||v_total_bytes/1048576);
dbms_output.put_line('Blocks not used: '||v_unused_blocks);
dbms_output.put_line('Bytes not used: '||v_unused_bytes);
dbms_output.put_line('MegaBytes not used: '||v_unused_bytes/1048576);
dbms_output.put_line('File ID - last extent with data(Block): '||v_last_used_extent_file_id);
dbms_output.put_line('File ID - last extent with data(Byte): '||v_last_used_extent_block_id);
dbms_output.put_line('Last block with data: '||v_last_used_block);

end;
/


hth
Gregg

[fraze]

That wont work in this case, the deallocate unused clause only releases space above the high water mark, as his table has had rows deleted the space freed up will be below the high water mark. The only was to free that space is to import/export or do an alter table move

[slimdave]

forget that export/import method -- use "alter table ... move" then rebuild indexes.

btw, 10g has a command to move the individual rows in order to lower the HWM, and hence allow the table space to be deallocated.