ORACLE PARTTITION

create table sales_data1(customer_name varchar2(50),
                        p_id number,
                        price number,
                        create_time date
                       )
                        PARTITION BY RANGE (create_time)
                        SUBPARTITION BY HASH (customer_name)
                          SUBPARTITION TEMPLATE(
                                        SUBPARTITION p_part1 TABLESPACE part1_1,
                                        SUBPARTITION p_part2 TABLESPACE part2_1,
                                        SUBPARTITION p_part3 TABLESPACE part3_1,
                                        SUBPARTITION part4 TABLESPACE part4_1
                          )
                      (
                        PARTITION d_p_part1 VALUES LESS THAN(TO_DATE('1-1-2008','DD-MM-YYYY'))TABLESPACE part1,
                        PARTITION d_p_part2 VALUES LESS THAN(TO_DATE('1-1-2009','DD-MM-YYYY'))TABLESPACE part2,
                        PARTITION d_p_part3 VALUES LESS THAN(TO_DATE('1-1-2010','DD-MM-YYYY'))TABLESPACE part3,
                        PARTITION d_p_part4 VALUES LESS THAN(TO_DATE('1-1-2011','DD-MM-YYYY'))TABLESPACE part4
                      );
                      select  count(1) from sales_data
insert into sales_data

create table t1 as select * from sales_data where 1=0
drop table t1

create table t1(customer_name varchar2(50),
                        p_id number,
                        price number,
                        create_time date
                       )
                        PARTITION BY HASH (customer_name)
                        (
                                   PARTITION d_p_part1 TABLESPACE part1,
                        PARTITION d_p_part2 TABLESPACE part2,
                        PARTITION d_p_part3 TABLESPACE part3,
                        PARTITION d_p_part4 TABLESPACE part4
                        )
                      

drop table t1
drop table t1

select * from sales_data
drop table t1

select count(*) from sales_data partition(d_p_part3)
select * from t1
select dbms_metadata.get_ddl( 'TABLE','T1') from dual;
select * from dba_segments where segment_name ='SALES_DATA'

alter table sales_data exchange partition d_p_part3 with table t1;

drop table t1

create index unique index test_p_uni on sales_data()

alter table sales_data1 add column test_id
drop table sales_data

select count(1) from sales_data

create table sales_data2(customer_name varchar2(50),
                        p_id number,
                        price number,
                        create_time date
                       )
                        PARTITION BY RANGE (create_time)
                        SUBPARTITION BY HASH (customer_name)
                          SUBPARTITION TEMPLATE(
                                        SUBPARTITION p_part1 TABLESPACE part1_1,
                                        SUBPARTITION p_part2 TABLESPACE part2_1,
                                        SUBPARTITION p_part3 TABLESPACE part3_1,
                                        SUBPARTITION part4 TABLESPACE part4_1
                          )
                      (
                        PARTITION d_p_part1 VALUES LESS THAN(TO_DATE('1-1-2008','DD-MM-YYYY'))TABLESPACE part1,
                        PARTITION d_p_part2 VALUES LESS THAN(TO_DATE('1-1-2009','DD-MM-YYYY'))TABLESPACE part2,
                        PARTITION d_p_part3 VALUES LESS THAN(TO_DATE('1-1-2010','DD-MM-YYYY'))TABLESPACE part3,
                        PARTITION d_p_part4 VALUES LESS THAN(TO_DATE('1-1-2011','DD-MM-YYYY'))TABLESPACE part4
                      )tablespace users;

ALTER TABLE sales_data2 MODIFY PARTITION d_p_part1 ADD SUBPARTITION part5 ;

drop table
truncate table sales_data
select owner,object_id,data_object_id,sysdate from dba_objects;

select * from user_segments where segment_name='SALES_DATA2'

begin
       for c in 1..200
       loop
            if mod(c,4)=1
            then
             insert into  sales_data select object_id,object_id,data_object_id,sysdate-365*3 from dba_objects;
            end if;
            if mod(c,4)=2
                         then insert into  sales_data select  object_id,object_id,data_object_id,sysdate-365*2 from dba_objects;
            end if;
            if mod(c,4)=3
                         then insert into  sales_data select  object_id,object_id,data_object_id,sysdate-365 from dba_objects;
            end if;
            if mod(c,4)=0
                         then insert into  sales_data select  object_id,object_id,data_object_id,sysdate from dba_objects;
            end if;
                        
       end loop;
       commit;
end;

select * from v$archived_log

truncate table sales_data

drop table sales_data
insert into sales_data(customer_name

select mod(3,4) from user_objects

create tablespace part1_1 datafile 'P1_1.dbf' size 50M;
create tablespace part2_1 datafile 'P2_1.dbf' size 50M;
create tablespace part3_1 datafile 'P3_1.dbf' size 50M;
create tablespace part4_1 datafile 'P4_1.dbf' size 50M;

alter database datafile 12 autoextend on;

select * from dba_data_files

select * from dba_data_files

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21818314/viewspace-664845/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21818314/viewspace-664845/