Table of Contents
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