-
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?
lucky
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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?
lucky
-
No matter if the datafile is 100% empty - while the datafile is there (alive and kicking) space would not be available to O/S.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
lucky
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|