So now when 12c is officially released, lets have a look on the new features/enhancement it offers. This post is basically outline the new features but not in details.
My future post(s) will deal with about these features in detail. Hope you like it and as usual any feedback is welcome.
First of all, The most important thing about 12c is <Multitenant Architecture (Container and Pluggable databases). So, before using Oracle database 12c please read the concept of Multitenant architecture in the 12c concept guide here and here.
More information about Multitenant environment can be found in Part VI of Oracle Database Administrator’s Guide 12c Release 1
And second, in this release Oracle Enterprise Manager Database Control is not included but Oracle introduced a new web-based tool Oracle Enterprise Manager Database Express (EM Express). You can also use Enterprise Manager Cloud Control 12c to manage your database.
- Oracle Database now support maximum of 100 Database writer process, though 1 is sufficient for most of the occasions. The first 26 DBWn processes named through DBW0-DBW9 and DBWa-DBWz and other 63 processes named through BW36-BW99.
The initialization parameter DB_WRITER_PROCESSES specifies the initial number of DBWR process(es) for an instance.
- A new process Listener Registration Process (LREG), is introduced to register the instance with the listeners. In the earlier releases prior to 12c PMON is responsible for registering information about the instance and dispatcher processes with the Oracle Net listener.
- The number of Archiver Process (ARCn) is increased from 10 to 30 (ARC0-ARC9 and ARCa-ARCt) and default is 4 (in earlier release the default was 2).
- New Administrative privileges to perform basic database operations. SYSDBA and SYSOPER is available from the ancient time, database 11g introduced SYSASM and 12c provide three new admin privilege for performing some regular tasks.
- SYSBACKUP – to perform backup and recovery operations using RMAN or SQL*Plus
- SYSDG – facilitates to perform Data Guard operations via DGMGRL command-line interface or using Data Guard Broker.
- SYSKM – to perform TDE keystore operations
The size of VARCHAR2, NVARCHAR2 and RAW data type has been increased from 4000 to 32767 bytes. To use this new increased size one has to set up initialization parameter MAX_STRING_SIZE. The default value for parameter is STANDARD which limits the length of VARCHAR2 and NVARCHAR2 to 4000 bytes/characters and RAW to 2000 bytes.
To able to use 32767 bytes limit introduced in 12c, set the MAX_STRING_SIZE = EXTENDED. The COMPATIBLE parameter in initialization file must be set to 18.104.22.168 or higher to use MAX_STRING_SIZE = EXTENDED. You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED but you cannot move back to STANDARD.
To increase the size, you have to (re)start the database in UPGRADE mode, change the value of MAX_STRING_SIZE to EXTENDED and run the utl32k.sql script reside in /rdbms/admin directory.
- Now, CASCADE clause is available with TRUNCATE statement. So, you don’t have to worry about all the child table. When use CASCADE with TRUNCATE, oracle will empty all the tables which has referential constraint pointed to the table being truncated. The only thing is necessary that foreign key is defined with ON DELETE CASCADE clause and must be ENABLED. It is a recursive operation with no set limit so all child, grand child, great grandchild, and so on tables will be truncated.
- 12c let you create multiple indexes on the same set of columns. The only restriction is some of characteristics should be different. For example, you can create both Bitmap and B*Tree indexes on same set of column as well as unique and non-unique index. The other thing to keep in mind is only one of these indexes can be visible at a time, and any other indexes must be invisible.
Invisible column. Oracle DB 11g introduced Invisible index and now with Oracle Database 12c one can create a column as invisible. Benefits – any generic operation on table will not show the column defined as Invisible until explicitly specified by name. For example, DESCRIBE command, SELECt * FROM or %ROWTYPE attribute in PL/SQL. Similarly when using INSERT statement, if you do not specify the invisible column name in the insertion list it won’t insert the value in it.
Use INVISIBLE clause to make/add an column as invisible:
CREATE TABLE test ( A NUMBER(3), B NUMBER(3) INVISIBLE);
and Of course you can make it visible using
ALTER TABLE <table_name> MODIFY (<col_name> VISIBLE); ALTER TABLE test MODIFY b VISIBLE;
DBA/ALL/USER_TAB_COLS view has a new column named HIDDEN_COLUMN to indicate whether the column is hidden (if value is YES) or not.
One fun fact – switching between invisible and visible column can change column ordering. Or you can read Tom Kyte’s blog post regarding this behavior.
And another thing – EXternal, Cluster or Temporary tables cannot have invisible columns.
- Identity columns. This 12c new feature auto-generates an integer value at the time of insertion just like like sequence. No need to create a separate sequence or trigger object. An identity column is defined with the table definition.
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] CREATE TABLE test (seq NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1));
An Identity column can be used as a primary key. Only one Identity column per table and this property cannot be inherited by CTAS statement.
- Starting with 12c you can issue most of the SQL commands in RMAN without prefixed with SQL keyword or enclose SQL commands in quotes. RMAN also support SQL*Plus DESC command.
- A view can be exported as a table using parameter VIEWS_AS_TABLES.
So these are some cool new feature from the docs (and many more in the next few posts) till then
Happy learning… 🙂
Oracle database 12c OBE tutorials can be found here.