Tuesday, October 31, 2006

Oracle Gurus

"Becoming an Oracle guru doesn't take remarkable intelligence or a pricey Harvard degree, but it does take persistence, drive, and a dedication to excellence."
- Don Burleson, How to become an Oracle Guru

Despite the appearance of merely trying to promote his team (most notably Steve Karam), Mr. Burleson actually raises some interesting points on a thought-provoking question.

What Makes an Oracle Guru?

So what does it take to become an Oracle guru, according to him? I'll group his dozen points into the underlying broad categories:

1. Credentials
A stellar education with prestigious degrees, awards, and certifications.

2. Communicating Their Knowledge
Via insightful blogs, publishing opportunities, and polished communications skills.

3. Attitude
Seeks challenging job opportunities, and is characterized by a "can do" attitude.

Who Are the Gurus?

Before I can properly evaluate Mr. Burleson's assertions, I need to know who the Oracle gurus are. So I unleashed the "Google Guru Wars" (say that five times fast). Here are the results of this dubious test:

Most popular results for "Oracle Guru" on Google
878 Jonathan Lewis
762 Tom Kyte
646 Don Burleson
486 Mike Ault
216 Steve Adams
3 Robert Vollman :)

Do they all meet Burleson's Three Criteria? Well the criteria are fairly subjective, but from what I know of these 5, they certainly do.

But let me ask two questions:
1. Is there something MORE to being an Oracle guru?
2. Is there something extraneous in Burleson's Requirements?

What is a Guru?

In my experience, guru simply means anyone with a very high level of knowledge and understanding. Generally I think it is also implied that they have a following of some kind.

Strictly translated, guru can mean "teacher" - and in many places like India and Indonesia I understand that guru is generally used in that sense. Certainly we picture gurus as having mentors.

Reviewing Burleson's Three Criteria

With that in mind, let's take one final pass through the three criteria.

1. Credentials

Our first goal must be to get past the subjectiveness of this criteria. After all, who is to decide which degree is prestigious enough, which certifications are necessary, and which awards qualify?

That being said, it's fair to say that an Oracle guru would certainly be capable of earning a post-secondary degree, Oracle certification, and an Oracle ACE. But I would also suppose that they may not have had the opportunity nor the desire to pursue this. These may be necessary to PROMOTE oneself as a guru, but not necessarily to BE one.

Nevertheless, this is still a valid criteria. But instead of tangible things like degrees, certifications and awards, which (if I may be so bold) would be possible (though extremely difficult) to accumulate without attaining a thorough understanding of Oracle, we need to measure it based upon the actual understanding.

How do you measure an actual understanding of Oracle? I don't have that answer, but I might propose to test their understanding, through questions and problems. Then again, I suppose that's what academic institutions, award review boards and certification exams do ...

2. Communicating Their Knowledge

I didn't like this criteria at first because it excluded some amazing DBAs with whom I have had the pleasure to work. However, given the definition of a guru I reviewed, it would seem like educating others is a necessary qualification.

Still, I've seen some people communicate a relatively mediocre understanding of Oracle through numerous papers and books and blogs. I've also seen brilliant writers publish only a single book (if any), and an unarguable guru like Jonathan Lewis, until very recently, had no blog at all.

I've seen some people answer countless questions on Oracle forums, and yet never really impart true insight or wisdom.

So again, this criteria suffers from subjectiveness. How do we measure how effectively a potential guru has communicated their knowledge? Number of students? Questions answered? The students' understanding of Oracle? Some combination thereof? If so, how do we test the understanding of the students if I've already conceded above that I don't know how to measure someone's understand of Oracle in the first place?

3. Attitude

Despite being the most subjective criteria of the three, I found the attitude that Oracle gurus share to be the most interesting, and the one on which I wish Don Burleson had elaborated further.

I like the way he describe Oracle gurus as those that seek challenges, and have a "can do" attitude. To me, those two qualities perfectly sum up how I've been able identify good sources of mentorship in my career. Some people might have fantastic knowledge and experience, but like to stick to what they know, and are stubbornly cynical about any problem that falls outside their comfort zone.

Wrapping Up

Sadly, I really don't think I've answered any questions or cleared anything up on this matter, and I apologise for that. However, if I have helped to promote and advance the discussion, then I'm glad I took the time to share my thoughts with everyone. I'd love to hear yours: what makes an Oracle guru?

Monday, October 30, 2006

Oracle Passwords

Hallowe'en is a time of fright and mystery, and some of the most common, mysterious Oracle questions I collect involve passwords. Here are some tricks and treats:

How do you change an Oracle password? ... If you/the user forgot it?
How are Oracle passwords stored?
Does it look the same for two different users using the same password?
Do the SQL Trace files reveal your secret password?
How do you hide your password from others when running scripts?
How do you set/enforce your password policy in Oracle?
Which password-related error messages can you get in Oracle, and what do they mean?

