How to write messages to the Oracle alert log and custom trace files - dbms_system.ksdwrt

>> Sunday, 24 April 2016

I've been finding this useful on occasion because I have plenty of jobs reading and reporting on alert log errors, so it's saved me having to think of another way to yell "hey DBA" :

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');


exec dbms_system.ksdwrt(2, 'ORA-09999 hey you wake up and investigate this');

You can put a wrapper around the call and grant execute on the function:

create or replace function write_alert_log(log_or_trace in number, text_message in varchar2) return number

  return 0;
  when others then
  return 1;


Talent and intellect is never displayed in disparagement (aka the worst flashlight I have ever owned)

>> Sunday, 31 January 2016

A simple statement appeared on twitter 

"This feature request scares me, but I reserve the right to be wrong."
This request was to allow sql statements to be run on multiple databases at the same time through a tool.  So a bit like OEM allows you to do multi database reporting or even patching.  Bearing in mind that anyone connected to a database via a tool will only be able to do what they actually have access to do, I don't really see this as a problem.  My response was:

"With privilege comes responsibility"

 I can have a staging server that can attach to multiple databases, I can write a simple shell script to run through all those database and execute whatever I like within the access privileges of the user I connect with.  Simples.   I could create a patch plan to patch an entire estate at the same time.  No problem. I could write a report to run on a production group of databases that does *this. (Hold that thought in mind, it will resurface.)

Some of the twitter circle within which I roam decided that 'people' would destroy the planet with such dangerous functionality:

"If it was my product I'd be worried about giving 'em so much rope" 

So who gets to decide the length of rope the common DBA can hang themselves with? My feeling is: the access level they are granted - nothing like an rm -r * as the oracle software owner to guarantee a long night ahead - who allowed the Oracle DBA access to the server they had to install the software on?! Fools, fools everywhere!

I think that the role of experienced people within a community, if they choose to engage, is to offer advice and to share experience but not to judge.

"We should judge" 

I wonder if perhaps more practical comments might be "experience has shown me this could easily cause problems" or "this sort of functionality should be fairly hard to 'switch on' or have high clarity"

But the general ensuing conversation was around: 'people' are idiots, 'people' would break stuff with it.

