jump to navigation

Friday Philosophy – Content, Copying, Copyright &Theft February 12, 2016

Posted by mwidlake in Blogging, Friday Philosophy, writing.
Tags: , ,
13 comments

There have been a couple of things this week that have made me think about the stuff that some of us write and what other people do with it.

I’m writing a book with 4 other people at the moment (the 4 being Arup Nanda, Brendan Tierney, Alex Nuijten and Heli Helskyaho, all experienced book publishers already – I’m the new kid) which is on SQL & PLSQL. It has been a very interesting experience. I knew writing a technical book was hard work, took a lot of time and that, frankly, the direct financial return on the effort is very, very poor. I know a few authors of Oracle books and I’d talked to them about it all, so I was aware. However, it turns out I did not really know how hard it was, I still did not understand how demanding of time and effort it was! But I had written technical blogs and a couple of articles before I started the book and I had developed the strong opinion that you do not take other people’s work, and you certainly do not take it without citing the original author – because you are actually stealing a lot of someone else’s time and effort.

Probable  front image of "the book"

Probable front image of “the book”

As a result, at the very start of writing my chapters I was determined that my content was going to be My Content. Me, my experience, the official documentation , my test databases – and a word document to receive the end product from those ingredients. I was not going to read what others had written recently on or around the topics I was covering as I did not want to be even subconsciously borrowing from other’s efforts {I say recently as I cannot unread what I had already read!}. I certainly did not want to be accused of doing so. If I was going to object to people stealing my content, I’d be hypocritical to actually commit the crime.

How very noble of me. How very silly of me.

A couple of months in I was talking to someone about the first chapter I was doing and how I was struggling to decide how to structure what I wanted to say. I knew the facts and features I wanted to cover but was unsure of how to make it flow so that it would make sense to the reader and build up their knowledge in steps. They asked me how other people had handled it and I gave them the little opinion piece I’ve just given you. And they laughed at me.
Was I including new stuff? Yes. Was I using my own experience? Yes. Was I going to cut lines, paragraphs, even pages out of other sources and put it in mine? No! Of course not! Well then why was I purposefully making life hard for myself?
Then they asked me the killer bit – Did I know every last thing about the topic? Hmm, no, probably not, but then no one knows every last thing and certainly has not used every little aspect of an oracle feature for real. So I was only going to put into my chapters parts of the topic? Well, I guess so. And that is what someone trying to learn about the feature wants? An expert opinion full of holes? That bit stumped me.

I was kind of writing my chapters to show how much I know. I was certainly limiting it to what I knew well. But the reader does not give a fig about how much I personally know, they are not hiring me to do a job. They are reading about a technical topic so that they can do their job. So I should be making sure I know as much as I can about the topic in order to describe it and I should describe all of it that I think could be useful to others, even if so far it has not been of use to me and the specifics of the problems I was solving. And how do I learn about technical stuff? I read the documentation… and blogs… and books… and play with it.

It also got me thinking about what I will feel like if people use my chapters in a couple of years to help them write about a topic (be it in a book, a blog or an article). If they simply copy my stuff, steal my words, I’ll be angry. If they copy it but just change a few bits to hide the fact I’ll be furious. But if they are writing this as they initially learned from me and then added their own experience and knowledge, I’ll be chuffed to bits – because I taught them. And now they would be teaching others.

So I started reading my modern books on the topics around what I was writing and looking at blog posts and articles more. I know I am doing a better job for the audience since I started doing that. However, the list of people I will need to thank in my bit of the acknowledgements is going up & up and I suspect that for years I’ll be meeting people at conferences & meetings and going “here’s a pint for the help you gave me! And, no, you did not know you had!”. {One thing that did worry the pants off me is that when I read around, it turns out that in my first chapter I uses an example very extensively that turns out to be the exact same example at least two other people have used – it’s convergent evolution, honest! But I’m sure someone at some point is going to point a finger… Oh well, the deadlines are too tight for me to change it now. I don’t even have time to write this blog really…}

There was a specific incident this week that made me think again about copying. I noticed (as I was checking out a relatively unused aspect of a PL/SQL tool and what I did not know about it – but others might benefit from knowing) that the same information was in two places. Exactly the same, word for word. Someone had stolen content from Tim Hall’s excellent Oraclebase site. And it was not just one article, it was dozens, with no citation of the original author anywhere and a copyright sign on the pages of stolen content. You can read about Tim’s ire in this blog post he wrote. He got more annoyed than I think he normally does as this guy had stolen stuff before and Tim was suffering from a cold. He got about as annoyed as I would get in that situation, in fact.

I also noticed as I investigated my currently-obscure aspect of PL/SQL that most of the content on the topic elsewhere was mostly chunks just taken from the oracle official documentation with a few lines wrapped around each chunk. Was that stealing content? I’m still not sure about that, but I think that if there is more borrowed content than original content, it’s at best Poor Effort and probably is Theft. If they do not even write their own demo code for the feature but take Oracle’s – it’s theft. Bad people.

I did nearly comment on Twitter that I never got my stuff stolen, as my stuff is mostly just opinion pieces like this and of no technical worth! But the very next day – Yep, you guessed it, someone stole one of my blog posts. There was a single link back to my original post at the very end but it was not a citation, it just said “reference Link Martin Widlake’s”. In fact, initially I think it just said “Reference Link”. He also has a copyright sign on his web pages. I currently don’t, maybe I should add one so that I can simply say “copyright, take it off else i’ll issue a Take Down request to your service provider”.

