Oracle 11 G New Features for DBA | Latest features in Oracle 11G

Oracle 11 G Features
Oracle 11 G Features

Oracle 11 G New Features for DBA :

Oracle has announced the new version Oracle 11 G and announced the new features in that version.Oracle has added around 500 Oracle 11 G New Features for DBA.(exact number is 482 features).The Most exciting features are DBA related features and simplicity and well structured DBA tasks.Another important Oracle 11 G New Feature is related to Performance Tuning and optimization.Oracle has added considerable enhancements in SQL to raise the performance of SQL Query.In this article i will explain the extra features related to SQL.


Oracle 11 G new Features
Oracle 11 G Features
  • Following are some Oracle 11 G New Features for DBA :

1.ALTER Table with Default values:

In Oracle previous version there is no facility to alter the table with adding the default values in it.In Oracle 11 G ,Oracle provides the facility to alter the table with adding default values in the specified column.So developer efforts of updating the column values has been reduced.


Add the New column in Employee table in which Employee_Status is ‘Working’


Alter table Employee

Add Empoyee_Status varchar2(20)  Default ‘Working’ not null;

2.New Table Datatypes added named ‘simple_integer’,’simple_float’,’simple_double’:

In Oracle 11 G new datatype is introduced which is simple_integer datatype.This simple_integer datatype is always not null.This datatype is faster than Integer and PLS_INTEGER datatype in SQL.These all datatypes are subtypes of NLS_INTEGER,INS_FLOAT,INS_DOUBLE datatype.

Simple_Integer is nothing but PLS Integer with not null constraint


v_start NUMBER;
v_loops NUMBER := 10000000;
v_pls_integer PLS_INTEGER := 0; —PLS integer declaration
v_pls_integer1 PLS_INTEGER := 1;
v_simple_integer SIMPLE_INTEGER := 0;
v_simple_integer1 SIMPLE_INTEGER := 1; –Simple_integer declaration

v_start := DBMS_UTILITY.get_time; –We are using the database utility get_time

FOR i IN 1 .. v_loops LOOP
v_pls_integer := v_pls_integer + v_pls_integer1; —Summation of PLS integers and check the time

DBMS_OUTPUT.put_line(‘PLS_INTEGER: ‘ || (DBMS_UTILITY.get_time – v_start) || ‘ hsecs’); —time for PLS integer

v_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP
v_simple_integer := v_simple_integer + v_simple_integer1; —Summation of simple_integer integers and check the time

DBMS_OUTPUT.put_line(‘SIMPLE_INTEGER: ‘ || (DBMS_UTILITY.get_time – v_start) || ‘ hsecs’);

END P_Simple_Integer_test;


PLS_INTEGER: 19 hsecs

3.Invisible Indexes:

Indexes can be created in Invisible mode in Oracle 11 G.The new parameter has been introduced named OPTIMIZER_USE_INVISIBLE_INDEXES and optimizer uses the same parameter if it sets to ‘TRUE’ or ‘FALSE’.This parameter sets to true or false at system level.


Create or replace index Index_name on Table_name(Column_name) Invisible;

Alter index:

ALTER Index Indexname set Invisible=[TRUE|FALSE];

4.SQL /*result_cache*/ Hint:

The result data is cached in the data buffer using this hint.This hint improves the performance of query drastically as the result data is stored in data buffer the data is been fetched directly from the buffer location.The RESULT_CACHE_MODE parameter specifies the applicability of result cache feature in SQL queries.It accepts two admissible values ‘Manual’ and ‘Force’.For MANUAL mode, RESULT_CACHE hint must be specified with the SQL statements to use the feature. In FORCE mode, server enables the caching feature with all the SQL statements.




Example of Hint:

Select /*+result_cache*/ Department_ID,Dept_num

from Department;

5.Read Only Mode of Tables:

Oracle 11G provides the facility to change the mode of the table to Read only mode.In read_only mode table can only be queried.The DML and DDL (Truncate and Alter) options are restricted in read_only mode.At any point of time the table mode has been changed from Read mode to write mode.



Query to Find Second highest salary of Employee with explaination..

6.Regular Expression (REGEXP_COUNT function):

REGEXP_COUNT is the new regular expression function, introduced in Oracle 11g release. This function is introduced for language support which is used to count the character or string appearances in a given string.

SELECT ‘Rohit Shinde’ Programmer,REGEXP_COUNT(Programmer, ‘s’, 1, ‘i’) ‘Expression_Output’ from dual;


Programmer   Expression_Output

Rohit Shinde   1

7.Virtual Columns:

Oracle 11 G provides new feature of virtual columns which allows user to provide the column virtually where value is specified in Expression itself.Virtual columns acts like a normal columns during indexing and partitioning.Virtual columns belongs to LOBs or collection datatypes.


