free space occupied by BLOBs previuosly

One of the columns of a table stores BLOB data in a separate tablespace. We have moved the data from BLOB column to file server. Now, I want to coalesce the free space previously occupied by BLOBs in tablespace and release the free space to OS.

Is it possible to do it? If yes, how?

Quote:

---

Originally Posted by mahajanakhil198

One of the columns of a table stores BLOB data in a separate tablespace. We have moved the data from BLOB column to file server. Now, I want to coalesce the free space previously occupied by BLOBs in tablespace and release the free space to OS.

Is it possible to do it? If yes, how?

---

If the idea is to return space to the O/S the way to do it is by dropping tablespace and related datafiles - better for tablespace to be empty :)

Procedure is clearly described in Oracle documentation.

Quote:

---

Originally Posted by PAVB

If the idea is to return space to the O/S the way to do it is by dropping tablespace and related datafiles - better for tablespace to be empty :)

Procedure is clearly described in Oracle documentation.

---

Not the BLOBs of all the rows are deleted, it is for few of them. I think that 'alter table ..... shrink space' will not work in this case. This is because the rows are not deleted but one column of some of the rows that had BLOB is moved from that column to file server leaving the free space. I would like to know a way that can coalesce fragmented BLOBs and realease the remaining free space in BLOB tablespace datafiles to OS. Is it possible?

No matter if the datafile is 100% empty - while the datafile is there (alive and kicking) space would not be available to O/S.

It is possible to release space to OS. I would like to share it in my favorite forum. I would also love if somebody shares his/her feedback.

Try this and monitor the o/p of these commands:
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

select sum(dbms_lob.getlength(BDATA))/1024/1024 from MAM_MMO_DATA;


SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_TYPE='LOBSEGMENT' AND TABLESPACE_NAME='QUARKDMS_BLOB' GROUP BY SEGMENT_NAME;

-------Move BLOB data from Database to file server. This is done by our application.----------

alter table MAM_MMO_DATA move lob (bdata) store as (tablespace quarkdms_BLOB);

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

select sum(dbms_lob.getlength(BDATA))/1024/1024 from MAM_MMO_DATA;

SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 FROM USER_SEGMENTS WHERE SEGMENT_TYPE='LOBSEGMENT' AND TABLESPACE_NAME='QUARKDMS_BLOB' GROUP BY SEGMENT_NAME;

alter database datafile 8 resize 2G;

All this works out correctly.