I’ve emailed him to say I’m not happy to have a word-for-word copy stolen and presented as his and I am certainly not happy that the pieces is appearing on the front of his web site advertising his services! It seems he is just one guy trying to make a living in rural Northern Pakistan. Should I be concerned about the theft of my article and ask him to remove it? If it is helping him make a living thousands of miles away and he has at least added a small citation at the end? Yes, because it is still theft. And if I do not highlight to him how much this annoys people, he will probably steal other stuff. If you don’t challenge bad behaviour you condone it.

And besides, if he does steal more stuff this will certainly include Tim’s material as his site is often on the first search-engine page on any Oracle Topic. And when he pinches Tim’s stuff, Tim’s gonna be angry…

Friday Philosophy – Database Performance is In My Jeans February 5, 2016

Posted by mwidlake in Friday Philosophy, humour.
Tags: ,
1 comment so far

Database performance is in my jeans. Not my genes, I really do mean my jeans – an old pair of denim trousers. I look at my tatty attire keeping my legs warm and it reminds me of Oracle database performance.

comfortable, baggy, old, DW jeans

comfortable, baggy, old, DW jeans

You can buy jeans in a range of styles & sizes. Just as you can set up your database in a number of standard ways. When you create a database with the install wizard or the DBCA (database configuration assistant) you get to pick from a few options. OLTP databases are like skinny, butt-tight jeans that fashionably young things might wear. I’m more of a Data Warehouse type. I like lose, baggy jeans with lots of space. However, no matter how good the initial setup, performance will degrade. Your jeans will get stretched, stained, more baggy and generally tatty. But you also get used to the performance of your database, it’s oddities and how to live with them. Your baggy, saggy jeans become comfortable.

I'm a dab-hand at doing turn-ups and SQL tuning

I’m a dab-hand at doing turn-ups and SQL tuning

Of course, you probably need to alter your database somewhat to suit you performance requirements. You could go to a tailor to get them done (pay a consultant) and make your jeans a top-notch fit but it’s expensive. Or ask the shop to alter them when you buy them (get some oracle consultancy as part of the purchase deal, to do a pretty average job of changing things). Or, if your requirements are specific (I can never get trousers with a leg length to suit me for some odd reason) and you have your have some skills (I can drive a sewing machine and, if needed and I have time, I can hand-sew) then you can tailor your jeans to your needs yourself. Little changes like this are like a bit of SQL tuning. Hand sewing is messing about with trace files.

You fix one performance bottle neck only to find the next one

You fix one performance bottle neck only to find the next one

Of course, over time more major performance issues will occur and the cracks will show. Well, tears. Bits of the system will give way and you’ll have to patch them. Sometimes the patch is a bit of an obvious cludge, but heck it does the job. The other option is to just live with the gaping knee, which is like not fixing your performance issue and just letting your knee get cold. My business requirements don’t allow for this, I need my knee covered and protected from the brambles and spiky stuff around the garden. And just like performance tuning, you fix one performance problem only to reveal the next point of weakness. The point of most stress in my jeans are the knees, what with all the gardening, crawling through hedges, kneeling in the dirt and grovelling to the wife. I patched that big tear across the knee – and within 2 weeks a new one started, just a little lower. You fix your critical batch load that is doing too much physical IO and now your problem is redo generation in the next step! I did not fix my performance bottle neck, I just moved it down the damned leg!

All those little tears needed a lot of fixing

All those little tears needed a lot of fixing

Many of us get tears in the knee of our jeans, it’s a common performance problem. But some performance problems are more esoteric. Not many people have had to patch the bottom of their jeans due to doing battle with barbed wire (and losing). I could do with self-healing jeans to match the self healing leg. I suppose with the latest dynamic performance tricks in the optimiser, we sort-of have self healing databases. I tried patching it with just the sewing machine but the damage was too great and so a swatch of fabric behind the area and a craze of zig-zag stitch is holding it all together. Maybe that’s like using row-level-security to allow different customer to see just their set of data. It works but it was a tad over-engineered.

Of course, over the years the requirements for your database and it’s performance are likely to vary and you might need to do more than a bit of sql tuning or tweaking of indexes. The sewing machine can’t fix all the problems with my tatty old jeans, especially as the workload first grew, shrank, and grew again. I needed a new performance enhancement tool. A belt. It’s stopped them falling down around my knees and also stopped them from cutting off the blood to my legs, depending on how well I’ve done at archiving off excess calories I no longer need.

Addition of a Modifiable Girth Control device

Addition of a Modifiable Girth Control device

The sad thing is, despite all my hard work, I think I’ll have to pension off these jeans soon. Just like computer system I’ve looked after for a while, I know where I am with them and I’ll miss them when I do a hardware refresh.

So there you go. How many of you thought that you could be reading about a tatty pair of jeans this week? I’m good to you lot.

Friday Philosophy – If Only I Was As Good a Programmer As I Thought I Was Aged 22 January 29, 2016

Posted by mwidlake in Friday Philosophy, humour, Perceptions, Programming, Uncategorized.
Tags: , ,
6 comments

I saw a tweet that made me smile a few days ago:

programmer quote

Like many of you, I started out my working life in IT as a computer programmer. Like some of you, I still am a computer programmer from time to time. I don’t think I’ve had a year of my working life when I did not do either some application development programming or some database infrastructure programming. I am constantly writing small (or not so small) SQL or PL/SQL programs to do what I do in Oracle.

