Oracle Tips: Oracle Database 11g New Features

ORACLE DATABASE 11g :    New Features

 

Oracle Database 11g, building on Oracle's unique ability to deliver Grid Computing, gives Oracle customers the agility to respond faster to changing business conditions, gain competitive advantage through technology innovation, and reduce costs.

With Oracle Database 11g you can:

• Adopt new technology faster with Real Application Testing
• Manage more data for less with advanced compression and partitioning
• Simplify systems by storing all your data in the Oracle Database with Oracle Fast Files
• Maximize the ROI of disaster recovery resources with new Data Guard advances
• Free critical personnel for strategic tasks with management automation

Boost DBA Productivity with Management Automation

 

Oracle Database 11g continues the effort—begun in Oracle9i Database and carried on

through Oracle Database 10g—of dramatically simplifying and fully automating DBA tasks.

 

New capabilities in Oracle Database 11g include

• Automatic SQL tuning with self-learning capabilities

• Automatic, unified tuning of both System Global Area (SGA) and Program Global Area

(PGA) memory buffers

 

New advisors for partitioning, data recovery, streams performance, and space management

• Enhancements to the Automatic Database Diagnostic Monitor (ADDM), which provide

a better global view of performance in Oracle Real Application Clusters (Oracle RAC)

environments and improved comparative-performance analysis capabilities

 

Enable Rapid Data Recovery Through Oracle Data Guard

 

Oracle Data Guard coordinates the maintenance and synchronization of your database

between local and remote servers for rapid recovery from disaster or site failure. Oracle

Database 11g provides a number of significant Oracle Data Guard enhancements, including

 

• The ability to run real-time queries on a physical standby system for reporting and

other purposes

• The ability to perform online, rolling database upgrades by temporarily converting a

physical standby system to logical standby

• Snapshot standby to support test environments

In addition, performance for both physical and logical standby is improved. Logical standby

now supports Extensible Markup Language (XML) Type character large object (CLOB)

datatypes and transparent data encryption. Automatic fast-start failover is now supported for

asynchronous transports.

 

Protect Data with Automatic Storage Management

 

With Automatic Storage Management, Oracle Database 11g automatically mirrors and

balances data across the available storage devices to protect data and optimize performance—

even when you add or remove new disks. Oracle Database 11g delivers several important

high-availability enhancements for Automatic Storage Management, including

• Support for rolling upgrades

• Automatic bad-block detection and repair

• Fast mirror resync, which efficiently resynchronizes storage arrays that Automatic Storage

Management mirrors when storage network connectivity is temporarily lost

 

Improve Online Operations

 

Oracle Database 11g includes a number of improvements to online redefinition

operations, including

• Finer-grained dependency tracking, which allows the addition of new columns and

procedures without incurring unnecessary recompilation of dependent objects

• Online alter table operations that are easier to execute

• Fast “add column” with default values

• Online index build with no pause to data manipulation language (DML) operations

 

Improve Online Operations

 

Oracle Database 11g includes a number of improvements to online redefinition

operations, including

 

• Finer-grained dependency tracking, which allows the addition of new columns and

procedures without incurring unnecessary recompilation of dependent objects

• Online alter table operations that are easier to execute

• Fast “add column” with default values

• Online index build with no pause to data manipulation language (DML) operations

 

Improve Online Operations

 

Oracle Database 11g includes a number of improvements to online redefinition

operations, including

• Finer-grained dependency tracking, which allows the addition of new columns and

procedures without incurring unnecessary recompilation of dependent objects

• Online alter table operations that are easier to execute

• Fast “add column” with default values

• Online index build with no pause to data manipulation language (DML) operations

 

Other Performance Improvements

 

Oracle Database 11g features other high-performance improvements, including

 

• Automatic compilation for PL/SQL and Java in the database

• Faster triggers, including more-efficient invocations of per-row triggers

• Faster simple SQL operations

• Faster Oracle Data Guard and Oracle Streams replication

• Faster and more-reliable direct connections to network file system (NFS) storage devices

• Faster upgrades

• Faster backup/restore for large files

• Faster backup compression

 

XML

 

Oracle Database 11g provides new XML storage and query enhancements, including

 

