分区表的一些操作例子

原来的笔记，操作分区表的一个例子（增加，删除，切换分区之类）

 

呵呵，里面还有RPS表的名字

 

Add & Split

Add & Split
--------------------------------------------------------------------------
-- list partitioned table
--------------------------------------------------------------------------
-- truncate table acct_spct_inst_x_acct_amt;
drop table acct_spct_inst_x_acct_amt purge;

--ACCT_SPCT_INST_X_ACCT_AMT
create table ACCT_SPCT_INST_X_ACCT_AMT(
    id                            number                 not null,
    arm_account_list_id           number                 not null,
    account_number                varchar2(20)           not null,
    amount                        number,
    load_batch_number             number       default 1 not null,
    CREATED_DATE                  date
 )
partition by list (arm_account_list_id)
(
--  partition p_axa_null values (null) --used for adding new partition
  partition p_axa_def values (default) -- used for splitting existed partition
);

--Primary Key
alter table ACCT_SPCT_INST_X_ACCT_AMT add(
    constraint ACCT_SPCT_INST_X_ACCT_AMT_pk
    primary key( id )
);

--Forien Key
alter table ACCT_SPCT_INST_X_ACCT_AMT add(
    constraint ACCT_SPCT_INST_X_ACCT_AMT_fk
    foreign key (arm_account_list_id)
    references arm_account_list (id)
);

--index
create index acct_spct_inst_x_acct_amt_cb1 on acct_spct_inst_x_acct_amt( account_number,arm_account_list_id ) local;
-- create index acct_spct_inst_x_acct_amt_cb2 on acct_spct_inst_x_acct_amt( arm_account_list_id ) local;

--------------------------------------------------------------------------------
-- ***** add new partition for newly added arm_acct_list
--------------------------------------------------------------------------------
alter table acct_spct_inst_x_acct_amt add partition p_axa_1340000 values (1340000);
insert into acct_spct_inst_x_acct_amt
  (id,
   arm_account_list_id,
   account_number,
   amount,
   load_batch_number,
   created_date)
values
  (ACCT_SPCT_INST_X_ACCT_AMT_S.nextval, 1340000, '123', 100, 1, sysdate);
commit;

alter table acct_spct_inst_x_acct_amt add partition p_axa_1340001 values (1340001);
insert into acct_spct_inst_x_acct_amt
  (id,
   arm_account_list_id,
   account_number,
   amount,
   load_batch_number,
   created_date)
values
  (ACCT_SPCT_INST_X_ACCT_AMT_S.nextval, 1340001, '123', 200, 1, sysdate);
commit;

select * from acct_spct_inst_x_acct_amt partition (p_axa_1340000);

--------------------------------------------------------------------------------
-- ***** split partition for newly added arm_acct_list
--------------------------------------------------------------------------------
alter table acct_spct_inst_x_acct_amt split partition p_axa_def values (1340000)
  into (partition p_axa_1340000,partition p_axa_def) update indexes;


-------------------------------------------------------------------------------
-- table for exchange with same structrue and indexes
-------------------------------------------------------------------------------
drop table ACCT_SPCT_INST_X_ACCT_AMT_EX;
create table ACCT_SPCT_INST_X_ACCT_AMT_EX(
    id                            number                 not null,
    arm_account_list_id           number                 not null,
    account_number                varchar2(20)           not null,
    amount                        number,
    load_batch_number             number       default 1 not null,
    CREATED_DATE                  date
 );
--index
create index acct_spct_inst_x_a_amt_ex_cb1 on acct_spct_inst_x_acct_amt_ex( account_number,arm_account_list_id ) ;
-- create index acct_spct_inst_x_a_amt_ex_cb2 on acct_spct_inst_x_acct_amt_ex( arm_account_list_id ) ;
 
insert into acct_spct_inst_x_acct_amt_ex
  (id,
   arm_account_list_id,
   account_number,
   amount,
   load_batch_number,
   created_date)
values
  (ACCT_SPCT_INST_X_ACCT_AMT_S.nextval, 1340000, '123', 1000, 2, sysdate);
commit;

-- truncate table acct_spct_inst_x_acct_amt_ex;
select * from acct_spct_inst_x_acct_amt_ex;

-------------------------------------------------------------------------------
-- exchange
-------------------------------------------------------------------------------
-- 1.disable constraint  2.exchange with index  3.enable constraint
alter table acct_spct_inst_x_acct_amt disable constraint ACCT_SPCT_INST_X_ACCT_AMT_pk keep index;
alter table acct_spct_inst_x_acct_amt disable constraint ACCT_SPCT_INST_X_ACCT_AMT_fk keep index;
alter table acct_spct_inst_x_acct_amt
  exchange partition p_axa_1340000 with table acct_spct_inst_x_acct_amt_ex
  including indexes update indexes;
alter table acct_spct_inst_x_acct_amt enable constraint ACCT_SPCT_INST_X_ACCT_AMT_pk ;
alter table acct_spct_inst_x_acct_amt enable constraint ACCT_SPCT_INST_X_ACCT_AMT_fk;

-- check index status
select t.table_name,t.partition_name from user_tab_partitions t where t.table_name = 'ACCT_SPCT_INST_X_ACCT_AMT';
select i.index_name, i.status from user_indexes i where i.table_name = 'ACCT_SPCT_INST_X_ACCT_AMT';
select i.index_name,i.partition_name,i.status from user_ind_partitions i where i.index_name in ('ACCT_SPCT_INST_X_ACCT_AMT_CB1','ACCT_SPCT_INST_X_ACCT_AMT_CB2');

-- relative package
select o.object_name, o.object_type, o.status from user_objects o where o.object_name = 'RPS_ARM_RUNTIME' ;
alter package RPS_ARM_RUNTIME compile;