Oracle database links using full TNS entries

Database links can be most useful, say you want to extract data to a reporting system or something like that. This is a quite common scenario people have problems with and come to the Oracle support IRC channel and ask about, especially developer with just a user account on the remote database and plain user account in the target database. They want to create a database link but don't have access to tnsnames.ora file at the OS level on the server, they may not even have SSH access to the server.
No problem, you can use the full TNS entry pretty much everywhere you can use the TNS alias, just remember to single quote it.

Database link example:

[hlinden@whale dblink]$ rsqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 29 11:17:26 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select db_link from user_db_links;

no rows selected

SQL> CREATE DATABASE LINK prodsyslink
 CONNECT TO ro_user IDENTIFIED BY ro_user_pass USING
 '(DESCRIPTION=(ADDRESS_LIST=(
   ADDRESS=(PROTOCOL=TCP)(HOST=prodserver)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=prod_db)))'
/

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
PRODSYSLINK

SQL> select * from t@prodsyslink;

         A          B
---------- ----------
         1          2
         2          1

SQL>

Cool, now it's possible to create a nice materialized view or whatever is needed.

Oracle docs about CREATE DATABASE LINK.

Redmond technology

Disclimer: Long pro-Microsoft ranting. I know most of my blog readers are hard core Unix geeks so please be warned if you are sensitive to Microsoft material.

Ok, I have to admit it, I'm posting this entry from a machine running Windows Vista.
And on top of that, I have to admit that Microsoft SQL Sever 2005 seems pretty cool. SQL server is not comparable to Oracle by far but it's certainly improved over the 2000 release. I've even fiddled around a bit with C# and OLDEDB providers in Vista. It's pretty sweet, using something like the Vista search API is peanuts, it's easy and clean, all the basics is there and easy accessible.
After using Vista for about a week (No, I did not download the torrent, I'm using a developer licensed copy from MSDN) I quite like most aspects of it, the new aero interface is quite fast, it's "cute" without getting annoying and slow. My favourite feature is the basic search functionality which works pretty much everywhere, you can sort of filter the result set in all views. If you type "Comm" in the Start-menu Windows filters the selection to the Command Prompt, so instead of classic browsing you can just type the first few letters of something and just hit enter, sort of like we have done in Unix for the last 15 years ;). Anyway, without a doubt, I feel more productive using Windows Vista than I have felt in a long time.


I've been quite distanced from the Redmond scene for a while, the "2003 kit" did nothing for me, but as a technologist I have to admit that there are one or two quite interesting things coming from the Redmond campus in the next six months (and I'm not talking about the Zune here.

Another thing that comes to mind here is that Microsoft is in a way shifting away from it's core customer base and trying to gain the interest of Unix shops and "the bigiron people".
Take a product like Exchange 2007, it's a pretty solid mail server (if you pair it with a couple of Postfix releays), but deploying a quite basic Exchange installation is getting pretty tricky, I have to admit I've only seen the beta2 release so far so the final version is probably better, compare that to the super simple and straight forward Exchange 5.4 deployment. Sure, you had a few gotchas in 5.5 and you always swore about all it's shortcomings, but the installation was done in 45 minutes or so. A company with say 100 employees and perhaps 3-4 servers can't just have "the IT guy" any more, not unless he is a pretty good and dedicated IT guy. Microsoft product deployments almost require consultants or at least a decent support channel. Things you previously associated more with larger and more back office deployments like an Oracle database, SAP or IFS.
They are adding all this great functionally, but are they abandoning the ease of use and simplicity of their server products?
Myself I don't mind the added complexity as long as the new features can help me get the most out of my deployment, something that Oracle has done an excellent job of. While it is a very complicated product, it is also the most powerful database on the market. Can we have it both ways? Probably not.

So what's cool?

• Vista in itself isn't that cool, but I think what people will do with it will be pretty cool. Both developers and end-users will benefit. I think we'll see the same thing we saw when the MacOS X revolution started. Innovation from all corners of the market.
  
• SQL Server 2005 - pretty cool. But not great, it'll kick some more Sybase butt and perhaps excite a few more developers to give up their Access MDB databases.
  
