blob space problem

From: Joan Hsieh <Joan.Hsieh_at_mirror-image.com>
Date: Fri, 09 Mar 2001 08:33:49 -0800
Message-ID: <F001.002C855C.20010309074658@fatcity.com>

Hi Dear List,

Does anyone can tell me how oracle manage the blob space?

I have a table with a blob column in it. The contents of the blob columns are stored in a different tablespace. my question is what happens to the "space occupied" by blob in the tablespace when it is deleted. Looks like we are very fragment on this tablespace. Right now the tablespace has 75 datafiles (4g each) and filled up very quickly. The problem is we can't continue to buy the disk. How we can reused the space?

OWNER

SEGMENT_NAME

PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME


------------------------------ ------------------ --------------------------

----

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT

----------- ------------ ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
----------- ----------- ----------- ------------ ---------- ---------------
RELATIVE_FNO BUFFER_
------------ -------

XPC
SYS_IL0000002887C00003$$

                               LOBINDEX           XPC_OBJ_LOB
          9        31877 4699987968     573729         18      261095424
  261095424           1  2147483645            0         99               2
           9 DEFAULT

XPC
OBJECTS_BLOB

                               LOBSEGMENT         XPC_OBJ_LOB
          9            2 2.9350E+11   35827300       1124      261095424
  261095424           1  2147483645            0         99               2
           9 DEFAULT

SQL:(mii1)>desc objects
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------


----

 OBJ_HASH                                  NOT NULL CHAR(16)
 OBJ_ENT_COUNT                                      NUMBER
 OBJ_BLOB                                           BLOB

SQL:(mii1)>select count(*) from dba_data_files where tablespace_name='XPC_OBJ_LOB';

COUNT(*)

Thanks,

Joan

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joan Hsieh
  INET: Joan.Hsieh_at_mirror-image.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Mar 09 2001 - 10:33:49 CST