I started programming in school, I did an “O” level in computer studies (the exams we sat in the UK aged 16, up until 1988!), and I was pretty good at the programming as compared to my fellow class mates. My first “real” program played Noughts and Crosses (tic-tac-toe to our American cousins and maybe others) and version 2 was unbeatable. Which at the time I thought was pretty cool.
but Wikipedia now tells me is pretty easy🙂. I also remember someone in the year above me unrolling some huge printout of the role-playing game he was writing (you know, the old textual “you have walked into a room where there is a lion, a bar of soap and a chandelier, what do you want to do?” sort of thing) and telling me I would never be able to do it. I just looked at the code and thought: Why have you hard-coded every decision and used all those GOTOs? Some sort of loop and a data block to look up question, answers and consequences would be much smaller and easy to extend? I don’t think he liked me voicing that opinion…

I did not do any programming of any consequence as part of my college course but after that I started work as a computer programmer (sorry “analyst programmer”) in the National Health Service. Again, I seemed better at it than most of those around me, fixing bugs that others had given up on and coding the tricky stuff no one else wanted to touch. And after a year or so, I was convinced I was a programming god!

I wasn’t of course. Part of it was my juvenile, naive ego and the other part was that, fundamentally, many of those around me were bad programmers. Anybody decent either did not join in the first place or got a better job elsewhere that paid more than the NHS did. I eventually did that myself and joined Oracle. Where I realised that (a) SQL confused the hell out of me and (b) when I started using PL/SQL there were plenty of people around me who were better at traditional programming than I.

I think it took me about a year to feel I was damned good at both of them. Guess what? I was wrong. I was simply competent. But after a year or two more I did two things that, for me, finally did make me into a good programmer:

  • I went contracting so I worked in a lot of places, saw a lot more examples of good and bad code and I met a lot more programmers.
  • I think I hit mental puberty and woke up to the fact that I needed to listen and learn more.

Since then, I think my own opinion of my coding skills has generally dropped year on year, even though I would like to think I continue to get better at actually constructing computer programs and suites of programs.

So yes, I wish I was as good a programmer now as I thought I was aged 22. And after 25 years at it (actually, pretty much 35 years at it on and off!) just like Rich Rogers (or is it John D Cook? I can’t quite decide if it is a quotation or not) I think I am finally getting moderately good at writing programs. If I continue to follow this trend, on my 65th birthday I will be convinced I can’t program for toffee and yet will finally be a Good Programmer.

I wonder if  anyone would still employ me to do it by then?

ORA_ROWSCN – When Was My Record Commited January 25, 2016

Posted by mwidlake in SQL.
Tags:
1 comment so far

I was going to do a follow-up post to my post on USERENV(‘COMMITSCN’) just to describe the slightly better known but still overlooked (and possibly more useful) ORA_ROWSCN – but I don’t need to!

As Neil Chandler has done this excellent post on it

Go and look at that post, it has all the information and detail you were actually looking for.

However, for any of you search-engine-seekers who can’t face the pain of following a link {it’s such a hard life for the modern knowledge-by-mouse-click generation}, here are the bare bones:

ORA_ROWSCN is a pseudo column that, by default, shows you the SCN (System Change Number) when the block was last changed. You can create a table with the ROWDEPENDENCIES extension to track it for individual rows but you can’t alter a table to add it. So usually you see a (conservative) last-change-scn for the block. See Neil’s blog post for more detail

To convert the SCN to a date/time there are various ways but Oracle will do it for you if it was within the last few days for you – with SCN_TO_TIMESTAMP function. If the row/block was last changed more than a few days ago, that function will error with ORA-08181 (I wish it just returned null rather than 08181, but then you can always wrap the call in your own function that handles that…)

Below is a short demo using test tables I don’t describe:

select house_number, addr_line_1,post_code
      ,ora_rowscn
      ,scn_to_timestamp(ora_rowscn) time_of_scn
from address 
where house_number = 100
and addr_line_1 like 'COTHAM SOUTH%'
/

  HOUSE
 NUMBER ADDR_LINE_1          POST_COD ORA_ROWSCN TIME_OF_SCN
------- -------------------- -------- ---------- ------------------------
    100 COTHAM SOUTH TERRACE SH5 8FA    11425626 24-JAN-16 20.44.56.00000
    100 COTHAM SOUTH DRIVE   LS20 1QY   11427281 24-JAN-16 20.51.29.00000
    100 COTHAM SOUTH         BD17 7JW   11437843 24-JAN-16 20.53.39.00000
    100 COTHAM SOUTH TERRACE LS7 9SK    11448376 24-JAN-16 20.54.56.00000
    100 COTHAM SOUTH TERRACE LS16 4SW   11460162 24-JAN-16 21.20.29.00000
    100 COTHAM SOUTH TERRACE LS7 1GL    11461400 24-JAN-16 21.25.48.00000
    100 COTHAM SOUTH         LS20 1TO   11471921 24-JAN-16 21.26.53.00000
    100 COTHAM SOUTH DRIVE   LS1 5EJ    11471921 24-JAN-16 21.26.53.00000
    100 COTHAM SOUTH DRIVE   SG 3LO     11482461 24-JAN-16 21.28.05.00000
...

--However, if the change is more than about 120 hours ago...
select surname,first_forename,dob,ora_rowscn
     ,scn_to_timestamp(ora_rowscn) time_of_scn
from person where surname='KINOCK'
and DOB between sysdate -10000 and sysdate -9500
/

     ,scn_to_timestamp(ora_rowscn) time_of_scn
      *
ERROR at line 2:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

-- which is a bit misleading as it was a valid SCN, just not for the SCN_TO_TIMESTAMP function
-- remove the column based on scn_to_timestamp...

