ITPub博客

首页 > 应用开发 > IT综合 > 分区使用脚本 NoPartition_To_Partition

分区使用脚本 NoPartition_To_Partition

原创 IT综合 作者:QDZHT 时间:2006-05-13 11:18:57 0 删除 编辑

如果表开始设计为非分区表,以后想变更为分区表,以下是测试脚本:

DROP TABLE t_partition
DROP TABLE t_nopartition

--create partition table
CREATE TABLE t_partition
(object_id NUMERIC,
object_name VARCHAR2(200))
PARTITION BY RANGE (object_id)
(PARTITION pMax VALUES LESS THAN (MAXVALUE) TABLESPACE example)

--creat nopartition table
CREATE TABLE t_nopartition
(object_id NUMERIC,
object_name VARCHAR2(200))

--fill data to nopartition table
INSERT INTO t_nopartition SELECT object_id,object_name FROM Dba_Objects

--exchange
ALTER TABLE t_partition
EXCHANGE PARTITION pMax WITH TABLE t_nopartition

--split
ALTER TABLE t_partition
SPLIT PARTITION pMax AT (5000)
INTO
(
PARTITION p1,
PARTITION pMAX
)

--create local index
CREATE INDEX idx_partition ON t_partition (object_id)
LOCAL
(PARTITION p1 TABLESPACE indx,
PARTITION pMax TABLESPACE indx
)

SELECT * FROM Dba_Ind_Partitions WHERE index_owner = 'MYTEST'

--CREATE global index
CREATE INDEX IDX_PARTITION_GLOBLE ON T_NOPARTITION(OBJECT_NAME)
TABLESPACE INDX

SELECT * FROM Dba_Indexes WHERE index_name = 'IDX_PARTITION_GLOBLE'

--split local index
ALTER TABLE t_partition
SPLIT PARTITION pMAX AT (10000)
INTO
(
PARTITION p2 ,
PARTITION pMAX
)

--rebuild unusable index
ALTER INDEX IDX_PARTITION
REBUILD PARTITION p2 TABLESPACE indx

ALTER INDEX IDX_PARTITION
REBUILD PARTITION pMax TABLESPACE indx

--rename table
ALTER TABLE t_partition RENAME TO t_nopartition

[@more@]

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

上一篇: 没有了~
下一篇: Exp升级问题
请登录后发表评论 登录
全部评论
  • 博文量
    9
  • 访问量
    52842