Platinum Partner

Oracle Database 12C new features

1) Table partition maintenance enhancements:-

Adding multiple new partitions

Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. The following example explains how to add multiple new partitions to an existing partitioned table:

SQL> CREATE TABLE emp_part
				(eno number(8), ename varchar2(40), sal number (6))
		PARTITION BY RANGE (sal)
		(PARTITION p1 VALUES LESS THAN (10000),
		 PARTITION p2 VALUES LESS THAN (20000),
		 PARTITION p3 VALUES LESS THAN (30000)
		);

Now lets add a couple of new partitions:

SQL> ALTER TABLE emp_part ADD PARTITION
		PARTITION p4 VALUES LESS THAN (35000),
		PARTITION p5 VALUES LESS THAN (40000);

In the same way, you can add multiple new partitions to a list and system partitioned table, provided that theMAXVALUE partition doesn’t exist.

How to drop and truncate multiple partitions/sub-partitions

As part of data maintenance, you typically either use drop or truncate partition maintenance task on a partitioned table.  Pre 12c R1, it was only possible to drop or truncate one partition at a time on an existing partitioned table.  With Oracle 12c, multiple partitions or sub-partitions can be dropped or merged using a single ALTER TABLE table_name {DROP|TRUNCATE} PARTITIONS command.

The following example explains how to drop or truncate multiple partitions on an existing partitioned table:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;

To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause, shown below:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

If you truncate or drop a partition without the UPDATE GLOBAL INDEXES clause, you can query the columnORPHANED_ENTRIES in the USER_INDEXES or USER_IND_PARTITIONS dictionary views to find out whether the index contains any stale entries.

Splitting a single partition into multiple new partitions

The new enhanced SPLIT PARTITION clause in 12c will let you split a particular partition or sub-partition into multiple new partitions using a single command. The following example explains how to split a partition into multiple new partitions:

SQL> CREATE TABLE emp_part
				(eno number(8), ename varchar2(40), sal number (6))
		PARTITION BY RANGE (sal)
		(PARTITION p1 VALUES LESS THAN (10000),
		 PARTITION p2 VALUES LESS THAN (20000),
		 PARTITION p_max VALUES LESS THAN (MAXVALUE)
		);

SQL> ALTER TABLE emp_part SPLIT PARTITION p_max INTO
		(PARTITION p3 VALUES LESS THAN (25000),
		 PARTITION p4 VALUES LESS THAN (30000), PARTITION p_max);

Merge multiple partitions into one partition

You can merge multiple partitions to a single partition using a single ALTER TBALE MERGE PARTITIONS statement:

SQL> CREATE TABLE emp_part
		(eno number(8), ename varchar2(40), sal number (6))
		PARTITION BY RANGE (sal)
		(PARTITION p1 VALUES LESS THAN (10000),
		 PARTITION p2 VALUES LESS THAN (20000),
		 PARTITION p3 VALUES LESS THAN (30000),
		 PARTITION p4 VALUES LESS THAN (40000),
		 PARTITION p5 VALUES LESS THAN (50000),
		 PARTITION p_max (MAXVALUE)
		);

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3,p4,p5 INTO PARTITION p_merge;

If the range falls in the sequence, you can use the following example:

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge;
2) Sequence as Default Value
With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.

Example:
create sequence test_seq start with 1 increment by 1 nocycle;

create table test_tab
(
    id number default test_seq.nextval primary key
);


3) Invisible column:
Oracle Database 12c provides you the Invisible column feature. A Column defined as invisible, will not appear in generic queries (select * from). An Invisible Column need to be explicitly referred to in the SQL statement or condition. Also invisible column must be explicitly referred in INSERT statement to insert the database into invisible columns.

Example:
SQL> create table my_table
  2  (
  3  id number,
  4  name varchar2(100),
  5  email varchar2(100),
  6  password varchar2(100) INVISIBLE
  7  );
  
SQL> ALTER TABLE my_table MODIFY (password visible);  
{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}