|
‘I object to
violence because when it appears to do good, the good is only
temporary; the evil it does is permanent.’ Gandhi |
The Oracle 10g database
more features that are important to note
Dynamic Performance Views
The
V$FAST_START_TRANSACTIONS view
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;
The V$FAST_START_SERVERS
view
Use the
V$FAST_START_SERVERS view to display historical information about
transaction recovery.
SQL> SELECT * FROM
v$fast_start_servers;
The DBA_ENABLED_TRACES view
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 do not 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
It is a method for simple
and complex patterns for searching and manipulating a text. You can
search, extract, format, and manipulate a text in the database. At the
beginning, it appears that the syntax is not very intuitive but by
second look, it may look easy. The technique more reflects as UNIX
style regular expressions.
Interfaces: Oracle
Regular Expressions are implemented by the following functions
available in SQL and PL/SQL.
· REGEXP_LIKE,
· REGEXP_REPLACE,
· REGEXP_INSTR,
and
· REGEXP_SUBSTR
Meta-characters:
The following is a list of supported Oracle Meta-characters use in
Oracle Regular Expressions.
|
Syntax |
Description |
Classification |
|
. |
Match any character |
Dot |
|
a? |
Match ‘a’ zero or
one time |
Quantifier |
|
a* |
Match ‘a’ zero or
more time |
Quantifier |
|
a+ |
Match ‘a’ one or
more time |
Quantifier |
|
a|b |
Match either ‘a’
or ‘b’ |
Alternation |
|
a{m} |
Match ‘a’ exactly
m times |
Quantifier |
|
a{m,} |
Match ‘a’ at
least m times |
Quantifier |
|
a{m,n} |
Match ‘a’ between
m and n times |
Quantifier |
|
[abc] |
Match either ‘a’
or ‘b’ or ‘c’ |
Bracket Expression |
|
(…) |
Group an expression |
Sub-expression |
|
|
Match nth
sub-expression |
Back-reference |
|
[:cc:] |
Match character class
in bracket expression |
Character Class |
|
[.ce.] |
Match collation
element in bracket expression |
Collation Element |
|
[=ec=] |
Match equivalence
class in bracket expression |
Equivalence Class |
NOTE:
It is important to realize
that the results of a particular regular expression query could be
different under a different locale such as changing NLS_SORT, Case and
Accent Sensitivity, etc.
Character Classes:
They are sensitive to the underlying character set such as the
[:lower:] character class.
The following is a list of
Oracle supports character classes, based on character class
definitions in NLS classification data:
|
Character Class
Syntax |
Meaning |
|
[:alnum:] |
All alphanumeric
characters |
|
[:alpha:] |
All alphabetic
characters |
|
[:blank:] |
All blank space
characters. |
|
[:cntrl:] |
All control
characters (nonprinting) |
|
[:digit:] |
All numeric digits |
|
[:graph:] |
All [:punct:],
[:upper:], [:lower:], and [:digit:] characters. |
|
[:lower:] |
All lowercase
alphabetic characters |
|
[:print:] |
All printable
characters |
|
[:punct:] |
All punctuation
characters |
|
[:space:] |
All space characters
(nonprinting) |
|
[:upper:] |
All uppercase
alphabetic characters |
|
[:xdigit:] |
All valid hexadecimal
characters |
Example: Consider a
simple query to convert the ‘McLean’ city name to a more readable
format. You should look for any instance for a lower case letter
immediately followed by an upper case letter. Your query should record
these two letters in back-references by using sub-expressions, then
replaces the first one, followed by a space, then followed by the
second letter:
SQL> SELECT
REGEXP_REPLACE(‘McLean’,
‘([[:lower:]])([[:upper:]])’,
‘1 2’) as “City”
FROM dual;
Equivalence Class:
It allows searching for all characters that have a common base letter.
Example:
SQL> SELECT
REGEXP_SUBSTR
(‘iSelfSchooling NOT
ISelfSchooling’, ‘[[=i=]]SelfSchooling’) as name
FROM dual;
Datatypes’ functions
support
Keep this in your mind that
these functions support CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR, and
NCLOB datatypes.
The REGEXP_LIKE function
It returns a Boolean
indicating whether the pattern matched or not.
Example: Consider to
write an expression that could search for common inflections of the
verb ‘try’. The following regular expression will match try,
trying, tried, and tries.
SQL> SELECT
REGEXP_LIKE (‘We are
trying to make the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’)
as REGEXT_SAMPLE
FROM dual;
The REGEXP_SUBSTR function
It returns the actual data
that matches the specified pattern.
Example: Consider to
write an expression that could return the ‘trying’ specified
pattern.
SQL> SELECT
REGEXP_SUBSTR (‘We are
trying to make the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’)
as REGEXT_SAMPLE
FROM dual;
The REGEXP_INSTR function
It returns the character
position of either the beginning or end of the match.
Example: Consider to
write an expression that could return the position of ‘trying’
specified pattern.
SQL> SELECT
REGEXP_INSTR (‘We are
trying to make the subject easier.’,
‘tr(y(ing)? | (ied) | (ies))’)
as REGEXT_SAMPLE
FROM dual;
The REGEXP_REPLACE function
It looks for an occurrence
of a regular expression and replaces it with the contents of a
supplied text literal.
Example:
SQL> SELECT
REGEXP_REPLACE (‘We are
trying to make the subject easier.’,
‘.’, ‘ for you.’ )
as REGEXT_SAMPLE
FROM dual;
Or, query a list of all
employees’ name that hired between 1996 and 1999.
SQL> SELECT ename FROM
emp
WHERE REGEXP_REPLACE
(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.
Occurrence in the REGEXP
functions
All functions take an
occurrence that specifies you require the nth matching expression in
REGEXP_SUBSTR and REGEXP_INSTR, the default for which is 1.
Example: Consider
extracting the 3rd field being the Oracle system idenfication in a
column.
SQL> SELECT
REGEXP_SUBSTR(‘system/password@myhost:1521:mysid’,
‘[^:]+’, 1, 3) as “SID
name”
FROM dual;
You also can the REGEXP
function in the DDL statement. Consider to configure a column to allow
only alphabetical characters within a VARCHAR2 column.
SQL> CREATE TABLE mytest
(c1 VARCHAR2(20),
CHECK (REGEXP_LIKE(c1, ‘^[[:alpha:]]+$)));
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;
/
|
“Let me not be
understood as saying that there are no bad laws, nor that
grievances may not arise for the redress of which no legal
provisions have been made. I mean to say no such thing. But I do
mean to say that although bad laws, if they exist, should be
repealed as soon as possible, still, while they continue in
force, for the sake of example they should be religiously
observed.” Abraham Lincoln (1809 - 1865) |
Questions:
Questions on
The Oracle
10g database more features that are important to note
Q: How do you use the
V$FAST_START_TRANSACTIONS view to monitor (in real-time) normal
transaction rollback and transaction recovery by SMON?
Q: How do you use the
V$FAST_START_SERVERS view to display historical information about
transaction recovery?
Q: How do you use the
DBA_ENABLED_TRACES view to display enabled and disabled trace?
Q: What is case-insensitive
sorting in the Oracle 10g?
Q: What is Quote Operator
q?
Q: What does the UTL_MAIL
package?
|
