iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

More features that are important to note in the Oracle 10g database

 

‘I object to violence because when it appears to do good, the good is only temporary; the evil it does is permanent.’

Gandhi

 

More features that are important to note in the Oracle 10g database

 

Dynamic Performance Views:

Use the V$FAST_START_TRANSACTIONS view to monitor (in real-time) normal transaction rollback and transaction recovery by SMON.

SQL> SELECT * FROM v$fast_start_ transactions;

 

Use the V$FAST_START_SERVERS view to display historical information about transaction recovery.

SQL> SELECT * FROM v$fast_start_servers;

 

Use the DBA_ENABLED_TRACES view to display enabled and disabled trace.

SQL> SELECT * FROM dba_enabled_traces;

 

Session-Based Tracing:

Now, you can consolidate the output tracing to a single output using the TRCSESS command line and then use the TKPROF tool to read it.

$ trcsess output=iself.trc clientid=iself

 

The DBMS_MONITOR statistics package

In the Oracle 10g database, you can control additional tracing and statistics gathering based on client identifier:

SQL> BEGIN

            dbms_monitor.client_id_stat_enable (your_client_id);

           END;

            /

$ trcsess output=iself.trc clientid=iself

 

Client Connection

If your platform is using native TCP/IP, then you don’t need to have tnsnames.ora or sqlnet.ora configured in the client machine.

SQL> CONNECT iself/schooling@//host_name:port/service_name

The default port number is 1521

SQL> CONNECT iself/schooling@//myserver/school    -- Assuming that myserver is the name of your database server and school is your service name.

 

Flushing the BUFFER CACHE memory

You should never do this unless you know the ramifications. It is useful on the test environment to determine the effects of changes in queries or application.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

 

Regular Expression Support

It is a method for simple and complex patterns for searching and manipulating. Its functions are:

REGEXP_LIKE,

REGEXP_REPLACE,

REGEXP_INSTR, and

REGEXP_SUBSTR

Example:

Query a list of all employees’ name that hired between 1996 and 1999.

SQL> SELECT ename FROM emp

            WHERE REGEXP(TO_CHAR(hire_date, ‘YYYY’), ‘^199[6-9]$’);

You used ‘^’ to indicate that the beginning of the line has to be 199, and [-] with $ to specify range of valid characters.

 

Case-insensitive sorting

In the Oracle 10g database, you can use the ALTER SESSION command to change NLS_SORT for your session to use case-insensitive binary sorting.

 

Connect as ISELF, and insert a record with employee’s name lower case.

SQL> CONNECT iself/schooling

SQL> INSERT INTO emp

            VALUES (9991, ‘scott’,’CLERK’, 7698,

            TO_DATE(’04-DEC-87’), 765.5, null, 30);

SQL> COMMIT;

 

Query the employees’ name.

SQL> SELECT ename FROM emp;

 

Now, change the NLS_SORT parameter to case-insensitive binary.

SQL> ALTER SESSION SET NLS_SORT = binary_ci;

 

Query the employees’ name again.

SQL> SELECT ename FROM emp;

Check the scott employee’s name before and after the ALTER SESSION command.

 

Quote Operator q

Now, you can eliminate previous additional quotation string. See the following example:

SQL> UPDATE customers

            SET comments =

            q’X In this example, ‘X’ is used as the quotation mark delimiter. X’;

            WHERE ID = 100;

SQL> COMMIT;

 

The UTL_MAIL package

You can send e-mail to a user. In order to use the package, you should have already run the utlmail.sql and prvtmail.plb scripts located in the $ORACLE_HOME/rdbms/admin directory.

 

Connect as sysdba and run the above scripts.

SQL> CONNECT / AS SYSDBA

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

 

Write a PL/SQL block to test a value of a binding variable. If it is less than 10, then email a message that we are going to have a shortage of books.

SQL> VARIABLE number_of_items NUMBER

SQL> VARIABLE my_message VARCHAR2

SQL> BEGIN

            :number_of_items := 8;

            :my_message := ‘There is a shortage of books. They are: ‘

                         || :number_of_items );

            IF :number_of_items < 10 THEN

                        -- email the responsible person

                        utl_mail.send (

                        SENDER       => ‘help@iselfschooling.com’,

                        RECIPIENTS            => ‘help@iselfschooling.com’,

                        SUBJECT    => ‘Make orders’,

                        MESSAGE    => :my_message);

            END IF;

          END;

          /

 

 

Google
 
Web web site