On the Third Day of 12.2, my DBA gave to me…
Partitioning New Features
[hr width=”2px” color=”#2980b9″ style=”solid”][/hr]
December 14, 2016
With more than 500 new features in the Oracle Database 12.2, Oracle delivers new functionality and features that customers had been asking for on Cloud Computing, Big Data, and Security for their database platform. One of the major enhancements, is the Very Large Database (VLDB) on database partitioning. Below are the commonly used features with Oracle database partitioning and the list of all Oracle Database 12.2.
Convert Non-Partitioned Table to a Partitioned Table
Starting with Oracle Database 12.2, you can now convert a non-partitioned table to a partitioned table online. To make this change, use the ALTER TABLE with MODIFY clause and ONLINE keyword to enable parallel/concurrent DML for the conversion. Indexes are also maintained and can be converted to partitions as part of the process. During the conversation process, all DML transactions continue as normal. For VLDB database customers who want to adopt a partitioning strategy, they can convert to partitioned tables without any downtime.
ALTER TABLE mytable MODIFY
PARTITION BY RANGE (tab_id) INTERVAL (25) ( PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (1000))
( IDX1_tab_id LOCAL (PARTITION ip1 VALUES LESS THAN (MAXVALUE)));
Now, we can set partitions and sub-partitions to read-only for historical protection of unintentional DML. You can leverage the CREATE TABLE and ALTER TABLE with the read-only clause. By making the partition/subpartition read-only, all DML operations will be disabled.
CREATE TABLE mytable (
tab_id NUMBER(10) NOT NULL,
tab_desc VARCHAR2(20)) READ WRITE PARTITION BY RANGE (tab_id)
( PARTITION mytable_p1 VALUES LESS THAN (100),
PARTITION mytable_p2 VALUES LESS THAN (1000) READ ONLY)
Multi-Column List Partition
Oracle’s enhanced list partitioning functionality allows multiple key columns. Allowing partition criteria for list partitioned tables to be multiple columns, extends…
[link_button link=”https://viscosityna.com/resources/dba-resources/twelve-days-12-2/day-2-data-guard/” size=”small” color=”#1CC6DA” align=”left”]Get Full Version[/link_button]
[hr width=”1px” color=”#16a085″ style=”dashed”][/hr]