VSIZE Function

This example demonstrates the use of the VSIZE function, which tells you how much space Oracle needs to store a value. VARCHAR2 columns are variable length so the space required depends on the length of the value in the column. CHAR columns are fixed length so the space required depends on the column definition. DATE columns always seem to take up 7 bytes. NUMBER columns often need fewer characters to store than you might think (I will try to look at this further in a future post):

SQL> create table emp
  2  (first_name varchar2(10),
  3   surname    char(10),
  4   hire_date  date,
  5   salary     number)
  6  /

Table created.

SQL> insert into emp values
  2  ('Joe', 'Bloggs', sysdate, 1000000)
  3  /

1 row created.

SQL> insert into emp values
  2  ('John', 'Smith', sysdate-1000, 1234567)
  3  /

1 row created.

SQL> insert into emp values
  2  ('Andrew', 'Reid', sysdate-2000, 9999999)
  3  /

1 row created.

SQL> select first_name, vsize(first_name)
  2  from emp
  3  /

FIRST_NAME VSIZE(FIRST_NAME)
---------- -----------------
Joe                        3
John                       4
Andrew                     6

SQL> select surname, vsize(surname)
  2  from emp
  3  /

SURNAME    VSIZE(SURNAME)
---------- --------------
Bloggs                 10
Smith                  10
Reid                   10

SQL> select hire_date, vsize(hire_date)
  2  from emp
  3  /

HIRE_DATE VSIZE(HIRE_DATE)
--------- ----------------
09-FEB-12                7
15-MAY-09                7
19-AUG-06                7

SQL> select salary, vsize(salary)
  2  from emp
  3  /

    SALARY VSIZE(SALARY)
---------- -------------
   1000000             2
   1234567             5
   9999999             5

SQL>

SQL*Plus host Command

The SQL*Plus host command allows you to run operating system commands from within a SQL*Plus session:

SQL> host date
Wed Feb  8 10:56:34 GMT 2012

SQL>

Alternatively, you can use an exclamation mark:

SQL> ! echo "Andrew was here"
Andrew was here

SQL>

... and, if you do, it is not necessary to leave a space between the exclamation mark and the OS command:

SQL> !pwd
/home/oracle/examples/host

SQL>