• Binary XML storage and XML path indexing for schema-less XML documents

• Expanded support for standards, such as XML Query (XQuery) 1.0, Java Content

Repository (JCR) 1.0, SQL:2007, and service-oriented architecture (SOA)

• XML repository enhancements such as events, XML Linking Language (XLink)/XML

Inclusions (XInclude), and NFS 4.0

• Streams replication and logical standby support for XML Type (CLOBs)

Oracle Text

New Oracle Text enhancements include

• Query performance and scalability improvements

• Advanced multilingual search

• Online indexing operations

• Support for Oracle Enterprise Manager for ease of administration

• User-defined relevance scoring

 

PL/SQL

 

Oracle Database 11g delivers dramatic performance improvements with new “native”

compilation of PL/SQL. Native compilation is easily turned on by setting a single parameter.

There’s no need for a C compiler, and there are no file system DLLs to manage. Native

compilation can improve performance 50 to 100 percent for pure PL/SQL code and 10 to 30

percent for more typical application code containing SQL (when compared to noncompiled

code).

 

Other enhancements include

 

·   New table Data Type "simple_integer" - A new 11g datatype dubbed

simple_integer is introduced. The simple_integer data type is always NOT

NULL, wraps instead of overflows and is faster than PLS_INTEGER.

 

Performance improvements for triggers

 

• Finer-grained dependency tracking

• Dynamic SQL enhancements

• A more powerful performance analysis tool

 

 

SQL Performance Analyzer (sql replay):

 

With this feature we can predict the impact of system changes on a workload. Using this we can forecast changes in response times for SQL after a change. This can be very handy in cases like parameter changes, schema changes, hardware changes, os changes, db upgrades…Any change that influence sql plans is a good candidate for this feature.

 

Oracle Tips : How to use a Cursor variable

 A cursor variable is a pointer (declared as type ref cursor) to an actual cursor. Code which is written to manipulate a cursor variable can be reused for successive assignments to different actual cursors.

 

 

--Example for using a cursor variable

DECLARE

 my_cursor        SYS_REFCURSOR;  --Declaring a cursor variable

 v_choice           NUMBER(1);

 v_dept_row dept%ROWTYPE;

 v_emp_row  emp%ROWTYPE;

BEGIN

 

 v_choice := &choice;   --Getting option

 

 --Opening cursor for different conditions using only one cursor variable in PL/SQL Body

 IF v_choice =1 THEN

    OPEN my_cursor           

            FOR select * from dept where deptno=10;

            FETCH my_cursor INTO v_dept_row;

            DBMS_OUTPUT.PUT_LINE(v_dept_row.dname);

 ELSIF v_choice =2 THEN

    OPEN my_cursor

            FOR select * from emp where ename='KING';

            FETCH my_cursor INTO v_emp_row;

            DBMS_OUTPUT.PUT_LINE(v_emp_row.ename);

 ELSE

    OPEN my_cursor

            FOR select * from emp where empno=721;

            FETCH my_cursor INTO v_emp_row;

            DBMS_OUTPUT.PUT_LINE(v_emp_row.ename);

 END IF;

 CLOSE my_cursor;

 

END;

 

--How to declare a cursor inside a cursor

DECLARE

 emp_cur           SYS_REFCURSOR;

 v_deptno          dept.deptno%type;

 v_dname          dept.dname%type;

 

 CURSOR cur_dept IS

 SELECT deptno,dname,

 CURSOR(select empno,ename from emp where deptno=dept.deptno)

 FROM dept where deptno=30;

 

 v_empno          emp.empno%type;

 v_ename          emp.ename%type;

 

BEGIN

  OPEN cur_dept;

  FETCH cur_dept INTO v_deptno,v_dname,emp_cur;

  DBMS_OUTPUT.PUT_LINE(v_dname);

  DBMS_OUTPUT.PUT_LINE('---------------------------');

  LOOP

    FETCH emp_cur INTO v_empno,v_ename;

            EXIT WHEN emp_cuR%NOTFOUND;

            DBMS_OUTPUT.PUT_LINE(v_ename);

  END LOOP;

  CLOSE emp_cur;

  CLOSE cur_dept;

END;

Simple Procedure to send mails from Oracle