• .NET framework 3.0 - same as the two above. Managed code is here to stay, .NET, Java and python. Now when you can have managed code in unmanaged code (confusing yes) I think we'll see a more rapid adaptation.
  

Enough of this silliness, I need to sort myself out, cat some files, pipe some stuff.
Seriously, I'll stick to my motto, use whatever tools are best for the job and don't be afraid to try new products. Homogeneous environments and all that is nice, but when it all comes down to providing the service your clients require you have to look at all the options. Web 2.0 is all about providing a better service than the competition. If that's Microsoft or OpenSolaris, so be it.

Classic screenshot:

More cores, less space

Dell just started shipping the "mark ii" 9-series boxes capable of taking the new quad-core Intel Xeon processor.
Unfortunately the older 9th generation are not compatible with the new quad core processors. Intel made some last minute design changes to the new chip that required Dell to revise their motherboard designs.
Look out for a "II" symbol on the front of your 9th gen boxes.
Quad-core will be available on the SC1430, 1900, 2900, 1950, 2950 and the 1955 blades.
Performance is said to be up to 60% better than a dual core system, which sort of puts 2-socket machines in "enterprise territory" for the first time. Sure, we got the 8-core Sun T1, but that is a quite niche market machine.
A 8-core Dell PowerEdge 2950 with dual quad core procesors, 8Gb RAM and a pair of 72Gb SAS disks costs just under £6000 ($10kUSD).

I'd love to do some JBoss benchmark on a mk2 1950.

Oracle merge-statement

The merge statement has been in Oracle since version 9, but is still not very widely used.
When looking at data loading and merging code I think the most common bit of PL/SQL I've seen is the "merge" statement written in pl with slow and boring if-statements to see check if an entity exists in a table and if it exists the pl-code will do an update, otherwise it will do an insert.
Two words, super slow.

A much better solution is to use the merge-statement which does exactly that, it's basically
"if exists then update else insert". Merge data from one data entity to another.
The basic concept is sort of a join between the loading entity and the destination table.
You can load from a normal table or from select query.

Example:

SQL> select * from emp_load;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30

SQL> MERGE INTO emp e USING emp_load l
  ON (e.empno=l.empno)
  WHEN MATCHED THEN
    UPDATE SET
      e.ename = l.ename,
      e.job = l.job,
      e.mgr = l.mgr,
      e.hiredate = l.hiredate,
      e.sal = l.sal,
      e.comm = l.comm,
      e.deptno = l.deptno
  WHEN NOT MATCHED THEN
    INSERT VALUES (
      l.empno, l.ename, l.job,
      l.mgr, l.hiredate,
      l.sal, l.comm, l.deptno
    )
/

5 rows merged.

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30
 7369 SMITH      CLERK      7902 17-DEC-80   800                  20
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600      300         30
 7521 WARD       SALESMAN   7698 22-FEB-81  1250      500         30
 7566 JONES      MANAGER    7839 02-APR-81  2975                  20
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850                  30
 7782 CLARK      MANAGER    7839 09-JUN-81  2450                  10
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7839 KING       PRESIDENT       17-NOV-81  5000                  10
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500        0         30
 7876 ADAMS      CLERK      7788 23-MAY-87  1100                  20
 7900 JAMES      CLERK      7698 03-DEC-81   950                  30
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7934 MILLER     CLERK      7782 23-JAN-82  1300                  10

16 rows selected.

If you want to load from a query you can use this syntax:

MERGE INTO emp e USING 
    (select * from emp_load 
      where hiredate > sysdate - 7) l
  ON (e.empno=l.empno)
...

Check out the merge docs .

The Week of Oracle Database Bugs

In an effort to try to get Oracle to improve their response time and responsiveness to fixing critical security flaws in the Oracle database the Argeniss security firm has started the "The Week of Oracle Database Bugs".
In all honesty, Oracle are really bad at fixing critical bugs. Not that many databases are Internet facing, but the concept of social hacking and internal hacking is not unheard of.

I hope we see a positive response from Oracle.

Random bits

