Niall's Oracle Pages

One of the things I have been working on recently is a simple script to extract schema creation ddl from various dev schemas that we have around the place, whose documentation is.. ,well perhaps light is the politest word.

anyway that was the idea, first set some transforms to get rid of extraneous stuff (like the storage clauses), and to add in the sqlterminator to each statement, then loop through the various types of objects in the appropriate order liberally sprinkling calls to the DBMS_METADATA.GET_xxx routines.

The first hint of trouble came when I tried to recall the published interface to DBMS_METADATA and couldn't, no worries just log onto my local 10g box


SQL>DESC DBMS_METADATA
ERROR:
ORA-03117: two-task save area overflow

oops, that really shouldn't happen.

Next, logon to the 9i box that I was actually interested in - fortunately this worked and I could get the get_ddl, get_granted_ddl etc syntax.

The next issue, and one that I alluded to earlier is that when the get_granted_ddl query should return no rows, in fact it returns an ugly error.


ERROR:
ORA-31608: specified object of type SYSTEM_GRANT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 631
ORA-06512: at "SYS.DBMS_METADATA", line 1339
ORA-06512: at line 1


This according to the Tar I raised will be fixed in a later release. Incidentally the bug that is being fixed has as a work around Don't use dbms_metadata.get_granted_ddl to return system grants. I don't know about you but that feels a bit like saying don't use Create table ... to create tables.

The current problem that I have is that GET_DEPENDENT_DDL is also erroring out, and in this case the problem isn't that no objects are dependent upon this schema (I don't know what the problem actually is yet).

All in all not very satisfactory.

There was a discussion over on the Dizwell Forum regarding ASM, which as discussions tend to do moved on to Oracle Managed Files and the fact that you don't need to specify file name or file size anymore.

I felt that this didn't quite go far enough.


C:\Documents and Settings\user>oradim -new -sid demo
Instance created.

C:\Documents and Settings\user>orapwd file=e:\oracle\10g\db\database\pwddemo.ora password=demo entries=5

C:\Documents and Settings\user>set oracle_sid=demo

C:\Documents and Settings\user>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Fri May 27 14:21:58 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to an idle instance.

SYS@demo>startup nomount
ORACLE instance started.

Total System Global Area 788529152 bytes
Fixed Size 790936 bytes
Variable Size 204467816 bytes
Database Buffers 583008256 bytes
Redo Buffers 262144 bytes
SYS@demo>create database demo;

Database created.

SYS@demo>



No need to know what the files are, what the users passwords are, etc etc.

Ever read the contents of $ORACLE_HOME/rdbms/admin?

Loads of good stuff in there. Right now I'm evaluating dbms_metadata - and sadly coming to the conclusion that you don't want to use it to dig out schema ddl.

So I've logged at least one tar ORA-31608 when extracting grant information doesn't mean I errored out, it probably means no grant information to return.

In the mean time Ive dug into catmeta.sql and dbmsmeta.sql lots of interesting stuff in there. Interesting in the sense of Oh thats what that view does and interesting in the sense of oops had to withdraw that bit while the XDK was fixed. Anyway given a bit of R & D time read some of the header files in $ORACLE_HOME/rdbms/admin It'll be more interesting and understandable than you think.

I think I'll leave the last word to catmeta.sql


-- view to get a simple set of column attributes.
-- NOTE: Originally we had just an attribute called 'name' that was either
-- c.name or attrcol$.name selected via a DECODE of c.property bit 1.
-- However, for an as yet unexplained reason, this causes a full table scan
-- on col$ in outer views that used this view.


don't you just want to know why?

I just noticed today that Sue Harper, who is the Designer product manager at Oracle corp has a designer/jdeveloper blog going here. It looks like this will be a prime site for developers and architects using Oracles development tools.

Thanks to the surfer who visited here looking for a picture of Rachel Carmichael (yes, she's beautiful; no, I don't have pictures), I rediscovered her top 20 mistakes a dba can make on search Oracle(registration required). If you are interested I've only made 9 of them, I feel quite smug really. (Number 10 tomorrow I expect).

Anyway mistake 19 reads
Don't script anything, and don't save or document what you've done -- you'll never need that stuff again anyway.Famous last words that could turn into your infamous last day.