We can send emails using this program from oracle using any exchange server:-

 

 

 

Create or Replace Procedure Send_Mails (                       
                           sender     IN VARCHAR2,
                           recipients IN VARCHAR2,
                           subject    IN VARCHAR2,
                           message    IN VARCHAR2,
                           priority   IN NUMBER ) IS
 /*------------------------------------------------------------------------------------------------------------------------------------------
 Procedure to Send Mails with text attachments
 Parameters:-
 sender                               -    sender email id eg; maneesh.mohan@sos.sungard.com
 recipients                           -    recipient address, currently supports only one recipient
                                               eg; mh2000@gmail.com
 subject                              -    mail subject
 message                              -    message to display as body
 priority                             -    message priority valid range (1 .. 5)
 --------------------------------------------------------------------------------------------------------------------------------------------*/
 conn                  UTL_SMTP.CONNECTION;
 smtp_host             VARCHAR2(1000):='mailserver';
 smtp_port             NUMBER:=25;
 smtp_domain           VARCHAR2(1000):='smsi';
 boundary         CONSTANT VARCHAR2(256) := 'NextPart_000_0605_01C775EB.8BD624E0';
 first_boundary        CONSTANT VARCHAR2(256) := '--' || boundary || utl_tcp.CRLF;
 v_mes            VARCHAR2(1000);
 crlf                  VARCHAR2(5):=UTL_TCP.CRLF;
 -----------------------------------------------------------------------------------------------------------------------------------------
 BEGIN
 -- Open SMTP Connection
 conn := utl_smtp.open_connection(smtp_host, smtp_port);
 utl_smtp.helo(conn, smtp_domain);
 utl_smtp.mail(conn,sender);
 utl_smtp.rcpt(conn,recipients);
 --Start mail body
 utl_smtp.open_data(conn);
 v_mes:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi ss' ) || crlf ||
'From: ' || sender || crlf ||
'Subject: ' || subject || crlf ||
'To: ' || recipients || crlf ||
'Mime-Version: 1.0' || crlf;
--Add mail priority
IF priority IS NOT NULL THEN
    v_mes := v_mes || 'X-Priority: ' || to_char(priority) || '; ' || crlf;
END IF;

v_mes  :=   v_mes ||
          'Content-Type: multipart/mixed; boundary="' || boundary || '"' || crlf ||
          '' || crlf || '' || crlf ||
          first_boundary ||
          'Content-Type: text/plain; ' || crlf ||
          'Content-Disposition: inline;' || crlf ||
          'Content-Transfer-Encoding: 7bit' || crlf ||
          '' || crlf ||
          message || crlf ;

utl_smtp.write_data(conn,v_mes);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);

EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
  BEGIN
    utl_smtp.close_data(conn);
  EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    NULL;
  END;
  RAISE_APPLICATION_ERROR(-20000,'Failed to send mail :' || SQLERRM);
 
End Send_Mails;

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Q-quote operator introduced in Oracle 10g

 

We make use of single quotation mark in SQL and PL/SQL to identify sting literals. If the literal itself contains a single quote, we need to add one more quote next to it. This additional quote acts as an escape character and removes conflict with the outside quotes that are enclosing the string.

 

Oracle realises that long complex strings having lot of single quotes can turn out to become cumbersome and prone to errors that may not be caught during testing.

 

Release 10g onwards, a new quoting mechanism is provided in the form of "q". This new quote operator allows us to choose our own quotation mark delimiter.

 

Here are some examples -

SQL> select 'nitin's web blog. It's personal..' str from dual;

select 'nitin's web blog. It's personal..' str from dual

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

 

What we normally do:-

SQL> select 'nitin''s web blog. It''s personal..' str from dual;

STR

--------------------------------

nitin's web blog. It's personal..

1 row selected.

 

Here is use of Q - quote operator. The above statement can also be represented as any one of the below.

 

select q'(nitin's web blog. It's personal.)' str from dual;

select q'[nitin's web blog. It's personal.]' str from dual;

select q'Anitin's web blog. It's personal.A' str from dual;

select q'/nitin's web blog. It's personal./' str from dual;

select q'Znitin's web blog. It's personal.Z' str from dual;

