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;
/
|