Description:-
- In Oracle 18c Database use ONLINE option with merge partition and sub partition sql statements to enable the online merge partition for heap tables.
- So it can be provide concurrent DML operation without any interruption, while doing merge partition operation.
This option will help us to increase the availability of applications.
Let’s Start the Demo:-
Step1:-Create a sample partitioned table with a three partitions.
CREATE TABLE sample (
id NUMBER,
name VARCHAR2(10),
created_date DATE,
CONSTRAINT pl_1 PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION p_2016 VALUES LESS THAN (TO_DATE(’01-JAN-2017 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’)),
PARTITION p_2017 VALUES LESS THAN (TO_DATE(’01-JAN-2018 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’)),
PARTITION p_2018 VALUES LESS THAN (MAXVALUE)
);
Step2:-To check the table partitions and index information.
CREATE INDEX sample_created_date_index ON sample (created_date) LOCAL;
CREATE INDEX sample_name_idx ON sample (name);
INSERT INTO sample VALUES ( 1, ‘JOHN’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 2, ‘MARK’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 3, ‘TYE’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 4, ‘NIK’, TO_DATE(’01-FEB-2016′,’DD-MON-YYYY’));
INSERT INTO sample VALUES ( 5, ‘JACK’, TO_DATE(’01-FEB-2017′,’DD-MON-YYYY’));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, ‘sample’, cascade => TRUE);
Step3:-Display the current table partitions and index information:
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,partition_name,num_rows FROM user_tab_partitions where table_name=’SAMPLE’ ORDER BY 1, 2;
COLUMN index_name FORMAT A30
COLUMN partitioned FORMAT A11
SELECT index_name,partitioned,status FROM user_indexes where table_name=’SAMPLE’ ORDER BY 1;
SELECT index_name,partition_name,status FROM user_ind_partitions ORDER BY 1, 2;
Step4:-Perform an online operation to merge the P_2016 partition into the P_2017 partition.
ALTER TABLE sample MERGE PARTITIONS p_2016, p_2017 INTO PARTITION p_2017 update indexes ONLINE;
EXEC DBMS_STATS.gather_table_stats(USER, ‘sample’, cascade => TRUE);
Step5:-Finally merge the two partitions using online feature.
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,partition_name,num_rows FROM user_tab_partitions ORDER BY 1, 2;
Creating an Oracle 18c Database Cloud Service Instance Here
Catch Me On:- Hariprasath Rajaram
LinkedIn: https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
