Database Design

One of the developers on our team was assigned to design and code a new feature in the system. This particular feature was not too complex. However in the past, this developer only did some coding. He called me up and asked me how he should implement the changes. I told him he should understand the requirements, and put together a design. He was very nervous about this. He shouldn't be. The guy has a PhD in computer science. He is was also hired as a senior developer. This should be a cake walk.

The guy came up with a design to add a column to one of our tables to track the timing of when to do some follow on tasks. That sounded good until he wanted to set the value in a trigger. Unfortuantely the trigger was on the table he was trying to modify. He found this did not work, as it was illegal in the specific trigger he was modifying. Then he decided he needed a small additional table to track the timing.

Unfortunately, the database changes went into review by an external team. They found the design lacking. This external team came up with all kinds of other designs to solve the problem. Unfortunately this was late in the development life cycle. That's where I got dragged into the scene. I took a look at all the proposed designs. Each of them would work. In the end, the main driver for sticking with the original design was that the work was already complete.

The outside team got some recommendations in though. There were be a history of the timing. There would be extra columns tracking who did what. Now that little table is getting bigger and more complex. It still will achieve the business objective. And the work is just about done. The moral of the story is that you have to practive doing designs before you can produce solid ones.

Hitting Max Users

My boss called me up this morning. The customer had a high priority issue. Our software was not allowing them to create new users in the system. The only lead I had was that our software reported a -1985 error. Now I know that number is not one of our internal error numbers. So it had to be an Oracle error. ORA-01985 means we have reached LICENSE_MAX_USERS. Oh boy.

I could not interrogate the production database. No rights. A DBA eventually got on and found that the parameter was set to 2001. Whoa. We have more than 2k users? Well the solution is to bump up the parameter. Initially the boss man said we could rev this param value up to 5000. Then after hearing the current value was 2001, he thought 3001 was a good new number.

I played around in a development database. Figured someone could issue an ALTER SYSTEM SET LICENSE_MAX_USERS = 1. Luckily a DBA came on and added a SCOPE BOTH to the end of that command to ensure it sticks after the database is rebooted. I am not a DBA. So I don't know much about the server parameter file (spfile). I just know that when we makes changes like this, it needs to stick immediately and permanently. That must be what SCOPE BOTH controls.

TNS Names Alias Cache

Our DBA team keeps a TNSnames.ora file out on a network drive. They maintain it with all the databases anyone would need to connect to. All the developers use this copy of the file to map TNS alias names. Today one developer found out that when he first boots up, our application cannot seem to properly access that file.

The developer said he must first access the network share with Windows Explorer before any of our apps can use the share to get to the TNSnames.ora file. He thinks that maybe Windows need to cache the network share information or something. That did not sound like a sound theory. But the evidence was plain. He said the first time after a reboot, he always get a database not found error. The second time he tries, our apps work fine.

Well there may be some truth to this theory. However he went on to generalize that maybe the users of our system might encounter some problem when cached data is not present, and a database call times out. That just does not seem to have a leg to stand on though.

For starters, we put the TNSnames.ora file on the local C: disk drive of our customer' computers. So there should be no delay in accessing that file. Sure the database server is far away, accessed over the network. However I cannot believe that some delay will cause a timeout resulting in random application errors.

Garbled Data

Our test team was trying to recreate some problems detected by our customers. They were having a hard time. So they took a look around at the data in the different tables in this part of the subsystem. They found the main table that drives the processing. Unfortunately, most of the data seemed to be all garbled up.

They were using SQL Developer. One of the testers wondered if this could be a problem with the query tool. That was doubtful. But hey. It could not hurt to try viewing the data with another tool. So another tester pulled out TOAD and tried to bring up the data. Most of the data seemed empty.

The testers figured that SQL Developer was just displaying all the data, regardless of how strange it was. TOAD on the other hand, was just supressing the display of weird looking data. This did not help us understand why the data was bad. It just was an interesting observation.

The data in the main table was SQL*Loaded from a file the testers were creating. Further inspection of this file showed that the positioning was incorrect. Perhaps that was the source of the weird looking data. Further research is required.

Ad Hoc Reporting

I had written a script to restore some data that had been lost. A team convened to run my script. The thing completed very quickly. After some spot checks, we determined my script was a success. The DBAs left the call shortly after that. There was one last task left. My boss wanted me to write some SQL to produce a comma separated file with the data.

That sounded simple enough. SPOOL the data out to a file. The main command was a big SELECT statement which included all the column values separated by commas. I gave it a try in development and found the lines were wrapping. So I SET LINESIZE to 200. We run the job in the production environment.

Ooops. The headers are showing up on every page. Should have SET HEADING OFF. Then we import the data into Microsoft Excel. Some rows are not lining up correctly. The LINESIZE needed to be bigger. Maybe set it to 2000? Is that too high to work? Also some of the data had commas within the values. Oh man this is getting tricky.

In the end I resorted to a tool like PL/SQL Developer to do the trick. It can export data with ease.

Overcoming the ORA-01440

I had to test the effect of changing a column type from NUMBER(1) to NUMBER. Luckily our client code did not choke. I guess they translate into integers in the programming language on the client. After the test was done, I tried to alter the table back to its original state. Unfortunately I ran into an ORA-01440.

The ORA-01440 happened because I was trying to change the type to a type with decreased precision. Apparently you can only do that if the column you are changing is empty. Well I figured I could get around that by making all the values in the column to be NULL. I could clean the data up later.

There was one additional problem in my plan. The column I was dealing with could not be NULL. I was in a bind. That's when Tech on the Net provided the idea to just DELETE the records. That way there was no data to hold me back. Actually I exported the data and reimported it after the type was changed. This is something you won't learn in the books. You got to experience this specific type of problem to be able to work around it.

Oracle Data Dictionary

I just read an article from Oracle Magazine on the data dictionary views. There was a figure with 12 different user views of interest. I am trying to digest all the information and make some connections between the views. Let's start with USER_SOURCE. Okay I have employed that one before. It can tell you the source code of something you have compiled into the database, such as a stored procedure.

Now an object you compile into the database will also have an entry in USER_OBJECTS. That is also familiar territory. But there is also a USER_OBJECT_SIZE view. I bet you can figure out what that view tells you. Now other views hold object information of a certain kinds. Your triggers are shown in USER_TRIGGERS, and your procedures in USER_PROCEDURES. But have you ever heard of the view USER_TRIGGERS_COLS? Me neither. Looks like it maps triggers to the table/columns they use.

I have never queried USER_ERRORS directly. I just type SHOW ERRORS in SQL*Plus. To do some source code analysis, it looks like I might check out USER_DEPENDENCIES, USER_ARGUMENTS, or USER_IDENTIFIERS. Looks like I got some homework to do. If I find anything of use, I will definitely share it.