Posts by Grégory Guillou
I’ve recently moved to Ubuntu Linux, and this post describes my attempt to play around with Oracle ASM on Ubuntu. For this demonstration, I used Oracle 184.108.40.206 on Gutsy Gibbon.What I really like about Oracle is that once you’ve done something, you have ten different ways to go. I hope this post will enable you to explore some 11g’s new features on your favorite operating system.
I have been using this (see post) SQL*Plus command for a while now, and guess what? It doesn’t work with 10g.I thought MySQL was trying to become as big as Oracle but it could be that Oracle is trying to become as cool as MySQL — it has had something like this for a long time!
At the risk of making it seem like this is all we’re talking about here at Pythian, here we go again. Paul Vallee pointed me towards this article by John Dvorak that more or less echoes a blog post I wrote in French the day previous for my personal blog that you can read here. So, first things first, Dvorak’s article leads me to think my theory is indeed wrong: if Dvorak says I’m right ; I’m afraid I’m wrong.
I’ve been told that using NOT EXISTS in (Oracle) SQL is a bad idea, and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN. So I decided to check if it is true.
Oracle Grid Control documentation warns against leaving the emkey in the Grid Control repository, if it is not removed after it has been copied it is easy to decrypt data, like passwords. Oracle Management Service 10.2 uses several ways to protect these sensitive data, including Virtual Private Database and Password Encryption. To overcome the first one, you have to connect to the database as the SYS user, for the second one, you have to know the encrypted password form, the key, and the associated algorithm. Obviously, the key used to cipher the password is the emkey. So the next question is, “Where are stored the ciphered passwords?”.
I’m a Linux fan, and when it comes to specific problems, I’m afraid not all operating systems are equally armed. Enabling a specific user to listen on a port below 1024 is one of these problems that was solved for years with various approaches. So you may think, obviously you can access the GridControl 10.2 agent on Linux with HTTPS only, on port 443! And obviously you can — but…
If you are or have ever been a SQL developer, it’s very likely you’ve been asked to return the rows from two joined tables, including all the rows from both tables that do not have a corresponding row in the other table. Oracle 9i introduced the FULL OUTER JOIN syntax to better address this scenario. Now it looks as if 11g has introduced a new algorithm to handle that. So how can you get a look at this? Find out here.
It seems Oracle 11g introduces a difference between count(*) and count(1). The way this happens is just the opposite of what I was thinking would happen. NB: I ran my test using “220.127.116.11 32bits” on Ubuntu Linux 7.04 (Feisty) which is not officially supported1, and which has already lead me to some unexpected behaviors. If this difference with count() is really the 11g way and not buggy behavior related to the Ubuntu install, I’m glad to have found it. Here’s what you can do to observe (or confirm or dispute) this.
Today, I installed RAC on Oracle Enterprise Linux 5 (OEL5), and I can tell you that there’s nothing exceptional about the process. The only trouble I encountered had nothing directly to do with the installation. Besides that, it’s all quite simple once 10g’s prerequisites are met. After an hour and a half, it was all wrapped up, at least for two nodes. I didn’t see anything revolutionary differences, but nonetheless there are some points worth mentioning.
So what is SQL Performance Analyzer (SQLPA)? The DBMS_SQLPA package enables you to register and compare the statistics of several SQL query executions stored in an SQL Tuning Set (STS). With SQL Performance Analyzer, you can compare the executions of queries before and after you make some changes to your database. As you might guess, I’m going to illustrate this new feature in Oracle 11g with a simple example.