I must admit that I haven't been very active on the blogging side lately. Been quite busy at work and a fair amount of my free time has been consumed by excessive Battestar Galactica viewing. :)
Work has been quite busy lately, not sure what I've actually done, lots of small things add up I guess. We recently signed a few new customers and need to configure staging environments and of course they have to use the most boring stuff around, Sybase and boring Webfear.
Why can't people just face the facts and use JBoss and Oracle?

On the plus side I've had to get a closer look at the FSC PrimePower Solaris boxes. The SPARC64 processor isn't that bad actually, it's pretty fast. I'd love to get some "end customer" pricing for their systems (noone pays list).

Productivity wise I'm writing a small system to find new physical devices on a LAN, it simply polls the MAC address table from a switch and stores the entries in a PostgreSQL database and when a new MAC is found it sends an e-mail alert to the administrator. That bit is currently working and I'm just writing a small PHP-interface to add comments to MACs so they can be easily identified. I'll post the code when it's done if anyone is interested.

Album recommendation:
And the artist of the "random time unit" is Blondie!
The album Autoamerican must without a doubt be one of the most creative and uprising albums of all times. Rap, jazz, reggae and disco on the same album is not something we see every day.

Oracle Alert log shell alias

Inspired by Laurent Schneiders recent shell alias blog post I decided to post a nice little shell alias I use.
The alias opens the current alert log file in vi, it reads the background dump dest from sqlplus and constructs the full path to the file name. Works pretty well, it's quite convenient to have one single command to quickly open the alert log, it requires that your oraenv (ORACLE_HOME, PATH and ORACLE_SID) is set correctly.

The alias:
alias alert='vi + $(printf "set heading off pages 0 feedback off\n select value from v\$parameter where name='\''background_dump_dest'\'';\n"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log'

If you just want the last 20 lines of the alert log you can use this alias:
alias talert='tail -20 $(printf "set heading off pages 0 feedback off\n select value from v\$parameter where name='\''background_dump_dest'\'';\n"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log'

Oracle controlfile backup script

Wrote a small script to get a "usable" copy of the "CREATE CONTROLFILE" statement one can backup to trace. In a heavily used database it can be quite tricky to find the control file backup without actually "looking for it", in the sense of actually query the database for your thread number.
The script will dump the controlfile to trace and copy it to the local directory with the file name control_INSTANCENAME_TIMESTAMP.sql, should be fairly simple to integrate the script in to a generic backup process.

#!/bin/bash
ORACLE_HOME=/u01/oracle/10g
ORACLE_SID=peggy
PATH="$PATH:$ORACLE_HOME/bin"
export PATH ORACLE_HOME ORACLE_SID

sqlplus -S / as sysdba <<EOF  >/dev/null
set echo off feedback off verify off pages 0
column TRCLOC new_value TRCFILE
column TRCDATE new_value FILEDATE
column INSTNAME new_value INSTANCE
select to_char(sysdate, 'YYYYMMDD_HH24MISS') TRCDATE
  from dual;
select instance INSTNAME
  from v\$thread;
alter database backup controlfile to trace;
select
        c.value || '/' ||
        instance || '_ora_' ||
        ltrim(to_char(a.spid,'fm99999')) ||
        '.trc' TRCLOC
from
        v\$process a, v\$session b,
        v\$parameter c, v\$thread c
where
        a.addr = b.paddr
        and b.audsid = userenv('sessionid')
        and c.name = 'user_dump_dest'
/
host cp &TRCFILE control_&instance._&filedate..sql
exit
EOF

Example usage:

oracle@htpc:bin$ ls
backup_controlfile.sh
oracle@htpc:bin$ ./backup_controlfile.sh
oracle@htpc:bin$ ls -1
backup_controlfile.sh
control_peggy_20061105_225110.sql
oracle@htpc:bin$ grep -m1 "CREATE CONTROLFILE" control*
CREATE CONTROLFILE REUSE DATABASE "PEGGY" NORESETLOGS  ARCHIVELOG
oracle@htpc:bin$

Found the trace location query over at Asktom.

Quick update:
The blogger editor buggered up the script the first time around, had to use ampersand encoding to get the << and > to work properly.