Oracle 10g Range partition table split lock測試

經過實測, 如果有任何session 在最後的partition (maxvalue) 做insert , 是會造成該partition 無法做切割的 ,

  除非等到所有session的 commit 做完...

  另外 

  在maxvalue partition 有資料的前提下,切割後會有副作用:

  若相關索引為GLOBAL index , 會造成整個索引失效, 馬上影響線上查詢 , 必須重建所有索引 , 若索引為GLOBAL, 則不建議線上split partition...

      

       

       若相關索引為LOCAL index , 則只有新切的parttion 索引會失效 , 重建新的索引較快 , 影響範圍較小.

        

SQL>  若以下有結果 則索引為GLOBAL / LOCAL index , 若為GLOBAL 則不建議線上split

select index_name, locality from dba_part_indexes where owner='PIN' and table_name='EVENT_T';

SQL>  若以下有結果 則索引為普通index , 亦即當初建在partition table上面的index , 僅為普通索引

select index_name , status from dba_indexes where owner='PIN' and table_name='EVENT_T';

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

相關說明文件如下 http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm

Splitting Partitions

The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete. You can also use the SPLIT PARTITION clause to redistribute the I/O load.

This clause cannot be used for hash partitions or subpartitions.

If the partition you are splitting contains any data, indexes may be marked UNUSABLE as explained in the following table:

Table Type	Index Behavior
Regular (Heap)	Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement: The database marks UNUSABLE the new partitions (there are two) in each local index. Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.
Index-organized	The database marks UNUSABLE the new partitions (there are two) in each local index. All global indexes remain usable.

#TEST

create user HR identified by HR default tablespace users temporary tablespace temp;

grant connect to HR;

grant unlimited tablespace to HR;

--確定各partition之datafile 均被建立

create tablespace PBIRTH_Q1 datafile '/oracle/oradata/orcl/pbirth_q1.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

create tablespace PBIRTH_Q2 datafile '/oracle/oradata/orcl/pbirth_q2.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

create tablespace PBIRTH_Q3 datafile '/oracle/oradata/orcl/pbirth_q3.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

create tablespace PBIRTH_Q4 datafile '/oracle/oradata/orcl/pbirth_q4.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

create tablespace PBIRTH_MAX datafile '/oracle/oradata/orcl/pbirth_max.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

create tablespace p_2012_old datafile '/oracle/oradata/orcl/pbirth_2012_o.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

create tablespace p_2012_new datafile '/oracle/oradata/orcl/pbirth_2012_n.dbf' size 10M autoextend on next 5M maxsize 32767M extent management local uniform size 5M;

CREATE TABLE HR.EMPLOYEE_BIRTH

(

BIRTHDAY date,

EMPLOYEE_NAME varchar(20)

)

PARTITION BY RANGE(BIRTHDAY)

(

  PARTITION p1 VALUES LESS THAN(TO_DATE('01/04/2011','DD/MM/YYYY') ) tablespace PBIRTH_Q1,

  PARTITION p2 VALUES LESS THAN(TO_DATE('01/07/2011','DD/MM/YYYY') ) tablespace PBIRTH_Q2,

  PARTITION p3 VALUES LESS THAN(TO_DATE('01/10/2011','DD/MM/YYYY') ) tablespace PBIRTH_Q3,

  PARTITION p4 VALUES LESS THAN(TO_DATE('01/01/2012','DD/MM/YYYY') ) tablespace PBIRTH_Q4,

  PARTITION pmax VALUES LESS THAN (MAXVALUE) tablespace PBIRTH_MAX

);

@session1

SQL>

insert into HR.EMPLOYEE_BIRTH values ( to_date('2011/02/06', 'YYYY/MM/DD'),'test' );

@session2

SQL>

ALTER TABLE HR.EMPLOYEE_BIRTH SPLIT PARTITION

      pmax at (TO_DATE('02/10/2012','DD/MM/YYYY')) INTO (

      PARTITION p2012_old tablespace p_2012_old

    , PARTITION p2012_new tablespace p_2012_new) UPDATE GLOBAL INDEX;

               *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

--Other mainenance command

ALTER TABLE HR.EMPLOYEE_BIRTH SPLIT PARTITION

      pmax at (TO_DATE('02/10/2012','DD/MM/YYYY')) INTO (

      PARTITION p2012_old tablespace p_2012_old

    , PARTITION p2012_new tablespace p_2012_new);

ALTER INDEX HR.BIRTH_IDX SPLIT PARTITION

      pmax at (TO_DATE('10/02/2012','DD/MM/YYYY')) INTO (

      PARTITION p2012_old tablespace p_2012_old

    , PARTITION p2012_new tablespace p_2012_new) UPDATE GLOBAL INDEX;

ALTER TABLE HR.EMPLOYEE_BIRTH

    ADD PARTITION PMAX 

    VALUES LESS THAN  (MAXVALUE)

    TABLESPACE PBIRTH_MAX;

alter table HR.EMPLOYEE_BIRTH drop partition p2012_new;

alter table HR.EMPLOYEE_BIRTH drop partition p2012_old;

alter table HR.EMPLOYEE_BIRTH drop partition PMAX;

ALTER INDEX HR.BIRTH_IDX REBUILD PARTITION p2012_new;

ALTER INDEX HR.BIRTH_IDX REBUILD PARTITION p2012_old;


相關網路文章
Oracle-善用Partiton Table#簡介
http://ithelp.ithome.com.tw/question/10031545