which is all well and good. I however have spent 3 extraneous days rebuilding a test system from live following all the scripts the guy who originally did the rebuild made. That's 3 separate imports of the same inconsistent database export. grrrr. So my corollary or expansion, make sure that you document in the script what the script is doing and how and why and make damn sure that if you run it repeatedly it prompts for the things it expects to change each time, and fails if you don't supply them.

If you are anything like me you generate a lot of sql scripts. Then when you come to reuse them you vaguely remember that you had a script that did this but can't remember what it was called or where it was located. In my case I wanted to find the wrapper for DBMS_METADATA to generate schema ddl that I wrote earlier last year. One of the enhancements that Windows XP Search brings is that it no longer seems to be able to find anything! This is due to a change in behaviour from previous versions of windows that treated all files as plain text, unless it knew that is was of a different type. XP now does not search files unless it has a handler that knows about that file type. .sql is not a file type that has a handler associated with it and so, by default, all your scripts become unsearchable.

It turns out that this is fixable. What is needed is to add a handler into the registry so that the free text filter will process .sql files. This is done as follows.

Open the registry

Navigate to HKEY_CLASSES_ROOT\.sql

Add a new key PersistentHandler

Set the value of the default string to {5e941d80-bf96-11cd-b579-08002b30bfeb} which is the classid of the text filter

This can be seen in the screenshot below



Having done this of course and found my script I ran into bug 3208903 which meant that my script doesn't work.

Bug 3208903 is an interesting bug. If you run a query documented in the DBMS_METADATA documentation you run across an ORA-31608 error. This is closed as not a bug. It seems to me that at the very least its a doc bug.

