Hi,
We need to reclaim unused LOB space on database.
Our database supports OTM application and there are three tables with CLOB column: I_LOG, I_TRANSACTION and I_TRANSMISSION - are so huge...
Last week we implemented the out of the box procedure domainman.transmission_purge in order to keep only 60 days for I_tables.
But, unused LOB space on database was not reclaimed, so I followed this doc: (http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#CHDHBHAB)
for try to reclaim unused LOB space on database, and nevertheless unused LOB space on database was not reclaimed.
Please, anybody can help me?
--identifying columns:
SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME
2 from dba_lobs
3 where TABLE_NAME ='I_LOG'
4 /
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
GLOGOWNER I_LOG I_MESSAGE_TEXT I_LOG_I_MESSAGE_TEXT
SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME
2 from dba_lobs
3 where TABLE_NAME ='I_TRANSACTION'
4 /
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
GLOGOWNER I_TRANSACTION XML_BLOB I_TRANSACTION_XML_BLOB
SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME
2 from dba_lobs
3 where TABLE_NAME ='I_TRANSMISSION'
4 /
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
GLOGOWNER I_TRANSMISSION XML_BLOB I_TRANSMISSION_XML_BLOB
--looking free space before run SHRINK SPACE:
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name
order by 4 desc
/
TABLESPACE_NAME size MB free MB % used
---------------------------------------- ------------ ----------- ------
TEMP **TEMP** 6,000 0 100
LOB3 73,000 3,640 95
LOB2 46,500 3,935 91
DATA 21,000 3,058 85
LOB1 23,000 4,085 82
SYSAUX 17,000 2,984 82
LOB4 21,000 3,805 81
UNDOTBS2 6,000 1,559 74
REPORT 173 50 71
SYSTEM 1,000 285 71
ARCHIVE 100 31 69
INDX 15,000 5,094 66
BPL_DAY7 15 5 66
BPL_DAY5 15 5 66
BPL_DAY6 15 5 66
LOB7 150 60 60
MSG_PART_TBS1 50 23 54
BPL_DAY3 15 8 46
BPL_DAY4 15 8 46
MSG_LOB_TBS1 500 285 43
BPL_DAY1 10 6 40
PART_3 3,000 1,796 40
PART_1 1,978 1,175 40
LOB5 250 155 38
REPORTINDX 30 19 36
BPL_DAY2 25 17 32
PART_2 2,500 1,872 25
LOB6 100 75 25
PART_4 12,532 11,075 11
DATA_DBA 75 69 9
USERS 50 49 2
31 rows selected.
--Running SHRINK SPACE
SQL> alter table I_TRANSACTION MODIFY lob (XML_BLOB) (SHRINK SPACE);
Table altered.
Elapsed: 00:04:38.29
SQL>
SQL> alter table I_LOG MODIFY lob (I_MESSAGE_TEXT) (SHRINK SPACE);
Table altered.
Elapsed: 00:00:28.07
SQL>
SQL> alter table I_TRANSMISSION MODIFY lob (XML_BLOB) (SHRINK SPACE);
Table altered.
Elapsed: 00:00:42.04
--looking free space after run SHRINK SPACE:
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name
order by 4 desc
/
TABLESPACE_NAME size MB free MB % used
---------------------------------------- ------------ ----------- ------
TEMP **TEMP** 6,000 0 100
LOB3 73,000 3,640 95
LOB2 46,500 3,935 91
DATA 21,000 3,058 85
LOB1 23,000 4,085 82
SYSAUX 17,000 2,984 82
LOB4 21,000 3,790 81
UNDOTBS2 6,000 1,493 75
REPORT 173 50 71
SYSTEM 1,000 285 71
ARCHIVE 100 31 69
INDX 15,000 5,093 66
BPL_DAY7 15 5 66
BPL_DAY5 15 5 66
BPL_DAY6 15 5 66
LOB7 150 60 60
MSG_PART_TBS1 50 23 54
BPL_DAY3 15 8 46
BPL_DAY4 15 8 46
MSG_LOB_TBS1 500 285 43
BPL_DAY1 10 6 40
PART_3 3,000 1,796 40
PART_1 1,978 1,175 40
LOB5 250 155 38
REPORTINDX 30 19 36
BPL_DAY2 25 17 32
PART_2 2,500 1,872 25
LOB6 100 75 25
PART_4 12,532 11,075 11
DATA_DBA 75 69 9
USERS 50 49 2
31 rows selected.