【實驗】【PARTITION】RANGE分割槽表刪除分割槽

secooler發表於2009-07-13
語法
刪除分割槽表的分割槽：
alter table Partition_Table drop partition Partition_Name update indexes;
刪除子分割槽表的分割槽：
alter table Partition_Table drop subpartition Partition_Name update indexes;


1.建立樣例分割槽表
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
  2  partition by range(id)(
  3  partition t_range_p1   values less than (10)       tablespace tbs_part01,
  4  partition t_range_p2   values less than (20)       tablespace tbs_part02,
  5  partition t_range_p3   values less than (30)       tablespace tbs_part03,
  6  partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);

Table created.

sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE    T_RANGE_P1           10         TBS_PART01
T_PARTITION_RANGE    T_RANGE_P2           20         TBS_PART02
T_PARTITION_RANGE    T_RANGE_P3           30         TBS_PART03
T_PARTITION_RANGE    T_RANGE_PMAX         MAXVALUE   TBS_PART04

2.刪除T_RANGE_P3分割槽
sec@ora10g> alter table t_partition_range drop partition T_RANGE_P3 update indexes;

Table altered.

sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE    T_RANGE_P1           10         TBS_PART01
T_PARTITION_RANGE    T_RANGE_P2           20         TBS_PART02
T_PARTITION_RANGE    T_RANGE_PMAX         MAXVALUE   TBS_PART04

3.刪除分割槽是較迅速的清理資料的手段（因為是DDL語句）
sec@ora10g> select * from t_partition_range partition(t_range_p1);

        ID NAME
---------- --------------------------------------------------
         5 Andy1
         5 Andy1
         5 Andy1
         5 Andy1
         5 Andy1
         5 Andy1

6 rows selected.

sec@ora10g> select * from t_partition_range;

        ID NAME
---------- --------------------------------------------------
         5 Andy1
         5 Andy1
         5 Andy1
         5 Andy1
         5 Andy1
         5 Andy1

6 rows selected.

sec@ora10g> alter table t_partition_range drop partition t_range_p1 update indexes;

Table altered.

sec@ora10g> select * from t_partition_range partition(t_range_p1);
select * from t_partition_range partition(t_range_p1)
                                          *
ERROR at line 1:
ORA-02149: Specified partition does not exist

sec@ora10g> select * from t_partition_range;

no rows selected

這也體現了分割槽技術在海量資料庫的維護上的靈活性。

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-608967/，如需轉載，請註明出處，否則將追究法律責任。

相關文章