Niall's Oracle Pages

Mark Rittman points out that Oracle have just announced some changes to the OCP certification program. Chief amongst these is the news that it will only be necessary to pass 2 exams for the 10g DBA credential rather than the 4 currently required. The rationale for this is given as
By leveraging the innovative and automated features of Oracle Database 10g, Oracle University has significantly reduced the cost and time to train and certify.

I have to say that I'm not convinced. It probably is the case that 10g is easier to manage than its predecessors, but for me the education program was the single strong point of the OCP program. It has been far too easy for people with good memories, but little experience to pass the exams - if you want to go this route the exam cram books are good. As with the MCSE program this has meant that what is in principle a good thing has become, in practice, dilute. The quality of education that oracle themselves provide is excellent, but I cannot see how you can effectively teach all that is required to make good use of the power of the Oracle product in just 10 days. Exam 1 for example takes you from how to write a select statement to how configure backup sets and recover from various failure scenarios, by way of performance tuning and diagnosis and enterprise manager. In 5 days.

As it happens I do have the OCP certification, and am glad that I achieved it, but that was for the value of the education primarily, and the exposure to technologies that we don't use.

Larry Wolfson pointed out to me a potentially rather serious problem with the after servererror trigger to trap system events that I posted recently. In the case for which the trigger is designed, I am trying to obtain the sql statement that causes a particular server error. It doesn't take a great deal of thought to realize that one could in principle extend the trigger to log all server errors. This raises the question - what happens if there is no sqltext associated with the error - for example 1017 on failed login. What we would probably like to happen is for the statement to be a null string. This is what happens on 9205


UTILS 16-JUN-2004 08:49@oranet>insert into monitored_errors values(1017);

1 row created.

UTILS 16-JUN-2004 08:49@oranet>commit;

Commit complete.

UTILS 16-JUN-2004 08:49@oranet>conn niall/peter
ERROR:
ORA-03113: end-of-file on communication channel

Warning: You are no longer connected to ORACLE.


This is bug 3124081 which is fixed in 10g. The fix doesn't obviate the need for error handling if you want to trap all errors - but it does give you that possibility. For completeness on 10g you get the following with the trigger as is.


UTILS 16-JUN-2004 09:53@nl1010>conn peter/rabbit
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 19
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
16-JUN-2004 09:53@>


Pete Finnigan made an announcement of a new paper on SGA direct attach that he is hosting. This led me to Kyle Hailey's earlier presentation on the same subject (with a suitably nminimalist and geeky colour scheme), and that led me to Kyle's excellent site at sourceforge. There are a number of real goodies in there, but the stuff that made me think the most was the article on using graphics for tuning. In particular it made me think seriously about whether EM10g might become the tool of choice for the serious Oracle Performance analyst. Kyle makes some excellent arguments about the ability of graphical information to direct tuning focus. If like me you in general can't stand GUIs check out the presentation.

Recent discussions on news://comp.databases.oracle.server led me to draw up a little test that - hopefully anyone - can run that tests the assertion that repeated inserts into the same part of an index lead that part of the index to increase in depth, when the rest of the index doesn't.

The methodology is quite simple, create a small single column table with an even spread of values. Take a treedump to display the index structure graphically, then insert a large number of identical values, take a second treedump and compare the structure. You can play with the number of values in the table to start with, and the number of identicals to insert to your hearts content. The script is here.

It might be worth just introducing you to a (9i) treedump. It looks like this


