Manipulating Partitions in Oracle

Working as an Oracle DBA with Oracle 12c is akin to being the master puppeteer, pulling the strings to make the system dance to your tune.
Imagine your database as a grand library. In this library, each table is a bookshelf, and each row is a book. Partitioning is the act of dividing up your grand library into more manageable, smaller rooms. It's akin to having a separate room for fiction, non-fiction, reference books, etc. That way, you can isolate and deal with only a relevant subset of books at a time, thereby improving the library's efficiency.

1. Creating a Range Partition: It is like categorizing your books based on the period they were published. Here's the incantation to create a range partitioned table on the column order_date:
   

   CREATE TABLE orders (
       order_id NUMBER(10),
       customer_id NUMBER(10),
       order_date DATE
   )
   PARTITION BY RANGE (order_date)
   (
       PARTITION orders_q1 VALUES LESS THAN (TO_DATE('01-APR-2023','DD-MON-YYYY')),
       PARTITION orders_q2 VALUES LESS THAN (TO_DATE('01-JUL-2023','DD-MON-YYYY')),
       PARTITION orders_q3 VALUES LESS THAN (TO_DATE('01-OCT-2023','DD-MON-YYYY')),
       PARTITION orders_q4 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY'))
   );
   

   
   This masterpiece partitions your orders table into quarters, making each quarter's data its own realm.
2. Adding a Partition: As time passes, you're going to need to make room for more books. You can add a partition for Q1 of 2024 like this:
   

   ALTER TABLE orders 
   ADD PARTITION orders_q1_2024 
   VALUES LESS THAN (TO_DATE('01-APR-2024','DD-MON-YYYY'));
   

   
   You just opened a new room in your grand library.
3. Dropping a Partition: Over time, you might find some rooms that are no longer relevant, like say, the data from Q1 of 2023. In that case, you don't hesitate:
   

   ALTER TABLE orders 
   DROP PARTITION orders_q1;
   

   
   That's the equivalent of removing an entire room from your library, taking it off your hands and mind.
4. Splitting a Partition: Sometimes, one room might become too crowded and difficult to manage. In such a scenario, you might want to split a partition, say orders_q4 into November and December:
   

   ALTER TABLE orders 
   SPLIT PARTITION orders_q4 
   AT (TO_DATE('01-DEC-2023','DD-MON-YYYY')) 
   INTO (PARTITION orders_nov, PARTITION orders_dec);
   

You have just split one hefty room into two smaller, more manageable ones. These steps are a performance dance, the choreography that ensures your system remains spry and swift, no matter the volume of data it has to juggle. Always remember to warm up (i.e., backup) before you dance, and to reflect (i.e., monitor performance) after each performance.

The code you create to

1. move a partition to a different tablespace,
2. merge two partitions into one, and
3. rename a partition

should look something like this:

ALTER TABLE WHOLESALE_ORDER
MOVE PARTITION LOW_PART TABLESPACE LOW_PART_TS
/
ALTER TABLE WHOLESALE_ORDER
MERGE PARTITIONS
LOW_PART, MED_PART 
INTO PARTITION LOW1_PART
/
ALTER TABLE WHOLESALE_ORDER
RENAME PARTITION LOW1_PART TO LOWEST_PART
/

You can move a LOB partition into a different tablespace. This is useful if the tablespace is no longer large enough to hold the partition. To do so, use the

ALTER
TABLE ... MOVE PARTITION 

ALTER TABLE current_table 
MOVE PARTITION partition_name
TABLESPACE destination_table_space
LOB (column_name) 
STORE AS (TABLESPACE current_tablespace);