Using Regular Expressions in 10g

Oracle Database 10g introduces support for Regular Expressions, which is a method of describing both simple and complex patterns for searching and manipulating. You can use several predefined meta character symbols in the pattern matching.

Meta Characters:

* matches zero or more occurrences

+ matches one or more occurrence

? matches zero or one occurrence

. matches any character in the supported character set, except NULL

| Alternation operator for specifying alternative matches (a|b matches a or b)

^/$ Matches start of line/end of line

[] Bracket expression for a matching list matching any one of the expressions in the list

[...] match any character in the list

[^...] match any character not in the list

{m} matches exactly m occurrences

{m,} matches atleast m occurrences

{m,n} matches atleast m times but no more than n times

(...) Subexpression, treat expression as a unit

SQL> select * from emp where regexp_like(ename, '^A+');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- -------------------------------
7499 ALLEN SALESMAN 7698 20-FEB-81 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM



SQL> select * from emp where regexp_like(ename, 'A+');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------------------------------
7499 ALLEN SALESMAN 7698 20-FEB-81 12.00.00.0000000 AM
7521 WARD SALESMAN 7698 22-FEB-81 12.00.00.0000000 AM
7654 MARTIN SALESMAN 7698 28-SEP-81 12.00.00.0000000 AM
7698 BLAKE MANAGER 7839 01-MAY-81 12.00.00.0000000 AM
7782 CLARK MANAGER 7839 09-JUN-81 12.00.00.0000000 AM
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM
7900 JAMES CLERK 7698 03-DEC-81 12.00.00.0000000 AM


SQL> select * from emp where regexp_like(ename, 'AMS$');

EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ------------------------------
7876 ADAMS CLERK 7788 23-MAY-87 12.00.00.0000000 AM

Using ipcs to peek into kernel's storage mechanisms for IPC objects

The ipcs [-q] [-s] [-m] [-l] command gives you the kernel's storage mechanisms for IPC objects.
ipcs -q: Show only message queues
ipcs -s: Show only semaphores
ipcs -m: Show only shared memory
ipcs -l: Shows message queues, semaphores, and also shared memory

Commands for CPU/Memory/Process information on Linux/Unix servers

top displays all sorts of CPU/Memory/Process information
free -m will show you stats about RAM usage in MB
uptime will show you the load average for the past 1min, 5mins and 15mins

cat /proc/cpuinfo [OR] cat /proc/cpuinfo | more will give you general information about the CPU(s) and memory on server
vmstat will give you virtual memory statistics and vmstat n will give you a run down of memory use, disk operations and cpu usage every n seconds
i.e. vmstat 1 will get a run down of memory use, disk operations and cpu usage every 1 sec

Using WITH clause

WITH clause can be used only with the SELECT clause and can hold more than one query. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.

Using WITH clause has following advantages:

• It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• It can improve performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace

Example:
SQL> with emp_sal as(select sno, sum(salary) as tot_sal from sample group by sno),
avg_sal as (select sum(tot_sal)/count(*) as tot_avg from emp_sal)
select * from emp_sal where tot_sal > (select tot_avg from avg_sal)
order by sno;

SNO TOT_SAL
---------- ----------
1 23000
2 10000
4 10000

WHERE Vs HAVING clause in SQL statement

Listed below are some reasons on when to use WHERE or HAVING clause in SQL SELECT statement.

• WHERE clause is used to exclude rows before the grouping of data
• Aggregate functions cannot be used in the WHERE clause
• HAVING clause is used to exclude one or more aggregated results after grouping data
• HAVING clause conditions can have aggregate functions
• WHERE and HAVING clauses can be used together in a SQL statement

Setting up Cygwin X Server on client

Once you have installed Cygwin, you should be able to set up X Server for display purposes. Follow the steps below for setting it up.

1) Edit the batch file 'cygwin.bat' (Will be in the installed folder)

You will see the below line in it
bash --login -i

Add the following statement next to it (Note that the startxwin.sh should be there in C:\Cygwin\usr\X11R6\bin )

bash --login -i /usr/X11R6/bin/startxwin.sh

2) Once you are done editing, run the batch file as administrator:
You should be able to see the X windows popping up:

3) Once you see X window, issue xhost + command so that clients can access from any host. See the below image.

Issue xcalc or xclock command to test if you were able to see the GUI.


NOTE: If you get Reason: spawn:fork() failed. Then close the window, and run the batch file again. I often get this reason, and I simply run it twice or thrice to run successfully, doono why.

3) If you are using putty on the client, export DISPLAY environment variable in that session:
export DISPLAY = 192.168.2.1:0.0 (marked in red is IP address of client)

Problem: If you don't set up DISPLAY.
If you don't set up your DISPLAY variable, you will see the following error:
[oracle@vinay 10201]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-02-28_12-04-45AM. Please wait ...[oracle@vinay 10201]$ X connection to localhost:11.0 broken (explicit kill or server shutdown).

Solution: Set the DISPLAY variable
[oracle@vinay 10201]$ echo $DISPLAY
localhost:11.0
[oracle@vinay 10201]$ export DISPLAY=192.168.216.1:0.0
[oracle@vinay 10201]$ echo $DISPLAY
192.168.216.1:0.0
[oracle@vinay 10201]$ ls
doc install response runInstaller stage welcome.html
[oracle@vinay 10201]$ ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-02-28_12-05-19AM. Please wait ...[oracle@vinay 10201]$