19 March 2017

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 VARCHAR2NVARCHAR2, 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 VARCHAR2NVARCHAR2, 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

Differences between IaaS, PaaS and SaaS



Infrastructure as a Service (IaaS)Providing the fundamental building blocks of computing resources, IaaS takes the traditional physical computer hardware, such as servers, storage arrays, and networking, and lets you build virtual infrastructure that mimics these resources, but which can be created, reconfigured, resized, and removed within moments, as and when a task requires it. The most well known IaaS provider, Amazon Web Services, offers a variety of options, including their “EC2” computing platform, and “S3” storage platform.

Platform as a Service (PaaS)
Operating at the layer above raw computing hardware, whether physical or virtual, PaaS provides a method for programming languages to interact with services like databases, web servers, and file storage, without having to deal with lower level requirements like how much space a database needs, whether the data must be protected by making a copy between 3 servers, or distributing the workload across servers that can be spread throughout the world. Typically, applications must be written for a specific PaaS offering to take full advantage of the service, and most platforms only support a limited set of programming languages. Often, PaaS providers also have a Software as a Service offering (see below), and the platform has been initially built to support that specific software. Some examples of PaaS solutions are the “Google App Engine” system, “Heroku” which operates on top of the Amazon Web Services IaaS system, and “Force.com” built as part of the SalesForce.com Software as a Service offering.

Software as a Service (SaaS)
The top layer of cloud computing, Software as a Service is typically built on top of a Platform as a Service solution, whether that platform is publicly available or not, and provides software for end-users such as email, word processing, or a business CRM. Software as a Service is typically charged on a per-user and per-month basis, and companies have the flexibility to add or remove users at any time without addition costs beyond the monthly per-user fee. Some of the most well known SaaS solutions are “Google Apps”, Salesforce.com, and Microsoft’s “Business Productivity Online Suite”