How to Partitioning in existing table using EXCHANGE PARTITION

Partitioning in existing table

Hello, friends in this article we are going to disuse how to partitioning in 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 sample table using 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 below command.

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

Create Second Sample table

Create second sample table with partition, remember both tables has same structure, if not same you will be face 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 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 it carefully and before drop your table take a backup for security purposes.

SQL> drop table ocptech;

Rename Table

Rename second table with old one using below query.

SQL> rename ocptech2 to ocptech;

Gather States

After renaming table, now we need to gather states.

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

Check partition in 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“, that means now your table has partition.

Check partition wise data in table using below command.

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

SPLIT PARTITION

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

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

After 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 in multiple partition 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/shripaldba
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