select surname,first_forename,dob,ora_rowscn
     --,scn_to_timestamp(ora_rowscn) time_of_scn
from person where surname='KINOCK'
and DOB between sysdate -10000 and sysdate -9500

SURNAME         FIRST_FORENAME  DOB               ORA_ROWSCN
--------------- --------------- ----------------- ----------
KINOCK          ABIGAIL         22-APR-1989 00:00    2518996
KINOCK          FRANCESCA       23-APR-1989 00:00    2539749
KINOCK          GIANO           10-NOV-1989 00:00    2567890
KINOCK          GILLIAN         11-JAN-1990 00:00    2716278
...

Look, you really should go and look at Neil’s post: click here

Friday Philosophy – Database Dinosaurs January 22, 2016

Posted by mwidlake in Friday Philosophy, Perceptions, working.
Tags: , ,
14 comments

I’m guessing many of you reading this are over 40. I know some of you are actually beyond the half century and a couple of you are….older! If you are younger than 40, just print out this and put it in an envelope marked “read a decade later than {current date}”. It will have become relevant for you by then…

beware the network admin

Beware the network admin – creative commons, Elvinds

So wind back your memories to those glorious days in your first role working with IT. For most of us it was half our lives back or more, when we were in our early 20’s or even in our teens. One of you was 18, I know, and I knew one guy who started as a salaried, paid programmer at 16. Do you remember those old guys (and occasional gals) you met back then? Often with beards, an odd sense of “style” and a constant grumbling murmur that, if you listened closely, was a constant diatribe about the youngsters “not getting it” and this UNIX thing not being a “proper OS” {fill in whatever was appropriate for the upstart OS back when back where for you}.

Don't annoy the DBA

Don’t annoy the DBA

You are now that person. I know, you don’t feel like it – you can still do all this technology stuff, you program better now than ever, you know how to get the job done and you have kept up with the tech as it moves forward. And you sure as hell do not look as weird as those oldsters did! Well I have bad news. You do look as weird as those old guys/gals to any youth about {and is that not a good thing, as most of them look a right state} and you have probably not kept quite so up with the tech as you think. You have to keep partly up-to-date as the versions of Oracle or whatever roll on, else the career becomes tricky. But as I’ve realised this last few weeks, you probably use old coding techniques and ways of doing things. This is maybe not a bad thing in you day-to-day job as these older ways *work* and doing it that way is quicker for you than spending time checking up the latest “time saving” shortcuts in the code you write. I’ve had that brought home to me recently as I’m working in PL/SQL at the moment and I am using some code I initially wrote back in the last century {I love saying that} as the basis of an example. It works just fine but I decided I should re-work it to remove now-redundant constructs and use features that are current. It is taking me a lot of time, a lot more than I expected, and if I was writing something to Just-Do-The-Job with slightly rusty ways, I’d have it done now. That is what I mean about it not being such a bad thing to use what you know. So long as you eventually move forward!

Of course it does not help that you work on a legacy system, namely Oracle. I am not the first to say this by a long, long shot, Mogens Norgaard started saying this back in 2004 (I can’t find the source articles/document yet, just references to them} and he was right even then. If you think back to those more mature work colleagues when we started, they were experts in legacy software, OS’s and hardware that did in fact die off. VMS went, OS/2 died, Ingress, Informix, Sybase and DB2 are gone or niche. And don’t even mention the various network architectures that we had then and are no more. Their tech had often not been around as long as Oracle has now. And I know of places that have refreshed their whole application implementation 3 or 4 times – and have done so with each one based on a later version of Oracle (I do not mean a migration, I mean a re-build).

Or the Sys Admin

Or the Sys Admin

The difference is, Oracle has had a very, very long shelf life. It has continued to improve, become more capable and the oracle sales & marketing engines, though at times the bane of the technologist’s lives (like making companies think RAC will solve all your problems when in fact it solves specific problems at other costs), have done a fantastic job for the company. Oracle is still one of the top skills to have and is at the moment claiming to be the fastest growing database. I’m not sure how they justify the claim, it’s a sales thing and I’ve ignored that sort of things for years, but it cannot be argued that there is a lot of Oracle tech about still.

So, all you Oracle technologists, you are IT Dinosaurs working on legacy systems.

But you know what? Dinosaurs ruled the earth for a very, very, very long time. 185 million years or so during the Mesozoic period. And they only died out 65 million years ago, so they ruled for three times as long as they have been “retired”. We IT Dinosaurs could well be around for a good while yet.

We better be as there is another difference between when we started and now. Back then, we youth were like the small mammals scurrying in numbers around the dinosaurs(*). Now we are the dinosaurs, there does not seem to be that many youth scurrying about. Now that I DO worry about.

(*) the whole big-dinos/small scurrying mammals is a bit of a myth/miss-perception but this is not a lesson on histozoology…

Getting Your Transaction SCN – USERENV(COMMITSCN) January 19, 2016

Posted by mwidlake in development, performance, SQL.
Tags: , , ,
2 comments

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it so I asked Jonathan and he pointed me straight to this post he did about it a while back. What a nice chap. However, the post is from 1999 (last CENTURY!) so I thought I should just check it out first…

I’ll say up front that this is an undocumented feature of a now-deprecated function. Well, almost undocumented – older SQL reference manuals mention that ‘commitscn’ returns a NUMBER as opposed to the VARCHAR2 returned by most parameters you can use with USERENV, but it does not list it as a valid parameter for that function.
USERENV has been deprecated since Oracle 10g (see the old 10g documentation link here about USERENV) and you have been instructed to use SYS_CONTEXT(‘userenv’,’parameter‘) as described in the 12c database SQL reference manual here. However, there is no way to get the commit SCN from SYS_CONTEXT that I can find, so I thought I’d check out if USERENV(‘COMMITSCN’) still works. It does, on my version of Oracle 12.1.0.2!

