Here are the commands you can use:
SQL> alter table oe.cat_req split partition
2 cat_req_win_2008 at (to_date('20080101','YYYYMMDD')) into
3 (partition cat_req_win_2008 tablespace prd04,
4 partition cat_req_spr_2008 tablespace prd01);
Table altered.
SQL> create table oe.arch_cat_req_spr_2007 as
2 select * from oe.cat_req partition(cat_req_spr_2007);
Chapter 16: Managing Large Databases 579
Table created.
SQL> alter table oe.cat_req
2 drop partition cat_req_spr_2007;
Table altered.
The data dictionary view DBA_TAB_PARTITIONS reflects the new partitioning scheme, as you
can see in this example:
SQL> select partition_name, high_value
2 from dba_tab_partitions
3 where table_name = 'CAT_REQ';
PARTITION_NAME HIGH_VALUE
-------------------- ----------------------------------------
CAT_REQ_FAL_2007 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
CAT_REQ_SUM_2007 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
CAT_REQ_WIN_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
CAT_REQ_SPR_2008 MAXVALUE
4 rows selected.
Note that if you had dropped any partition other than the oldest partition, the next highest
partition ???takes up the slack??? and contains any new rows that would have resided in the dropped
partition; regardless of what partition is dropped, the rows in the partition are no longer in the
partitioned table.
Pages:
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859