函数索引与段收缩！

ylw6006

可否介绍下为什么基表在存在函数索引或者全文索引的时候就不支持shrink space操作，如果存在这些索引，进行段收缩前是否一定要删除这些索引，收缩完后重建？是否有其他更好的方式实现？

reference：http://www.itpub.net/thread-1605020-1-1.html

ylw6006

thanks,回答的很详细！

Maclean Liu(刘相兵

[oracle@vrh8 ~]$ oerr ora 10631
10631, 00000, "SHRINK clause should not be specified for this object"
// *Cause: It is incorrect to issue shrink on the object
// *Action: Verify the object name and type and reissue the command

ODM TEST:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table t_shrink;

Table dropped.

SQL> conn maclean/maclean
Connected.
SQL>
SQL>
SQL> create table t_shrink as select * from dba_source;

Table created.

SQL> exec dbms_stats.gather_table_stats('','T_SHRINK',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> delete from t_shrink where rownum < 600000;

295678 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t_shrink enable row movement;

Table altered.

SQL> alter table t_shrink shrink space;

Table altered.

SQL> create index i_shrink on t_shrink(upper(name));

Index created.

SQL> alter table t_shrink shrink space;
alter table t_shrink shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object







SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8: 10631 trace name errorstack level 4;
Statement processed.
SQL> alter table maclean.t_shrink shrink space;
alter table maclean.t_shrink shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object


SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_11594.trc







select count(*) from sumkey$ where detailobj# = :1 and  detailcolfunction = 3



PARSING IN CURSOR #2 len=77 dep=1 uid=0 oct=3 lid=0 tim=1305717448254666 hv=3003638668 ad='8c91aa88'
select count(*) from sumkey$ where detailobj# = :1 and  detailcolfunction = 3
END OF STMT
PARSE #2:c=0,e=351,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1305717448254659
EXEC #2:c=2000,e=1639,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1305717448256362
FETCH #2:c=0,e=127,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1305717448256531
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3 pr=0 pw=0 time=150 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=406 op='TABLE ACCESS FULL SUMKEY$ (cr=3 pr=0 pw=0 time=86 us)'
*** 2012-05-15 00:04:27.014
ksedmp: internal or fatal error
ORA-10631: SHRINK clause should not be specified for this object
Current SQL statement for this session:
alter table maclean.t_shrink shrink space
----- Call Stack Trace -----














递归SQL 检测到对象上存在 function -based index后 报错 ORA-10631

stack call  为 opiexe=> atbdrv => atb_shk_drv => atb_shk_prim => ktskshk1=> ktsk_init_shk_ctx=> ktsk_init_heap_structures => kdsh_load_index =>ksesec0 报错



SHRINK SPACE会导致表上的rowid发生变化， 默认情况下SHRINK SPACE会维护索引。

但是函数索引较为特殊，维护函数索引的代价会相当高，且可能难以保证在线完成。 所以ORACLE到目前版本11.2.0.3 都不支持有函数索引Function-INDEX 的表上做 SHRINK SPACE操作。


但是  FUNCTION-INDEX 本身做SHRINK SPACE则是可以的



SQL> alter index i_shrink shrink space;

Index altered.








select count(*) from sumkey$ where detailobj# = :1 and  detailcolfunction = 3
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=55746


SQL> select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=55746;

      POS#    INTCOL#       COL#     SPARE1        BO#     SPARE2
---------- ---------- ---------- ---------- ---------- ----------
         1          6          0          1      55745          0

create table sumkey$             /* summary key table */
( sumobj#       number not null, /* parent summary obj */
  sumcolpos#    number not null, /* position of column in select list */
  containercol# number not null, /* pos. in container where stored */
  detailobj#    number not null, /* detail table object from FROM */
  qbcid         number not null, /* query block id */
  detailobjtype number,          /* detail table type - see sumdetail$ */
  detailcol#    number,          /* col number within detail table */
  groupingpos#  number,          /* pos. in GB clause */
  exprlen       number,
  expression    varchar2(4000),
  textlen       number,
  text          long,
  detailcolfunction number,      /* 0 = regular, 1 = partition key */
                                 /* 2 = partition marker, 3 = rowid */
  spare1        number,
  spare2        number,          /* length of column datatype info text */
  spare3        varchar2(1000),  /* column datatype info text */
  spare4        date,
  nodetype      number,          /* node type - see qkegstyp */
  ordinalpos    number,          /* for GSets - ordinal position */
  parentpos     number,          /* for GSets - parent postion (ordinal) */
  inline#       number,          /* inline view number */
  instance#     number           /* instance # for duplicate table */
)
/


ODM FINDING:

ORA-10631 When Trying To Shrink A Table That Has A Function-based Index.


Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 10-Nov-2010***
Symptoms

Attempts to shrink a table using SQL*PLUS fail with error :

ORA-10631: SHRINK clause should not be specified for this object.
Cause

This is an expected behavior according to the documentation.

Oracle? Database SQL Reference
10g Release 2 (10.2)
Part Number B14200-02

ALTER TABLE

Restrictions on the shrink_clause

The shrink_clause is subject to the following restrictions:

You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.

Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.

This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.

You cannot specify this clause for a compressed table.

You cannot shrink a table that is the master table of an ON COMMIT materialized view.

Rowid materialized views must be rebuilt after the shrink operation.
Solution

To implement the solution, please execute the following steps:

1- Get the DDL of the function-based index on the table.

select INDEX_NAME, INDEX_TYPE
         from dba_indexes
         where table_owner = '<owner>' and
         table_name = '<tabname>';

2- Drop the function-based index.
3- Shrink the table.
4- Recreate the index again on the table.