星期四, 3月 01, 2012

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';



-------------------------------------------------------------------------------------------------------------------------------------------------------------------
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;
--確定各partitiondatafile 均被建立
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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...