This new feature in 12C allows to make invisible columns for generic queries, operations. To make invisible column you need to use INVISIBLE clause.
Following operations don’t see invisible columns
Very often developers try to add primary constraints on a table and they can’t do it because of duplicates in data. So question is how to quickly find the duplicates without effort.
Let’s prepare test data
create table test_tbl (id number not null); begin for i in 1..10 loop insert into test_tbl values(i); end loop; insert into test_tbl values(10); commit; end; / select * from test_tbl order by id; ID ---------- 1 2 3 4 5 6 7 8 9 10 10
so adding primary key raises exception
alter table test_tbl add constraint test_tbl_pk primary key(id);
SQL Error: ORA-02437: cannot validate (TOMASZ.TEST_TBL_PK)
- primary key violated
02437. 00000 - "cannot validate (%s.%s) - primary key violated"
*Cause: attempted to validate a primary key with duplicate
values or null values.
*Action: remove the duplicates and null values
before enabling a primary key.
New extensions are added for SQL in 12C
This new Oracle 12C feature enables to define auto numbering for a column. Only one column can be defined as identity.
It is recommended to read about other extensions in 12C for default column values
identity_options – defines sequence generator. It creates a sequence in database that is later used to generate values for identity column.
DEFAULT ON NULL in Oracle 12c assign default value if INSERT attempts to assign a value that evaluates to NULL.
drop table test_tbl; create table test_tbl ( id number default on null 5, id1 number); insert into test_tbl values(null, 10); insert into test_tbl values(100, 20); select * from test_tbl; ID ID1 ---------- ---------- 5 10 100 20
In Oracle 12c DEFAULT values for columns can directly refer to Oracle sequences.
create sequence test_seq; create synonym test_seq1 for test_seq; create table test_tbl (id number default test_seq.currval, id1 number default test_seq.nextval, id2 number default test_seq1.currval, id3 number); insert into test_tbl(id3) values(10); insert into test_tbl(id3) values(20); insert into test_tbl(id3) values(30); select * from test_tbl; ID ID1 ID2 ID3 ---------- ---------- ---------- ---------- 1 1 1 10 2 2 2 20 3 3 3 30
This article presents how to quickly install Oracle Database 11G Release 2 (11.2.0.3) and as Oracle Grid 11G Release 2(11.2.0.3) on Windows.
Check article for new release Install Oracle 12C Release 1 (12.1) on Windows 7, 8
Software used:
You can download Oracle Database 11G Release 2(11.2.0.3) and Oracle Grid 11G Release 2(11.2.0.3). You can download them on Oracle support formerly MetaLink.
p10404530_112030_MSWIN-x86-64_1of7.zip - database software p10404530_112030_MSWIN-x86-64_2of7.zip - database software p10404530_112030_MSWIN-x86-64_1of7.zip - grid software
Hardware and software requirements for database and grid software:
Windows 8 Support
Starting with Oracle Database 11g Release 2 (11.2.0.4), Oracle Database server and client are supported on Windows 8. Oracle RAC is not supported on Windows 8.
In case you are not planning to install grid 2GB ram is enough.
This installation was done for 11.2.0.3 on Windows 7 but with release 11.2.0.4 it’s possible on Windows 8.
Introduction
New higher limits (in bytes) are available for following types
Previous limits prior to Oracle Database 12c
NOTE – remember new limits are always 32767 bytes even if you are using CHAR semantic
If your database was started in restricted mode you can encounter following error
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:39:28 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: sys@ora12c as sysdba Enter password: ERROR: ORA-12526: TNS:listener: all appropriate instances are in restricted mode
To solve this problem you need to make static registration of your service in listener.ora.
Smart Flash Cache concept is not new in Oracle 12C – DB Smart Flash Cache in Oracle 11g.
In this release Oracle has made changes related to both initialization parameters used by DB Smart Flash cache. Now you can define many files|devices and its sizes for “Database Smart Flash Cache” area. In previous releases only one file|device could be defined.
DB_FLASH_CACHE_FILE = /dev/sda, /dev/sdb, /dev/sdc DB_FLASH_CACHE_SIZE = 32G, 32G, 64G
So above settings defines 3 devices which will be in use by “DB Smart Flash Cache”
New view V$FLASHFILESTAT – it’s used to determine the cumulative latency and read counts of each file|device and compute the average latency.
Have a fun 🙂
Tomasz