select q'|nitin's web blog. It's personal.|' str from dual;

select q'+nitin's web blog. It's personal.+' str from dual;

 

(New added)

select q'znitin's web blog. It's personal.z' str from dual;

 

And so on. After the Q-quote is specified, provide the single quote along with a unique character at the beginning of the string. To close the string, provide the same character followed by the single quote. The single and the provided character form the two character string enclosure.

 

If you decide to use braces, Oracle expects matching enclosures, i.e., Open brackets should be represented by closed brackets at the end of the string. Other characters can be represented as they are at both ends.

 

All we need to take care of is that the last two character delimiter does not show up in the string itself. For instance the below will throw error as the closing characters get repeated within the string.

 

SQL> select q'anitin's web blog. Ita's personal.a' str from dual;

ERROR:

ORA-01756: quoted string not properly terminated

 

The same can be used in PL/SQL also.

SQL> declare

2 l_str varchar2(100) := q'[nitin's web blog. Ita's personal.]';

3 begin

4 dbms_output.put_line(l_str);

5 end;

6 /

nitin's web blog. Ita's personal.

PL/SQL procedure successfully completed.

 

I recently came across a program that framed dynamic INSERT statement to copy data from one database to another. Unfortunately, one of the record columns had a single quote embedded in the string and this resulted in the program unit to fail in production. This happened in release 9i and the only alternative available was to remove/double the single quotes from the string literal. From release 10g, Q-quote could also be used to prevent this problem. I will simulate the same scenario and try this out.

 

SQL> create table am100(col1 number, col2 varchar2(100));

Table created.

SQL> create table am102(col1 number, col2 varchar2(100));

Table created.

SQL> insert into am100 values(1, q'[nitin's web blog. It's personal]');

1 row created.

SQL> insert into am100 values(2, q'[this is a simple string]');

1 row created.

SQL> insert into am100 values(3, q'[this is just another string]');

1 row created.

SQL> select * from am100;

COL1 COL2

---------- ----------------------------------

1 nitin's web blog. It's personal

2 this is a simple string

3 this is just another string

3 rows selected.

SQL> commit;

Commit complete.

 

Now I will call a simple routine that will read the data and generate dynamic SQL for inserting into another table.

 

declare

l_str varchar2(4000);

begin

for rec in (select col1, col2 from am100) loop

l_str := 'insert into am102(col1, col2) values (' || rec.col1 ||',''' ||

rec.col2 || ''')';

dbms_output.put_line(l_str);

execute immediate l_str;

end loop;

end;

/

 

The unit errors out as below, because of the single quote mismatch.

insert into am102(col1, col2) values (1,'nitin's web blog. It's personal');

begin

*

ERROR at line 1:

ORA-00917: missing comma

ORA-06512: at line 4

 

I will now modify the program unit to add Q-quote. We can also use the REPLACE function to remove or double-up single quote in strings, both options are given below.

 

[] With REPLACE function (doubling the single quote);

declare

l_str varchar2(4000);

begin

for rec in (select col1, replace(col2, '''', '''''') col2 from am100) loop

l_str := 'insert into am102(col1, col2) values ('

|| rec.col1 ||',''' || rec.col2 || ''')';

dbms_output.put_line(l_str);

execute immediate l_str;

end loop;

end;

/

 

Output generated:-

insert into am102(col1, col2) values (1,'nitin''s web blog. It''s personal')

insert into am102(col1, col2) values (2,'this is a simple string')

insert into am102(col1, col2) values (3,'this is just another string')

 

[] With Q-quote

declare

l_str varchar2(4000);

Begin

for rec in (select col1, 'q''[' || col2 || ']''' col2 from am100) loop

l_str := 'insert into am102(col1, col2) values ('

|| rec.col1 ||',' || rec.col2 || ')';

dbms_output.put_line( l_str );

execute immediate l_str;

end loop;

end;

/

 

Output generated:-

insert into am102(col1, col2) values (1,q'[nitin's web blog. It's personal]')

insert into am102(col1, col2) values (2,q'[this is a simple string]')

insert into am102(col1, col2) values (3,q'[this is just another string]')

 

Q-quote will be a handy option when we deal with huge text literals.