SQL> desc newapas.apas_attr
Name Null? Type
----------------------------------------- -------- ----------------------------
UNID NOT NULL VARCHAR2(32)
PUNID NOT NULL VARCHAR2(32)
NAME NOT NULL VARCHAR2(4000)
SAVESTATE VARCHAR2(10)
FILENAME VARCHAR2(255)
FILESIZE VARCHAR2(50)
FILEPATH VARCHAR2(500)
ISCHECKED VARCHAR2(20)
FORMPROP VARCHAR2(40)
CREATEDATE VARCHAR2(20)
MATERIALID VARCHAR2(32)
FROMSHARE VARCHAR2(32)
CONFILE BLOB
LOCKED VARCHAR2(2)
SORTID NUMBER(38)表中有一个blob类型的字段confile,现在希望把该字段的内容清空,并且回收空间,做如下操作:
(1)查询原来所占空间:
SQL> select segment_name,bytes,blocks,extents from dba_segments
2 where owner='NEWAPAS' and (segment_name='APAS_ATTR' or segment_name in
3 (select segment_name from dba_lobs where owner='NEWAPAS' and table_name='APAS_ATTR'))
4 /SEGMENT_NAME BYTES BLOCKS EXTENTS
---------------------------------------- ---------- ---------- ----------
APAS_ATTR 134217728 16384 72
SYS_LOB0000083672C00013$$ 1.0173E+11 12417664 1755(2)把confile字段清空:
SQL> update newapas.apas_attr set confile='' ;(3)查询此时所占空间,发现没有改变:
SQL> select segment_name,bytes,blocks,extents from dba_segments
2 where owner='NEWAPAS' and (segment_name='APAS_ATTR' or segment_name in
3 (select segment_name from dba_lobs where owner='NEWAPAS' and table_name='APAS_ATTR'))
4 ;SEGMENT_NAME BYTES BLOCKS EXTENTS
---------------------------------------- ---------- ---------- ----------
APAS_ATTR 134217728 16384 72
SYS_LOB0000083672C00013$$ 1.0173E+11 12417664 1755(4)执行shrink space后再查询:
SQL> alter table newapas.apas_attr enable row movement;Table altered.SQL> alter table newapas.apas_attr shrink space;Table altered.SQL> select segment_name,bytes,blocks,extents from dba_segments
2 where owner='NEWAPAS' and (segment_name='APAS_ATTR' or segment_name in
3 (select segment_name from dba_lobs where owner='NEWAPAS' and table_name='APAS_ATTR'))
4 ;SEGMENT_NAME BYTES BLOCKS EXTENTS
---------------------------------------- ---------- ---------- ----------
APAS_ATTR 109445120 13360 70
SYS_LOB0000083672C00013$$ 1.0173E+11 12417664 1755发现还是基本没改变。请问:该如何回收该字段所占用的空间呢?谢谢!
Name Null? Type
----------------------------------------- -------- ----------------------------
UNID NOT NULL VARCHAR2(32)
PUNID NOT NULL VARCHAR2(32)
NAME NOT NULL VARCHAR2(4000)
SAVESTATE VARCHAR2(10)
FILENAME VARCHAR2(255)
FILESIZE VARCHAR2(50)
FILEPATH VARCHAR2(500)
ISCHECKED VARCHAR2(20)
FORMPROP VARCHAR2(40)
CREATEDATE VARCHAR2(20)
MATERIALID VARCHAR2(32)
FROMSHARE VARCHAR2(32)
CONFILE BLOB
LOCKED VARCHAR2(2)
SORTID NUMBER(38)表中有一个blob类型的字段confile,现在希望把该字段的内容清空,并且回收空间,做如下操作:
(1)查询原来所占空间:
SQL> select segment_name,bytes,blocks,extents from dba_segments
2 where owner='NEWAPAS' and (segment_name='APAS_ATTR' or segment_name in
3 (select segment_name from dba_lobs where owner='NEWAPAS' and table_name='APAS_ATTR'))
4 /SEGMENT_NAME BYTES BLOCKS EXTENTS
---------------------------------------- ---------- ---------- ----------
APAS_ATTR 134217728 16384 72
SYS_LOB0000083672C00013$$ 1.0173E+11 12417664 1755(2)把confile字段清空:
SQL> update newapas.apas_attr set confile='' ;(3)查询此时所占空间,发现没有改变:
SQL> select segment_name,bytes,blocks,extents from dba_segments
2 where owner='NEWAPAS' and (segment_name='APAS_ATTR' or segment_name in
3 (select segment_name from dba_lobs where owner='NEWAPAS' and table_name='APAS_ATTR'))
4 ;SEGMENT_NAME BYTES BLOCKS EXTENTS
---------------------------------------- ---------- ---------- ----------
APAS_ATTR 134217728 16384 72
SYS_LOB0000083672C00013$$ 1.0173E+11 12417664 1755(4)执行shrink space后再查询:
SQL> alter table newapas.apas_attr enable row movement;Table altered.SQL> alter table newapas.apas_attr shrink space;Table altered.SQL> select segment_name,bytes,blocks,extents from dba_segments
2 where owner='NEWAPAS' and (segment_name='APAS_ATTR' or segment_name in
3 (select segment_name from dba_lobs where owner='NEWAPAS' and table_name='APAS_ATTR'))
4 ;SEGMENT_NAME BYTES BLOCKS EXTENTS
---------------------------------------- ---------- ---------- ----------
APAS_ATTR 109445120 13360 70
SYS_LOB0000083672C00013$$ 1.0173E+11 12417664 1755发现还是基本没改变。请问:该如何回收该字段所占用的空间呢?谢谢!
然后把原来的TRUNCATE,然后重命名。
SQL> alter table newapas.apas_attr modify (confile blob) shrink space;
alter table newapas.apas_attr modify (confile blob) shrink space
*
ERROR at line 1:
ORA-22296: invalid ALTER TABLE option for conversion of LONG datatype to LOB
update newapas.apas_attr set confile='' ;
而是:
update newapas.apas_attr set confile='' where ...;
即有些记录的内容留着有些记录的内容清空,那该怎么做呢?
analyze table newapas.apas_attr compute statistics;
Segment shrink is not supported for LOB segments even if CASCADE is specified.2、使用move是可以的。
alter table newapas.apas_attr move lob(confile) store as(tablespace 指定表空间名称);3、你也可以使用create table...as select..建立一个新表或者导入导出方式。
但我记得如果只想回收LOB字段的表空间是可以的,仔细看了下我2楼语句好像反了
你重新试试呢:
alter table newapas.apas_attr modify lob(confile) shrink space;
做完之后再分析下表:
analyze table newapas.apas_attr compute statistics;如果还是不行,也许就是唐人说的shrink space不支持lob字段。