Oracle Express Edition and MS SQL Server Express Edition

Today I was listening to a course from the dark side ;) One quite small fact (I was not aware of) about SQL Server 2005 Express Edition compared to Oracle 10g XE caught my attention though.
These versions are similar in their limitations i.e.

• 1 CPU
• 1 GB RAM
• 4 GB user data

But there is one difference not in favour of Oracle - for SQL Server Express Edition one can create up to 16 instances on a single box, but for Oracle XE only one. Of course in general it is not the main decisive factor to choose between SQL Server and Oracle, but I can imagine some scenarios where more than one instance of Oracle XE on a single box would be nice, for example, simultaneous development of different applications on XE.

A new experience

I've completed my first Oracle certified training course in Mebius Latvia. This time as a trainer though.
For already a few years I feel necessity somebody to tell what I've learned and how to do or not to do certain things in Oracle ;)

The first step was my website where I could put some articles telling some experience stories. Now I've completed the next step – directly telling people how to work with Oracle. The content of my first course as a trainer was quite trivial – 10g PL/SQL fundamentals. Although describing more than 250 slides in two days, even quite easy slides, was a completely new experience for me. I hope I've completed it more or less so that my trainees understood what I was talking about ;) In fact I was really satisfied with them, because all had more or less experience with Oracle and programming as such, so there wasn't necessity to tell for example what the "IF" statement and logical values are. In the very beginning I frankly told them that questions help not only trainees to deeper understand the covered material but questions also help the trainer to feel better and understand that at least some trainees are following the lesson material ;) So they really followed my plea and time after time we had even discussions about one or another topic. So I'm thankful to my trainees, I hope they understood at least main things I told them and I'm looking forward to my next course after a while.

Flashback for source code

Recent OTN forum question made me think that I'm quite lucky. Everywhere I've worked we had one or another source control software for source code. All scripts for schema creation as well as procedural units were put and maintained there. Sometimes reading about other people's problems that they've lost the only (sic!) version of their procedure dropping the schema or just recreating the procedure gives me the shudders. So for everyone that yet hasn't understood - keeping procedural units only in the db means it is just matter of time when you'll lose them. Either all of them or just one because of erroneous create or replace but sooner or later the time will come ;)

OK but what if you are unlucky and just realized that you have overwritten the last and only version of your source code unit (procedure, package, function, etc)? If you are at least on 9i (10g as well of course) you are not completely lost. Flashback that works on regular user's tables works in the same way also on internal tables holding source code. So the only problem remains how to get it and how to get it fast! Fast because it is just like with lost extremities - surgeon can put it back only if you are acting fast. And fast because the old data can simply be overwritten if Oracle feels the necessity. And now what's the mechanism is?

There are 2 scenarios:
1. query directly from base tables owned by sys or
2. query using provided dba/all/user views i.e. dba/all/user_source.

In any case for ordinary user by default it is not possible. You need sys access to do that or grant privilege from sys to any other user. Even DBA role is not enough.

1st scenario is as follows:

1) get object_id (from dba/all/user_objects)
SELECT object_id
FROM user_objects
WHERE object_name = 'yourname'
AND object_type = 'yourtype';

2) get text from source$ as of timestamp
SELECT source
FROM sys.source$
AS OF timestamp timestamp_before_error
WHERE obj# = object_id_from_1st_step
ORDER BY line;

1st step can be done by everyone. 2nd step needs flashback on source$.

2nd scenario is as follows:

simply select text from user/all/dba_source as of timestamp.
SELECT text
FROM dba_source
AS OF timestamp timestamp_before_error
WHERE name = 'yourname'
AND owner = 'yourowner'
AND type = 'yourtype'
ORDER BY line;

Of course this can be done by sys. I was not able to do that by other user because user_source for example is based on source$, obj$ and fixed table x$joxfs. I was able to grant flashback on source$, obj$ to another user but for fixed tables it is not possible (quite understandable as these are actually memory structures). But it seems on the other hand without flashback one cannot make queries from view that is based both on ordinary tables and fixed tables.
Above experiments were performed both on 9.2.0.7.0 and 10.2.0.1.0 EE Oracle.