Solved

lobsegments

Posted on 2011-02-17
19
511 Views
Last Modified: 2012-05-11
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
Comment
Question by:msimons4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 2
19 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 416 total points
ID: 34918417
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
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 84 total points
ID: 34918423
hi

run this command and your trubles will be over.
(10g and above)
 ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34918453
>> ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);


FYI:  I believe this requires 10gR2 or above.
0
Independent Software Vendors: 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!

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34918471
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
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 84 total points
ID: 34918561
hi @slightwv

i belive that is what i posted.

and localy managed tbs is the default since oracle 9.2
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34918589
>>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
 

Author Comment

by:msimons4
ID: 34919351
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34919404
Are you sure nothing else is in that tablespace?
0
 

Author Comment

by:msimons4
ID: 34919634
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34919712
You are trying to resize the new one?
0
 

Author Comment

by:msimons4
ID: 34920040
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34920520
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
 

Author Comment

by:msimons4
ID: 34920672
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
 

Author Comment

by:msimons4
ID: 34920702
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34920795
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
 

Author Comment

by:msimons4
ID: 34951634
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34951691
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
 

Author Comment

by:msimons4
ID: 34951775
I certainly works for tables and indexes, but there is something up with these lobsegments.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
ID: 34952213
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question