Home » RDBMS Server » Server Administration » how to split a default subpartition (11.1.0.7 windows xp)
how to split a default subpartition [message #526683] Wed, 12 October 2011 07:56 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
I create a range-list partition table using the subpartition template,and there is a default subpartition p_default,when i insert a row which provcode values is 6 into the table,and it belong to the default subpartition,how can i spilt it into a new subpartition such as p_6, and make the row belong to the subpartition p_6?
create table tb_hxl_user_split
(
  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 p_1 values (1) ,
 subpartition p_2 values (2) , 
 subpartition p_3 values (3) ,
 subpartition p_4 values (4) , 
 subpartition p_5 values (5) ,
 subpartition p_default values (default) ,
 )
(
  partition hxl_user_20111001 values less than 
(to_date(' 2011-10-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
); 

insert into tb_hxl_user_split(statedate,provcode,usernumber) values(date'2011-01-01',1,'12301');
insert into tb_hxl_user_split(statedate,provcode,usernumber) values(date'2011-01-01',2,'12302');
insert into tb_hxl_user_split(statedate,provcode,usernumber) values(date'2011-01-01',3,'12303');
insert into tb_hxl_user_split(statedate,provcode,usernumber) values(date'2011-01-01',4,'12304');
insert into tb_hxl_user_split(statedate,provcode,usernumber) values(date'2011-01-01',5,'12305');
insert into tb_hxl_user_split(statedate,provcode,usernumber) values(date'2011-01-01',6,'12306');

[Updated on: Wed, 12 October 2011 08:24] by Moderator

Report message to a moderator

Re: how to split a default subpartition [message #526686 is a reply to message #526683] Wed, 12 October 2011 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter table tb_hxl_user_split split subpartition HXL_USER_20111001_P_DEFAULT
  2  values (6) into (subpartition HXL_USER_20111001_P_6, subpartition HXL_USER_20111001_P_DEFAULT);

Table altered.

Regards
Michel
Re: how to split a default subpartition [message #526687 is a reply to message #526686] Wed, 12 October 2011 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And for future partitions:
SQL> alter table tb_hxl_user_split set subpartition template ( 
  2   subpartition p_1 values (1) ,
  3   subpartition p_2 values (2) , 
  4   subpartition p_3 values (3) ,
  5   subpartition p_4 values (4) , 
  6   subpartition p_5 values (5) ,
  7   subpartition p_6 values (6) ,
  8  subpartition p_default values (default)
  9  );

Table altered.

Regards
Michel
Re: how to split a default subpartition [message #526868 is a reply to message #526687] Thu, 13 October 2011 07:27 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: find out what have been assign to a role
Next Topic: Error While Creating The Database
Goto Forum:
  


Current Time: Sun Dec 21 20:31:57 CST 2014

Total time taken to generate the page: 0.07935 seconds