News, views, tips and tricks on Oracle and other fun stuff

The Smallest Database Management System Is Just 359 Kilobytes


As you may already know, Google recently released Google Gears, an open source browser extension that lets developers create web applications that can run offline using JavaScript APIs.

Google Gears provides three key features:

  • A local server, to cache and serve application resources (HTML, JavaScript, images, etc.) without needing to contact a server.
  • A database, to store and access data from within the browser.
  • A worker thread pool, to make web applications more responsive by performing expensive operations in the background.

Apart from the coolness and usefulness of Gears (I can now read my Google Reader feeds offline), what also caught my attention was its use of a database to store and access data. After all, that’s what databases are good at, data storage and management. I was curious to know what type of a database Gears uses. Well, according to its database module API, Google Gears uses the open source SQLite database system.sqlitelogo

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.

SQLite is really interesting. There is no set up procedure to initialize it before using it. Databases need minimal or no administration. There is no need to maintain a separate server process dedicated to SQLite. It supports ACID and is thread-safe.

SQLite supports a large subset of SQL-92 data definition and manipulation features. You can create tables, indexes, triggers, and views. You can manipulate stored information using INSERT, DELETE, UPDATE, and SELECT SQL constructs. However, some SQL-92 features are not yet supported.

SQLite stores an entire database in a single, ordinary native file that can reside anywhere in a directory of the native file system. Any user who has a permission to read the file can read anything from the database.

sqlite3What’s also interesting about SQLite is its size. The whole code footprint ranges from 224KB up to 513KB depending on what compiler optimizations are used. I downloaded the pre-compiled SQLite binaries for windows, it is just one 359KB file, sqlite3.exe.

sqlite3 is somewhat similar to Oracle’s SQL*Plus. You can use it to enter SQL commands. For example, from the DOS command prompt, the following creates a database file named eddie.db:

C:\Users\Eddie\Documents\sqlite>sqlite3 eddie.db
SQLite version 3.3.17
Enter “.help” for instructions

Now, let’s do some DML and DDL:

sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values(‘hello!’,10);
sqlite> insert into tbl1 values(‘goodbye’, 20);
sqlite> select * from tbl1;
sqlite> CREATE TABLE tbl2 (
   …>    f1 varchar(30) primary key,
   …>    f2 text,
   …>    f3 real
   …>    );

Does SQLite have a data dictionary? Yes it does, and it’s only one table called sqlite_master:

sqlite> select * from sqlite_master;
table|tbl1|tbl1|2|CREATE TABLE tbl1(one varchar(10), two smallint)
table|tbl2|tbl2|3|CREATE TABLE tbl2 (
   f1 varchar(30) primary key,
   f2 text,
   f3 real

Interesting. Going back to Gears, if you are wondering where the location of the database file is, it basically depends on the browser and the platform you are using.

If you are wondering what can SQLite be used for, of course, you have Google Gears as a perfect example. But, there are other appropriate uses as well.

By now, you may be thinking about Oracle Database Express Edition as also being an entry-level, small-footprint database, but hey, SQLite does not even come close to Oracle XE. Each one serves and is suitable for different types of applications. Moreover, do you really think that a 359KB RDBMS can put a dent into a 165MB RDBMS?

Comments Off | Filed in Oracle, Technology | Tags: , , ,

Ubuntu on Microsoft Virtual PC Works Like a Charm

I have installed and been using Windows Vista Ultimate on my laptop for a few weeks. I have had no major problems with Vista. In fact, I like it.

However, this post is not about Windows Vista. I just want to share with you my success in running Ubuntu as a guest OS on Vista. No, I did not use VMware. I used (the free) Microsoft Virtual PC 2007. I also successfully installed Oracle Database 10g Express Edition on Ubuntu.

The following pages were very helpful:

18 Comments | Filed in Technology | Tags: , , ,

How to Give Your System a Break from Oracle XE

There was a question on OraQA about how to stop Oracle XE from starting up with your computer. When you install Oracle XE on Windows, it creates a service called “OracleServiceXE” with a startup type of “Automatic” which means that the service starts automatically when the computer boots.

One way to prevent Oracle XE from starting up automatically is to change the startup type of the Oracle service to “Manual”. You can do that by navigating to your Control Panel -> Administrative Tools -> Services, double click on the Oracle service and change the Startup type to Manual.

Another way to prevent Oracle XE from starting up automatically when Windows starts is to use the ORADIM command-line tool that is available with the Oracle database. From the DOS command line execute the following:

oradim -edit -sid XE -startmode manual

Now, when you want to use Oracle XE, you start it up like this:

oradim -startup -sid XE

And you shut it down like this:

oradim -shutdown -sid XE

Or you can use the “Start Database” and “Stop Database” shortcuts installed with Oracle XE. Navigate to Start -> All Programs -> Oracle Database 10g Express Edition ->

Now you know how you can squeeze out a bit of extra performance from your computer when you are not using Oracle XE :)

