关闭

alter table xx move tablespace xx

3787人阅读 评论(0) 收藏 举报

SQL> select table_name,tablespace_name from user_tables where table_name='T_NEW';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_NEW USERS

SQL> select num_rows,blocks,last_analyzed from user_tables where table_name='T_NEW';

NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- --------------
50024 180 31-8月 -07

SQL> ALTER TABLE T_NEW MOVE TABLESPACE TEST;

表已更改。

SQL> select num_rows,blocks,last_analyzed from user_tables where table_name='T_NEW';

NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- --------------
50024 180 31-8月 -07

发现统计表的信息依然存在.

但是10G的文档是这么说的:
Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_NEW');
BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,'T_NEW'); END;

*
第 1 行出现错误:
ORA-20000: index "TEST"."IND_T" or partition of such index is in unusable
state
ORA-06512: 在 "SYS.DBMS_STATS", line 13056
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1


SQL> DESC USER_INDEXES
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)

SQL> SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES;

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C005548 EMP
IND_T T_NEW
SYS_C005541 BOOKS
SYS_C005540 AUTHORS
SYS_C005547 DEPT
SYS_C005536 T
IND_FLA1 FLASH1

已选择7行。

SQL> ALTER INDEX IND_T REBUILD;

索引已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_NEW');

PL/SQL 过程已成功完成。

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME ='T_NEW';

TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- --------------
T_NEW 50024 136 03-9月 -07


重新进行表分析后发现索引是已经失效了!
需要重建索引,重新分析后,发现BLOCKS减少
 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:211728次
    • 积分:3481
    • 等级:
    • 排名:第9499名
    • 原创:140篇
    • 转载:8篇
    • 译文:0篇
    • 评论:14条
    JAVA JSP