There are some strict limits to this function. To begin with, you can’t select it, you can only use it on insert/update:


-- attempt a simple select of the SCN
mdw> select userenv('commitscn') from dual;
select userenv('commitscn') from dual
               *
ERROR at line 1:
ORA-01725: USERENV('COMMITSCN')  not allowed here

--But I can use in an insert,
mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (100,'abcd',userenv('commitscn'))

1 row created.

mdw> select * from test_scn where seq_no=100

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
       100 abcd         11144739

-- Now commit my new record
mdw> commit;
Commit complete.

mdw> select * from test_scn where seq_no=100
  2  /

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
       100 abcd         11144753

--LOOK at the value for SCN_NO now! Compare to before the commit!

If you look at the above you will see a couple of things. The first is that, as I said, you cannot SELECT the function USERENV(‘COMMITSCN’).
The other is, though a value is put into the column when I insert a row using it, and I see that when I query the information back, it changes when I commit. This is because Oracle is recording something at the point of commit, not at the point of the SQL statement running – and the new SCN is only generated when you commit. A lot could have happened since I did the INSERT, I might have gone for a cup of tea and a batch job kicked off doing 1 million transactions, each with it’s own SCN. So though a placeholder of the current SCN is put into your view of the table row, the value put in the actual table is generated at the time of the commit.

Another limiting rule is that you can only reference USERENV(‘COMMITSCN’) once in a transaction, for one row. If I try and create 2 rows using the function in the same transaction I get an error, if I try to update more than 1 row I get an error:

mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (101,'abcd',userenv('commitscn'))

1 row created.

mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (102,'abcd',userenv('commitscn'))

insert into test_scn (seq_no,vc1,scn_no)
            *
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

-- now test updating several records
mdw> commit;
Commit complete.

mdw> select * from test_scn;

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
         1 AAAAAAA      11143743
         2 AAAAAAA      11143746
         3 AAAAAAA      11143749
         4 AAAAAAA      11143774
         5 AAAAAAA      11143777
       100 abcd         11144753
       101 abcd         11145543

mdw> update test_scn set scn_no = userenv('commitscn');
update test_scn set scn_no = userenv('commitscn')
       *
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

-- but one row works
mdw> update test_scn set scn_no = userenv('commitscn') where rownum =1;
1 row updated.

USERENV(‘COMMITSCN’) is old, undocumented and limited in use. So why am I looking at it, let alone even telling you all about it? {Apart from the fact that a lot of you *love* this sort of obscure, tid-bitty stuff}. Well, as there is no replacement for it that I am aware of. You can get the current SCN in a couple of ways, the easiest probably being to get it from V$DATABASE:

mdw> select current_scn from v$database;
any key>

CURRENT_SCN
-----------
   11146718

However, that is the last SCN used at the time you check it and is not the SCN when you commit. ie it is a different thing. I always find it irksome on those odd occasions when something is deprecated in Oracle with nothing really to replace it.

I just demonstrate again that USERENV(‘COMMITSCN’) is a little special below, and not the same as just selecting SCN from V$DATABASE. Before I go any further, I think the value USERENV(‘COMMITSCN’) puts into the table is the actual COMMIT SCN minus 1. I mostly think this as Jonathan said so🙂. I do see each time I run this test that the first select from V$DATABASE and then my insert and a commit straight away results in a value in the table 2 higher than the select.

Further iterations (2nd and 3rd in this case) show the value selected from V$DATABASE and the value inserted into TEST_SCN immediately after are the same, and are 3 higher than the previous iteration. I anticipated an increase of two, once for the change to the UNDO tablespace for the insert and once for the insert. I am not sure where the third one comes in.

However, in the fourth iteration I have a PAUSE in my SQL*Plus script between checking V$DATABASE and doing my insert and, in a second session, I do some simple inserts and commits {it does not matter what, so I don’t show it}. Thus the difference between the SCN I collected from V$DATABASE and the value inserted into the table.
Finally, in the fifth iteration, I check the value in V$DATABASE, do the insert, query it back and see the two are the same. And THEN I pause so I can do some changes and commit them in my second session. After I’ve done that I continue my first session which commits my latest insert into TEST_SCN. I check the values actually stored in the table and, just as at the top of this post, you see that the value actually preserved in the table is a later SCN than the placeholder one. It is doing something special.

(the below has the noddy code to create my test table and sequence as well as the test)

-- test_scn1
--create table test_scn (seq_no number,vc1 varchar2(10),scn_no number)
-- create sequence scn_seq;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
pause 'commit some stuff in second session and then press any key'
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
pause 'commit some stuff in second session again and then press any key'
select * from test_scn;
commit;
select * from test_scn;
select current_scn from v$database;

-- the output of the test
mdw> @test_scn1

--check V$DATABASE SCN
CURRENT_SCN
-----------
   11147809

-- Create and commit 1 row
1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
-- note that the inserted SCN is 2 higher than the current SCN.

--Same steps, 2nd iteration
CURRENT_SCN
-----------
   11147814

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
-- select SCN and inserted SCN are the same, 3 higher than first iteration

-- same steps, 3rd iteration
CURRENT_SCN
-----------
   11147817

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
-- select SCN and inserted SCN are the same, 3 higher than first iteration

-- 4th iteration, a pause
CURRENT_SCN
-----------
   11147820

