Oracle的分區操作和修改分區主鍵

1、增加一個分區

ALTER TABLE sales

ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )

TABLESPACE tsx;

增加一個清單分區

ALTER TABLE q1_sales_by_region

ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')

STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3

NOLOGGING;

2、合并分區

alter table dept coalesce partition

3、删除分區

ALTER TABLE sales DROP PARTITION dec98;

ALTER INDEX sales_area_ix REBUILD（如果含有全局索引）;

4、合并分區

ALTER TABLE four_seasons

MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;

Then, rebuild the local index for the affected partition.

-- Rebuild index for quarter_two, which has been marked unusable

-- because it has not had all of the data from Q1 added to it.

-- Rebuilding the index will correct this.

--

ALTER TABLE four_seasons MODIFY PARTITION

quarter_two REBUILD UNUSABLE LOCAL INDEXES;

5、移動一個分區

ALTER TABLE parts MOVE PARTITION depot2

TABLESPACE ts094 NOLOGGING;

6、重建一個local索引

ALTER INDEX I_FOUR_SEASONS_L REBUILD PARTITION I_QUARTER_FOUR

7、重命名一個分區

ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks

8、一個分區拆分為兩個分區

ALTER TABLE RANGE_EXAMPLE SPLIT PARTITION

PART_1 at

(TO_DATE(' 1994-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))

INTO ( PARTITION PART_1 tablespace st1,

PARTITION PART_3 tablespace users)

9、truncate一個分區

ALTER TABLE sales TRUNCATE PARTITION dec98;

ALTER INDEX sales_area_ix REBUILD;

10、如果存在限制的情況，先disable限制

ALTER TABLE sales

DISABLE CONSTRAINT dname_sales1;

ALTER TABLE sales TRUNCATE PARTITTION dec94;

ALTER TABLE sales

ENABLE CONSTRAINT dname_sales1;

11、修改分區主鍵---要打開行遷移功能

例：分區主鍵為330482

alter table lymx enable row movement;--開啟行遷移

update lymx set lqbabh='e05d5fbf13684a42aaf00226093d4e9c',xzqbm='330482',fzjgbh='f4734d2a1d494987873b0b42132e63b6',gxsj=to_date('2014/01/16 14:41:39','yyyy/MM/dd hh24:mi:ss'),tjsj=to_date('2014/01/16 14:41:39','yyyy/MM/dd hh24:mi:ss')

where zsgbh between '027978251' and '027978500';

轉載于:https://www.cnblogs.com/lt-style/p/3668342.html