?
Solved

lobsegments

Posted on 2011-02-17
19
Medium Priority
?
520 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 1664 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 336 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 1664 total points
ID: 34918453
>> ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);


FYI:  I believe this requires 10gR2 or above.
0
Industry Leaders: 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 1664 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 336 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 1664 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 1664 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 1664 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 1664 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 1664 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 1664 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 1664 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

771 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