Difference between 10g, 11g and 12c

Difference between 10g, 11g and 12c

Oracle Database 10g New Features:

NID utility has been introduced to change the database name and id.

Automated Storage Management (ASM). ASMB, RBAL, ARBx are the new background processes related to ASM.

New memory structure in SGA i.e. Streams pool (streams_pool_size parameter), useful for datapump activities & streams replication.

From Oracle 10g, the spool command can append to an existing one.
SQL> spool result.log append

Ability to rename tablespaces (except SYSTEM and SYSAUX), whether permanent or temporary, using the following command:

SQL> ALTER TABLESPACE oldname RENAME TO newname;

Oracle Database 11g New Features:

Ability to mark a table as read only.
SQL> alter table table-name read only;

Temporary tablespace or it's tempfile can be shrinked, up to specified size.
SQL> alter tablespace temp-tbs shrink space;
SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};

Introduction of ADR, single location of all error and trace data in the database, diagnostic_dump parameter

A new parameter is added in 11g, called MEMORY_TARGET, automatic memory management for both the SGA and PGA.

A system privelege is introduced in 11g called SYSASM, and this is granted to users who need to perform ASM related tasks.

Oracle Database 12c New Features:

Moving and Renaming datafile is now ONLINE, no need to put datafile in offline.
SQL> alter database move datafile 'path' to 'new_path';

No need to shutdown database for changing archive log mode.

Like sysdba, sysoper & sysasm, we have new privileges, in Oracle 12.1.0.
sysbackup for backup operations
sysdg for Data Guard operations
syskm for key management

Patching:
Centralised patching.
We can retrieve OPatch information using sqlplus query, using DBMS_QOPATCH package

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

We can test patches on database copies, rolling patches out centrally once testing is complete.

New Commands
create pluggable database ...
alter pluggable database ...
drop pluggable database ...


New background processes - LREG (Listener Registration), SA (SGA Allocator), RM.

Oracle Database 12c Data Pump will allow turning off redo for the import operation (only).
$ impdp ... transform=disable_archive_logging:y

There are lots of differences! 

Some of the big ones are: 

- Multitenant/pluggable databases 
- In Memory (12.1.0.2 
- JSON support (12.1.0.2) 
- Adaptive queries 
- New histogram types 
- SQL enhancements (match_recognize, PL/SQL in the with clause, ...)