Virtual Machines Are Your Friends
VirtualBox, Virtual PC, VMware Workstation and a few other software packages help you create and run multiple virtual machines on your desktop or laptop enabling you to run multiple operating systems simultaneously on a single computer.
My personal favorite is VirtualBox (recently acquired by Sun Microsystems), not only because it is freely available as Open Source Software and runs on Windows, Linux and Macintosh but also because it has great features and supports a large number of guest operating systems.
Last Saturday I used VirtualBox to create a new virtual machine (VM) running Windows XP Pro and Oracle Database 11gR1. First, I created a “base” VM with only Windows XP pro SP3 installed. I then detached the virtual disk file (VDI) from the VM. I ended up with a VDI file that I can clone as many times as I want eliminating the need to install a new operating system every time I create a new VM.
Cloning a virtual disk in VirtualBox is done using the command line. For example, to clone WindowsXP.vdi as a new virtual disk called WindowsXPProOraDB11gR1.vdi, you would issue this command:
VBoxManage clonevdi WindowsXP.vdi WindowsXPProOraDB11gR1.vdi
You would then assign WindowsXPProOraDB11gR1.vdi as the virtual hard drive of a new virtual machine.
The installation of Oracle Database 11gR1 on a new Windows XP VirtualBox VM on my laptop went very smoothly, but I had to troubleshoot a couple of things on the VM: A missing loopback adapter and a 100% CPU usage.
My laptop and the newly created VM do not have a static IP address. Dynamic Host Configuration Protocol (DHCP) is used to assign dynamic IP addresses on the network. According to the documentation, before installing Oracle Database onto a computer that uses the DHCP protocol, you need to install a loopback adapter to assign a local static IP address to that computer. I followed the instructions found in the Oracle Database Pre-installation Requirements document to install the loopback adapter. After that, the database installed without any issues.
Now that my WinXP VM and 11g Database are up and running, I noticed that the oracle.exe process was consuming 100% of the CPU. I waited a few minutes, but the CPU usage did not go down, it stayed at 100%. After a quick search, I found these two forum posts: post 1 and post 2. Since this is a test database, I did not hesitate to follow the instructions in post 2 and was able to bring the CPU usage down to a normal level.
Virtual machines are a great way to learn and try new software and applications without the need to buy new hardware or messing up your existing computer. You may even have fun embarking on some virtual adventures.
5 Useful Links for 2008-06-13
-
Fun ways to test your knowledge of PL/SQL and other topics.
-
A nice guide about how to troubleshoot an Oracle related problem.
-
Sometimes it is good to go back to basics.
-
Oracle 10g DB + APEX 3.1 + 100mb Tablespace + 2 Database Users. $24.95 for the first 6 months. $49.95 thereafter. Not bad.
-
Solutions to what problems?
More from my bookmarks on del.icio.us
Filed in Links with Comments OffCool Undocumented OVERLAPS Predicate
David Aldridge’s post about OVERLAPS caught my attention. I did not know that you could do something like this in Oracle:
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 22:06:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect eddie/awad
Connected.
SQL> SELECT *
2 FROM dual
3 WHERE (add_months(sysdate, -6), DATE '2008-08-08')
4 OVERLAPS
5 (sysdate - 180, interval '2' YEAR);
D
-
X
Indeed, like David, I searched the Oracle docs and I could not find anything describing OVERLAPS. I extended my search to the OTN Forums and I got these hits:
So, what does OVERLAPS really do? according to Mimer SQL Reference Manual:
The OVERLAPS predicate tests whether two “events” cover a common point in time or not, and has the form:
(expression, expression) OVERLAPS (expression, expression)
Each of the two “events” specified on either side of the OVERLAPS keyword is a period of time between two specified points on the timeline. The two points can be specified as a pair of datetime values or as one datetime value and an INTERVAL offset.
Each “event” is defined by a two expressions constituting a row value expression having two columns.
The first column in each row value expression must be a DATE, TIME or TIMESTAMP and the value in the first column of the first “event” must be comparable.
The second column in each row value expression may be either a DATE, TIME or TIMESTAMP that is comparable with the value in the first column or an INTERVAL with a precision that allows it to be added to the value in the first column.
The value in the first column of each row value expression defines one of the points on the timeline for the event.
If the value in the second column of the row value expression is a datetime, it defines the other point on the timeline for the event.
If the value in the second column of the row value expression is an INTERVAL, the other point on the timeline for the event is defined by adding the values in the two column of the row value to expression together.
The NULL value is assumed to be a point that is infinitely late in time.
Either of the two points may be the earlier point in time.
If the value in the first column of the row value expression is the NULL value, then this is assumed to be the later point in time.
I wonder if or when a similar documentation will be added to the Oracle SQL Reference.
Warning: Undocumented features should not be used in production systems.
Filed in Oracle with 3 Comments | Tags: sql, undocumentedANY Types, Pipelined Table Functions, Dynamic SQL and Pivot
Amazing what you can do with ANYTYPE, ANYDATA, ANYDATASET, DBMS_TYPES, DBMS_SQL and pipelined table functions implemented using the interface approach. You can end up with a dynamic sql mashup like you’ve never seen before, and pivot like you’ve never pivoted before. Of course, if you’re on Oracle DB 10g or below, you can never pivot like this.
Filed in Oracle with 1 Comment | Tags: datatype, dynamic, pipelined, pivot, sql