'commit some stuff in second session and then press any key'
-- I did indeed change and commit some stuff in second session, before I create my record in test_scn

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
-- larger gap in SCN (11147817 to 11147831

-- 5th iteration, pause now after insert and before commit
CURRENT_SCN
-----------
   11147834

1 row created.
'commit some stuff in second session again and then press any key'
-- I did indeed change and commit some stuff in second session 

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
        15 AAAAAAA      11147834

-- Notice the current_scn from V$DATABASE and the last row in the table match, 11147834..

Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
        15 AAAAAAA      11147842

-- But after the commit the SCN in row "15" has increased to 11147842.

CURRENT_SCN
-----------
   11147851

-- and the next check of V$DATABASE SCN shows the usual increase of 3 by the commit.

As you can see from the above, USERENV(‘COMMITSCN’) is doing something a little special and, despite all the limitations, I might actually have a use for it…

Friday Philosophy Guest: Open Source Projects January 15, 2016

Posted by amitzil in Architecture, Friday Philosophy, Guest Post, Perceptions.
Tags: , ,
4 comments

This post is Guest Post by my friend Liron Amitzi, an Oracle Ace, presenter and instructor who specialises in Oracle design & infrastructure. You can find his blog over here.  And with that, over to you Liron🙂

 

I have been wondering about open source projects for a while. I’ve talked to quite a few people about it, and still don’t really understand some of it. So I decided to write a post about my thoughts regarding this issue.

I’m not going to talk (or even mention) specific projects, but it is very interesting to me how these projects run.

During the years I have worked with quite a lot of open source software and I like some of it a lot. I completely understand how small projects work, such as text editors, small schedulers and others. With these relatively small projects, I can easily see that someone needs such software and simply sits down and writes it (alone or in a small group). When it is ready, I can see that they want to share it with the world – and open source is perfect for that. I can even understand that they will want to update it, add features, support it a little bit, etc. Another side to it that I can see is a developer that writes software to get his reputation going in the community: in order to get a job, an interesting project or simply fame.

However, I’m quite puzzled with the big open source projects, such as databases, queue management, large monitoring systems and more. I know that behind at least some of these software products, there are actual companies that invest money and people. And I don’t really understand how it works as companies need to cover their expenses, salaries, and of course, make a profit.I know that there are many ways to make money out of open source projects. Some companies charge for support, some for education & courses, and some for professional services & consulting. However, in some cases the companies that provide these services are not related to the company that sponsored the development.

So what makes a company develop or support a development of an open source project? I can think of a couple of reasons:

  • As the software will be free and open source today is very common, it will get this software many potential customers and foothold
    After getting a foothold, the company can charge for specific features or other complementary software.
  • Business decisions can also be a cause. A company that makes a lot of money from software might decide to give some back to the community so people will “like” the company more.

Still, when it is a big software project and requires a lot of resources, I can’t see why a big company will go for open source.

  • First, they can make it a freeware without releasing the code.
  • Second, at the bottom line, they will have to make money on this somehow.

So, if they release the project as an open source but charge for education or professional services, I guess that the education and professional services will cost more, so at the end they will make the same amount (and we will pay the same amount) as with licensed software and cheaper courses and professional services.

Am I missing something?

What do you think? I’d love to know.

About Liron

Liron Amitzi and Steven Feuerstein

Liron Amitzi and Steven Feuerstein

Liron Amitzi is originally from Israel and now lives in Canada with his wife and two children. I met Liron at the UKOUG Irish User Group conference in Dublin last year and again at Oracle Open World 2015, when it struck me that he looks a lot like  Steven Feuerstein (Liron is on the left). Liron has been an Oracle ACE since 2009 and has helped run the Israel Oracle User Group since 2011. He specialises in High Availability solutions, design, infrastructure, performance and recovery. As well as presenting he is also an instructor and lecturer in Oracle course.

The IT Blight of Working During Holidays December 24, 2015

Posted by mwidlake in humour, on-call, Private Life, working.
Tags: ,
6 comments

I’ve been thinking today about those people in IT who are going to have to either work or be on call during the festive period. Twitter has become a lot more quiet today and most of the activity is not-work-related. My blog traffic is now a trickle and there is a general feeling of doing more family, non-work things for a couple of days, which I think is good for all of us from time to time. Maybe more times than current working culture and practices allow for.

The endless daily grind - even at Christmas

The endless daily grind – even at Christmas

But in the IT industry, especially if you are an administration-type (DBA, Sys Admin, Network Admin, stuff like that) there is often a need to do work at this time as systems are quiet or can even be shut down. Some places do release and upgrade work over the quiet period, so developers and designers can be pulled into festive-season work too. Even if you are the sort of organisation that has a code freeze for Christmas/New Year, there will be a rota of people who need to either monitor systems or respond if something goes “Bang!”. Those of us “blessed” with those roles will be on the on-call rota, tasked with at the least staying sober and often with monitoring duties. For some people in some organisations, you know you will in fact have an endless stream of “why in the heck am I having to do this” tasks to do.

I’ve done my share and I feel for those who are made to work over this time who really would rather not. In fact, I’ve done more than my share. Actually, A lot more than my share. You see, I do not have children – my wife and I established very early on in our relationship that producing new versions of me was a damned bad idea, even if new versions were leavened with her better characteristics {and if they got her worst ones along with mine, ohhhhh terrible consequences}. So as someone with no children there has always been more pressure on me to take more than my 1/number-in-team share of the Christmas, New Year, Easter, Bank Holiday etc work. I’ve also come under pressure not to take time off during school holidays, to cover for those who need to do so for the sake of fitting in with the kids. Now, I don’t want to go away on holiday when everywhere is covered in kids as kids are too self-centered, noisy and annoying (ie very like me) for me to put up with. But I would like occasionally to have a week off, in the summer, to sit in the garden. But the biggest pressure has always been over taking more of the Christmas work. Because, I am told, it is important family time – it’s for the kids

I get that, I do. But then, if you have kids they are actually your fault. You did things to have them. Trust me, I’ve got a degree in biology, I know where kids come from🙂. At the start of my working career I was fine to take on more of the work/monitoring/staying sober duties. But as the status of not-having-kids lasts a lot longer than having-young-kids (or more recently, with people my age, young-grand-kids) it had been a constant expectation of me for about 20 years – until I stopped playing. I stopped on the grounds, after 2 decades, that I had Done My Bit. I threw my toys out my pram and said I deserved my share of time off at Christmas (to pick said toys up, of course). I solved the problem more recently by trying to be unemployed at such times.

Anyway, forgive the rant, I feel better now. But my extra-Christmas-Duties have made me realise more how much of a pain it is to have to work when most people are enjoying themselves. So I feel for those that are having to do it and do not want to. I truly know how it is and all I can say is “thank you for doing your bit”. Especially if you have done it despite having young kids. And especially if you have had to do it for 20+ years to cover for all those damned work-shy parents (joke!).

The ironic thing is that this year I will be working over Christmas. But I don’t mind as it is my choice. And I am doing so in warm sunshine, with a glass of wine, and in fact I can stop whenever I like. That is the joy of writing over doing stuff people need to be done now.

Merry Christmas everyone, especially to the unwilling workers.

Miserable Dark Days of Winter Relieved by Data December 22, 2015

Posted by mwidlake in humour, off-topic, Private Life.
Tags: ,
2 comments

This post is all about my fascination with short day times and hits on my blog on the topic, it is not even to do with why the day time is so short in the UK, Europe & US right now. This is nothing to do with Oracle databases or working in IT – go elsewhere and look at eg XKCD or Dilbert now if that is your thing…

I’ve blogged a few times at this time of year about the oddity of when evenings start drawing out being around a week before the shortest day, and even about how I have become fascinated by how popular this off-topic post has become. Worry not, I shall not go over that date-discrepancy material again or how we in the Northern Hemisphere wrongly think our winter is when we are furthest from the sun {when in reality it is when we are closest!}. If you want to know all that stuff I updated this post to cover the details for 2015.

Fundamentally, when I worked every day, every week in the City I just hated having to travel to work in the dark, sit in a dull room all day and then drag my backside home in the dark. It was hammered home to me how much I hated it one year when I worked in a huge open-office environment where I sat in the center of the building – on the ground floor of a 10-story-building. I stared out into the “Light Well” in the center of the building. There was no “Light”. Even at midday on a day blessed with cloudless sunshine, there was no real light. What we got was a grey illumination over the plastic bags, scraggy weeds and dead pigeons that littered the ground at the bottom of this light *pit*. I used to escape into the light at lunch time but such was the culture of the office that actually taking a lunch break was a sign of weakness and lack of dedication. Screw ’em, I took my lunch time as I remembered being human once…

HIts on my “evenings Drawing out” blog

HIts on my “evenings Drawing out” blog

During that particular job, someone in the team left. They had found the tunnel and they dug like crazy and got beyond the perimeter fence. So a desk came up two “deeper in” the mine. But, and this was the crucial thing, it looked out in the direction of a real, stuck-to-the-outside-of-the-building window. I had moved my stuff onto his desk and my chair in his place before the smell of his daily burrito had faded at all. My boss at the time was not happy – “Why have you moved?”. “Well, the space came up”. “Did I say you could move?!?”
“Put it this way, I can now see if it is still daylight and maybe make out if it is raining or not. I could move back, but then I could start randomly killing my work colleagues – What do you prefer?”. He shut up.

I think I have established that I do not like the lack of daylight that Winter brings and I do not even live that far North. My friends in Scotland, Norway, Finland and a host of other would countries would scoff at my distress. But I do now have a distraction. Even as we approach and pass the day on which evenings draw out, drop down to the dismal point when the daytime is shortest (today, if you read this on the day of publication) and slowly start to pull out of the pit of winter, I watch with fascination the number of hits on my obscure web pages on the topic. My blog is all about Oracle tech and IT angst. It is not about astronomy, astrology (spit) or astrophysics. I have done 4 posts on this in 5 years and just one gets a low-but-steady trickle of google hits. And as we pass through the darkest section of the year, I watch the hits and stats on that page. As a world-wide thing it is irrelevant, as a percentage of my site hits it is a not-considerable-but-not-quite-insignificant post either. But I watch it as it distracts me from the winters’ gloom. I love the fact that there is a “june” peak when those in the Southern hemisphere find it and a larger “December” peak when the Northern hemisphere stumble across a post on a nerd’s blog site that tells them what they want to know – when it starts to get lighter.

Anyway, today (shortest day this year, December 22nd) I escape. I’m going to Madeira. It is pleasantly warm and gets 10 hours of daylight compared to 7.5 in the UK. I’ll take that. But I’ll keep watching the post all the way through December and in to January, maybe into February, as the hits on the post decrease and the evening daylight increases.

It’s a bit early, but Happy New Year everyone🙂

Friday Philosophy – Publishing rather than Presenting December 18, 2015

Posted by mwidlake in Oracle Scene, publications, writing.
Tags: , ,
6 comments

Have you ever considered writing articles on Oracle subjects? Unlike presenting, there is no need to stand up in front of a scary crowd, remember what it was you wanted to say and risk someone calling you out from the crowd & accusing you of being an idiot {NB people worry a lot about that last one, but I have only seen it happen once – and no one much liked the person doing the calling}. Presenting is not for everyone. But it is not the only way to engage with people or share your knowledge. When you write an article you get to take your time, ensure you are saying what you want to say and you can correct it over time. You can also ask friends to check it over for glaring mistakes or badly written prose before you submit it. I do.

Oracle Scene, Autumn/Winter 2015

Oracle Scene, Autumn/Winter 2015

I can’t say I am an expert, I’ve only written a few articles for publication myself, but I have also been helping out with Oracle Scene in my role as deputy editor. I’ve reviewed a lot of material and helped one or two people update their articles. But there are some ways in which I think publishing is a superior way of communicating when compared to presenting. As I mentioned before, you get more time to “deliver” the material. When you present you will have prepared your slides or demonstrations and, I’d hope, you have practiced it. But the actual delivery is “Bang!” you’re up. What you say, you say, what you don’t is not going to be said – unless it is on the slides (which people may or may not read). With an article, what you actually put out there is something you can check and hone until you are happy. Or you get too close to the submission time to mess about any more…

A published article is there and it will stay there. Presenting is gone as soon as you finish it (unless it has been recorded – and my experience is that recorded presentations do not get watched that often). Many more people are likely to see an article than see you present, especially if you get it into something like Oracle Magazine… Or “Hello”, but that is pretty unlikely for an article about HR apps in the Cloud. That persistence is also a bit of a drawback I find, as I am even more concerned about getting it right. I don’t want to have something that people can constantly point at and say “Hey, that Widlake guy! He actually still USES the Buffer Cache Hit Ratio!”. But it drives me to produce something of a slightly better quality, I feel, than when I present or blog.

I obviously blog quite a bit but I hesitate to say that a blog is quite the same as having something published. When I blog it is me having my say to an audience that chooses to come by and look. If I mess up, you all know who messed up. If I publish, I have to produce something good enough for someone else to say “yeah, that is good enough to be in my publication”. And if I have messed up, I’ve messed up a bit of their publication. I can actually modify or remove anything I blog, it is under my control. However, when I do an article in a magazine, it is fixed once it has passed the copy edit check. So blogs are different, they are “softer”. I would say, though, that web sites that give information in a more formal way, like the wonderful Oracle Base by Tim Hall or fantastic oracle-developer by Adrian Billington are more like published material. A kind of half-way-house.

Where a published article wins out over a blog is in audience reach. I know that lots of people who would never visit my blog will see it, maybe people who will remember the great article I did and even recognise my name. You never know, one day it might help me land a piece of work. A published article will also be read by people outside of my sphere, some people who are reading it for the Apps content might look over my article, especially one that is an introduction to a subject.

Another of the great things about a published article is it can be referenced back to or, if it is a printed publication, there on your desk to look at as you try things out on the computer. We all tend to have larger computer screens now and even multiple ones {I would struggle to go back to a single screen} and use online material, but nothing beats having a physical copy to read and move about the desk. It leaves the computer screens free for everything else and you can take the magazine or printout around with you when you don’t want to have a laptop or tablet with you.

I guess I am more proud of my publications in Oracle Scene than my blogs. My mum even paid a tiny bit of interest in me having an article in a “real” publication.

os57cover

And this leads me on to the real purpose of this piece. I’d encourage you to submit articles to Oracle Scene. The call for articles for edition 59, to be published in Spring 2016, closes on 11th January. You can find the editorial calendar here which tells you about the dates for the next and future publications. If you want an excuse to get away from the relatives this Christmas, why not write and submit an article? We are always looking for good articles and series of articles. Check out the current edition online {the current edition is free to anyone to view online} to see what sort of things we cover, which is all aspects of the Oracle tech and Oracle apps. We are particularly keen to get more Apps articles as they are currently under-represented, but we of course are also interested in technical pieces.

We are moving to publishing Oracle Scene four times a year and with more content each copy. With “Oracle Magazine” going digital-only, I think Oracle Scene is now the only physically published magazine on Oracle technology. Oracle’s “Profit” magazine is still available in print but it is mainly focused on the business side of using Oracle solutions. When I was in the US for OOW15 I mentioned Oracle Scene to a few people and that it was still a physical publication, as well as available digitally, and that seemed to be of interest to most of them. Physical copies are available at all UKOUG events and are placed in Oracle Offices. If you have ever sat in reception waiting to see someone in Oracle, there were probably a few copies near you! You may well have read some of it, whilst waiting for Larry to see you.

I’ll finish with a few words on what we look for in articles {I may well do a longer piece on this at a later date, especially if any of you tell me you would like to see it}. We avoid sales pieces. If you work for “United Mega Corp” and every sentence has “United Mega Corp” in it or you are just trying to sell United Mega Corp’s sales portal system, then you are unlikely to get your article accepted – you can pay for advertising space for that. However if you work for “Incredible IT Systems” and write a piece on using pluggable database and mention “Incredible IT Systems” once or twice, or that you have experience in the field you can offer to customers, all is good. Other than that, we simply want well-written articles that will help people use a feature of Oracle, better understand some aspect of their Apps offerings or allow a compare & contrast across possible solutions. Basically, we want to publish things that UKOUG members and the wider Oracle community want to read.

Go on, think about it. Give it a go. And if you actually want to spend time with the relatives over Christmas, write a piece for one of the editions later in the year.