*** 2004-06-09 08:44:00.794
----- begin tree dump
branch: 0x240128c 37753484 (0: nrow: 7, level: 2)
    branch: 0x24012e5 37753573 (-1: nrow: 29, level: 1)
        leaf: 0x240128d 37753485 (-1: nrow: 19 rrow: 19)
        leaf: 0x24012b4 37753524 (0: nrow: 19 rrow: 19)
        leaf: 0x24012e2 37753570 (1: nrow: 13 rrow: 13)
        leaf: 0x2401320 37753632 (2: nrow: 19 rrow: 19)
        leaf: 0x2401345 37753669 (3: nrow: 19 rrow: 19)
        leaf: 0x24013a8 37753768 (4: nrow: 17 rrow: 17)
        leaf: 0x2401294 37753492 (5: nrow: 18 rrow: 18)
        leaf: 0x24012e1 37753569 (6: nrow: 19 rrow: 19)
        leaf: 0x240130a 37753610 (7: nrow: 19 rrow: 19)
        leaf: 0x2401347 37753671 (8: nrow: 19 rrow: 19)
        leaf: 0x240138c 37753740 (9: nrow: 19 rrow: 19)
        leaf: 0x24012b6 37753526 (10: nrow: 19 rrow: 19)
        leaf: 0x24012c5 37753541 (11: nrow: 19 rrow: 19)
        leaf: 0x24012f4 37753588 (12: nrow: 13 rrow: 13)
        leaf: 0x240132c 37753644 (13: nrow: 19 rrow: 19)
        leaf: 0x240134b 37753675 (14: nrow: 19 rrow: 19)
        leaf: 0x24013c0 37753792 (15: nrow: 11 rrow: 11)
        leaf: 0x2401298 37753496 (16: nrow: 19 rrow: 19)
        leaf: 0x24012d5 37753557 (17: nrow: 19 rrow: 19)
        leaf: 0x24012f6 37753590 (18: nrow: 13 rrow: 13)
        leaf: 0x240132e 37753646 (19: nrow: 19 rrow: 19)
        leaf: 0x2401397 37753751 (20: nrow: 18 rrow: 18)
        leaf: 0x24013c4 37753796 (21: nrow: 12 rrow: 12)
        leaf: 0x24012a6 37753510 (22: nrow: 19 rrow: 19)
        leaf: 0x24012d6 37753558 (23: nrow: 19 rrow: 19)
        leaf: 0x24012f5 37753589 (24: nrow: 13 rrow: 13)
        leaf: 0x240132d 37753645 (25: nrow: 19 rrow: 19)
        leaf: 0x240139f 37753759 (26: nrow: 19 rrow: 19)
        leaf: 0x24013c8 37753800 (27: nrow: 11 rrow: 11)
    branch: 0x240134c 37753676 (0: nrow: 24, level: 1)
        leaf: 0x2401295 37753493 (-1: nrow: 19 rrow: 19)
        leaf: 0x24012d7 37753559 (0: nrow: 19 rrow: 19)
        leaf: 0x2401305 37753605 (1: nrow: 13 rrow: 13)
        leaf: 0x2401335 37753653 (2: nrow: 19 rrow: 19)
< omitted >

branch: 0x2401330 37753648 (5: nrow: 29, level: 1)
        leaf: 0x2401290 37753488 (-1: nrow: 19 rrow: 19)
        leaf: 0x24012c4 37753540 (0: nrow: 19 rrow: 19)
        leaf: 0x24012f2 37753586 (1: nrow: 13 rrow: 13)
        leaf: 0x240132f 37753647 (2: nrow: 19 rrow: 19)
        leaf: 0x240134a 37753674 (3: nrow: 19 rrow: 19)
        leaf: 0x24013bc 37753788 (4: nrow: 16 rrow: 16)
        leaf: 0x24012a4 37753508 (5: nrow: 19 rrow: 19)
        leaf: 0x24012e7 37753575 (6: nrow: 19 rrow: 19)
        leaf: 0x240131d 37753629 (7: nrow: 19 rrow: 19)
        leaf: 0x2401346 37753670 (8: nrow: 19 rrow: 19)
        leaf: 0x24013a0 37753760 (9: nrow: 19 rrow: 19)
        leaf: 0x24012b3 37753523 (10: nrow: 19 rrow: 19)
        leaf: 0x24012da 37753562 (11: nrow: 19 rrow: 19)
        leaf: 0x2401310 37753616 (12: nrow: 13 rrow: 13)
        leaf: 0x2401334 37753652 (13: nrow: 19 rrow: 19)
        leaf: 0x24013bb 37753787 (14: nrow: 19 rrow: 19)
        leaf: 0x24013b5 37753781 (15: nrow: 11 rrow: 11)
        leaf: 0x2401292 37753490 (16: nrow: 19 rrow: 19)
        leaf: 0x24012c6 37753542 (17: nrow: 19 rrow: 19)
        leaf: 0x24012f3 37753587 (18: nrow: 12 rrow: 12)
        leaf: 0x240132a 37753642 (19: nrow: 19 rrow: 19)
        leaf: 0x240134e 37753678 (20: nrow: 19 rrow: 19)
        leaf: 0x24013ac 37753772 (21: nrow: 17 rrow: 17)
        leaf: 0x2401291 37753489 (22: nrow: 19 rrow: 19)
        leaf: 0x24012e8 37753576 (23: nrow: 19 rrow: 19)
        leaf: 0x240131f 37753631 (24: nrow: 14 rrow: 14)
        leaf: 0x240131e 37753630 (25: nrow: 19 rrow: 19)
        leaf: 0x24013c7 37753799 (26: nrow: 14 rrow: 14)
        leaf: 0x24013c3 37753795 (27: nrow: 10 rrow: 10)
