UNIX timestamp to Oracle Date Conversion

Earlier today a situation came up where a UNIX timestamp (a count of the number of seconds from January 1, 1970, midnight GMT) needed to be converted into an Oracle DATE format. The Oracle TO_DATE (covered in more detail in my article Oracle, SQL, Dates and Timestamps) does not support this type of conversion.

A Google search confirmed for me that their was no easy way to make the TO_DATE function do this, but I did find this article from the Oracle + PHP Cookbook on Oracle’s site which contained this simple function to convert UNIX timestamps to Oracle dates.

* Converts a UNIX timestamp into an Oracle DATE
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
oracle_date DATE;


IF unixts > max_ts THEN
‘UNIX timestamp too large for 32 bit limit’
ELSIF unixts < min_ts THEN RAISE_APPLICATION_ERROR( -20901, 'UNIX timestamp too small for 32 bit limit' ); ELSE oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND'); END IF; RETURN (oracle_date); END; / [/SQL] Once compiled, you can use the function to convert numerical UNIX timestamps into Oracle dates in the same manner you would use the TO_DATE function to convert text to dates. Here is an example: SQL> select unixts_to_date(1094165422) from dual;


oracle, sql, dba, database administration, database development, unix, unix timestamp, timestamp

SQL*Plus Substitution Variables

This tutorial from the Oracle Technology Network outline the use of the three types of variables available in SQLPlus. Bind variables, substitution variables and system variables are all explained in this succinct yet thorough tutorial. The best I’ve seen on the subject.

SQL*Plus Substitution Variables from Oracle Technology Network.

For information on other Oracle web resources, check out my other article on the topic.

oracle, dba, database, database administrator, rdbms, database development, database design

PL/SQL Tutorials and Reference

After some web searching I found these two resources on PL/SQL. Of course there are many others, but these two seem fairly good without being overly complicated.

Using Oracle PL/SQL from Stanford University

PL/SQL Reference & Tutorial from Elliot Spencer’s web site

While neither of these sites display the polish of a site like w3schools.com they both have great, well organized information.

For more of my favorite Oracle resources, check out my previous article on Oracle Web Resources

oracle, sql, plsql, dba, database administration, database, database design

Oracle spfile and dbstart

Why won’t my database start when I send the dbstart command?

There is a known bug with the dbstart command on Oracle 9i (and possibly later) servers with databases with server parameter files (spfiles). This bug will cause your database not to start if there is no pfile (initSID.ora) in the default location. You will typically see the error below, followed by the sound of users whining.

bash-2.05$ dbstart

Can't find init file for Database "ORADB".
Database "ORADB" NOT started.

While there are a few options to remedy this, here are my two favorite.

Solution 1: Create a dummy pfile

I like to create a dummy pfile in the default location ($ORACLE_HOME/dbs/init$ORACLE_SID.ora) that contains only the following:

# This database uses an SPFILE to acquire it's parameters for startup.
# The spfile is located at $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
# This init file is here only as a placeholder so dbstart and dbshut
# work normally. For more info on spfiles see
# http://www.lifeaftercoffee.com/?p=76
# JE 1/6/2005

With this in place (for each database) the dbstart command works normally.

Solution 2: Create a pfile from the spfile

If you connect to the database, running or not, and execute the SQL command CREATE pfile FROM spfile; a pfile will be created in the default location. On startup the pfile will be ignored as long as there is an spfile. This allows dbstart to behave normally and also gives you a backup of the spfile; however this can become confusing and the pfile will not be kept up to date by the database.

For more information on spfiles and pfiles see my article Oracle pfile and spfile for parameters.

oracle, dba, database administration, database

Oracle pfile and spfile for parameters

In Oracle Databases through 8i, parameters controling memory, processor usage, control file locations and other key parameters are kept in a pfile (short for parameter file).

The pfile is a static, plain text files which can be altered using a text editor, but it is only read at database startup. Any changes to the pfile will not be read until the database is restarted and any changes to a running database will not be written to the pfile.

Due to these limitations, in 9i Oracle introduced the spfile (server parameter file). The spfile cannot be edited by the DBA; instead it is updated by using ALTER SYSTEM commands from within Oracle. This allows parameter changes to be persistent across database restarts, but can leave you in a pinch if you need to change a parameter to get a database started but you need the database running to change the parameter.

