Solved

lobsegments

Posted on 2011-02-17
19
501 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
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 76

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
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
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 76

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 76

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 76

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 76

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 76

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 76

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 76

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

808 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