ORACLE 12C Features

Oracle Database 12c introduces a new multitenant architecture that makes it easy to deploy and manage database clouds.

DEFAULT Values Using Sequences
In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence pseudocolumns as the default values for a column. You should also consider using Identity columns for this purpose.

In the following example you can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert. This behaviour can be modified using the ON NULL clause described in the next section.

CREATE SEQUENCE t1_seq;

CREATE TABLE t1 (
  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)
);

INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

SELECT * FROM t1;

        ID DESCRIPTION
---------- ------------------------------
         1 DESCRIPTION only
       999 ID=999 and DESCRIPTION
           ID=NULL and DESCRIPTION

3 rows selected.

SQL>The fact we can use both the NEXTVAL and CURRVAL pseudocolumns gives us the ability to auto-populate master-detail relationships, as shown below.

CREATE SEQUENCE master_seq;
CREATE SEQUENCE detail_seq;

CREATE TABLE master (
  id          NUMBER DEFAULT master_seq.NEXTVAL,
  description VARCHAR2(30)
);

CREATE TABLE detail (
  id          NUMBER DEFAULT detail_seq.NEXTVAL,
  master_id   NUMBER DEFAULT master_seq.CURRVAL,
  description VARCHAR2(30)
);

  

Oracle 12c Rel 1 New Feature-Implicit Result Set :
Now we can create a stored procedure to return one or more resultsets containing this data. Also you need not explicitly define the ref cursor as OUT parameter.

create or replace procedure pimprefcur
authid current_user
is
vemp sys_refcursor;
vdept sys_refcursor;

begin...
open vemp for 'select ename from emp where empno=7788';
dbms_sql.return_result(vemp);
open vdept for 'select dname from dept where deptno=10';
dbms_sql.return_result(vdept);
end;
/

---executing the above procedure would display the values returned by both the
---ref cursors

SQL> exec pimprefcur;
PL/SQL procedure successfully completed.

ResultSet #1
ENAME
----------------------
SCOTT

ResultSet #2
DNAME
----------------
ACCOUNTING

 Oracle 12c Rel1 New Feature: Online Migration of Table Partition & Sub partition made online unblocking DDL and DML:

DML and DDL changes are made to the partition. It removes the read only state for the actual MOVE command.
Global Index is being maintained so does not require a manual rebuild

--Following statem...ent is used to move partition or subpartition when tablespace is offline
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;

--Following statement is used to move parition or subpartition online maintaining any local and global index on the table.
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;

Oracle 12c Rel1 New Feature: Temporary Undo:
Prior to Oracle 12c R1, any DML you do on a "Global temporary table" (GTT) would generate undo and this would inturn generate redo records.

From Oracle 12c R1 the UNDO generated by temporary tables can now be stored in a temporary tablespace instead of storing it in UNDO tablespace.

The advantage of temporary undo : 1) reduction in undo tablespace 2) less redo data generation

To use this feature (at system or session), enable TEMP_UNDO_ENABLED initialization parameter

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=TRUE;

*If you are doing lot of temporary work where you use GTT think of using Temporary Undo

Oracle 12c Rel1 New Feature: Invisible Columns in Table:

You will be able to hide columns in a table.

SQL> CREATE TABLE emp (empno number(6), ename varchar2(50), sal number(10,2) INVISIBLE);

--You can also alter a table to "VISIBLE" or "INVISIBLE":
SQL> ALTER TABLE emp MODIFY (sal visible);

--From SQL prompt when you issue DESC it will not display the invisible column. To display invisible col...umn from within SQL Plus, issue:
SQL> SET COLINVISIBLE ON

--When you insert values into the table having invisible column, have to include all the columns names :
SQL> Insert into emp (empno, ename, sal) values (1001,'Sairam',7868);

1 row created.

--If you try to insert without specifying column names and specily all the column valus it will throw an error
SQL> Insert into emp values (1001,'Sairam',7868);
*
ERROR at line 1:
ORA-00913: too many values

---When inserting just specifying only the visible column values will work fine
SQL> Insert into emp values (1001,'Sairam');

1 row created.

--remember generic queries like the following will not show the result of invisible column
SQL> select * from emp;

 Multiple indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.

Here’s an the example:

SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);
SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;

 Adding multiple new partitions

Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. The following example explains how to add multiple new partitions to an existing partitioned table:

