Oracle 12c Features for developer

Invisible columns

We can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa.

You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.

CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);

SQL> ALTER TABLE emp MODIFY (sal visible);

Temporary Undo

To be able to use the new feature, the following needs to be set:

• Compatibility parameter must be set to 12.0.0 or higher
• Enable TEMP_UNDO_ENABLED initialization parameter
• Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space
• For session level, you can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE;

Query temporary undo information

The dictionary views listed below are used to view/query the information/statistics about the temporary undo data:

• V$TEMPUNDOSTAT
• DBA_HIST_UNDOSTAT
• V$UNDOSTAT

To disable the feature, you simply need to set the following:

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

Identity Columns

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

Ignoring the identity_options, which match those of the CREATE SEQUENCE statement, this syntax allows us to use three variations on the identity functionality.

Before we can look at some examples, you need to make sure your test user has the CREATE SEQUENCE privilege. Without it, attempts to define an identity column will produce a "ORA-01031: insufficient privileges" error.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

GRANT CREATE TABLE, CREATE SEQUENCE TO test;
CONN test/test@pdb1

Using ALWAYS forces the use of the identity. If an insert statement references the identity column, even to specify a NULL value, an error is produced.

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);

SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL>

Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity. Attempting to specify the value NULL in this case results in an error, since identity columns are always NOT NULL.

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  description VARCHAR2(30)
);

SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
                                                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."IDENTITY_TEST_TAB"."ID")


SQL>

Using BY DEFAULT ON NULL allows the identity to be used if the identity column is referenced, but a value of NULL is specified.

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  description VARCHAR2(30)
);

SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

1 row created.

SQL> SELECT * FROM identity_test_tab;

 ID DESCRIPTION
---------- ------------------------------
  1 Just DESCRIPTION
       999 ID=999 and DESCRIPTION
  2 ID=NULL and DESCRIPTION

SQL>

Based on the requirement for the CREATE SEQUENCE privilege, it is not difficult to deduce that a sequence is being used to populate the identity column.

COLUMN object_name FORMAT A20

SELECT object_name, object_type
FROM   user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_92117         SEQUENCE
IDENTITY_TEST_TAB    TABLE

2 rows selected.

SQL>

The [DBA|ALL|USER]_TAB_IDENTITY_COLS views show information about identity columns.

SET LINESIZE 100
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A15
COLUMN generation_type FORMAT A10
COLUMN identity_options FORMAT A50

SELECT table_name, 
       column_name,
       generation_type,
       identity_options
FROM   all_tab_identity_cols
WHERE  owner = 'TEST'
ORDER BY 1, 2;

TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
IDENTITY_TEST_TAB    ID              ALWAYS     START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
                                                999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
                                                , CACHE_SIZE: 20, ORDER_FLAG: N

SQL>

The link between the table and the sequence is stored in the SYS.IDNSEQ$ table.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

COLUMN sequence_name FORMAT A30

SELECT a.name AS table_name,
       b.name AS sequence_name
FROM   sys.idnseq$ c
       JOIN obj$ a ON c.obj# = a.obj#
       JOIN obj$ b ON c.seqobj# = b.obj#;

TABLE_NAME      SEQUENCE_NAME
-------------------- ------------------------------
IDENTITY_TEST_TAB    ISEQ$$_92117

SQL>

Sequence usage is now visible in execution plans.

SET AUTOTRACE ON
SET LINESIZE 200

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

Execution Plan
----------------------------------------------------------
Plan hash value: 993166116

----------------------------------------------------------------------------------------------
| Id  | Operation   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |       |    1 |  100 |    1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | IDENTITY_TEST_TAB |      |      |    |      |
|   2 |   SEQUENCE   | ISEQ$$_92117      |      |      |    |      |
----------------------------------------------------------------------------------------------

Row Limiting Clause for Top-N Queries 

Top-N Queries

The syntax for the row limiting clause looks a little complicated at first glance.

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Actually, for the classic Top-N query it is very simple. The example below returns the 5 largest values from an ordered set. Using the ONLY clause limits the number of rows returned to the exact number requested.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

Using the WITH TIES clause may result in more rows being returned if multiple rows match the value of the Nth row. In this case the 5th row has the value "8", but there are two rows that tie for 5th place, so both are returned.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

