RE: How to store CLOBS without wasting storage space

--openmail-part-09237c24-00000001

Content-Type: text/plain; charset=US-ASCII; name="BDY.TXT"
Content-Disposition: inline; filename="BDY.TXT"
Content-Transfer-Encoding: 7bit

Andreas,

I may be way off here, but as per my understanding, out of line LOBs don't use rollback segments and instead use internally pre-allocated space based on the PCTVERSION parameter (which defaults to 10%). The current (non default value?) may affect you storage. Could you check the value of this for the concerned LOBs?

John Kanagaraj
Brunei Shell Petroleum
http://www.geocities.com/john_sharmila

God so loved the world that He didn't send a committee! (See John 3:16 for details)
** Opinions expressed here are solely mine and not necessarily those of my employer **

-----Original Message-----

Sent: Monday, May 15, 2000 3:24 AM
To: Multiple recipients of list ORACLE-L

I have a simple table (docnum char(10), content clob). The table takes about 8000 rows. The complete size of the clob is about 15MB. I created the table (include the LOB storage) with the following storage parameters:

  initial 2M next 2M pctincrease 1

With these settings I want to know how much storage is really allocated. After loading the data with sqlldr I can see that the lob segment of the table has grown up to 42 MB. This is about three time more the data it holds really.

How can I minimize this waste of storage space when using blobs ?

Thanks,
Andreas

--

Author: Andreas Jung
  INET: ajung_at_sz-sb.de

--openmail-part-09237c24-00000001

Content-Type: application/ms-tnef; name="WINMAIL.DAT"
Content-Disposition: attachment; filename="WINMAIL.DAT"
Content-Transfer-Encoding: base64

