Pythian Group

Using OLE DB to get SQL Server to connect to Oracle servers can be done quite easily, but there are a few little tricks you should know to make it go smoothly. Once it’s working it seems to work quite well. I hope this blog post will save you a few headaches.

Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. Read the rest of this entry . . .

Recently, a customer request came in to limit the runtime of a query in a 10g database for a certain user. But since connection pooling was being used, the session itself should NOT be killed.

Now, why would somebody ask for that?

I think a reason to implement such a restriction is, for example, if a website application requests data from the database and the query takes too long, then the user sitting in front of the web browser cancels the request, and so it would be beneficial to also cancel the query and not have it still running in the background. There might be quite a few other reasons why such restrictions should be implemented.

Anyway, so what would be the best solution for this? The Resource Manager, a feature not too often used in Oracle.

Actually, “overlooked” might be the better word—overlooked when investigating performance problems. I imagine it must be a new DBA’s nightmare to be confronted with end user complaints of “bad performance”, only to find after a long search that the Resource Manager has restricted the CPU for a specific user.

But back to the topic. Resource Manager not only allows one to define how much CPU a certain user or group gets, it also lets one switch into lower priority groups and kill a query while leaving the session running. It is like running a SELECT statement in sqlplus, and pressing ctrl-c. The session is still alive, yet the query is canceled.

Sure, the Resource Manager is documented by Oracle, but when I started to test this feature, I came across a few very interesting things that are not as well documented, and that’s what prompted me to write this post:

Read the rest of this entry . . .

I have been using this SQL*Plus command for a while now:

SQL> show spparameter service_names

SID      NAME          TYPE        VALUE
-------- ------------- ----------- ---------
*        service_names string      REDX

And guess what ?

• The right syntax is actually show spparameters (with a “s” at the end of it).
• It’s an 11g New Feature!

It doesn’t work with 10g:

SQL> show spparameters service_names

SP2-0735: unknown SHOW option beginning "spparamete..."
SP2-0735: unknown SHOW option beginning "service_na..."

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! Now Oracle should add SHOW TABLES too.

Anyway, this is really 11g’s most useful feature so far, though.