Create a table with interval partitioning:
create table table_new
tablespace tbs_tablespace
PARTITION BY RANGE (dt_action)
INTERVAL (NUMTOYMINTERVAL(1,'year') )
(PARTITION p_0 VALUES LESS THAN ('01-JAN-2011') )
as select * from table
where
rownum < 1;
Set several tablespaces for new partitions that will be selected in round-robin fasion:
create table t(col1 date,col2 varchar2(100))
partition by range (col1)
interval(numtoyminterval(1,'MONTH')) store in (tbs1,tbs2,tbs3)
(PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) tablespace tbs1)
Change partitioning interval for the table. This change affects only newly created partitions.
ALTER TABLE object_changes_pt SET INTERVAL (NUMTOYMINTERVAL(1,'month') );
At times one would want to change properties/attributes of a table so
new partitions are affected by change not the partitions already
created, like example compression, change PCTFREE, default tablespace.
-- enable compression for all operations and change PCTFREE
SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES COMPRESS FOR ALL OPERATIONS PCTFREE 5;
-- Change default tablespace for new partitions
SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE;
It can be done on partition indexes too.
SQL> ALTER INDEX scott.local_index MODIFY DEFAULT ATTRIBUTES TABLESPACE NEW_TABLESPACE_IDX;
Look what partitions have already been created:
select * from dba_tab_partitions
Split existing parititons
Create new local index for the partitioned table and build it partition by partition.
1. Create
create index object_changes_pt_i2 on object_changes_pt(dt_action) tablespace tbs_object_change local unusable
2. Look at what partitions appeared
select * from dba_ind_partitions
3. Run rebuild for each parititon
alter index object_changes_pt_i2 rebuild partition SYS_P4517 nologging parallel 6
Change default tablespace for new partitions:
ALTER TABLE OBJECT_CHANGES_PT MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_object_change_p;
| 
Меню сайта