28 February 2007

Oracle Parallel Basics with Doug Burns

Doug Burns has a great video presentation up at the Pythian blog, in which he gives a great explanation of the basics of Oracle's parallel execution feature.

It's a great video and it got my head straight on a lot of things (and just created more work for objects that I need to clean up!). One caveat is that, for me anyway, the video seemed to progressively lag behind the audio as the presentation went on. Slightly annoying, but still worth the watch.

For Great Justice!

This brought a wry smile to my face. Although Ujang is royally screwed, Mladen still had the presence of mind to set him up the bomb.

I hope you know what I'm talking about.

26 February 2007

lnnvl() ftw

Courtesy of Jonathan Lewis, I present you with LNNVL(). For those of you who forget to consider the possibility of NULL values in your query predicates, this could be a godsend. Should clean up a lot of predicates that had to use OR cases just to separately check for NULLs.

As Jonathan describes it: "The function lnnvl(predicate) returns true if its parameter returns false or null."

Data and Datatypes

Somehow while reading through some Oracle blogs this weekend, I found myself re-reading a post from Tom Kyte last year about mis-used datatypes. Such things as people using string datatypes to hold date values, or people using VARCHAR2(4000) for every field in every table. Tom does a fantastic job (as always) explaining the many reasons this is just awful, including loss of data integrity and performance.

Always worth a (re)read.

23 February 2007

Stealing My Material: or How I Though of Blogging About v$sql_bind_capture before hali did

This past Wednesday in #oracle, I asked how one views what values are being used in a SQL statement with bind variables. hali led me to v$sql_bind_capture. We played with some joins to v$sql and v$session and all was well. I immediately recognized the blog-worthiness of the discussion and tagged the concept for later blogging.

Well hali beat me to it. From now on I'll just start blogging about things before I even have all of the information. For now you can read his post to see the usefulness of the v$ view. And leave comments about how he's a dirty Scandinavian thief!

19 February 2007

Role Showdown: CONNECT vs RESOURCE

Today in #oracle, a question on privileges led me to discuss the Oracle roles CONNECT and RESOURCE. These two roles are often misused, especially RESOURCE. Jon Emmons highlights this in an old blog post. Important to note is that the CONNECT role contained a lot of dangerous extras up until 10gR2. The user on IRC confirmed this on his 10gR1 install, being able to create tables with just the CONNECT role. I suggested that he revoke CONNECT and just grant CREATE SESSION, which ended up working great for him. When/if his organization upgrades to 10gR2, then he can grant CONNECT, which (as Emmons notes) only grants CREATE SESSION anyway.

So by default, an Oracle database user doesn't even have the privilege to log into the database. That is what CREATE SESSION does. And even then, the user can't create objects (tables, indexes, etc.) in its own schema unless those privileges (or the system-wide "ANY" privileges) are gratned, plus the user will need to have some quota in the tablespace that it hopes to use.

This way of doing things is definitely a good one, IMHO. If you have many users connecting to an OLTP application schema, they don't need to create/drop objects. Only grant what they need to perform their tasks. In a lot of applications, for example, this probably means not letting users delete rows from the application tables (using an "ACTIVE" flag instead).

So, to summarize, CONNECT and RESOURCE prior to 10gR2 are a mess. 10gR2 lets you just grant CONNECT to allow users to connect, and then grant RESOURCE if they need to create their own objects (still may need quota privileges).

15 February 2007

Back in the Saddle

After a recent Windows Update root certificate update hosed my VPN client, I decided to just go back to Linux. That warm fuzzy feeling has returned. I'm currently running ubuntu on my nc6320. No, I don't know if gdm supports the fingerprint scanner for login (a neat thing in XP, but really unnecessary).

Now I just need to get the Oracle EM db control installed ...

13 February 2007

What is so special about SYSDBA?

Last night in #oracle, someone new to Oracle had some confusion with the SYSDBA privilege. I proceeded to drop some science on him, but here's a basic primer:

  • Only a user with the SYSDBA privilege can startup or shutdown an instance.
  • The oracle OS user can "connect / as sysdba" without providing any further authentication. This is why you want to restrict access to that user as well. In fact, that user (and probably anyone in the dba OS group) can "connect sys/not_a_real_password as sysdba" and it will always succeed. Beware!
  • When you connect AS SYSDBA, you'll be logged in as SYS, regardless of what user you originally were connecting as.
Thanks to ADP for the resources. René has a nice resource site there and I reference it a lot, as well as his blog.

Osama Team Hunger Force


08 February 2007

Honorable Mention

I had to do a triple-take when I actually saw my name in print in the latest Pythian LogBuffer post. Then I had to re-read again to see if it was positive or negative. It was just for my post linking to the SQL implementations page, but still fun.

I was going to go with a Sally Field-esque acceptance speech, but I'll think it's better to go with the #wilug standby, "I'm kind of a big deal around here."

ASSM IS EVIL!!!!!!11

OK not really, but HJR wants you to take caution before assuming that ASSM is the second coming. You probably don't want to use it in your direct-path/append-insert bulk loaded warehouse environment.

I'm running a hybrid on 10gR2, and hope to fully use ASM when we migrate to the new 64-bit hardware (*drool*), so I'll have to do some testing on the bulk-loaded tables.

04 February 2007

K-Fed is my hero

Gotta give K-Fed some credit (yes I just said that) for this one.

01 February 2007

Comparison of different SQL implementations

Another gem found off of the oracle-l list is this page, which offers a nice breakdown of the differences between the various RDBMS implementations (Oracle, Postgres, MySQL, etc.) in terms of their SQL implementations.

Buy a disclaimer now!

That's right folks, I'm capitalizing on TERROR! The government isn't too sure what is a bomb or not these days, so in order to make things easier, I have a shirt (probably more later) and a sticker that you can use to make your non-bombs more obvious.

Update: now your dog is safe. Thank me later.