A simple implementation of Collatz conjecture in PL/SQL: create or replace type int_tab_typ is table of integer; / create or replace function collatz(p_n in integer) RETURN int_tab_typ PIPELINED as n integer; BEGIN if p_n < 1 or mod(p_n,1)>0 then RETURN ; end if; n:=p_n; while n > 1 loop pipe row (n); if mod(n,2)=1 then n:=3*n+1; else n:=n / 2; end if; end loop; pipe row(n); end; / select * from table(collatz(101)); More on Collatz Conjecture (Wikipedia). ...

Not talking about a lost wallet with money, but the wallet used for encryption in the Oracle database and with encrypted backup. A wallet is used for storing keys used to encrypt/decrypt data to/from the database, as with Transparent Data Encryption. The documentation on Advanced Security says that Oracle searches the parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora, or if not found it searches for WALLET_LOCATION in the same file. If none of them are given it searches in the default location of the wallet for the database. I found different references to where this default location is, depending on underlying OS and version. After a few rounds of trial and error (always receiving ORA-28368) I gave up and resorted to tracing my process: ...

If you have a lousy database model, you can get around it with triggers. If you need to hide some business logic from everybody else, you can do it with triggers. If you need a mechanism that bypass the consistency in the database, you can do it with triggers. They live a life on their own, seemingly autonomous from everything else and one easily forgets they exist. That is why I hate them. What matters if you do an database export in consistent mode if some active triggers make the data inconsistent during import? Triggers are like secret police, they are threat to the democracy if they take over. ...

If your database is using anything else than UTF8 as database character set you may consider to migrate from it. Oracle states in the Globalization Guide (10gR2): At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8. Depending on what characters you actually have in your database you have three options on how to do this: Changing the character set with the package CSALTER. Only data dictionary is migrated. ...

Using the CONVERT command in RMAN can be an efficient way to migrate a database from one OS to another, if they have the same endian. We migrated 9 databases on 10.2.0.4 from HP-UX (on Itanium) to AIX. The databases where distributed on three servers with not exactly the same hardware configuration. On one of the servers we encountered an error described in bug 888530 on Metalink or My Oracle Support. The process terminated prematurely with ORA-7445 when converting an undo tablespace. The other tablespaces were migrated fine, but the undo tablespace ended up with a missing datafile. After the error occurred the RMAN session was left hanging and after killing it the migrated datafile belonging to undo was removed; may be that had something to do with the file system being NFS mounted. Anyway, our solution was to create another undo tablespace and change the undo_tablespace parameter, and then dropping the corrupted one. In one of the tests we did, we managed to do recovery on the migrated undo tablespace, but some funny error messages showed up in the alert log, probably because it was not finally migrated to the new platform. Meaning, if you get this error, create a new tablespace for undo and drop the old one.

Case: I wanted to reinstall the Enterprise Manager agent on a host. I went on to delete the host in the list of targets in EM. Then after a new installation and synchronization I somehow managed to push the list of targets from GC to the agent and thereby removing the host as a target in the local targets.xml file. Result: The monitored host did never show up in the target list of hosts even after a successful installation of the agent. ...

I’ll probably need this in the not-so-distant future. Here is the link to Noon’s write-up when I need it. His blog is recommended, by the way.

Friday I passed the OCP exam (1Z0-043). In two months I have studied for, and taken three tests in order to achieve OCP certification. Before I started on this in January I had noe previous experience with Prometric testing or certification at all. But I knew I had read a post about it earlier and started looking up an old post by Chen Shapira about how she prepared for the OCA exam . I ordered the same book as well as another book for the test that was added as an extra requirement for the OCA level December last year. I more or less made the same experience that she did, in my words: ...

This year started off fine with a new job. I quit the company I joined just 25 months earlier, in itself a defeat. Though I gained much useful experience there, some signals convinced me to move on, and now I am quite happy that I did so. I joined Steria Norway to work as a senior system consultant, and so far it looks quite promising. The company is internationally focused and one is encouraged to build networks outside one’s country, something I find interesting. There is constant pressure to improve; yesterday I finished the second exam in order to achieve my OCA certification (Last December Oracle introduced a second exam as a requirement for the OCA). And I won’t stop there. ...

A couple of months ago I had an incident on a live production system. The obvious error was to execute a DDL statement at 9 am that should have been performed during off-business hours. On the other hand I guess you cannot avoid any ad hoc change on a live system forever. That will require you to be aware of any problem and issue that may strike your system. Say you have an issue right now, users are complaining, or to put it bluntly, someone is losing money. You think you have the solution, but before you actually apply it you want to know if it will actually work or make matters worse. Most people will run to their test system, at least to check that the syntax is right. I had verified it long time ago and knew it would work. However, there is one problem with test systems. Though we strive to make them as similar to the original production system as possible, they never are. Even if you can afford the seemingly waste of hardware and space, something is likely to be missing in the test system. In my case it was the users… With Oracle 11g and Real Application Testing (which I have not tried yet) the load can be better mirrored in the test system, but I can’t imagine it will behave as real users. ...