Solved

lobsegments

Posted on 2011-02-17
19
493 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
  • 10
  • 7
  • 2
19 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 416 total points
Comment Utility
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
Comment Utility
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 76

Assisted Solution

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


FYI:  I believe this requires 10gR2 or above.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
Comment Utility
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
Comment Utility
hi @slightwv

i belive that is what i posted.

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
Comment Utility
>>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
Comment Utility
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 76

Assisted Solution

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

Author Comment

by:msimons4
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

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

Author Comment

by:msimons4
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
Comment Utility
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
Comment Utility
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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 416 total points
Comment Utility
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
Comment Utility
I certainly works for tables and indexes, but there is something up with these lobsegments.
0
 
LVL 76

Assisted Solution

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now