SQL> CREATE TABLE emp_part
    (eno number(8), ename varchar2(40), sal number (6))
  PARTITION BY RANGE (sal)
  (PARTITION p1 VALUES LESS THAN (10000),
   PARTITION p2 VALUES LESS THAN (20000),
   PARTITION p3 VALUES LESS THAN (30000)
  );
Now lets add a couple of new partitions:

SQL> ALTER TABLE emp_part ADD PARTITION
  PARTITION p4 VALUES LESS THAN (35000),
  PARTITION p5 VALUES LESS THAN (40000);

The following example explains how to drop or truncate multiple partitions on an existing partitioned table:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;
To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause, shown below:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

 ROW limiting for Top-N result queries

There are various indirect approaches/methods exist to fetch Top-N query results for top/bottom rows in the previous releases. In 12c, retrieving Top-N query results for top/bottom rows simplified and become straight forward with the new FETCH FIRST|NEXT|PERCENT clauses.

In order to retrieve top 10 salaries from EMP table, use the following new SQL statement:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
    FETCH FIRST 10 ROWS ONLY;
The following example fetches all similar records of Nth row. For example, if the 10th row has salary of 5000 value, and there are other employees whose salary matches with the Nth value, the will also be fetched upon mentioning WITH TIES clause.

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
    FETCH FIRST 10 ROWS ONLY WITH TIES;

The following example limits the fetch to 10 per cent from the top salaries in the EMP table:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
    FETCH FIRST 10 PERCENT ROWS ONLY;
The following example offsets the first 5 rows and will display the next 5 rows from the table:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
    OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
All these limits can be very well used within the PL/SQL block too.

BEGIN
  SELECT sal BULK COLLECT INTO sal_v FROM EMP
    FETCH FIRST 100 ROWS ONLY;
END;

 WITH clause improvements
In 12c, you can have faster running PL/SQL function/procedure in SQL, that are defined and declared within the WITH clause of SQL statements. The following examples demonstrate how to define and declare a procedure or function within the WITH clause:

WITH
  PROCEDURE|FUNCTION test1 (…)
  BEGIN
    <logic>
  END;
SELECT <referece_your_function|procedure_here> FROM table_name;
/
Although you can’t use the WITH clause directly in the PL/SQL unit, it can be referred through a dynamic SQL within that PL/SQL unit.

  

Extended data types
In 12c, the data type VARCHAR2, NAVARCHAR2, and RAW size will support up to 32,767 bytes in contrast to 4,000 and 2,000 in the earlier releases. The extended character size will reduce the use of going for LOB data types, whenever possible. In order to enable the extended character size, you will have to set the MAX_STRING_SIZE initialization database parameter to EXTENDED.

Wants to know, who deleted my table

A developer came to me demanding to know who deleted One table he had been using.

But i have a problem every developer using same user login, suddenly i thought if i can find HostName than my problem will be solved.
I spent some minute to write this query :-

Details : find which user and hostname dropped a table in a database.
-----------------------------------------------------------------------------------------------
Select LoginName,ObjectName,ObjectType,Name,HostName from fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
value
FROM [fn_trace_getinfo](NULL)
WHERE [property] = 2
)),1) T join Sys.trace_events TE ON T.EventClass = TE.trace_event_id and T.ObjectName is not null and TE.name='Object:Deleted'

RESULT_CACHE oracle 11g feature

A table that has no frequent update is being queried frequently in a function and the function is being called multiple times.  In each function call, query will be executed and the results will be sent to the caller.  Execution of query is a overhead where it go through the SQL layer in SGA to get the unchanged results. This will lead to performance problem.

RESULT_CACHE mechanism caches the unchanged results in memory when the function is called for the first time.  Oracle searches for the results in the cache if the function is called again with same parameters and return the results without executing the function.  When there is any change in parameter,  then the results will be cached again for one time.

If there is any update in the table, the cache will be invalidated and will be cached with the latest data when the function is called again. This will drastically improve the performance and we just need to include one statement
result_cache result_on (<table name>) in the existing function to implement caching!

Syntax: 

RESULT_CACHE RELIES_ON (<table name>)

Sample:

create or replace function f1 (p_eno number)
return employee%rowtype
result_cache result_on (employee)
is
    rec  employee%rowtype;

begin
    select * into rec from employee where empno = p_eno;
    return rec;
end;