DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: free space occupied by BLOBs previuosly

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by mahajanakhil198 View Post
    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.

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by PAVB View Post
    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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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
  •  


Click Here to Expand Forum to Full Width