12c database is full of new features.Here are Very useful 10 new things in 12c database
1) In Pre 12c, Only the user with sysdba can dba_recyclebin
SQL> CONNECT / AS SYSDBA
SQL> purge dba_recyclebin
In 12c, it has been changed,It is nolonger require the sysdba privelegde
It Uses a new system privilege: PURGE DBA_RECYCLEBIN
So you can grant this privs to some other user
SQL> CONNECT system
SQL> grant PURGE DBA_RECYCLEBIN to scott;
SQL> CONNECT scott
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
2) We dont require sql quotes in RMAN in 12c
Pre R12c
RMAN > sql ‘ALTER TABLESPACE TOOLS
ADD DATAFILE ”/u000/data/tools.dbf” SIZE 10M’;
in 12c
RMAN > ALTER TABLESPACE TOOLS
ADD DATAFILE ”/u000/data/tools.dbf” SIZE 10M;
Related: Oracle Database 12c -Multitenant Architecture
3)We can use desc table in RMAN prompt also
4) Transportable database used to work on Image copies in pre 12c, with 12c, we can also use backup set to transfer data across the platform
The way its works
Source conversion
RMAN> BACKUP TO PLATFORM ‘Linux x86 64-bit’
FORMAT ‘/u67/backup/database_tb.bck’ DATABASE;
Destination conversion
BACKUP FOR TRANSPORT FORMAT ‘/u67/backup/database_tb.bck’
DATABASE;
5) New Table recovery method in 12c
we can recover the table using this RMAN method in 12c.It works as explained below
1) we enter the recover table with theoption of time,SCN or sequence number
2) RMAN determines the backup based on your specification.
3) RMAN creates an auxiliary instance by using the AUXILIARY DESTINATION clause, if specified.
4) RMAN recovers your tables or table partitions, up to the specified point in time, into this auxiliary instance.RMAN just restore the system,undo tablesapce and the tablespace where table data resides. and recover upto the point required.
5) RMAN creates a Data Pump export dump file that contains the recovered objects with the DUMP FILE=name and DATAPUMP DESTINATION=<OS path>.
Related: Top 10 New Features of Oracle 12c
6) Starting in Oracle Database 12c, the optimizer automatically decides if dynamic sampling is useful and what dynamic sampling level will be used for all SQL statements.Automatic dynamic sampling is enabled when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the value of 11.Increase the scope to non-parallel statements.Extend capabilities to include joins and group by.
Make results of dynamic sampling queries persistent in cache to eliminate overhead of repeated sampling.
7. In 12c,Online Statistics Gathering is done for Bulk-Load
CREATE TABLE AS SELECT
INSERT INTO … SELECT into an empty table by using a direct path insert.
Statistics are available immediately after load.No additional table scan is required to gather statistics.
8) we have provision of invisible column or hidden column in 12c
CREATE TABLE test (txt1 VARCHAR2(10), txt2 NUMBER INVISIBLE);
Here txt2 is hidden column
We cannot view the column when describing the table. we can alter the table to make it visible.
we have to specifically quote it when using it
There is a command SET COLINVISIBLE in sqlplus which will make all the hidden column visible when describing
9) With the release of Oracle Database 12c, Oracle has introduced this new SQL syntax to simplify fetching the first few rows. The new sql syntax “Fetch First X Rows only” can be used.
10) Oracle Flex ASM: Removes the requirement to have one and only one ASM instance per cluster node. Not all nodes in a RAC cluster must have an ASM instance running