I'm trying to understand this. How many records are in the table? Is it four? If so 115,437,005 / 4 gives an avg_row_len of about 27.5 MB. AVG_ROW_LEN for a table does not include the lob segment; the number is terribly high. What is your table definition including the definition of your lob segment?
Here's one of mine, slightly altered. The database version is 8.1.6.3 CREATE TABLE x (FILE_ID NUMBER(7,0) NOT NULL ,FILE_TYPE VARCHAR2(1) NOT NULL ,FILE_FORMAT VARCHAR2(1) NOT NULL ,FILE_NAME VARCHAR2(80) NOT NULL ,FILE_PRIVATE VARCHAR2(1) NOT NULL ,COMMENTS VARCHAR2(240) ,FILE_DATA BLOB ,DATE_CREATED DATE NOT NULL ,CREATED_BY VARCHAR2(30) NOT NULL ,DATE_UPDATED DATE ,UPDATED_BY VARCHAR2(30) ,CONTENT_TYPE VARCHAR2(240) ,CONTENT_DISPOSITION VARCHAR2(240) Lob(FILE_DATA) STORE AS loby (TABLESPACE zzzzzzz PCTVERSION 5 ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING) The bp_files table is in a tablespace with locally managed extents with a uniform size 0f 128K. The lob segment is in a locally managed tablespace with 4 MB uniform extents. Sizes of the individual lobs vary with the largest one being just over 15 MB The avg_row_len of bp_files is 201 bytes. The size of the lob segment according to debasements is 742,391,808 bytes whereas SELECT SUM(dbms_lob.getlength(file_data) from x is 729,736,842. The difference between these numbers averages less than 7 KB per BLOB. It appears you are trying to apply table physical attribute parameters to the lob segment. Take a look at pctversion defined in the docs as .... The maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used. You would set your pctversion to 0 if no updates will take place. Ian -----Original Message----- Sent: Wednesday, March 06, 2002 9:54 AM To: Multiple recipients of list ORACLE-L I am in the process of storing 32Mb image files into a 8.1.7 database, I have set pct_free to 0, but I left pct_used at 90 and the tablespace is locally managed with a uniform extent of 32Mb. The database has a db_block_size of 8k. I am considering moving it to 16k, as opposed to 32k, as it has a mix of smaller files ( > 8k, < 7Mb) and then the larger tables ( 32 Mb). When I loaded 4 images (to create one image), and analyzed the tables, I received the following output. Size (numrow*avg_row_len) = 115,437,005 Bytes (Segments) = 167,772,160 Blocks = 20,480 Extents = 5 As you can see, I am wasting 50Mbs of space for 4 images. I realize that if I add another image to the larger one, I most likely only waste 18 Mbs. I have figured out that this wastage is due to the pct_used parameter. After re-re-re-reading the documents, I should have set the pct_used higher then 90. Question : Should I set pct_used to 100 or 99 ? Once these images are loaded,the tablespace will be set to READONLY. No updates will occur. Thanks Darren ---------------------------------------------------------------------------- ---------------------------------------------- Darren Browett P.Eng This message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] ---------------------------------------------------------------------------- ----------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