Would they?  All of them? Some might. But then but some people will also chown -R oracle:dba * on a production server at \  (Got root?! How he lol'd as he rebuilt the server ... not) 

But is it right to say you can't have potentially useful functionality because you could potentially break something with it?  

"..that's what people asking for {the} capability will do.." 

Will they?  Some might get bitten but if something is well designed then mistakes are less likely and people who forge ahead and do something that is a bad idea....well shit happens and we learn by mistakes (or preferably practise in a sandpit) not by being protected from ourselves.

So back to *this 

 "The relevance is, that's what people want to do to their 60 db's simultaneously"

Do they?  Maybe they want to do something useful and productive too.

"My experience has shown me that people do this

Not with this tool, because that functionality isn't there.

And I suspect it never will be there, but that's ok because my answer to the person asking for it would be: there's more than one way to skin a cat and have a look at your other options for running actions on multiple databases if that's what you need to do.

But to the experienced, highly skilled, highly talented people out there:  Who made you the idiot police?

Show me someone that's never screwed up and I'll show you someone that's never pushed themselves or worked dog tired under stress. On huge estates wrong services or servers sometimes get shut down. Admins are flogged in public or occasionally dismissed.

Should Jonathan Lewis have been considered unsafe to be let loose with sqlplus because he ran a 'bad' query. Should the idiot police say 'this is what people will do if we allow them to'

No of course not. And Jonathan himself says about it 'hey world look at this, my bad, probably best if you don't do the same' by sharing his experience. (His comments about the poor quotes and the part reproduction of the shared information is a side issue).

I think it is usually best not to say 'why on earth do you want to do that you fool?' but to say 'what do you want to achieve here?' (This has generally been the art of response to worrying questions on Ask Tom that was Tom and seems to continue now)

Sometimes we have to remember the very real pressure of a shouty manager saying they want some information from every database by 4pm today and that so often, when the most bizarre JFDIs come in, there is no option to say 'well that's dumb' but we just have to work out a way to do something with least damage all round.

Sometimes the real world pressures can make questions look foolish and sometimes the true talent of an individual is the way in which those 'foolish' questions are answered.

But true talent and intellect is never displayed in disparagement.

But back to tools:  "TOOLS DO WHATEVER YOU USE THEM FOR"  (Special thanks to the ever gracious @thatjeffsmith for that link balm)

And some people will use them in a way that will make you face-palm

And others will never fail to surprise you.


My experience of UKOUG TECH15 (UK Oracle User Group Technology Conference 2015)

>> Wednesday, 16 December 2015

This year's UK Oracle User Group Technology Conference took place in Birmingham.

I went on the middle day of the 3 (4 including super Sunday) day event.

The agenda is huge on every day.  There are 7 streams: Database, Apex&DB Dev, Big Data and Warehousing, Business Analytics, Engineered Systems, Integrations and Collaboration, Operating Systems, Virtualisation, Web&Mobile apps, Keynotes and General Cross Topic.  And within those streams there are different presentations happening all day, each day - that's a lot to choose from.

So remembering that Apps15 and JDE15 are also happening at the same time you start to get a feel for how huge it is.

In fairness I rarely use this word's awesome (!)

I sat down with a print out of the streams I was interested in and a highlighter pen and planned my day in advance.

I plumped for:

Women in IT Breakfast : Debra Lilley and Evolution
Advanced ASH Analytics ASH Masters:  Marcin Przepiorowski
Keynote: Oracle Database 12c What's next?
Oracle Flashback: Connor McDonald
Adaptive dynamic sampling : Christian Antognini
Interpreting AWR Reports:  Franck Pachot
Just Don't Do it: Jonathon Lewis
Evolution of performance Management: Oracle 12c Adaptive optimization: Nelson Calero

The mobile app for the conference works well, you tell it your agenda and it tells you where to go and when, simples.  The full agenda is on it to look through, the speaker lists and you can leave feedback for the presentations through it.

It was a full day 8am to 5:30pm with little break.  I admit to being brain fried by the end of the day.  Whilst all the presentations were worthwhile, I have to say that Connor and Franck made the day worth its weight in membership fee and travel time.

I find it hard to explain the value of this type of training...yes, it is training.  Some of it is sound bite, some of it is in-depth, all of it excites me, all of it motivates me to want to use these things in my day to day work and yes, they motivate me to work smarter....I suspect most people attending feel the same way.

I'm not sure you could keep up the level of presentation attendance I did in one day for 3 (or 4) full days but there are other 'diversions'.

There was a set of challenges through the 'conference explorer' for each day to enter a prize draw (and get scores on the doors), some involved visiting exhibitor stands, getting selfies, tweeting stuff, finding stuff in the app, meeting new people.  I quite like things like that.

The loudest exhibitor stand seemed to by the golfing area that had putting for pounds.  Clever draw.  As popular as the Scalextric a few years ago.

There was the drinks in the conference hall after the days work and that was followed by the Big Birmingham Bash.  I didn't stay for the bash and I know very little of it,  I suspect what happens in Birmingham stays in Birmingham!!

 Badges have become quite the thing and there were many 'in house' and 'out of house' ones to be collected.

Part of the draw for me for the UKOUG is the incredible community it is.  I now know people that go to these events I am proud to call my friends.  Of course the good and the great are there to be spoken to (So many ACEs, Masters, OakTable members) but also the people I talk to a lot on social media (there may be some cross over there!).  The people that help each other as problems present themselves across the year.  The person that responds to a twitter beg when you really can't find something in a manual, the ones who write all those great blog posts you've been cribbing off all year.  It's a great opportunity to give them a hug/whiskey/wide berth* (delete as appropriate based on last interaction.)

As I said to one twitter friend, you'll spot me I'm playing 'Where's Wally' but in monochrome!

One year I swear I will get there for the full conference! Now that really would be quite awesome but I think I would have to go for the full red and white stripes.

If you are a member of the UKOUG and get a conference pass but don't use it: huge mistake - no, seriously - just go - get that training and networking in.

If you aren't a member of the UKOUG you should look at joining.  If you are working for a company there are many ways you can talk to them about justifying the cost of membership.

For me this one day taught me that we can use Flashback data archive now and it is a good solution for some of my archiving woes.  I found out I knew more about Sharding and other up and coming changes than the guy from Oracle that comes into my company to brief us on new features. I got more confident about the way I interpret AWR, and I have already seen Adaptive Sqlplans in explain plans I've been looking at and thought "ahhhh I know what you are and how you got there!"

It's all good.


  © Blogger template Simple n' Sweet by 2009

Back to TOP