SQL>

In addition to limiting by row count, the row limiting clause also allows us to limit by percentage of rows. The following query returns the bottom 20% of rows.

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

SQL>

Paging Through Data

Paging through an ordered resultset was a little annoying using the classic Top-N query approach, as it required two Top-N queries, one nested inside the other. For example, if we wanted the second block of 4 rows we might do the following.

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   rownum_order_test
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

With the row limiting clause we can achieve the same result using the following query.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

The starting point for the FETCH is OFFSET+1.

The OFFSET is always based on a number of rows, but this can be combined with a FETCH using a PERCENT.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

Not surprisingly, the offset, rowcount and percent can, and probably should, be bind variables.

VARIABLE v_offset NUMBER;
VARIABLE v_next NUMBER;

BEGIN
  :v_offset := 4;
  :v_next   := 4;
END;
/

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET :v_offset ROWS FETCH NEXT :v_next ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

SQL>

The DEFAULT ON NULL clause

Starting with Oracle Database 12c, a column can be assigned a default non-null value whenever the user tries to insert NULL into the column. The default value will be specified in the DEFAULT clause of the column with a new ON NULL extension.

Note that the DEFAULT ON NULL cannot be used with an object type column.

The following script creates a table t_def_cols. A column ID has been defaulted to a sequence while the column DOJ will always have a non-null value:

/*Create a sequence*/
CREATE SEQUENCE seq START WITH 100 INCREMENT BY 10
/
/*Create a table with a column defaulted to the sequence value*/
CREATE TABLE t_def_cols
( id number default seq.nextval primary key,
name varchar2(30),
doj date default on null '01-Jan-2000'
)
/

The following PL/SQL block inserts the test data:

/*Insert the test data in the table*/
BEGIN
INSERT INTO t_def_cols (name, doj) values ('KATE', '27-FEB-2001');
INSERT INTO t_def_cols (name, doj) values ('NANCY', '17-JUN-1998');
INSERT INTO t_def_cols (name, doj) values ('LANCE', '03-JAN-2004');
INSERT INTO t_def_cols (name) values ('MARY');
COMMIT;
END;
/

Query the table and check the values for the ID and DOJ columns. ID gets the value from the sequence SEQ while DOJ for MARY has been defaulted to 01-JAN-2000.

/*Query the table to verify sequence and default on null values*/
SELECT * FROM t_def_cols
/

       ID NAME     DOJ
---------- -------- ---------
       100 KATE     27-FEB-01
       110 NANCY   17-JUN-98
       120 LANCE   03-JAN-04
       130 MARY     01-JAN-00

Support for 32K VARCHAR2

Oracle Database 12c supports the VARCHAR2, NVARCHAR2, and RAW datatypes up to 32,767 bytes in size. The previous maximum limit for the VARCHAR2 (and NVARCHAR2) and RAW datatypes was 4,000 bytes and 2,000 bytes respectively. The support for extended string datatypes will benefit the non-Oracle to Oracle migrations.

The feature can be controlled using the initialization parameter MAX_STRING_SIZE. It accepts two values:

• STANDARD (default)—The maximum size prior to the release of Oracle Database 12c will apply.
• EXTENDED—The new size limit for string datatypes apply. Note that, after the parameter is set to EXTENDED, the setting cannot be rolled back.

The steps to increase the maximum string size in a database are:

1. Restart the database in UPGRADE mode. In the case of a pluggable database, the PDB must be opened in MIGRATE mode.
2. Use the ALTER SYSTEM command to set MAX_STRING_SIZE to EXTENDED.
3. As SYSDBA, execute the $ORACLE_HOME/rdbms/admin/utl32k.sql script. The script is used to increase the maximum size limit of VARCHAR2, NVARCHAR2, and RAW wherever required.
4. Restart the database in NORMAL mode.
5. As SYSDBA, execute utlrp.sql to recompile the schema objects with invalid status.

The points to be considered while working with the 32k support for string types are:

• COMPATIBLE must be 12.0.0.0
• After the parameter is set to EXTENDED, the parameter cannot be rolled back to STANDARD
• In RAC environments, all the instances of the database comply with the setting of MAX_STRING_SIZE