----- end tree dump



As you will hopefully see (if the formatting isn't munged up) you get a nice tree like structure listing the branch and leaf blocks that comprise the index. (and some information like the number of rows in them.

the syntax to run this command is

alter session set events 'immediate trace name treedump level <objectid> ';



My conclusions - no matter how hard you try oracle indexes are
always height balanced. Give it a whirl yourself and see what happens.

We have one of those irritating issues with one of our Oracle systems, periodically this system, or rather a process using it, will encounter the server error ora-01652, this shows up in the alert.log monitoring that we do.Whilst the cause of the error is well known (generally running out of sort space), we had no reports from end users of the problem, and neither could we tie it down to a scheduled process. So the question remained how do you determine which session and/or sql is the source of an error in the alert.log.

One approach would obviously be to consider that as no-one is complaining, and no scheduled or background tasks appear to be failing, that this is not a business problem at all. If you don't want to do this, I knocked up a quick and dirty monitoring tool that allows you to capture arbitrary server errors into a table along with the sql that caused it. The technique I use takes advantage of System Events which I believe are new or certainly extended in 9i. These events are a compelling alternative to the traditional use of the USERENV context for this class of task.

The scripts are available subject to the usual use at your own risk and common sense guidelines here. Extract the files into a directory of your choice, read all the scripts first - take note particularly that the script creates a UTILS user with create session rights, so you may wish to revisit this after the install - and use errInstall to install the user and trigger.

Insert error codes as needed into monitored_events to start the monitoring. Delete from monitored_events to stop monitoring and disable the trigger to turn off monitoring altogether.

One of the much talked about technologies in 10g (though you can use it in 9.2 at the appropriate patch level) is HTMLDB. One of my problems when looking at it is evaluating where it might be useful. Sample applications are a great way to beef interest in a technology, and Mark Rittman points out a great example at this page in the HTMLDB site. HTMLDB is also of course the technology behind Ask Tom.

How Do I get Started with Oracle?

This is actually a fairly common question and not one that is often addressed adequately. The following is the, slightly edited text of a reply sent by Howard Rogers www.dizwell.com to someone who asked this exact question. The post is so good, that with Howard's permission I reproduce it here. The 'here' referred to below is the usenet group comp.databases.oracle.server which can be reached via the link on the left or via your favourite newsreader.

Be patient. You're starting from scratch, so there's a lot to learn, and much of it may seem very strange to begin with. Visit The online documentation set to get access to the latest Oracle documentation (though your CD set will probably include it already). Read the official Concepts Guide. Then visit sites such as that maintained by Jonathan Lewis, and Ask Tom. Visit Google and hunt around for advice. Take nothing on face value: test it yourself to destruction. Lurk here for a month or two, and try and pick up on the sort of questions being asked, and ask yourself how *you* would answer them. Then see what answers actually come through, and compare. When you're feeling brave, post some of your answers and see how they are taken by people.

Buy books. Anything with the names Jonathan Lewis, or Thomas Kyte on the cover are *extremely* good bets for accurate advice. O'Reilly are good. As are Apress (used to be Wrox) Avoid anything that mentions OCP (Oracle Certified Professional). The qualification is not worth a damn, and leads you straight into myth territory.

If you can afford an Oracle training course (most people can't), go. Go to DBA Fundamantals I if you can... the architecture stuff they cover on that is extremely good (but you play Russian Roulette with the instructor you get. If the guy starts reading from the course notes in the first hour, then leave and ask to be scheduled with someone else at a later date). DBA Fundamentals II is quite good, but is light on networking, and the Backup and Recovery stuff is not difficult. Don't bother with Performance Tuning, because it's a badly-written course that addresses none of the real issues. I think the general thing is to get involved and to get communicating, and you've already started that by posting here. Just don't stop now.

The other general thing is to test and test and test, and verify for yourself. Experiment, and stuff the consequences. The other thing I would suggest, given your background, is that you learn Oracle at the command line, and learn *Oracle*. Worry about putting a graphical front-end app. on top of Oracle later. When you've got command-line Oracle sorted, the graphical front-ends will come as second nature, and will be better for the understanding you have of the back-end architecture and inner-workings.

And finally, enjoy yourself. Oracle is quite a majestic system. It's got an internal logic that is beautiful, and the sense of power that you can get from controlling those internal workings can be quite exhilerating. So have fun.

HJR.

Somewhat adapted by Niall

When using Microsoft Visual Studio .Net and the MICROSOFT OLEDB provider for Oracle then using Server Explorer you can typically see a number of database objects, and what Microsoft call a Database Diagram.



This functionality - in particular the Database Diagram functionality relies on the fact that the development environment creates the following objects in the schema to which you connect


OBJECT_NAME OBJECT_TYPE
------------------------------ ---------------
DT_ADDUSEROBJECT FUNCTION
DT_DROPUSEROBJECTBYID PROCEDURE
DT_SETPROPERTYBYID PROCEDURE
MICROSOFTDTPROPERTIES TABLE
MICROSOFTSEQDTPROPERTIES SEQUENCE
MICROSOFT_PK_DTPROPERTIES INDEX


It follows that you get various errors if the schema you are using does not have create table,create sequence and create procedure rights.

You don't get the same problems if you use the Oracle OLEDB provider, but you don't see so many objects either.

Entirely non-Oracle related.

Laura's grandmother, and hence Rachel and Sam's great-grandmother
died today at 98.

I'm not sure that it is appropriate entirely to be sad - though of
course the family are - not just because of the great age at which
she died, but because of the wonderful life that she lived. She had
3 children, Brian who is Laura's Dad, Gerard his brother and Claire
the only girl. All are special people whom I am privileged to know -
Brian for obvious reasons, Claire because she has spent the best
part of 40 years dealing with challenging kids with learning and
behavioural problems - a year of her life was spent teaching one to
one a little girl who would not speak; but because I can find it on
the net and it sums up the sort of person Gran inspired others to be
I'll leave you with the Heythrop biography of Gerry. He is
undoubtedly his mother's son

There is a wording in St Paul's Cathedral in London regarding the
lack of a memorial to its remarkable architect - If you seek his
memorial look around you. It is even rarer to be able to say of a
person - if you seek her memorial, regard her family

sort of sadly

Niall
===============
Gerry grew up in Glasgow in the early 1940s. Ever an intelligent and
alert child, he used to memorise the silhouettes of German aircraft
just in case he would be called on to shoot one down. He was at this
time eight years of age and fortunately he was not called on to do
his duty in this way for King and country. There were occasional
lapses of judgement however - he came home one day with a small
piece of metal with German words on it: clearly a fragment of a
German aircraft that did not make it back to Berlin. His mother
admired this, as mothers do, of course, and asked him where he got
it. "I swopped it for my bike" proclaimed the future head of
philosophy. At which point his mother took charge of the situation,
with the piece of German metal in her hand she visited the house of
the boy now in possession of Gerry's bike, had a conciliatory
conversation with his mother, and returned home with the Hughes bike.
=================


The Jesuits