The scary truth is that there is no mystery to any of the answers. Each answer can be readily found in either the Oracle Documentation (more specifically, the Oracle Administrator's Guide), or among the plentiful resources of the Internet. It's all in knowing where to look! I've collected a few answers to get you started.

Changing Your Oracle Password

If you already know your Oracle password, you can change it using an SQL*Plus session by simply typing "password" and following the instructions.

If you have the privileges, you can also change a password using ALTER USER.

ALTER USER username IDENTIFIED BY password;

That is also the trick if you have lost your password. Of course, you must have the privileges.

Hopefully its not the privileged user you've lost the password for, but if that's the case, you can always try to the default Oracle usernames and passwords (but presumably you changed it already). Perhaps you want to allow more users SYSDBA privileges? I'll get to that ...

How Oracle Passwords Are Stored

You can see the usernames and encrypted passwords in the DBA_USERS table (assuming you have privileges).

SELECT username, password FROM dba_users WHERE username = 'SCOTT';

If two users have the same password, will it mean they'll have the same encrypted password? NO. You can try it yourself, or just ask my featured blogger Steve Callan. Steve also explains how a DBA can't determine your secret password by reading SQL TRACE of your session.

Steve also turns us onto a little trick where we can save that encrypted password and re-set it after it was (accidentally or deliberately) changed using ALTER USER.

ALTER USER username IDENTIFIED BY VALUES 'encrypted password';

Check out the Dizwell wiki for a cleaner example of restoring an encrypted password.

Your Oracle Password Policy

Steve also mentioned a certain password policy script, which you should find here:


Oracle supports some fairly sophisticated password policies, not just requiring a certain length or requiring numerics. I won't elaborate on the details, which you can find in the Oracle Administrator Guide.

There are plenty of (non-Oracle-specific) articles out there on the need for a strong password policies. For instance, according to a fact sheet from Red Database Security, changing the minimum length of a password from 8 characters to 9 can make the different between 2 days and 57 for a brute force attack!

Oracle Password Error Messages

Speaking of Red Database Security, they did the job of summarizing the various error messages you can get when you try to log in, and what they mean.

ORA_28000: The account is locked
Wait for PASSWORD_LOCK_TIME or contact your DBA

ORA-28001: The password has expired
Change the password or contact your DBA

ORA-00988: Missing or invalid password(s)
Use double quotes for the password (e.g. alter user scott identified by "!alex";)

ORA-01017: Invalid username/password; logon denied
Contact your DBA if the problem still persists

The Password File

I mentioned two things above:
1. Your encrypted passwords are stored in the database, accessible through DBA_USERS.
2. You can grant SYSDBA privileges to other users.

If my first point is correct, what is the password file? And what does the second point have to do with anything? Well, put 1 and 2 together. The password file is how you grant those SYSDBA privileges to other users.

How? By using ORAPWD: The Oracle Password File Utility. You'll find it in $ORACLE_HOME/bin, and you can create the file like so:

orapwd file=filename password=syspassword entries=maxsysdbas force=Y

You can find more information in the Oracle Administrator's Guide, right at the top.

Speaking of which, here is another error you might get: ORA-01996. In this case, you were trying to grant SYSDBA privileges to a user when you already have assigned this to the maximum number of users. Use this utility to increase your limit.

Finally ...

You wrote a script which connects to Oracle, and you're worried that other users can see the login credentials you used to execute the script. What is the correct way around this?

Connor McDonald discusses four ways, using OS accounts, internal accounts, /NOLOG and a last-resort sqlplus trick.

Alternatively we can do what we always do when we want to know something about Oracle, we Ask Tom. Or, more precisely, we assume someone already has and find out what he said. In this case, he instructs us to use the 'identified externally' directive.

Normally I make a reference to Tom Kyte the final word, but this time the honour will go to someone equally worthy: Jonathan Lewis. His blog is long overdue, and you can find his link second from the top, right next to Tom's (sorry Eddie).

Tuesday, October 24, 2006

Oracle Conferences

If you follow the various blogs on the right, you'll see that practically everybody (except me) is currently attending Oracle OpenWorld 2006. It's an annual convention held in San Francisco late in the year. Lasts about 4 days, and costs around 2000 USD$.

I'd love to attend Oracle OpenWorld some day. Or practically any conference. I would especially like to visit the annual conference hosted by UKOUG. It generally takes place shortly after Oracle OpenWorld, and its about the same cost, but maybe a few hundred bucks cheaper. Makes up for the airfare to Birmingham.

Maybe for a warm-up I'd finally meet up with Dan Morgan at the Puget Sound Oracle User's Group's OracleDay in Seattle. It's one day, and I'm pretty sure its cheap (or free) for members. If you're not in my area, check out the calendar I got from the OTN web site that lists some of the local user groups annual meetings.

Judging from that list, there are three other conferences I would keep my eye on:

1. Oracle Development Tool Users Group Kaleidoscope, in June, to be held in Daytona Florida next year.
2. The 2nd ever PL/SQL-specific OPP 2007, for 2 days in February/March in San Francisco.
3. Oracle Applications User Group Collaborate 2007 in Las Vegas next April for 4 days.

If ever I do go to my first Oracle convention, I'll be sure to mention it here and hopefully I can meet some of you there.

Thursday, October 19, 2006

3 Easy Ways to Improve Your PL/SQL

Want to know what has been calling the PL/SQL Procedure you've written?
Want to know how to tell how far along your long-running PL/SQL Procedure is?
What to know how to write fast mass insert/deletes like a pro?

I have found you the answer for all three, courtesy of my fine colleagues featured among my links. Allow me to guide you through these three great articles.

Who Is Calling Your Procedure?

The answer to our first question comes courtesy of Oracle's Blogger of the Year Eddie Awad.

Eddie won this award not just by virtue of being first in an alphabetical listing, but also by the sheer volume of focused articles of high interest, each one backed up by references and proven tests. His article about OWA_UTIL.WHO_CALLED_ME is no exception. His article includes:
1. A working example you can cut and paste,
2. A link to Oracle's documentation, and
3. A link to Dan Morgan's syntax reference

Thanks to his diligence, it literally took only minutes to add this type of instrumentation to my existing code. When something goes wrong, I know exactly who called my procedure, and where. This even works if the calling code is wrapped. (Proof forthcoming)

Given how easy it is, I plan on testing the overhead/performance to see how feasible it would be to start using this as a metric by creating this table and adding this line:

(caller_name VARCHAR2(100), line_number NUMBER(8));

INSERT INTO MyProcCalls (caller_name, line_number) VALUES (caller_name, line_number);

Then I'll start looking like a pro already.

How Far Along Is My Procedure?

This answer comes from my newest featured blogger Andy Campbell. He has been an Oracle DBA for 7 years and, like me, started a blog to record the things he learns and doesn't want to forget.

Let's take advantage of Andy's experience on how to instrument our PL/SQL code further by including calls to DBMS_APPLICATION_INFO to record our procedure's on-going progress.

Andy explains, complete with examples, how to use this supplied package (available as far back as Oracle 7) to have your procedure write information into session-tracking tables using SET_MODULE and then SET_ACTION.

Of course the big pay-off comes at the end of his article where he executes his long-running procedure and then queries the session-tracking tables like V$SESSION to find out how far along the procedure is. Brilliant!

Let me supplement his article with the following three references:

1. Supplied Packages References, Chapter 3: DBMS_APPLICATION_INFO

2. Another one of my favourite featured bloggers, Oracle ACE of the Year Tim Hall, is reknowned for his great articles. He also has a write-up on how to use DBMS_APPLICATION_INFO and the V$SESSION tables.

3. Finally, what list of mine would be complete without including Dan Morgan's reference?

Thanks to Andy's working example, it literally took me minutes to further instrument my PL/SQL code and fool people into thinking they were written by a true Oracle pro.

How Do I Write Mass Inserts/Deletes Like a Pro?

If we're going to code like a pro, we need to take at least one page out of Tom Kyte's playbook. Tom Kyte's work is beloved for its passion, dedication, completeness and accuracy.

Tom's has a passionate distaste for bad practises, including slow-by-slow processing. Programmers unused to thinking in sets are at a distinct disadvantage when programming database applications. Why? Because they do their inserts and deletes one-by-one in a for loop.

As database programmers know, this approach is completely unnecessary and wasteful. They know instead to look for a solution that does some kind of bulk processing when doing inserts, deletes or updates on a large number of records.

Tom illustrates this point with a specific case where particularly bad Oracle code had been written.

The original requirement was to prune down a list of employees, removing duplicate employees by choosing the one with the most recent hiring date.

What did the original programmer do? First, created a big master table of all employees, then when through them in a loop, each time deleting employees that produced more than one record when grouped by employee number.

Study how it was done by the original programmer. It might not look like an altogether unfamiliar approach. But what do you think about this result?

Well, you may find that the result was not only very slow because it would loop as often as your most common duplicate, but potentially logically incorrect because it would remove an employee entirely if it had duplicate records with the same hiring date.

Instead look how a true pro handles this situation. Tom handles this case in a single, efficient SQL statement.

He breaks the problem down to its simplest form: we need a list of each employee with its data, with only a single row per employee representing the most recent hiring date.

Therefore Tom populates such a table by first adding a column signifying the most recent hiring date for each employee (using PARTITION BY, a technique we've discussed many times before), then by selecting only the rows where the hiring date and this most recent hiring date match.

I admit this third tip is a little harder to digest than the first two, because you have to learn how to think like a pro. Break a problem down, and approach it like a database would by thinking in sets.


The true secret behind these three tips is actually to read. There are a lot of Oracle professionals just like you, and we're fortunate that so many of them are generous with their knowledge. Thanks!!!

Looking for more great articles whenever I'm quiet? Please bookmark the links along the side of the page. And visit the articles in my archives: you may have especially missed some of the early ones.

Tuesday, October 17, 2006

Software Vendor Customer Support

Having worked in technical support for software vendors for 4 years, I know a few strategies that can help customers get the best response.

1. Get training
Rely on the assistance of the software vendor for as little as possible. Invest in training your own people by signing them up for courses and, more importantly, giving them the time to learn the product and how your company uses it. Once knowledgeable, your expert can get the best results from the vendor.

2. Avoid customizations
No product is going to fit your needs exactly, and it's tempting just to make little fixes here and there, but resist the urge. Just because you can do something, it does not necessarily follow that you should. Consider leaving the software as-is and just changing your process. Customizations can cause problems, become (legitimate or not) obstacles preventing the vendor from providing you assistance, and make upgrades more problematic.

3. Stay current, but not too current
Generally the vendor offers the best support for versions immediately before the latest version. If your version is too old, they may have fewer people that know it and they may stop supporting it altogether. You don't want to stay too current - let the other customers find the bugs in the latest releases. Of course, if you need the functionality in the latest version and the vendor is prepared to give you extra support for being a guinea pig, go for it.

4. Allow for remote access
Generally you should never allow the vendors on to your system, but sometimes it will be necessary, so be set up to make that as easy as possible.

5. Wait until you have time
There is no point opening a ticket with them until your resources have the time to act on whatever they suggest. Take too long to respond and your item will be downgraded, perhaps permanently. If something is low priority and you just want it "on the record", then make sure you say so.

6. Assign the right priority
Every software vendor will have standard definitions where you define the severity/priority/impact of a problem. It is tempting to assign a higher priority, but once you're downgraded you may be given a lower priority than items that were opened correctly at this new downgraded level. You can also get a reputation that may cause truly important items to get downgraded in the future. Any way you slice it, clearly explain the business impact of your problem, regardless of priority, to avoid any problems.

7. Answer the standard questions in advance
Don't wait for them to ask you the usual questions: When did this start, what changed, etc. Anticipate their questions and answer them up front. Look back at the questions they usually ask as a clue.

8. Provide a complete, independent test case
Doing a little work beforehand can save a lot of time later. Eliminate distractions and narrow the problem down to a simple, reproducible step-by-step description that will work anywhere. Try to use default data wherever possible. A test case is worth a thousand words, so this will save you days or weeks of back-and-forth explanations.

9. Provide all logs, configuration files
Don't wait for them to ask, go ahead and attach all relevant configuration files and logs. Tell them what you've already tried (from standard troubleshooting techniques in the manual) and what the results were for those tests.

10. Make it as easy as possible
Try to make your case look as simple and clear as possible. Some vendors measure their service representatives by how many items they close to the customer's satisfaction. So they like to pick off the low hanging fruit. If your case looks hard, it may get neglected, or assigned to someone not new or skilled enough to dodge the tough ones.

11. Open separate tickets
Try not to lump everything into one ticket, or you risk having your problem only partially solved. Why not open a separate item, and have them work on your issues in parallel? If they're somewhat related, you can always say so in your description. Worse case scenario, solving one solves the other and you can just close both.

12. Escalate when you're stuck
If you're not getting anywhere, ask for the issue to be escalated to the manager. S/he will make sure the right people are working on it, that there is a resolution plan, and communicate that information to you. Keep going up if you have to, managers have managers too.

13. If you need extra, pay for it.
If, for whatever reason, you can't do certain things for yourself, or you need some extra help from the software vendor, offer to pay. Money talks! You will get results far faster and more reliably than if you just try to threaten or cajole them into it.

14. Make sure the solution is permanent
Try to keep the focus on identifying and solving the root cause, not the consequence. It is almost always advisable to end a resolution with a documentation enhancement or a new item in their solutions knowledge base. Volunteer to proof read it.

Note: I originally wrote this article in April, but it was on my personal blog. It occurs to me that the Oracle professionals that read this blog might be supporting 3rd-party Oracle-based applications, or perhaps dealing with Oracle support directly, and therefore would enjoy this article too.

Also, given my lack of material lately, this is the best way to confirm I am still alive. :)

This page is powered by Blogger. Isn't yours?