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

Little known SQL*Plus SHOW command options

The SQL*Plus command SHOW shows you the value of a SQL*Plus system variable, or the current SQL*Plus environment.

SHOW displays the value of any system variable set by the SET command. But, did you know that SHOW can also be used to display other types of information?

For example, SHOW PARAMETERS displays the values of initialization parameters in effect for the current session and SHOW SPPARAMETERS (new in 11g) displays the values of initialization parameters in the server parameter file (SPFILE):

Continue reading…

Comments Off on Little known SQL*Plus SHOW command options | Filed in Oracle | Tags:

SQL*Plus Timers

SQL*Plus TIMING is a handy little utility that you can use to do a performance analysis on any commands or blocks run during a period. It is different from the SET TIMING command which is used to display timing data after each SQL command or PL/SQL block you run.

Here is an example:

TIMING SHOW lists the current timer’s name and timing data:

SQL> timing show
SP2-0325: no timing elements to show

TIMING START sets up a timer and makes t1 (or whatever timer name you like) the name of the timer:

SQL> timing start t1
SQL> timing show
timing for: t1
Elapsed: 00:00:06.95

Another call to TIMING SHOW displays the elapsed time since the start of the timer:

SQL> timing show
timing for: t1
Elapsed: 00:00:16.01

You can have more than one active timer by STARTing additional timers before STOPping the first; SQL*Plus nests each new timer within the preceding one. The timer most recently STARTed becomes the current timer:

SQL> timing start t2

t2 is the current timer now:

SQL> timing show
timing for: t2
Elapsed: 00:00:02.25

TIMING STOP lists the current timer’s name and timing data, then deletes the timer. If any other timers are active, the next most recently STARTed timer becomes the current timer, in this example it’s t1:

SQL> timing stop
timing for: t2
Elapsed: 00:00:18.81
SQL> timing show
timing for: t1
Elapsed: 00:01:14.18
SQL> timing stop
timing for: t1
Elapsed: 00:01:22.09
SQL> timing show
SP2-0325: no timing elements to show

TIMING with no clauses lists the number of active timers:

SQL> timing
no timing elements in use    
SQL> timing start t1
SQL> timing
1 timing element in use
SQL> timing start t2
SQL> timing
2 timing elements in use

CLEAR TIMING deletes all timers:

SQL> clear timing
timing for: t2
Elapsed: 00:22:38.36
timing for: t1
Elapsed: 00:22:53.96

Sources and Resources:

Comments Off on SQL*Plus Timers | Filed in Oracle, Tips | Tags:

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 on The Smallest Database Management System Is Just 359 Kilobytes | Filed in Oracle, Technology | Tags: , , ,

Did You Know That About PL/SQL Variables?

In his latest post titled Less is More, More or Less, John Russell, the man behind, reveals a very interesting fact about the space needed to store PL/SQL variables of varchar2 datatype. He writes:

If you don’t know exactly how much space is needed for a PL/SQL variable, the most space-efficient thing you can do is counter intuitive: declare a gigantic variable… Once you go above 2000 characters, the variable stops being statically allocated to accommodate its maximum size, and starts being dynamically allocated every time it’s assigned, based on the exact size that’s really needed.

He gives an example:

— Rest of 512 characters are wasted empty space
email_address varchar2(512) := ‘';
— Although declared with length 32000, only 24 characters are allocated
email_address varchar2(32000) := ‘';

I know that, using DUMP, you can return the length of a variable in bytes, but the question is how do you really calculate the total space allocated to a certain PL/SQL variable?

John also gives an additional tip:

When you want a variable to hold the contents of a table column, there is a simple way to match the right length: var1 my_table.my_column%type;

Using anchored type declarations is one of PL/SQL’s best practices. But, what if you have a variable that is a concatenation of two or more table columns? Here is the trick:

cursor my_cursor is select col1 || col2 concatenated from my_table;
var2 my_cursor.concatenated%type;

We let Oracle figure out the maximum length of the concatenated columns, then piggyback on that result to make a variable of the same type.

John always has interesting tips. Here are 3 Useful SQL*Plus Tips I found on his blog a few weeks ago.

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

3 Useful SQL*Plus Tips

I stumbled on the following SQL*Plus tips at a new Oracle blog called Tahiti Views:

@ (“at” sign) vs. @@ (double “at” sign): With @@, all the import commands are processed relative to the directory where the original file sits, not the directory where you run SQL*Plus.

Splitting Up Package Code: You can split a big PL/SQL package script file into many smaller ones using the @ (“at” sign) SQL*Plus command. For example:

create or replace package foo

Turn a File into a String Literal: Also using the @ command, you can turn the entire content of a file into a string literal. For example:

from dual;

You can also use the alternative quoting mechanism in case the file contains single quotes. For example:

from dual;


5 Comments | Filed in Oracle, Tips | Tags:

Your SQL Tool Part II

As a follow-up to my previous post about the different SQL development tools, here is the result of the quick poll I had on this site.

I asked the question: What SQL development tool do you use most? There were a total of 150 answers, broken down like this: Continue reading…

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

Tom Kyte’s Newest Book and SQLPlus Prompt

I downloaded part of Tom Kyte’s newest book “Expert Oracle Database Architecture – 9i and 10g Programming Techniques and Solutions” from Tom’s blog and started reading with interest (Can’t wait until the book is available at Amazon). I got to page 25, “Setting Up Your Environment” and read through page 28 when I noticed the following: Continue reading…

3 Comments | Filed in Oracle | Tags: ,

Your SQL tool

Some Oracle professionals swear by SQL Plus, others just don’t even use it. There are a lot of tools out there for interacting with the Oracle database (and other databases as well) that serve as an alternative for SQL Plus; to name a few: Continue reading…

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


While reading the oracle-l mailing list, someone asked a question about (auto)commiting transactions in SQL Plus. Consider: Continue reading…

2 Comments | Filed in Oracle | Tags: ,

Oracle SQLPlus

Even though I use Sql Navigator at work, I also often use SQLPlus. Almost everyone who has access to an Oracle database, has access to SQL*Plus. On the other hand, not everyone has SQL Navigator or similar third party tools installed on his/her machine.

SQLPlus can be started in GUI mode (Windows only) or in command-line mode. I like the command-line mode because it is more flexible and can be used on any platform.

By default, when you start SQLPlus, the prompt changes to SQL>: Continue reading…

13 Comments | Filed in Oracle | Tags: ,