A 9i (or later) database can have either a pfile or an spfile, or even both, but how can you tell which you have? If you have both, which one is being used? How do you go from one to the other? How do you get out of the chicken-and-the-egg quandary of a database that will not start up without you changing a parameter that’s in that file you can’t update unless the database is up?

Note: This information is based on an Oracle 9i installation on Solaris. Your mileage may vary. I have also chosen to ignore issues of RAC installation. In my example I have used ORADB as my SID.

Am I using a pfile or an spfile?

The first thing to check is if you have a pfile or spfile. They can be specified at startup or found in the default location. The default path for the pfile is $ORACLE_HOME/dbs/init$ORACLE_SID.ora and the default for the spfile is $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora.

If both a pfile and an spfile exist in their default location and the database is started without a pfile='/path/to/init.ora' then the spfile will be used.

Assuming your database is running you can also check the spfile parameter. Either the command SHOW PARAMETER spfile or SELECT value FROM v$parameter WHERE name='spfile'; will return the path to the spfile if you are using one. If the value of spfile is blank you are not using an spfile.

The path to the spfile will often be represented in the database by ?/dbs/spfile@.ora. This may seem cryptic, but Oracle translates ? to $ORACLE_HOME and @ to $ORACLE_SID so this string translates to the default location of the spfile for this database.

How can I create an spfile from a pfile?

As long as your pfile is in the default locations and you want your spfile in the default location, you can easily create an spfile with the command CREATE SPFILE FROM PFILE;.

If you need to be more specific about the locations you can add paths to the create command like this:

CREATE SPFILE='/u01/app/oracle/product/9.2/dbs/spfileORADB.ora'
FROM PFILE='/u01/app/oracle/product/9.2/dbs/initORADB.ora';

These commands should work even when the database is not running! This is important when you want to change a database to use an spfile before you start it.

How can I create a pfile from an spfile?

The commands for creating a pfile are almost identical to those for creating a spfile except you reverse the order of spfile and pfile:

If your pfile is in the default location and you want your spfile created there as well run CREATE SPFILE FROM PFILE;.

If you have, or want them in custom locations specify the paths like this:

CREATE PFILE='/u01/app/oracle/product/9.2/dbs/initORADB.ora'
FROM SPFILE='/u01/app/oracle/product/9.2/dbs/spfileORADB.ora';

Again, this can be done without the database running. This is useful when the database fails to start due to a parameter set in the spfile. This is also a good step to integrate into your backup procedures.

How can I see what’s in my spfile

To view the settings in the spfile we have two options: First, we can use the command above to create a pfile from the spfile. This is simple, and fairly fast, but unnecessary if the database is running.

The better way, if the database is running, is to select the parameter you want to view from the oracle view v$spparameter with a command like this:

SELECT value FROM v$spparameter WHERE name='processes';

If you try to view the spfile with a text editor it may seem like it is plain text, but beware! The spfile will not behave correctly (if it works at all) if it has been edited by a text editor.

How can I update values in my spfile?

The values in spfile are updated with the ALTER SYSTEM command, but to update the spfile we add an additional parameter of SCOPE.

ALTER SYSTEM SET processes=50 SCOPE=spfile;

This command would update the parameter processes in the spfile. Since this parameter can only be set at startup, we say SCOPE=spfile and the change will be reflected when the database is restarted. Other options for SCOPE are memory which only changes the parameter until the database is restarted, and both which changes the instance immediately and will remain in effect after the database is restarted.

How can I update values in my spfile when my database won’t start?

So your database won’t startup because of a problem in your spfile. You can’t edit it with a text editor and you can’t use ALTER SYSTEM because your database is not running. It sounds like a problem, but really isn’t. Here’s what you do:

Connect up to your database as sysdba. You should get the message Connected to an idle instance

Run the command CREATE pfile FROM spfile; specifying the location as above if necessary. You should now have a fresh version of the spfile.

Edit the pfile to update the parameter you need to update.

Run the command CREATE spfile FROM pfile; to move the changes you have just made back into the spfile.

Startup the database normally. It should read the changed spfile and start up correctly. You can optionally delete the pfile if you are done.

oracle, dba, database administration, database