Employee_num NUMBER,
Salary NUMBER,
Dearness_allowance NUMBER,
Total_salary NUMBER AS ((Salary+Dearness_allowance)    –this column is virtual column

8.Fully Automatic Tuning:

In Previous version Oracle 10 G only advices for tuning using SQL Tuning Adviser.In Oracle 11 G provides facility of automatically applying the SQL profile to improve the performance of the query. The performance comparisons are done by a new administrative task during a user-specified maintenance window.


The partitioning plays an important role in database performance and every SQL professional wants to do the perfect partitioning to improve the performance of SQL queries.There are following new features added in SQL partitioning:

1.System Partitioning

2.Reference Partitioning

3.Interval Partitioning

4.Extended Composite Partitioning

10.Analytical Functions-NTH,LISTAGG:

Oracle 11 G specifies two new aggregate functions:

LISTAGG aggregates a column values in a single row format.


NTH_VALUE is an extended format of FIRST_VALUE and LAST_VALUE functions to get a random row from a grouped result set.


LISTAGG – LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (ORDER
BY clause) [OVER PARTITION BYclause]

Real Life Scenario:

Kindly consider following table.We need to find aggregate results departmentwise in form of rows.

Employee table:

Employee_num Employee_name Department ID Salary
1 Amit 100 680000
2 Rohan 100 550000
3 Rohit 101 430000

Query used:

select Department_ID,listagg(Employee_name,’,’) within group(order by Employee_name) as Employee_name from Employee group by Department_id;


Department ID Employee_name
100 Amit,Rohan
101 Rohit

So  listagg function is very useful function for converting columns in to single row format.

11.Skip locked utility:

Oracle 11 G provides the new utility where the records are directly fetched from table.The records from running transactions are not considered.

“Skip locked utility doesnot fetches the locked records in transaction”


select * from Employee

for update

Skip locked;

12.XML SQL queries – Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored inside tables.


Here I have tried to cover the Newly added features in Oracle 11G. Please do let me know if any other information needs to be added in this.Please comment below if you needed extra information or suggestions if any..


About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development


  1. Write more, thats all I have to say. Literally, it seems as though you relied on the video to
    make your point. You obviously know what youre talking about, why waste your intelligence on just posting videos to
    your blog when you could be giving us something enlightening to read?

  2. I’m amazed, I must say. Seldom do I come across a blog that’s both equally
    educative and entertaining, and without a doubt, you’ve hit the nail on the head.
    The issue is something that not enough people are speaking intelligently about.
    I’m very happy that I stumbled across this in my hunt for something relating to this.

  3. Magnificent beat ! I would like to apprentice while you amend your website, how could i subscribe for a weblog web site?
    The account aided me a acceptable deal. I were a little bit
    acquainted of this your broadcast offered vivid clear idea

  4. I was wondering if you ever thought of changing the layout of your blog?
    Its very well written; I love what youve got to say.

    But maybe you could a little more in the way of content so people could connect with it better.
    Youve got an awful lot of text for only having 1 or two images.
    Maybe you could space it out better?

    • Sure…I will work on it..I will add more pictorical views to explain the concepts better…Thanks for valuable suggestions…

  5. I’m gone to inform my little brother, that he should also visit this webpage on regular
    basis to obtain updated from most up-to-date gossip.

  6. Hello,I check your blogs named “Oracle 11 G New Features | Latest features in Oracle 11G” like every week.Your story-telling style is awesome, keep doing what you’re doing!

  7. Hello! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche.
    Your blog provided us beneficial information to work on. You
    have done a extraordinary job!

  8. Hey I know this is off topic but I was wondering if
    you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something
    like this. Please let me know if you run into anything.
    I truly enjoy reading your blog and I look forward to
    your new updates.

  9. This post gives clear idea in favor of the new users of blogging, that genuinely how to do blogging.

  10. Very nice post. I just stumbled upon your weblog
    and wanted to say that I have truly enjoyed surfing around your blog posts.

    In any case I will be subscribing to your feed and I hope you write again soon!

  11. You really make it seem so easy with your presentation but I find this topic to be really something which I think I would never understand.
    It seems too complex and extremely broad for me.

    I’m looking forward for your next post, I’ll try to get the hang of it!

  12. Hi, I do think this is an excellent site. I stumbledupon it 😉
    I’m going to return yet again since i have book-marked it.
    Money and freedom is the best way to change, may you be rich and continue to
    help others.

11 Trackbacks / Pingbacks

  1. Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example
  2. Interview Questions For Oracle | SQL interview Questions
  3. Oracle Partitioning | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  4. SQL Functions List,Functions in SQL,SQL Functions example,Aggregate functions,scalar functions,Aggregate functions in sql,scalar functions in sql,functions
  5. OBIEE
  6. Oracle 18c | Oracle 18c Features | Oracle 18c Advantages
  7. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  8. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  9. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  10. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  11. Oracle 11g vs 12c | Difference between Oracle 11G and Oracle 12c | Oracle 12c Features

Comments are closed.