使用子分区模板创建range-list分区

-- 创建Range List分区表

create table TB_HXL_USER

(
  STATEDATE DATE not null,
  PROVCODE NUMBER not null,
  USERNUMBER VARCHAR2(13) not null,
  REM1 VARCHAR2(1024),
  CREATE_DATE DATE,
  CREATE_BY NUMBER,
  LAST_UPDATE_DATE DATE,
  LAST_UPDATE_BY NUMBER
)

partition by range (statedate) subpartition by list(provcode)
subpartition template (
 subpartition p1 values (1) tablespace USERS,
 subpartition p2 values (2) tablespace USERS,
 subpartition p3 values (3) tablespace USERS,
 subpartition p4 values (4) tablespace USERS,
 subpartition p5 values (5) tablespace USERS
 )
(
  partition HXL_USER_20110516 values less than (TO_DATE(' 2011-05-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);
-- global index

create unique index idx_tb_hxl_user_u1 on tb_hxl_user(statedate,provcode,usernumber);

 

-- 1.增加分区

若在范围分区的表里创建了maxvalue分区，则不能新增表分区(报ora-14074错误), 但可以使用分区拆分.范围分区表新增分区后,不管是本地索引还是全局索引,都不会失效.

Alter Table TB_HXL_USER Add Partition HXL_USER_20110517 values less than (TO_DATE(' 2011-05-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

-- 2.更新子分区模板

更新了子分区模板后,以后新建得分区会按照新的模板创建子分区，但现有的分区中的子分区保持不变

Alter Table TB_HXL_USER Set Subpartition Template
(
 subpartition p1 values (1) tablespace USERS,
 subpartition p2 values (2) tablespace USERS,
 subpartition p3 values (3) tablespace USERS,
 subpartition p4 values (4) tablespace USERS,
 subpartition p5 values (5) tablespace USERS,
 subpartition p_default values (default) tablespace Users
)

--3.清空子分区模板

清空子分区模板后,以后新建分区的时候,系统默认会创建一个子分区。同样的，清空子分区只对新建的分区有效.

alter table tb_hxl_user set subpartition template();

 

-- 4.查询某个分区

select * from TB_HXL_USER partition(HXL_USER_20110516);

-- 5.查询某个子分区

select * from TB_HXL_USER subpartition(p31);

--6.查询子分区模板

Select *
From dba_subpartition_templates

--拆分默认子分区

--拆分后的子分区索引会失效

insert into tb_hxl_user(statedate,provcode,usernumber)
values(date'2011-05-16',6,'123');
commit;

alter table tb_hxl_user
 split subpartition hxl_user_20110516_p_default
 values (6) into (
 subpartition hxl_user_20110516_p6,
 subpartition hxl_user_20110516_p_default
 );

--查询某个符合分区表是否有默认子分区

Select a.subpartition_name,a.high_value
  From dba_tab_subpartitions a
  Join Dba_Objects b
    On (a.Table_Owner = b.Owner
        And a.Table_Name = b.Object_Name
        And a.subPartition_Name = b.Subobject_Name
       )
 Where b.Object_Id In
       (Select a.Obj#
          From sys.Tabsubpart$ a
          Join Sys.Obj$ b
            On (a.Obj# = b.Obj#)
          Join Sys.User$ c
            On (b.Owner# = c.User#)
         Where Upper(Long_To_Char(a.Rowid, 'SYS', 'TABSUBPART$', 'HIBOUNDVAL'))
         In ('DEFAULT','MAXVALUE')
           And c.Name = 'HXL' --schema name

        )
   And a.table_name = 'TB_HXL_USER'

-- 查询某个分区表是否有默认分区

Select a.*
  From Dba_Tab_Partitions a
  Join Dba_Objects b
    On (a.Table_Owner = b.Owner And a.Table_Name = b.Object_Name And
       a.Partition_Name = b.Subobject_Name)
 Where b.Object_Id In (Select a.Obj#
                         From Sys.Tabpartv$ a
                         Join Sys.Obj$ b
                           On (a.Obj# = b.Obj#)
                         Join Sys.User$ c
                           On (b.Owner# = c.User#)
                        Where Upper(Sys.Dbms_Metadata_Util.Long2varchar(10,
                                                                        'SYS.TABPARTV$',
                                                                        'HIBOUNDVAL',
                                                                        a.Rowid)) =
                              'DEFAULT'
                          And c.Name = 'HXL' --schema name

                       )
   And a.table_name = 'TB_HXL_USER' -- table name