To preserve the rows, you would use merge partition instead of drop partition.
Coalescing a Table Partition You can coalesce a partition in a hash-partitioned table to
redistribute the contents of the partition to the remaining partitions and reduce the number of
partitions by one. For the new CUST table you created earlier in this chapter, you can do this in
one easy step:
SQL> alter table oe.cust coalesce partition;
Table altered.
The number of partitions in CUST is now three instead of four:
SQL> select partition_name, tablespace_name
2 from dba_tab_partitions
3 where table_name = 'CUST';
PARTITION_NAME TABLESPACE
-------------------- ----------
SYS_P1130 PRD01
580 Oracle Database 11g DBA Handbook
SYS_P1131 PRD02
SYS_P1132 PRD03
3 rows selected.
Merging Two Table Partitions You may find out through various Oracle advisors that one
partition of a partitioned table is infrequently used or not used at all; in this situation, you may
want to combine two partitions into a single partition to reduce your maintenance effort. In this
example, you will combine the partitions MIDWEST and WESTCOAST in the partitioned table
SALES_BY_REGION_BY_DAY into a single partition, MIDWESTCOAST:
SQL> alter table oe.sales_by_region_by_day
2 merge partitions midwest, westcoast
3 into partition midwestcoast tablespace prd04;
Table altered.
Looking at the data dictionary view DBA_TAB_PARTITIONS, you can see that the table now
has only two partitions:
SQL> select table_name, partition_name, tablespace_name, high_value
2 from dba_tab_partitions
3 where table_owner = 'OE' and
4 table_name = 'SALES_BY_REGION_BY_DAY';
TABLE_NAME PARTITION_NAME TABLESPACE HIGH_VALUE
---------------------- ----------------- ---------- ---------------------
SALES_BY_REGION_BY_DAY MIDWESTCOAST PRD04 'WI', 'IL', 'IA', 'IN
', 'MN', 'CA', 'OR',
'WA'
SALES_BY_REGION_BY_DAY OTHER_STATES PRD03 default
2 rows selected.
Pages:
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860