对DML频繁的表执行shrink操作时报 ORA-10635: Invalid segment or tablespace type
操作系统版本:
$ oslevel -s
5300-07-01-0748
数据库版本 :
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.1.0.2.0 - Productio
NLSRTL Version 10.1.0.2.0 - Production
由于表LSKFCDYE的DELETE操作非常频繁,为了降低这批表的高水位线,执行shrink操作。
SQL> ALTER TABLE LSKFCDYE NABLE ROW MOVEMENT ;
Table altered.
当执行shrink操作的时候,结果报如下错误:
SQL> ALTER TABLE LSKFCDY SHRINK SPACE;
alter table LSKFCDY shrink space
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type
从10g ORACLE开始提供Shrink的命令,但10g的这个新特性仅对ASSM表空间有效
查询LSKFCDYE所在表空间管理方式,果然是LOCAL本地管理,因此也就知道错误的原因了
SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='DATAZS';
TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
JYCQDATAZS LOCAL
导致shrink space失败的原因参见Oracle官方介绍
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
IOT mapping tables
Tables with rowid based materialized views
Tables with function-based indexes
...
ORA-10635: Invalid segment or tablespace type
最新推荐文章于 2021-04-13 17:50:20 发布