• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

lobsegments

Is there any way to reclaim the unused space in a lobsegment/lob index? I understand that a column with a the lob will create and index that points to the chunk of data. I moved the table out of the tablespace and left only it lobsegments and lob index. There is 8G of unused space in this tablespace. When I move the logsegments out into another tablespace the index follows it automatically. However, no space is freed up unlike when you move table and index to another tablespace. I am also not able to resize the datafile which is no surprise since this is  why we move indexes and tables out of a tablespace in the first place in order to reclaim the unused space. Is there any way to reclaim the unused space in a lobsegment/lob index?
0
msimons4
Asked:
msimons4
  • 10
  • 7
  • 2
12 Solutions
 
slightwv (䄆 Netminder) Commented:
Can you not use the new 10g features to shrink/re-org datafiles that have been posted in some of your other question on this topic?
0
 
Aaron ShiloCommented:
hi

run this command and your trubles will be over.
(10g and above)
 ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);
0
 
slightwv (䄆 Netminder) Commented:
>> ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);


FYI:  I believe this requires 10gR2 or above.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
and:
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2143
0
 
Aaron ShiloCommented:
hi @slightwv

i belive that is what i posted.

and localy managed tbs is the default since oracle 9.2
0
 
slightwv (䄆 Netminder) Commented:
>>i belive that is what i posted.

I copied what you posted and used ">>" to show what I was tying after it were comments directly related to it.

>>and localy managed tbs is the default since oracle 9.2

and?
0
 
msimons4Author Commented:
I ran ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);and then moved to another tablespace but I still have 8G free but cannot resize the datafile, probably because I have still extents at the end of the datafile.

 
0
 
slightwv (䄆 Netminder) Commented:
Are you sure nothing else is in that tablespace?
0
 
msimons4Author Commented:
Sure, I just created it. Using 10gR2 and ASSM is auto. I moved the table with the lob column into it and there was no free space. Then I moved the lobsegments into it and it showed 8G free but I can't resize. Data exceeds resize value?
0
 
slightwv (䄆 Netminder) Commented:
You are trying to resize the new one?
0
 
msimons4Author Commented:
When I query free space I see there is 8G free in the tablespace that has only this logsegment. So I am trying to reclaim this space. I shrunk it, then moved it to a tablespace created just for this move and it is still showing 8G free but I cannot even resize the datafile by 1M.
0
 
slightwv (䄆 Netminder) Commented:
What is the inital extent size of the lob segment in question?  It's the INITIAL_EXTENT column in USER_SEGMENTS.

Also, I might be wrong but just because you inserted an object into a brand new datafile, I don't think Oracle is 'required' to allocate all the extents at the beginning.

It might do that but that would be a question for Oracle Internals folks.
0
 
msimons4Author Commented:
I expdp the table. Then I renamed the table. Then I impdp the table. Now both tables sit in the same tablespace. When I checked the free space was gone. What do you think happened here?
0
 
msimons4Author Commented:
I know what happened, the impdp took up the free space because there was 8G free and the table is 8G. So when I drop the table and move all contents to a new tablespace I'll have me 8G again which I can't reclaim. I guess it has something to do with lobs, what do you think?
0
 
slightwv (䄆 Netminder) Commented:
To be honest, I'm confused.

You used impdp to import the table, then you dorpped it, moved it to a new tablespace, ???

I'm not following the steps you did and where we are now.
0
 
msimons4Author Commented:
First I moved the table with the lob column into a newly created tablespace and there was no free space to reclaim. Then I moved the lobsegment which took the lob indexes with it into the same tablespace and in doing so created space that could no by be reclaimed, 8G. When I expdp/impdp the table with the lob column into the desired tablespace, the lobsegments/lob indexes showed up and no unclaimable free space was created.
0
 
slightwv (䄆 Netminder) Commented:
Moving all the objects should allow for datafile resize if there is freespace being reported.  If after moving the objects and there is 8G showing free and you cannot resize the datafile, I would contact Oracle Support.  You might be hitting a bug with lob segments.
0
 
msimons4Author Commented:
I certainly works for tables and indexes, but there is something up with these lobsegments.
0
 
slightwv (䄆 Netminder) Commented:
Lobs are funny creatures.  They create objects behind the scenes that might be messing you up.  Move is 'supposed' to keep everything nice and compact but it's possible you are hitting a bug.

Only Oracle Support can confirm what is happening and why you cannot shrink the datafile after moving lob segments.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 10
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now