Geek Answers Handbook

How to return storage for deleted large objects

I have a LOB tablespace. Currently there are 9 GB of 12 GB. And as far as I can tell, deleting records does not return any storage in the tablespace. (This was a simple method for monitoring storage - requests from user_extents, which applies to everything that I am allowed to do as non-DBA). I am concerned about the processing of further processing.

My concern just ends - we cost about 9 GB of the 12 GB available for the table space, and I want to figure out how to free up space before asking for more.

LOB columns are stored in a separate table space, although small files are allowed to be stored in a row.

This is Oracle 11.1, and the data is in the CLOB and BLOB columns in the same table. The LOB index segments (SYS_IL ...) are small, the entire storage is in the data segments (SYS_LOB ...)

We tried to clear and merge and did not get anywhere - the same number of bytes in user_extents.

"Changing the course of table xxx" will work, but we will need to move it somewhere to have enough space for the revised data. We would also have to do this after hours and, of course, rebuild the indexes, but this is easy enough.

Copy good data and truncate, then copy them back will also work. But this is pretty much what the alter table command does.

? " xxx move" ? , Oracle lob, ?

+5
2

, , , .

, LOB .

LOB

select bytes
    from user_segments
    where tablespace_name = yourTableSpace
      and segment_name = yourTableName;

Oracle doc, ,

ALTER TABLE yourLobTable MODIFY LOB (yourLobColumn) (SHRINK SPACE);

BASICFILE LOB.

,

select bytes
    from user_segments
    where tablespace_name = yourTableSpace
      and segment_name = yourTableName;

, .

, , , - .

, ( , ), ( , )

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

, KEEP , . , , DBA_FREE_SPACE.

, ,

ALTER DATABASE DATAFILE yourDatafile.dbf resize 500M

, , , . , . - , . Oracle .

, , - (5 ). , :

SELECT tablespace_name, file_name, bytes
  FROM dba_temp_files WHERE tablespace_name like 'TEMP%';
+6

, , , . , . , , , 1 100, - 50-75 - 1,3,5,7 ..

, , , , ?

+1

More articles:


All Articles