|
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=oracle.trc
clientid=oracle
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=oracle.trc
clientid=oracle
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
oracle/learning@//host_name:port/service_name
The default port
number is 1521
SQL> CONNECT
oracle/learning@//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 ORACLE, and
insert a record with employee�s name lower case.
SQL> CONNECT
oracle/learning
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 => �[email protected]�,
RECIPIENTS => �[email protected]�,
SUBJECT => �Make orders�,
MESSAGE => :my_message);
END IF;
END;
/
|
