Posts Tagged ‘lob’

oracle 9i: reclaim lob free space, tablespace map

June 22, 2011 1 comment

I’ve deleted a lot of LOB data (pdf, images, …) and I’ll never will reuse it, as I save now all the data on disk.

I want to reduce the  tablespace’s High Water Mark to avoid this error:

ORA-03297: file contains used data beyond requested RESIZE value

To achieve this, I create an auxiliary tablespace to move the LOB tables there:

SQL> create tablespace LOB_TEMP logging datafile '/app/oracle/oradata/db1/lob_temp_01.dbf' size 500M
extent management local uniform size 256K segment space management auto;

SQL> alter user <username> quota unlimited on LOB_TEMP;

Then, I move all the lobs to the new tablespace:

 select 'alter table ' || table_name || ' move lob ('|| column_name || ') store as (tablespace LOB_TEMP);' from user_lobs;

Now I have a lot of free segments in the former tablespace:

select file_id, block_id first_block, block_id+blocks-1 last_block, SEGMENT_NAME
where tablespace_name = 'FORMER_TS'
union all
select file_id, block_id, block_id+blocks-1, 'free'
where tablespace_name = 'FORMER_TS'

I perform a “move tablespace” for all the tables and indexes, just to reclaim some gaps:

select 'alter table ' || table_name || ' move;' from user_tables;
select 'alter index ' || index_name || ' rebuild;' from user_indexes where index_type <> 'LOB';

And then, move the lob segments to its original tablespace (although I prefer to have the LOB’s in its own tablespace):

select 'alter table ' || table_name || ' move lob ('|| column_name || ') tablespace (FORMER_TS);' from user_lobs;

When I select again for the free space, I have some gap of free space between used blocks, that I cannot reuse moving tables or rebuilding indexes. It’s difficult to understand how oracle manages the free space. Lets supose you have this scenario:

table (8 blocks)
table (8 blocks)
table (16 blocks)
index (8 blocks)
table (8 blocks)
free space (150 blocks)
table (8 blocks)
index (8 blocks)
index (8 blocks)
table (8 blocks)
free space (1000 blocks)

Even if I try to move the tables and indexes below the first free space, my data is not placed in that free blocks. To reduce even more this space:

  • move all the objects below the first free space (150 blocks) to another tablespace
  • once moved, all my free space is at the end of the datafile
  • move back the objects to its original tablespace
  • now the objects ocuppies the previous free space of 150 blocks, and I have at the end a free space of 1150 blocks, and now I can resize the datafile size

select lob length in oracle

If you want to know if a lob/clob is empty, you can use the DBMS_LOB package:

FROM  <table>
order by dbms_lob.getlength(<field>); 

If it returns null or zero, the lob is empty.

Categories: oracle Tags: , , , ,