Regular visitors will have noticed a colour change, from Orange which my friends over at the Dizwell forum universally hated, to blue on pale yellow (that's yellow Howard, not Pink :)). This change has met with, as the saying goes, a mixed reception. I still need to fix the right hand side bar, so any feedback on how horrific the site now looks - or alternatively how refreshing it is to see a dba with a nice looking site - is warmly welcomed.

Those who really liked the orange are warmly invited to spend some time at the Portadown News, it helps.

A throwaway comment over at the rather excellent Dizwell Forum, let me to think that I'd be meaning to reflect on the vanishingly small number of female DBAs for a while. This is part, of course, of a wider problem of representation of women in IT, one that Governments are recognising.

I've been fortunate to either work with, or know a few women who do work as DBAs or in IT generally, but if numbers posting in various Oracle forums or attending conferences is anything to go by these women are the exception rather than the rule. My take on this is pretty much that poularized by the book The Wisdom of Crowds, that is that a culture that embrace diverse backgrounds and gives voice to each of these tends to make smarter decisions than one that doesn't. Its one reason, in my opinion, for the success of modern day America, a diverse culture succeeds more than a mon-culture. The same it could be argued was true of the British Empire, the Roman Empire and various other successful cultures throughout history.

IT today however is, largely, a collection of geeky males. My friend Mogens Norgaard is fond of saying that all the DBAs these days are grumpy old men; this isn't quite true, people like Tanel Põder and Lisa Dobson prove otherwise, but its true enough that most DBAs reading this will at least smile in recognition.

Geeky, grumpy men turn out to be quite good at discovering and debating facts, holding forth, doing rigourous systematic work, and this is good, but it isn't all the DBA job needs (it also goes some way to explain the occasional intemperate and ill thought out exchange in the DBA world). It looks to me like the technical 'engine room' role of the DBA is likely to expand over the next few years into application integration, code review, systems design and other information management challenges that have at their core enabling people to use systems to do their jobs better. This sort of challenge is often expressed in the IT world as aligning IT with The Business, as if The Business were this remote external entity. If Men are from Mars.. has any value, and I think it has some, this dismissal of work colleagues as somehow other is an incredibly male, and restrictive and counterproductive viewpoint.

So I want more female DBAs and for the following reasons.

Female opinions will add to the effectiveness of the DBA Community

A more relationship oriented DBA culture will help promote key communication lines, to this strange entity called the Business and indeed to other key groups Developers - ed

We might get healthier debates with less name calling and more substance

I'll leave you with this thought, consider the amazon.com editorial review of Oracle DBA 101 below and ask yourself this, do you think that the attributes of the book and its usefulness for the productive DBA are unrelated to the gender of its authors?

Oracle DBA 101 offers a friendly place for budding Oracle professionals to learn critical database management skills. Refreshingly, the book's entertaining style doesn't preclude the authors from discussing advanced concepts.

The text opens with a discussion of everything an Oracle DBA is expected to do. It moves quickly into the particulars of the Oracle architecture, all the while maintaining a comfortable writing style that makes easy reading of material that is, because of its technical nature, very dry. It also includes excellent discussions of the valuable inner workings of Oracle such as the V$ views and tools such as the Optimizer and Explain Plan. This section, along with a later chapter on performance, provides techniques for diagnosing the causes of even mysterious performance symptoms. A chapter on backup and recovery is included, but it is fairly brief.

Oracle is a very complex product, and this book doesn't attempt to make a seasoned pro out of the reader. But it does provide a fine balance of big picture perspective and internal details to enable new DBAs to hit the ground running. --Stephen W. Plain

Topics covered: Database layout, installation and configuration, tablespaces, System Global Area, monitoring, DBA and V$ views, SQL*Plus overview, Optimizer, Explain Plan, TKPROF, Autotrace, performance tuning, backup and recovery.

Steve Vandiver, MAOP newsletter, April 2000
"...I like this book because it is very different than all the other technical books on the market."

Tom Kyte posted an interesting discussion about Rules of Thumb (ROT). One of the lines in it that could have made my hackles rise a bit was

It all depends. Think of some of the ROT you know about topics like gathering statistics, reorgs, access paths (there are people that will never use NOT IN, there are people conversely that will never use NOT EXISTS - because they had a 'bad experience' with one or the other. Neither is evil once you get to know them).

Why didn't it, well the whole tone of the piece is, for me, summed up by the following quote right at the start

I say, it depends. <snip> ROT in the hands of someone with experience, with knowledge, with lots of background - very useful. They understand when and where the ROT applies.

Emphasis mine.

It depends is one of the most overused sentences in the database world in my experience, It depends is fine so long as the speaker can say upon what does it depend. This sort of it depends is fine.
Its the It depends as a paraphrase for It might work, it might not, don't really know but I had this cient once who had great results .... that is a great evil. So next time someone says It Depends please reply, upon What? Remember in Maths (remove the s if you are American) It depends can be translated as x = f(a,b,c) if a,b and c can't be defined then you have a sure fire piece of woolly thinking.

Something that occasionally bugs me is the habit in our industry to want to throw out all the old rubbish and start over. This time we'll do it right. I think I first noticed this phenomenon in respect of our Intranet development, for a while there it seemed like it would get a new redesign about every 6-9 months. This usually meant a revamp of its appearance and moving everything around so no one could find it for a while. Then, when Oracle 9i came out RAC was all new and nothing to do with Oracle Parallel Server (in this case of course it was deemed important that it appear to be new rather than actually be new.

Anyway, I had a little example the other week of why this rarely works. Take a peek at Ocado, a uk online grocery you'll see the usual sort of story 'avoiding legacy issues', 'build a business centered around the customer' and so on. The usual pattern in the UK is that existing grocery stores take orders online and supply from the nearest (largeish) retail outlet that they have. This means that you may not get what you ordered - because it wasn't on the shelves of that store - and that the effectiveness of the system depends on a local store manager. Ocado don't do this - they don't have stores. They have a very nice website (skinnable even), and deliver from their warehouse stock (so that what you order is what you get). So we got a promotional flyer and a money off voucher so we decided not to use our existing supplier but see how they did. It wasn't good, the driver rang me just after he'd missed his delivery slot to say he was 3 hours late and that I would have to ring customer service to re-arrange. When I did, customer service didn't answer the phone for 47 minutes (at which point I gave up). I then emailed customer service, cc'ing the operations director. I received this reply at 6:50am the next morning.

Thank you for your E-mail.

This is not how our service is meant to work and I am dismayed at the lack of service that you received.

I am going to investigate what happened to your order last night and will contact you later today when I have some answers.

So this new service set up to avoid

legacy

issues managed to

Miss agreed delievery dates

Be unable to deal with customer service requests

Have senior management promise, possibly rashly, and not deliver

In other words it looks an awful lot like the existing services built on legacy systems - except worse.

What if anything has this to do with Oracle and the IT systems we use, support and implement? Well it seems to me that the urge to do everything new in our systems has two main issues.

We assume that we will do it right first time, next time

We don't deal with environmental issues

Joel Spolsky has a great article addressing the first assumption. The second assumption is what we in IT like to call the business, I hate that term by the way as it implies that we work for some other organisation (yes outsourced people do), Its hard doing customer service as Ocado are presumably finding, starting from scratch doesn't change this fact. Equally in almost every problem that is worth automating to some degree, its hard to do the underlying business process, just starting over - especially if you see more than two current industry buzzwords involved - seems to me a warning sign that the problem is perceived as being software, bt is almost certainly a 'soft' business problem.

Its tempting to write about Mike Ault's myths paper that appeared in Search Oracle today (registration required), but I won't because others have covered much the same ground elsewhere.

Instead I've been meaning for a while to go back to the scientist thing.

Despite being a part of the OakTable Network, I don't regard what Oracle practitioners usually do as Science. Instead I regard it as Engineering. My Dad who worked with big machines and everything would laugh uproariously at this notion by the way.

here's why. Science to me is the rational process of discovery about subjects (usually the Universe or stuff in it, but almost certainly extensible to human behaviours - the Social Sciences). In theory it goes like this

Observe behaviour.

Conjecture possible explanation (form a hypothesis).

Conjecture possible experiment to disprove hypothesis.

Perform experiment.

If experiment disproves theory reject hypothesis and go back to stage 2

Else go back to stage 3

(In practice of course the experiments get repeated several times with twists and generally the old hypothesis is rarely really rejected until its most vocal proponents die, though it maybe amended significantly)

Its interestingly one of the reasons that I'm not sure string theory counts as science - I'm unaware of testable predictions that it makes, its seems to be more or less a number of different computer simulations of mathematical models of stuff at unmeasurable scales. (I'm longing for someone to correct me here)

Now there is a case for this type of work sometimes. It is a little bit what lay behind my scripts to dump arbitrary index structures to disk, I maintain that b*tree index structures are always height balanced (not really a great hypothesis since its in the name and the documentation). If there is anyone left who believes they aren't just find me one that isn't.

Engineering on the other hand I define (and dictionary.com largely agrees) as the application of scientific discoveries and method to practical effect. So the bridge builder relies upon materials science and other physical sciences when designing bridges and as a result generally bridges are robust safe structures, though there are exceptions when something gets overlooked.

The last thing I think we need as groundwork is my mental definition of the scientific mindset, This mindset has four great attributes

its intensely curious

Its logical

its, if not creative, then imaginitive.

it always craves evidence

In other words it doesn't necessarily go out to observe behaviour, formulate explanations and test hypotheses. Instead it is inclined to take a similar approach to whatever practical issues it is faced with.

The child asking why the sky is blue is my classic example of this, She's curious about something, she can imagine that the sky might be say green or yellow, shes pretty sure that there is an explanation that she can understand and when an adult says because it is she isn't satisfied.

Now it should fall out pretty quickly that what I am saying is that what I consider we as Oracle practioners (or SQLServer or PostGres or Java or whatever product we use) should be doing is adopting the scientific mindset, not necessarily doing the research, but being curious about what principles have been shown to be appropriate to our class of problem, of being imaginitive in how we select tools and technologies to apply those principle, of being strictly pedantically logical in how we design and of always seeking hard, repeatable evidence to justify our solutions. Its not science (its engineering), but it is scientific

Mark Rittman, whom I am now even more jealous of because he now gets sensible connectivity on his local train "service" whereas I get none at all and no prospect of any on First Great Western and pay a fortune for it, points out an article by way of /. which may well have the DBA's (or reactionaries as the article has it) spluttering over their keyboards.

The article, which is a collaboration by a Distinguished Engineer on the Microsoft research staff Jim Gray and a marketing consultant Mark Compton, looks at the future of databases. My reading of the article is that its take on the future of databases is that they will abandon their basis in theory and design and become hosting environments for complex applications. Ok that's probably an unfair summary, but it does read like that. Now this maybe the way the marketing and feature add for RDBMS is going (host Java or C# in the db, the streaming and queueing interfaces, the XML additions and so on), but it sure makes depressing reading. I can't wait to see what Fabian Pascal makes of the paper, though I see he has a quote from the thread on the main page currently.