Basic operations on partitioned tables: A simple record ~~~ Instance: SQLcreatetablet_part (idint, col2int, col3int) 2 partitionbyrange (id) 3 (4partitionp1valueslessthan (10000), 5partitionp2valueslessthan (20000), clerk (300
Basic operations on partitioned tables: A simple record ~~~ Instance: SQL create table t_part (id int, col2 int, col3 int) 2 partition by range (id) 3 (4 partition p1 values less than (10000 ), 5 partition p2 values less than (20000), 6 partition p3 values less than (300
Basic operations on partitioned tables: A simple record ~~~
Instance:
SQL> create table t_part (id int, col2 int, col3 int)
2 partition by range (id)
3 (
4 partition p1 values less than (10000 ),
5 partition p2 values less than (20000 ),
6 partition p3 values less than (30000 ),
7 partition p4 values less than (40000 ),
8 partition p5 values less than (50000 ),
9 partition p6 values less than (60000 ),
10 partition p7 values less than (70000 ),
11 partition p8 values less than (80000 ),
12 partition p9 values less than (90000 ),
13 partition p10 values less than (100000 ),
14 partition p11 values less than (110000 ),
15 partition p12 values less than (maxvalue)
16 );
Table created.
SQL> insert into t_part select rownum, rownum + 1, rownum + 2 from dual connect by rownum <150000;
149999 rows created.
SQL> commit;
Commit complete.
1. Clear partitions and delete partitions
Alter table [partiontion_tablename] drop/truncate partition [partitionname];
SQL> alter table t_part drop partition p1;
Table altered.
SQL> alter table t_part truncate partition p2;
Table truncated.
2. Add partitions
Alter table [partiontion_tablename] add partition [partitionname] values less than (120000 );
An error is reported when a partition has maxvalue. You need to switch the Data Partition of the maxvalue partition to an intermediate table, drop the partition, and create a new partition, finally, insert the data of the intermediate table into the partition table.
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table t_part add partition p12 values less than (120000 );
Table altered.
3. Partition switching:
Alter table [partiontion_tablename] exchange partition [partitionname] with table [tablename];
SQL> create table t_norm (id int, col2 int, col3 int );
Table created.
SQL> select count (*) from t_part partition (p12 );
COUNT (*)
----------
40000
SQL> alter table t_part exchange partition p12 with table t_norm;
Table altered.
SQL> select count (*) from t_part partition (p12 );
COUNT (*)
----------
0
SQL> select count (*) from t_norm;
COUNT (*)
----------
40000
Similarly, we can exchange data from the t_norm table to the p12 partition.
SQL> alter table t_part exchange partition p12 with table t_norm;
Table altered.
SQL> select count (*) from t_part partition (p12 );
COUNT (*)
----------
40000
SQL> select count (*) from t_norm;
COUNT (*)
----------
0
4. Partition Cutting
Alter table [partiontion_tablename] split partition [partitionname] at ([values]) into (partition [partitionname1], partition [partitionname2]); -- partitionname1, 2 is the new name after split
SQL> alter table t_part rename partition p12 to pmax;
Table altered.
SQL> select count (*) from t_part partition (pmax );
COUNT (*)
----------
40000
SQL> alter table t_part split partition pmax at (120000) into (partition p12, partition pmax );
Table altered.
SQL> select count (*) from t_part partition (p12 );
COUNT (*)
----------
10000
SQL> select count (*) from t_part partition (pmax );
COUNT (*)
----------
30000
5. merge partitions
Alter table [partiontion_tablename] merge partitions [partitionname1], [partitionname2] into partition [partitionname_new];
SQL> alter table t_part merge partitions p12, pmax into partition pmax;
Table altered.
SQL> select count (*) from t_part partition (pmax );
COUNT (*)
----------
40000
