Splist分区要谨慎

USER1@ora11g>create table t1(x int,y int) partition by range(x) (partition partmax values less than (maxvalue)) tablespace USERS;

Table created.

Elapsed: 00:00:00.02
USER1@ora11g>insert into t1 select rownum,rownum from dba_objects where object_id <20;

18 rows created.

Elapsed: 00:00:00.02
USER1@ora11g>commit;

Commit complete.

Elapsed: 00:00:00.01
USER1@ora11g>create index idx_x on t1(x) global;

Index created.

Elapsed: 00:00:00.02
USER1@ora11g>create index idx_y on t1(y) local;

Index created.

Elapsed: 00:00:00.03
USER1@ora11g>select index_name,PARTITIONED,status from user_indexes;

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
IDX_X                          NO  VALID
IDX_Y                          YES N/A

Elapsed: 00:00:00.04
USER1@ora11g>select index_name,partition_name,tablespace_name,status from user_ind_partitions ;

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME      STATUS
------------------------------ ------------------------------ -------------------- --------
IDX_Y                          PARTMAX                        USERS                USABLE

Elapsed: 00:00:00.00

USER1@ora11g>alter table t1 split partition partmax at (10) into (partition P10 tablespace USERS,partition partmax tablespace USERS);

Table altered.

Elapsed: 00:00:00.18

USER1@ora11g>select index_name,partition_name,tablespace_name,status from user_ind_partitions ;

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME      STATUS
------------------------------ ------------------------------ -------------------- --------
IDX_Y                          PARTMAX                        USERS                UNUSABLE
IDX_Y                          P10                            USERS                UNUSABLE

Elapsed: 00:00:00.00
USER1@ora11g>select index_name,PARTITIONED,status from user_indexes;

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
IDX_X                          NO  UNUSABLE
IDX_Y                          YES N/A

Elapsed: 00:00:00.03
USER1@ora11g>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7364032/viewspace-615929/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7364032/viewspace-615929/