Sources and Resources:

1 Comment | Filed in Oracle, Tips | Tags:

I use Oracle Database 10g Express Edition to …

I like Oracle DB XE, not only because it is free and has all the power of an Oracle database, but also because it comes with Application Express, or APEX (formerly HTMLDB). I plan to learn APEX and use it as an ad hoc application builder for such applications that can be shared among team members or on the company’s Intranet.

I installed the demo Web Services application that comes with APEX. For testing purposes, I wanted to allow my coworkers access to this demo application on my Oracle DB XE instance on my PC. Not a big deal, I just gave them the URL to the application, something like this: But, they could not connect to the application.

Well, I discovered that there was a setting called “Manage HTTP Access” under the Administration section of APEX. By default, this setting was set to “Available only from local server”. I switched it to “Available from local server and remote clients”. Now anyone who is on the same network as my PC, is able to access the application.

Of course, if you are exposing access to the whole Internet, you may think twice before doing this. But since I’m sharing the application only among my team members and only inside the company’s firewall, I was not paranoid about security.

Another thing I find useful is to use my Oracle DB XE instance as a tool to load CSV or XML data to another non-XE database (or even XE) through database links.

Assuming you have a non-XE Oracle database instance called ENTORA, and you have a table in a schema in the ENTORA database that you want to populate from data in a CSV file. There are many ways to do that, here is one way to do it using your local Oracle XE instance.

First, in XE, you create a database link to ENTORA. Something like:

create database link ENTORA
  connect to <username> identified by <password>

Then, using APEX, you load the CSV file into either a new table or an exiting table. Loading the data into an Oracle XE user table is just a few clicks away.

Once you have the data loaded in the table, you could do this (connected to user@XE):

insert into t@ENTORA select * from t;

I’m assuming that both t@ENTORA and t@XE have the same structure and DB users have the right privileges.

Poof! Your CSV file is loaded into a table on your non-XE Oracle database.

For more Oracle DB XE benefits, check out Lewis Cunningham’s article on OTN: Oracle Database 10g Express Edition: Not Just for Learners.

If you have downloaded and installed Oracle DB XE, what do you use it, or plan to use it, for?

5 Comments | Filed in Oracle, Tips | Tags: , , ,

Oracle Database 10g XE out of Beta

Justin Kestelyn reports that the free Oracle Database 10g Express Edition is in production now. Woohoo! Thanks Oracle!

Now, waiting for Oracle SQL Developer to graduate :)

Comments Off | Filed in Oracle | Tags:

Podcast about Oracle DB XE

In case you have not noticed it yet, there is a new podcast on OTN: “Oracle vice president Tom Kyte talks about Oracle Database 10g Express Edition (XE)–the new, free, and small-footprint Oracle Database release-and its benefits for developers, DBAs, and ISVs”. Listen.

Comments Off | Filed in Oracle | Tags:

Free Oracle Database 10g

I bet that you did not believe your eyes when you saw the two words “Free” and “Oracle” right next to each other, did you? Well, it happened a few hours ago. Oracle has now a “freebie” database of the 10g version, the Oracle Database 10g Express Edition. So what is Oracle Database 10g Express Edition? According to this tutorial (via Sergio): Continue reading…

4 Comments | Filed in Oracle | Tags: ,