Hello everyone

This is another blog on Oracle. For some time I wanted an easy way of sharing my knowledge, experience and findings with everyone interested. I write articles sometimes (in Dutch Oracle magazines), but those have a somewhat large interval. A blog enables me to post potentially interesting things immediately and get feedback as well. I can be right, I can be wrong and in either case I would love to know. I hope some or many people will enjoy it!

Same mistake, three different errors…

Funny thing I had today. Well funny, not when you want to make progress in your work, but still interesting what I discovered when I tried to isolate the problem…I found a way to make the same mistake and have Oracle return three different errors, dependent on the order of columns in an Insert statement. I cannot explain why, so any feedback I am very willing to receive.

One way to find that ORA-942 causing missing grant

Hi all,

It has been too long since the last post. It is not that I do not want to, it is just way too busy…

I just came across a small problem where a .NET application written by someone else caused an ugly Stack trace with a ORA-942 (Table or View does not exist) after being moved to the next environment. The error was not there on development, so they asked me what the problem could be. At first I did my compare of grants to the Role I am supposed to use for the application. The problem is that on development the generic application user has direct grants and it is one of these direct grants that cause the problem. Off course I could take away those grants from the generic user, but that would only make the development environment crash, too. I need to find that grant given to the user which is not in the role (there were many, but the application works fine except for one action, so I do not want to provide all those direct grants to the role, I just want to grant that missing grant) and later on see what happens with the application if I remove all the direct grants from the user and only have the role left.

Installation of Oracle 11 Release 2 on Windows 7 64-bit

I never had so much trouble installing an Oracle version on a Windows OS as I had with installation of Oracle 11R2 on Windows 7 (64-bit). The issues I faced were twofold:

1. First I got messages (during installation of the software) that files could not be found in the target oracle folder
2. Second, after finding the solution for the first annoyance, I spent two hours finding out why everything installed correctly, I could create a database, but could not start a Listener. I am an Administrator on my laptop. The errors I got at starting a listener were ORA-12560 and ORA-12541.

How to deal with DBMS_RLS

These last weeks I am trying to get the VPD option (Virtual Private Database) enabled for my application. This time as a developer with no DBA or SYS privileges. I am used to being a DBA for the databases I work on and am used to being able to get the fullest out of the Oracle database. For one, because I think Oracle provides us with a great deal of built-in features that do things more efficiently than we can do ourselves (more efficient, built-in kernel code) and also especially because it seems useless and time-consuming to write code that is already supplied by built-in packages. Now these provided features may not all be as intuitive as we want to or lack some important features, but that is where we can spend some time extending them or making them more user-friendly by creating “wrapper” packages: packages you write that hide the complexity or unfriendliness of a built-in package or limit the features you can use. Because there are quite some packages out there that contain functions or procedures or even parameters that you do not want to give to your developers or end users.

Catching up…presenting and taking part in Challenges

I regret not being able to write new things more often, but in fact it only means I am OK: very busy with work, doing private stuff (which is important since life is not all about working), enjoying the three young kids and trying to achieve some good results in track and field. And for that last goal, I can say I did. After a few months of struggling to find a tiny piece of shape comparable to last years shape, just in time I found it. I won the gold medal in the Dutch Championships 400 metres hurdles. OK, be honest, for the ‘older’ men (40+). Still, my time was certainly not bad and about 2 seconds ahead of all other competitors of 35 years and older. And it brought me back in the top 30 of all Dutch 400 metres hurdles runners (Juniors and Seniors) in 2010. Nice. Just as nice was that I met Alex Nuijten (Amis) who volunteered there and even wrote about it on this nice blog. Thanks Alex.

One simple web page saved my day

As an Oracle specialist you meet it from time to time: something is not working as expected. Lots of times the information is right under your hands on the web. We open Google and search for some error message we get. If I cannot find it there I open Oracle support (for some time already the flashy new site, I am getting used to it after a few weeks of hesitation and missing MetaLink). Probably it should be the other way around: Oracle problems should be explained best on the Oracle support site. And they probably are, but they are a logon away and a slow startup of the homepage, which makes a Google search a little quicker. And with all the blogs people keep up and other informational sites, many times a Google search is enough. Today I had a problem for which I could not find anything on the web as well as on the Oracle support site.

Oracle Import issues: Resumable and buffer

Yesterday and today I noticed two things with Oracle import (in Oracle 10.2.0.4) I did not know:

1. An issue with the buffer parameter in Import
2. An issue with Resumable=Y and FROMUSER/TOUSER where the FROMUSER and TOUSER have different default tablespace and quotas

DBMS_REDEFINITION, clustering and how an outline helps to make it completely ONLINE

At last a new post from my hand after months of silence. Well, silence in writing then: in the mean time we got our third child and I participated in the World Championships Double Decathlon in Delft. Those two things were enough for me to handle next to regular work 😉

In this post I want to share good experience I had in the use of DBMS_REDEFINITION to speed up the Siebel OLTP application I am responsible for (at least, for the performance part of it). It is about clustering of data and rebuilding tables as ONLINE as can be and how I used a trick with a stored outline to overcome a performance problem during rebuild of one table.

How the optimizer may use an index for cardinality estimates without actually using it

Recently I was sent a data warehouse SQL query that performed badly. It took more than an hour and timed out. It was the first time that query was run with a particular parameter. Here I will show that to solve this, I created an index to give the optimizer better information, even though it did not actually use it to execute the query.

The trains are a nightmare, IT’s a Miracle and how little can you know…: Tanel Põder in Utrecht!

 

Networking and evaluations

Today was the first day of the second Miracle Masterclass this year: Tanel Põder has come to The Netherlands, saw a room filled with a lot of people (he called it the best seminar even before he started, just because of the amount of attendants) and made me feel I have an awful lot to explore yet. However, that is in fact a good thing and it made me get through the “trains nightmare” today with a smile: I had trouble in the morning since I left home far too late, had to cycle as fast as I could and jumped completely exhausted with sour legs into an already late train packed with people, so I had to stand. Good start of the day. And on the way back tonight all trains around Utrecht had trouble because of some “logistic problems”. Eventually I was not unlucky: only a half hour delay, although standing again (great thing a First class ticket to Utrecht, I could really use it 😦 …NOT!). But hey, I said I went through it with a smile. Because this first seminar day was already worth the money. The more new things you hear may make you feel exhausted and even a little frustrated, but at the same time satisfied because the net pay-off is awesome, right?