eJ8+Ik6/AQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5N aWNyb3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEDkAYADAAAAAEAAAADABcAAQAA ABwAAQOQBgAMAAAAAQAAAAMANgAAAAAAOgABBIABADUAAABSRTogSG93IHRvIHN0b3JlIENM T0JTIHdpdGhvdXQgd2FzdGluZyBzdG9yYWdlIHNwYWNlAJQSAQOQBgAMAAAAAQAAAAsAAgAB AAAADwABA5AGABAAAAABAAAAQAA5AKAuWHWcv78BMAQBA5AGACQAAAABAAAAHgBCAAEAAAAS AAAASm9obiBKLiBLYW5hZ2FyYWoAAAA7BgEDkAYAEAAAAAEAAABAAEgAgCOpd5y/vwFnBAED kAYASAAAAAEAAAAeAHAAAQAAADUAAABSRTogSG93IHRvIHN0b3JlIENMT0JTIHdpdGhvdXQg d2FzdGluZyBzdG9yYWdlIHNwYWNlAAAAAFkTAQOQBgAoAAAAAQAAAAIBcQABAAAAFgAAAAG/ v5x1JzMlQ1krgRHUk0IAYJeZEbMAAPEIAQOQBgAMAAAAAQAAAAMABhDTYhliygEBA5AGAAwA AAABAAAAAwAHEB8EAAA+AAEDkAYAeAAAAAEAAAAeAAgQAQAAAGUAAABBTkRSRUFTLElNQVlC RVdBWU9GRkhFUkUsQlVUQVNQRVJNWVVOREVSU1RBTkRJTkcsT1VUT0ZMSU5FTE9CU0RPTlRV U0VST0xMQkFDS1NFR01FTlRTQU5ESU5TVEVBRFVTRUlOAAAAAL4dAQOQBgAMAAAAAQAAAAMA EBAAAAAAJAABA5AGAAwAAAABAAAAAwAREAAAAAAlAAEDkAYAEAAAAAEAAABAAAcwcMHzyJq/ vwF9BQEDkAYAEAAAAAEAAABAAAgwcMHzyJq/vwF+BQEDkAYAIAAAAAEAAAACAQswAQAAABAA AABXQyUzgSvUEZNCAGCXmRGz/AUBA5AGAAwAAAABAAAAAwDeP69vAAA/AgEDkAYAJAAAAAEA AAADAEWBCCAGAAAAAADAAAAAAAAARgAAAAAQhQAAAAAAAJMCAQOQBgAkAAAAAQAAAAMARoEI IAYAAAAAAMAAAAAAAABGAAAAAAGFAAAAAAAAhQIBA5AGACQAAAABAAAAAwBHgQggBgAAAAAA wAAAAAAAAEYAAAAAEYUAAAAAAACWAgEDkAYAJAAAAAEAAAADAEiBCCAGAAAAAADAAAAAAAAA RgAAAABShQAA4xUAANADAQOQBgAsAAAAAQAAAB4ASYEIIAYAAAAAAMAAAAAAAABGAAAAAFSF AAABAAAABAAAADguNQCWAwEDkAYAJAAAAAEAAAALAEqBCCAGAAAAAADAAAAAAAAARgAAAAAG hQAAAAAAAJYCAQOQBgAkAAAAAQAAAAsAS4EIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAA nwIBA5AGACQAAAABAAAAAwBMgQggBgAAAAAAwAAAAAAAAEYAAAAAGIUAAAAAAACiAgEDkAYA LAAAAAEAAAAeAE2BCCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAA3gIBA5AG ACwAAAABAAAAHgBOgQggBgAAAAAAwAAAAAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAOACAQOQ BgAsAAAAAQAAAB4AT4EIIAYAAAAAAMAAAAAAAABGAAAAADiFAAABAAAAAQAAAAAAAADiAgED kAYAJAAAAAEAAAALAFCBCyAGAAAAAADAAAAAAAAARgAAAAAAiAAAAAAAAJwCAQOQBgAkAAAA AQAAAAsAUYELIAYAAAAAAMAAAAAAAABGAAAAAAWIAAAAAAAAogIBA5AGABQAAAABAAAAHgA9 AAEAAAABAAAAAAAAAF4AAQOQBgAMAAAAAQAAAAMAgBD/////kAQBCQAEAAIAAAAAAAAAAQOQ BgAMAAAAAQAAAAsAIwAAAAAALwABA5AGAAwAAAABAAAACwApAAAAAAA1AAEEkAYAxAMAAAIA AAASAAAAAwAAMAQAAAALAA8OAAAAAAIB/w8BAAAARwAAAAAAAACBKx+kvqMQGZ1uAN0BD1QC AAABAG9yYWNsZS1sQGZhdGNpdHkuY29tAFNNVFAAb3JhY2xlLWxAZmF0Y2l0eS5jb20AAB4A AjABAAAABQAAAFNNVFAAAAAAHgADMAEAAAAVAAAAb3JhY2xlLWxAZmF0Y2l0eS5jb20AAAAA AwAVDAEAAAACAfkPAQAAAEcAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAAARBvcmFjbGUtbEBm YXRjaXR5LmNvbQBTTVRQAG9yYWNsZS1sQGZhdGNpdHkuY29tAAADAP4PBgAAAB4AATABAAAA FwAAACdvcmFjbGUtbEBmYXRjaXR5LmNvbScAAAIBCzABAAAAGgAAAFNNVFA6T1JBQ0xFLUxA RkFUQ0lUWS5DT00AAAADAAA5AAAAAAsAQDoAAAAAAwBxOgAAAAAeAPZfAQAAABUAAABvcmFj bGUtbEBmYXRjaXR5LmNvbQAAAAACAfdfAQAAAEcAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAA AQBvcmFjbGUtbEBmYXRjaXR5LmNvbQBTTVRQAG9yYWNsZS1sQGZhdGNpdHkuY29tAAADAP1f AQAAAAMA/18AAAAAAgH2DwEAAAAEAAAAAAAABBIAAAADAAAwBQAAAAsADw4AAAAAAgH/DwEA AAA7AAAAAAAAAIErH6S+oxAZnW4A3QEPVAIAAAEAYWp1bmdAc3otc2IuZGUAU01UUABhanVu Z0Bzei1zYi5kZQAAHgACMAEAAAAFAAAAU01UUAAAAAAeAAMwAQAAAA8AAABhanVuZ0Bzei1z Yi5kZQAAAwAVDAIAAAACAfkPAQAAADsAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAAARBhanVu Z0Bzei1zYi5kZQBTTVRQAGFqdW5nQHN6LXNiLmRlAAADAP4PBgAAAB4AATABAAAAEQAAACdh anVuZ0Bzei1zYi5kZScAAAAAAgELMAEAAAAUAAAAU01UUDpBSlVOR0BTWi1TQi5ERQADAAA5 AAAAAAsAQDoAAAAAAwBxOgAAAAAeAPZfAQAAAA8AAABhanVuZ0Bzei1zYi5kZQAAAgH3XwEA AAA7AAAAAAAAAIErH6S+oxAZnW4A3QEPVAIAAAEAYWp1bmdAc3otc2IuZGUAU01UUABhanVu Z0Bzei1zYi5kZQAAAwD9XwEAAAADAP9fAAAAAAIB9g8BAAAABAAAAAAAAAV/xw== Received on Tue May 16 2000 - 20:08:56 CDT