0

My boss has a concern that creating LOB columns in our database tables could result in effectively permanent storage overhead if we do not partition the table and drop the partitions over time (as opposed to just deleting the records). He thinks that data for the LOB will be stored separately when the record is created, which seems to be correct, but that Oracle may not delete the data for the LOB column when the row is deleted.

Is this correct or even possible? I find it hard to believe that the DBMS would handle object storage so poorly. We are using Oracle 11g if that changes anything.

thesquaregroot
  • 1,414
  • 1
  • 21
  • 35
  • start by reading [here](http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45259). Note that smaller lobs (~4k bytes or smaller) are typically stored inline. How its stored/maintained depends on your setup and your data of course. – tbone Aug 04 '14 at 18:48
  • I've seen this page and just looked through it again and I didn't see much on how/when the out-of-line LOBs are deleted. It's possible I overlooked/missed it though. Is there a specific section you think covers this? – thesquaregroot Aug 04 '14 at 19:53
  • well, if the lobs are stored out of line (in a separate segment, will show as LOBSEGMENT type in user_segments), then deleting from the table will not deallocate the extents. The space is there and available, but will still be allocated. – tbone Aug 05 '14 at 14:19
  • Why wouldn't it deallocate the data? As ntalbs suggests this seems like a resource leak. Is this more of a physical vs logical space issue (like how deleting a file from your hard drive really just removes the reference to it, not the data itself) or does Oracle actually hang on to the LOB? Would that space be re-used to store new data? – thesquaregroot Aug 05 '14 at 21:29
  • The extents that are allocated stay with the table until its dropped or truncated. The space is free however to use for future data for the table. I think your boss wanted to partition the table so that he can drop/truncate a partition, "freeing" the space for other tables/objects. I think thats ok as long as the partitions make sense for the data and how its maintained (I wouldn't force fit some partition scheme just for this reason though). – tbone Aug 06 '14 at 13:12
  • Okay, that makes way more sense. The documentation seems to imply that somewhat but I don't think it's very clear. Could you post this as an answer so I can accept it? – thesquaregroot Aug 09 '14 at 03:06

1 Answers1

0

I eventually found this ask tom question which covers the same topic.

When the database creates an extent to store the LOBs it is associated directly with the table. When rows containing those LOBs are deleted, the LOBs are removed from the extent which remaining the same size (it does not shrink just because a LOB was removed). However, the table is able to reuse the space from the removed LOB as one would expect.

So while the database at large is not able to reuse the free space in an extent created for a table's LOB storage, the space IS free for use by the table. The extent will exist (and therefore take up space that would otherwise be completely available to the database) until table is dropped or truncated, or the extent is explictly deallocated.

In the end, unless the extent(s) remaining allocated to the table is a concern (or there are other concerns that may be addressed through partition), creating partitions solely for this purpose is unnecessary.

thesquaregroot
  • 1,414
  • 1
  • 21
  • 35