[Oracle] Partition table exchange Heap table

Heap Table --(exchange)--> Partiton table

SQL> create table part_tab1(id int,owner varchar2(30),name varchar2(30),type varchar2(30),created date)
  2  partition by range(id)
  3  (partition p1 values less than(10000) tablespace users,
  4   partition p2 values less than(50000) tablespace users,
  5   partition p3 values less than(100000) tablespace users
  6  );

Table created.    #建立測試的表,range partition

SQL> insert into part_tab1 select object_id,owner,object_name,object_type,created from all_objects;

56331 rows created.

Elapsed: 00:00:00.91   #寫入一部分資料
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select count(*) from part_tab1;

  COUNT(*)
----------
     56331

SQL> create index part_tab1_index on part_tab1(id) local;     #建立local indexes

Index created.

SQL> create table temp_part_tab1 as select * from part_tab1;   #建立臨時table

Table created.

Elapsed: 00:00:00.06
SQL> create index temp_part_tab1_index on temp_part_tab1(id);    #建立臨時table的indexes

Index created.

Elapsed: 00:00:00.11
SQL> update temp_part_tab1 set id=id+100000;

56331 rows updated.

Elapsed: 00:00:01.55
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> alter table part_tab1 add partition p4 values less than(200000) tablespace users;  #建立需要使用的partition

Table altered.

ERROR at line 1:
ORA-02149: Specified partition does not exist       #如果再exchange前沒有建立相應的parition會報這個錯誤.

SQL> alter table part_tab1 exchange partition p4 with table temp_part_tab1 including indexes without validation;

Table altered.

Elapsed: 00:00:00.10

ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition        #沒有新增 without validation選項,則exchange時會檢查id是否符合parition的規則.

SQL> select count(*) from part_tab1;

  COUNT(*)
----------
    112662

Elapsed: 00:00:00.01
SQL> select count(*) from part_tab1 partition (p4);      #資料已經全部exchange到partition p4中

  COUNT(*)
----------
     56331


SQL> select count(*) from temp_part_tab1;               #exchange後原有的臨時表中沒有資料了.

  COUNT(*)
----------
         0      
        
==========================================================================        
PartitonTable --(exchange)--> Heap table
       
SQL> alter table part_tab1 exchange partition p1 with table temp_part_tab1 including indexes;

Table altered.

Elapsed: 00:00:00.02
SQL>
SQL> select count(*) from temp_part_tab1;

  COUNT(*)
----------
      4490

Elapsed: 00:00:00.01
SQL> select * from part_tab1 partition(p1);

no rows selected

Elapsed: 00:00:00.01

SQL> select segment_name,partition_name from user_segments where segment_name like '%PART%';

SEGMENT_NAME                             PARTITION_NAME
---------------------------------------- ------------------------------------------------------------------------------------------
TEMP_PART_TAB1
PART_TAB1                                P2
PART_TAB1                                P3
PART_TAB1                                P4
PART_TAB1                                P5
TEMP_PART_TAB1_INDEX
PART_TAB1_INDEX                          P2
PART_TAB1_INDEX                          P3
PART_TAB1_INDEX                          P4
PART_TAB1_INDEX                          P5

10 rows selected.

Elapsed: 00:00:00.07

SQL> list
  1*  select index_name,partition_name,status from user_ind_partitions
SQL> /

INDEX_NAME                     PARTITION_NAME                                                                             STATUS
------------------------------ ------------------------------------------------------------------------------------------ ------------------------
PART_TAB1_INDEX                P5                                                                                         USABLE
PART_TAB1_INDEX                P2                                                                                         USABLE
PART_TAB1_INDEX                P3                                                                                         USABLE
PART_TAB1_INDEX                P4                                                                                         USABLE
PART_TAB1_INDEX                P1                                                                                         USABLE

Elapsed: 00:00:00.01

SQL> select index_name,table_name,status from user_indexes where table_name like '%PART%';

INDEX_NAME                     TABLE_NAME                                                                                 STATUS
------------------------------ ------------------------------------------------------------------------------------------ ------------------------
TEMP_PART_TAB1_INDEX           TEMP_PART_TAB1                                                                             VALID
PART_TAB1_INDEX                PART_TAB1                                                                                  N/A

Elapsed: 00:00:00.27