Tag: Oracle

SQL Developer and MS SQL Server…

This afternoon I’ve been cleaning up some data in an SQL Server database. I decided to use SQL*Developer to connect to SQL Server by following this post.

I made liberal use of the following tip when dealing with TEXT and NTEXT types.

The joys of dealing with multiple engines…

Cheers

Tim…

Wirth’s Law…

I was scooting around the net and I stumbled on a reference to Wirth’s Law and had a flashback (not Nam related) to a conversation I had about 14 years ago with my boss at the time. We were setting up the kit for a new automated warehouse solution (Oracle 7, HP 9000s and ServiceGuard if I remember correctly) and he said something along the lines of, “Why is it that for each customer we buy faster and more expensive computers, yet they take the same length of time to produce the results?”

The answer was pretty simple in that case. We were refurbishing the existing (fairly simple) warehouse as well as adding a completely new one. We were replacing some AVGs with a very complex conveyor layout, which required some difficult routing decisions. The basic “find me a space in the warehouse” decisions were replaced by pretty complex searches that had to take account of conveyor routing, system load and potentional sorting (and defragmentation) of the content in the warehouse. The customer needed a highly available solution, hence the use of ServiceGuard, so we more than doubled the hardware and software costs for no perceivable performance improvement. From the outside looking in it seemed like nothing had changed. It was still, “Here’s a pallet, put it in the racking”, but the process required to do that operation efficiently had increased in complexity manyfold.

So Wirth’s Law, “Software is getting slower more rapidly than hardware becomes faster”, is true because people’s expectation of what software can do for them is constantly expanding, without realizing the impact those expanding expectations have on the programming and hardware requirements. Added to that we have a generation of cut & paste developers and DBAs who also don’t understand the impact their lack of understanding has on the software they develop (see Gate’s Law).

I look forward to writing a post in 10 years where I can moan about Exadata V12 boxes struggling to complete my weekly loads before the end of the weekend. Of course I will forget to qualify that I’m loading Yottabytes of data in that time… 🙂

Cheers

Tim…

Oracle RAC on VirtualBox…

With the recent news that the latest version of VirtualBox now supports shared disks, I thought I better give it a go and see if I could do a RAC installation on it. The good news is it worked as expected. You can see a quick run through here:

This is pretty good news as that was the last feature that tied me to VMware Server. I’ve now moved pretty much everything I do at home on to VirtualBox and it’s working fine.

It’s worth taking a little time looking at the VBoxManage command line. Some of the operations, like creating the shared disks, have to be done from the command line at the moment. It’s also handy for running VMs in headless mode if you don’t want the GUI screen visible all the time.

Cheers

Tim…

Learn to search the Oracle Docs…

Question: What is the most important skill any Oracle DBA/Developer needs?

Answer: Knowing how to search the Oracle documentation.

The Oracle database is a massive product. There are sections of it I’ve never ventured near. Do I spend my time trying to remember syntax and procedures for every possible occurence? No. I try to understand the principles of what is going on and regularly refer back to the docs to fill in the gaps. I search the docs constantly. There are some very basic tasks that I would probably fail to complete without reference to the manual (or one of my articles). Whenever I see an error message I search the docs for that error message to see what the action against it is. I can’t imagine anyone being able to function as a DBA or developer without constant reference to the docs.

I am frequently asked questions that are very easily answered by searching the docs for the relevant keywords in the question. So the original poster has wasted the time taken to type the question, plus the time it takes for me to come online and answer it. Since most of my readers live in a different timezone to me, it can be several hours between the post arriving and me answering it.

So if you want to get good at Oracle, learn to search the docs. Every time you want to know something:

  1. Go to the online documentation for your DB version.
  2. Type in some relevant keywords. Stick to keywords, not whole sentances.
  3. Click the “Search” button.
  4. Refine your search if you don’t get the answer.

Over time you will become an Oracle Certified Professional Documentation Searcher… 🙂

Cheers

Tim…

Oracle Auditing: Something I should have known…

I wrote about some new bits of 11gR2 auditing a while ago. Well actually it’s a bit of audit vault functionality that was included in 11gR2.

While answering a question today I confidently proclaimed that while you can move the AUD$ and $FGA_LOGS tables to another tablespace, it’s not a great idea and could make things go bang when you come to patch/upgrade the system. Whilst looking up some links for further reading to send to the poster, I noticed note 731908.1, which says the DBMS_AUDIT_MGMT package has been backported to 10gR2.

It’s not included in the database documentation until 11gR2, but it is available from the patches listed in the note if you need it.

Pesky Oracle improving older releases… 🙂

Cheers

Tim…

Oracle Database SQL Expert (1Z0-047)…

I can see this post degenerating into a rant, so I would like to preemptively appologize to anyone involved in the production of this exam. I’m guessing it’s a real pain to develop these exams, especially when some ass like me starts moaning about them. Added to that, I’m guessing the word “Expert” means slightly different things to different people…

I’ve been barking on recently that in my opinion, the most important skill required by any PL/SQL developer is SQL, with knowledge of PL/SQL itself coming in second place. Having recently taken the “Oracle Database 11g: Advanced PL/SQL (1Z0-146)” exam (mentioned here), I thought it was a little hypocritical not to sit the “Oracle Database SQL Expert (1Z0-047)” exam as well, so this morning I did just that.

Here are some of my thoughts on the exam, in no particular order of importance:

  • Regular Expressions: I think it is important that people understand what regular expressions can do and when it is appropriate to use them, but I don’t think it is necessary to test people on the meta-characters themselves. That’s what the docs are for.
  • Analytic Functions: No sign of them in my questions from the pool. Surely analytic functions are more important than regular expression meta-characters.
  • The majority of the exhibits were pointless. It seems like they were placed there to waste the time of people with bad exam technique, rather than to assist in answering the question. This was especially true of the schema diagrams, which I only referred to once when the datatype of one of the columns was important.
  • Several of the questions could be answered without reading the question at all, as the incorrect answers jumped out at you because they contained blatantly incorrect statements.
  • Several of the questions included the “ANY” and “ALL” comparison conditions, which are barely mentioned in the documentation (here)*. I guess these are only included in Oracle because the are part of ANSI SQL. I can’t remember ever using them in Oracle or seeing them being used by others. I have come across them in MySQL so I knew what they were for, which was fortunate.
  • There were lots of questions that included DML against inline views rather than directly against tables. It got to the point where I felt like, “If it’s got braces in it I’m going to tick it”.

I very quickly turned into a grumpy old man and started to rush through the exam, spending most of my time thinking about writing this blog post, rather than the exam itself. 🙂

In the end I got 96%, which I guess means I got 3 questions wrong out of the 70. Serves me right for rushing it so I could come home and bitch about it. 🙂

So I am now an “Oracle Database: SQL Certified Expert” as well as a grumpy old shite…

Cheers

Tim…

* Updated thanks to Pierre’s comment.

Oracle Games Console Beta Program…

I just found out I’ve been accepted on to the Oracle Games Console beta program. It was a bit of a shock as I’ve got in trouble a few times for leaking information about it. I guess the powers that be have decided it’s safer to bring me into the fold than leave me out in the cold, blabbing about stuff.

For legal reasons I really can’t say anything about the kit itself, but it’s awesome…

I really think OpenWorld 2010 will be the debut. There were rumours of announcements before, but it’s looking really good for this September. One of the big worries has always been a lack of games, but the XBox and PS3 emulators are amazing. The upscale in video playback from the XBox games is incredible…

Cheers

Tim…