How to Partitioning in existing table using EXCHANGE PARTITION

Table of Contents

Partitioning in existing table

Hello, friends in this article we are going to disuse how to partition the existing table using EXCHANGE PARTITION. If you want to learn more about partition click here. In this article, we are going to perform the below actions.

Create a sample table and insert some records in this table.
Create a second table with the same structure and partition.
EXCHANGE PARTITION
SPLIT PARTITION

Changing a Nonpartitioned Table into a Partitioned Table

Create a sample table using the below command.

SQL> create table ocptech (id number,details varchar2(60),j_date date);

After creating a table Now insert some records into a table using the below query, that query will be inserted 10000 rows in your table automatically.

SQL> insert into ocptech
          select level,'Details for'|| level,
 add_months(to_date('01-JAN-2019','DD-MON-YYYY'), -TRUNC(dbms_random.value(1,4)-1)*12)
 from dual
 connect by level <=10000;

SQL> commit;

Gather stats before partition

Gather stats of this table using the below command.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'OCPTECH', cascade => TRUE);

Create Second Sample table

Create a second sample table with partition, remember both tables has the same structure, if not the same you will be facing an error.

SQL> create table ocptech2  (id number,details varchar2(60),j_date date)
          partition by range (id)
         (partition p1 values less than (maxvalue));

EXCHANGE PARTITION

Now it’s time to exchange the partition using the below query, do carefully.

SQL> alter table ocptech2
          exchange partition p1
          with table ocptech
          without validation
          update global indexes;

Drop original table

This is the most important part of this topic so carefully and before dropping, your table takes a backup for security purposes.

SQL> drop table ocptech;

Rename Table

Rename the second table with the old one using the below query.

SQL> rename ocptech2 to ocptech;

Gather States

After renaming the table, now we need to gather states.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'OCPTECH', cascade => TRUE);

Check partition in the non-partition table, now you see there is partition available.

SQL> SELECT partitioned FROM   user_tables
          WHERE  table_name = 'OCPTECH';

The above command will return “YES“, which means now your table has a partition.

Check partition-wise data in the table using the below command.

SQL> select partition_name,num_rows from user_tab_partitions
          where table_name='OCPTECH';

SPLIT PARTITION

If you want to make multiple partitions in your table then you need to split partition, do it using the below query.

SQL> ALTER TABLE OCPTECH
          SPLIT PARTITION P1 AT (999)
          INTO (PARTITION P1, PARTITION P2)
          UPDATE GLOBAL INDEXES;

After the split partition, we need to gather stats again.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'OCPTECH', cascade => TRUE);

Now you can see your table data is split into multiple partitions as per your condition.

SQL> select partition_name,num_rows from user_tab_partitions
          where table_name='OCPTECH';

Read - Transport Tablespace from Linux to Windows in ORACLE 11g & 10g

Connect with me on:

Instagram: https://www.instagram.com/digitalshripal
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology