space taken by table - including smart large obj,

I'm looking for a way to determine the amount of space "attributable" to a table, including the space taken by any smart large objects referenced by rows in the table. I realize that smart large objects technically aren't considered part of a single table, since there is can be a many-to-many relationship between smart large objects and table rows, but in the case I have in mind every smart large object is referenced by a single row of a single table. I've found lots of SQL for determining space taken by a table when you don't consider smart large objects, but nothing for the case I'm interested in. Since table rows contain smart large object references I figure there must be a way to follow those references and aggregate the space consumption of all the referenced objects. Thanks for any help you can provide! Mike

The simplest approach is to select sum(length(column)) for the whole table. which also works for simple large objects (BYTE and TEXT). FYI The normal calculation for the table will include include - the 72 byte smart LO descriptor which is stored on the date page for the associated row. - the 56 byte descriptor stored in the data row for simple large objects. If you want a complete answer then also include sbspace metadata which is much more complex. The sbspace metadata area contains: - Sbspace description partition a single structure to describe the smart blobspace which includes the attributes for the smart blobspace (-Df attributes used at creation time) - Chunk adjunct partition - information about each chunk in the partition including location and size of user-data metdata areas and location of the LO header partition for each chunk. This contains 1 row per chunk and it in the first chunk of the sbspace - LO header partition - For each smart LO in the chunk this describe the datetime the LO was created, the size of the LO and other attributes and includes an extent list allocated to the smart LO. There is one LO header partition per chunk - UD free list partition which tracks free extents in the user data areas of a chunk oncheck -pe will display this information including one line per smartblob e.g. SBLOBSpace [4,4,1] - Smart LO handle (sbspace,chunk,logical sequence number). In addition oncheck -cS will show metadata and extent information including the partnum for the LO partition header. This can then be fed into oncehck -pD to get the contents of the metadata tables. oncheck -g smb has several options smb s - sbspaces smb c - sbspace chunks smb h - LO header table header smb e - LO header and FD entries smb lod - LO header table header and entries smb fdd - LO file descriptor (FD) entries The e and lod options provide an list of entries in the LO header table which represent each smart LO in the sbspace including. - size Smart LO size - Smart LO handle Regards, David. On 21 March 2013 at 01:44 MIKE DUNHAM-WILKIE <mike@barrodale.com> wrote: > I'm looking for a way to determine the amount of space "attributable" to a > table, including the space taken by any smart large objects referenced by rows > in the table. I realize that smart large objects technically aren't considered > part of a single table, since there is can be a many-to-many relationship > between smart large objects and table rows, but in the case I have in mind > every smart large object is referenced by a single row of a single table. I've > found lots of SQL for determining space taken by a table when you don't > consider smart large objects, but nothing for the case I'm interested in. > Since table rows contain smart large object references I figure there must be > a way to follow those references and aggregate the space consumption of all > the referenced objects. > > Thanks for any help you can provide! > > Mike > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >