Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Hands On Labs with Data Guard

I have been planning on publishing more Hands On Labs for Data Guard on Oracle’s Technology Network using the Amazon Elastic Cloud (EC2) as the lab systems. But while I am working on more of them I thought I should post a note about the “OpenWorld 2009 Active Data Guard Hands On Lab” that we have already published. Please head for for a set of exercises designed to introduce you to Active Data Guard in Oracle 11g Release 2 as well as some basic configuration and testing exercises.  On that page is the download link for the handbook where you will find instructions on how to get going with my Amazon Cloud Machine (AMI)  image and all the exercises.

I am hoping to do a new and improved version of this hands on lab at OpenWorld 2010 which will include Active Data Guard functionality that I just did not have time to get into last year’s lab.  That new Hands On Lab will be published on OTN after OpenWorld.

Keep an eye on this category for announcements on any other training.


Back Again

So if all goes well the blog should be back again, now powered by WordPress. The blogger posts have made it back, but apparently 2006-2008 never existed. Fortunately it’s probably only me that cares. Those who know me personally will know that I moved jobs, and therefore needed to understand what the “private blogging” position [...]

On "Is a computer science degree a good goal?"

Dan Fink's "Is a computer science degree a good goal?" has gotten my wheels going. I think it's important to note this:

Computer Science ≠ Information Technology

Not only are these two disciplines not equal, neither is a subset of the other.

One of my most memorable culture shocks coming out of school into the Oracle domain was how many people didn't understand the difference between computer science, which is a specialized branch of mathematics, and information technology, which is a specialized branch of business administration. They both deal with computers (the IT major more than the CS one, actually), so of course there's risk that people will miss the distinction.

Over dinner Friday night with some of my friends from Percona, we touched on one of the problems. It's difficult for a technical major in school to explain even to his family and friends back home what he's studying. I remember saying once during my senior year as a math major, "I haven't seen a number bigger than 1 since I was a sophomore." I heard a new one tonight: "I got to the level where the only numbers in my math books were the page numbers."

It's difficult for people who don't study computer science to understand who you are or how the min/max kd-trees and deterministic finite automata and predicate calculus and closures that you're studying are different from the COBOL and SQL and MTBFs and ITIL that the IT majors are studying. It's easy to see why laypeople don't understand how these sets of topics arrange into distinctly different categories. What continually surprises me is how often even IT specialists don't understand the distinction. I guess even the computer science graduates soften that distinction when they take jobs doing tasks (to make a living, of course) that will be automated within ten years by other computer scientist graduates.

I agree with Dan and the comments from Tim, Robyn, Noons, Gary, and David about where the IT career path is ultimately headed in the general case. What I don't believe is that the only career path for computer scientists and mathematicians is IT. It's certainly not the only career path for the ones who can actually create things.

I believe that college (by which I mean "University" in the European sense) is a place where the most valuable skill you learn is how to learn, and that, no matter what your major, as long as you work hard and apply yourself to overcoming the difficult challenges, there will be things in this world for you to do to earn your way.

I really hope that the net effect of a depressed, broken, and downward-trending IT industry is not that it further discourages kids from engaging in math and computer science studies in school. But I don't want for so many of our kids today who'll be our adults of tomorrow to become just compartmentalized, highly specialized robots with devastatingly good skills at things that nobody's really willing to pay good money for. I think that the successful human of the future will need to be able to invent, design, create, empathize, teach, see (really see), listen (not just hear), learn, adapt, and solve.

...Just exactly like the successful human of the past.

Inserts Experiencing an Increasing CPU Consumption

Last week I had to analyze a strange performance problem. Since the cause/solution was somehow surprising, at least for me, I thought to share it with you.
Let me start by quickly describing the setup and what was done to reproduce the problem:

Database version: Oracle Database 11g Enterprise Edition Release (64-bit)
Operating system: Solaris 10 (SPARC)
To [...]

Hotsos 2010 – About swag, the Oscars and other stuff

Its Sunday and its raining outside. The nice weather on Saturday (approx. sunny / 20 degrees Celsius) has gone. After a decent flight on Friday where I actually made it to switch in Houston from the international Continental flight, going through customs and pick the next one, a domestic Continental Express flight, within the boundaries of 1 and 1/2 hour. I was so fast that switching for one flight to the other, that apparently my luggage didn’t manage to travel with the last flight. So after I found out that my luggage was still somewhere in Houston, I got from Dallas Love Field to the Omni Hotel in Las Colinas, Irving, where the Hotsos conference will be held again. The whole area is a bit in shambles because they are rebuilding a lot of the environment. While getting to the Grapevine Mills mall yesterday, I noticed that they also a building a new Irving Convention Center along the highway. Another addition to the already crowded Dallas/Plano/Irving Metroplex.

After doing some shopping on Saturday (and found a great shop that printed text on t-shirts from scratch, behold some new XML swag 8-) ), probably the only day I get the chance, I just caught up with Mark Bobak afterward, while he was driving to get some dinner outside the hotel. We ended up in a Boston Pizza revenue enjoying a pizza and a burger. Talked about Mark’s, probably biggest Oracle XMLDB environment implementation worldwide, its status / challenges after I had been helping him for 3, 4 weeks doing the POC end August 2009 on an Oracle 11.2 environment. We also discussed speaking on Hotsos, if not only because it starts to sink in that I will be speaking on this performance driven symposium. It will be Mark’s second presentation and my first… As always I am open for suggestions from more seasoned colleagues like Mark, so we had a good laugh and shared some tips. I already had the pleasure of seeing Mark’s presentation during UKOUG 2009, so I hope he will forgive me that’s probably one of the presentations that I will have to skip on Tuesday.

Afterward we caught up with Mark Farnham and Doug Burns at the Omni Hotel premises. It didn’t take long before Doug and I called it a night. Went out for a last smoke before I got to my room, were I (can’t help myself) tried to solve a last Oracle XMLDB OTN Forum question. Its Sunday while I am writing this. I got brutal woken up by Gerwin Hendriksen at 8.30 AM. Doug called it during breakfast (Gerwin, Doug and me) me being a slacker; I just call it too early… If not only, besides trying to get into the rhythm of the Dallas timezone, also to see some of the Oscars which I now have the opportunity to see it “live” on the television instead of seeing a summery of it on a Dutch channel.

On a side-note, you probably know that Tanel’s presentation on Tuesday isn’t that interesting. I mailed him some months ago, if he wouldn’t be in for a single trip to the Bahamas or something for that particular Tuesday afternoon, but he replied that I shouldn’t be to worried, because his presentation on Tuesday didn’t have any demo’s and most people wouldn’t be that interested in that one therefore anyway. Some one or two people would probably go to my presentation instead, if not only due to the fact that he has 3 presentations in total plus a training day where people might go to / choose from. One of the advantages of having been invited as a member to the OAKTable network. You can always build upon a fellow member.

Back to working on my presentation, its raining outside anyway…


I think I have the room next to Doug’s… He really should get those toys in order (TV noise all over the place). The hotel walls aren’t that thick as they once were…

Who's using a database link?

Every once in awhile it is useful to find out which sessions are using a database link in an Oracle database. It's one of those things that you may not need very often, but when you do need it, it is usually rather important.

Unfortunately for those of us charged with the care and feeding of the Oracle RDBMS, this information is not terribly easy to track down.

Some years ago when I first had need to determine which sessions were at each end of database link, I found a script that was supplied courtesy of Mark Bobak. When asked, Mark said he got the script from Tom Kyte. Yong Huang includes this script on his website, and notes that Mark further attributed authorship in Metalink Forum thread 524821.994. Yong has informed me that this note is no longer available.  I have found the script in other locations as well, such Dan Morgan's website. So now you know the scripts provenance.

Here's the script, complete with comments.  Following the script is an example of usage.

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">-- who is querying via dblink?
-- Courtesy of Tom Kyte, via Mark Bobak
-- this script can be used at both ends of the database link
-- to match up which session on the remote database started
-- the local transaction
-- the GTXID will match for those sessions
-- just run the script on both databases

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx
and s2.sid = w.sid

Now let's take a look a the results of the script.

Logging on to DB1 as system, create a database link to db2 using the SCOTT account:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">create database link scott_link connect to scott identified by "tiger" using 'db2';

Make sure it works:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">system@db1 SQL> select sysdate from dual@scott_link;

03/05/2010 10:13:00

1 row selected.

Now logon to DB1 as sysdba and run who_dblink.sql:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">sys@db1 SQL> @who_dblink

--------------------- ----------------------------------- ---------- ---------- - ----------
oraserver.-21901 DB1.d6d6d69e.3.16.7190 500.15059 SYSTEM I SQL*Net me

1 row selected.

Now do the same on DB2:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">sys@db2 SQL> @who_dblink

--------------------- ----------------------------------- ---------- ---------- - ----------
ordevdb01.-21903 DB1.d6d6d69e.3.16.7190 138.28152 SCOTT I SQL*Net me

1 row selected.

How do you identify the session on the database where the database link connection was initiated?

Notice that the output from DB1 shows the PID in the ORIGIN column.  In this case it is 21901.

Running the following SQL on DB1 we can identify the session from which the SYSTEM user initiated the database link connection:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">select
b.sid SID,
b.serial# SERIAL#,
from v$session b,
v$process d,
v$sess_io e,
b.sid = e.sid
and b.paddr = d.addr
and b.username is not null
-- added 0.0000001 to the division above to
-- avoid divide by zero errors
-- this is to show all sessions, whether they
-- have done IO or not
--and (e.consistent_Gets + e.block_Gets) > 0
-- uncomment to see only your own session
--and userenv('SESSIONID') = b.audsid
order by

---------- ------ -------- -------------------- ------------------------
SYS 507 12708 oracle 22917

SYSTEM 500 15059 oracle 21901

2 rows selected.

The session created using the SCOTT account via the database link can also be seen using the same query on DB2 and looking for the session with a PID of 21903:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">USERNAME    SID SERIAL# OSUSER  PROCESS
---------- ---- ------- ------- ------------
SCOTT 138 28152 oracle 21903
SYS 147 33860 oracle 22991
146 40204 oracle 24096

3 rows selected.

who opened that DB Link

very nice analysis of tracking down who has a dblink open on another database

ORASRP: 10046 trace file formating

In case you have seen this free tool:

It's pretty cool. Takes 10046 trace files and analyzes and formats them.

stopping long running queries (UNIX vs Windows)

Here is an interesting discussion on stopping long running queries by Tanel Poder:

in summary, it's relatively easy to stop a long running query on UNIX but not on windows. I've known this for years, but not know exactly why except that windows somehow sets up the connection differently than UNIX. Why this hasn't been address in all these years I don't know. If you've ever tried a "cntrl-c" on windows sqlplus you know it doesn't do much except possibly kill your window but the query keeps running on the database. In the case where your client PC dies or the connection breaks, the query happily continues to run on the database. Sort of annoying. In this case I have to in general kill the Oracle session on the database with the kill session command or use "kill -9" on the UNIX shadow process for the session. If I want the session to keep running and just cancel the query I can use "kill -URG" on the shadow process on UNIX, but if my database is running on windows how could I do that?
Tanel's article points out that their might be solution using

You can set the consumer group for a session to CANCEL_SQL to cancel its current call:

session_id IN NUMBER,
session_serial IN NUMBER,
consumer_group IN VARCHAR2);

youtube test