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

Two students in my database class came to me yesterday with questions about a nested query they had written. The query looked fine, but when executed the query returned no rows. After considerable investigation I realized the subquery being evaluated against was not only returning the obvious values, but also returned NULL.

It turns out NULL gets very special treatment in Oracle. NULL is treated as unknown. Basically it cannot be evaluated against anything because you can’t evaluate something you can’t measure. To try to make this clear I offer the following examples:

Note: dual is a table used frequently in testing. It has no data but can be used to return calculations, text, test conditions, etc.

Here’s a basic select and condition that will always succeed:

SELECT 'true' FROM dual WHERE 1 = 1;


I think we can agree that, at least in the reality where we care to evaluate database queries, that 1=1, so this query returns the rows selected. In this case we have only selected one row, the string ‘true’

Now let’s take a look at the unusual behavior of NULL. First, here’s a query that should return no rows:

SELECT 'true' FROM dual WHERE 1 = NULL;

no rows selected

This makes sense because NULL does not equal 1, but now let’s look at another form of this statement:

SELECT 'true' FROM dual WHERE 1 != NULL;

no rows selected

Logically we think that 1 is different from NULL, so this should have returned ‘true’, but Oracle has a different idea. Oracle evaluates this by asking “Does 1 not equal an unknown value?” This makes as much sense to Oracle as asking “Does 3.17 equal a tree?” or “Is my birthday red?” so no matter what makes sense to us, Oracle evaluates this condition as FALSE.

We can take this one step further by executing the following query:


no rows selected

This illustrates that Oracle is completely unwilling to even try to evaluate NULL, but it starts to make sense that you would not say one unknown is, or isn’t equal to another unknown; therefore, NULL cannot be said to either be equal, or not equal to NULL.

Now let’s take a look at an IN condition.

SELECT 'true' FROM dual WHERE 5 NOT IN (1, 2, 3);


This IN statement returns TRUE because 5 is NOT IN the set of 1, 2, 3. Now let’s look at a slight variation.

SELECT 'true' FROM dual WHERE 5 NOT IN (1, 2, 3, NULL);

now rows selected

While 5 does not explicitly appear in the set, we do not know what NULL is. Since we cannot evaluate on the unknown NULL, the condition fails and no rows are returned.

So we can see that NULL must be handled as a special case. To handle this we must use IS NULL or IS NOT NULL. If we want to evaluate two values to see if they are both NULL we could use the following:



Here we see that, while NULL = NULL is not a valid condition, NULL IS NULL works just fine. Now let’s consider this in the context of a subquery.

SELECT first_name, last_name FROM faculty
WHERE id NOT IN (SELECT instructor_id FROM class);

This query will be valid only if the subquery SELECT instructor_id FROM class does not return any NULL values. If there are entries in the class table which have NULL values in the instructor_id column, the WHERE condition will always fail and no rows will be returned.

To make this statement more reliable (since we may plan not to have any NULL values now but some may make it in there) we can add a condition to the subquery.

SELECT first_name, last_name FROM faculty
SELECT instructor_id FROM class
WHERE instructor_id IS NOT NULL);

Now the result set from the subquery will never contain NULL and the condition will be properly evaluated.

oracle, dba, database administration, database, database programming

It is important for optimal performance to make sure you match up your Oracle RDBMS installation with your OS. Running a 32-bit version of Oracle on a 64-bit OS is may not give you peak performance, but also will not be able to address large segments of RAM and large files. So how do you know what your OS supports? How can you tell if that Oracle install from before you started is 64-bit? Here’s how:

Is my Operating System 64-bit?

In Solaris, from the command line (you don’t have to be root in most cases) run this command:

/usr/bin/isainfo -kv

If your OS is 64-bit, you will see output like:

64-bit sparcv9 kernel modules

If your OS is 32-bit, you will get this output:

32-bit sparc kernel modules

For Linux users

If you are running Linux, you can check your distribution with the uname command:

uname -m

The output will read x86_64 for 64-bit and i686 or similar for 32-bit.

How about this Oracle install? Is it 64-bit?

The question here is weather your Oracle binaries are 64-bit. While some of the binaries associated with Oracle may be 32-bit, the important ones will be 64 bit. To check those, follow these steps from the command line:

file oracl*

This will display the file type of your oracle binaries. If you are running 64-bit binaries, the output should look like this:

oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
oracleO: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

If your binaries are 32-bit, the output will look like this:

oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

If you find you are running 32-bit and decide to go to 64 be careful. The switch can be a bit tricky. Read the documentation closely and make sure your service contract is payed up!

oracle, dba, database administration, database, solaris, linux, sun, sun microsystems, 32-bit, 64-bit

National Weather Service LogoIn my previous post Better Weather Forecast I encouraged folks to try out the National Weather Service website for local forecasts without the irritating ads that The Weather Channel seems to run.

Well, one of my primary gripes about the National Weather Service has always been their obscure domain name,, was always a bit tough to remember, and it made it tough to spread the word too.

They seem to have gotten around to updating some links non their site and I noticed that many of their links now go to! This makes things much easier!

So either I’ve been missing it this whole time, or they’ve decided to start using this domain. Either way, check out for your local forecast national coverage, and even hurricane information! It’s still better than, and now it’s easier to remember!

weather, weather forecast, forecast, local weather, hurricane

In my previous article Oracle, SQL, Dates and Timestamps I talked about inserting, selecting and comparing dates in Oracle. Now I want to cover some functionality for converting between time zones.

We’ll use the table in the form specified in the previous article, but insert one more row:

insert into dates values(6, to_date('09/20/05 23:15', 'MM/DD/YY HH24:MI'));

The contents of the table now look like this:

1 09/14/05, 21:08
2 09/27/05, 00:00
3 10/02/05, 22:05
4 09/01/05, 17:01
5 09/12/05, 14:30
6 09/20/05, 23:15

Changing Time Zones

The date format in Oracle does not contain time zone information, but the database does. To find out the time zone set, execute this query:

SELECT dbtimezone FROM dual;


The time zone can be updated with the command:

ALTER database SET TIME_ZONE = '-05:00';

where you can specify the offset from Greenwich mean time or a valid time zone from the list in the v$timezone_names view. Note that this is one of the few of the ‘v$’ views which are plural.

Switching Time Zones

The function new_time is used to convert a time to different time zones. To illustrate this we’ll look at entry 5 from the dates file.

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 02:30 PM

This database is in US Eastern time but we want to display the time in US Central.

SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 01:30 PM

Here we clearly see the time converted to Central. Note that the new_time function is performed on the date field, not on the to_char. Now let’s grab this time in Pacific time:

SELECT entry, to_char(new_time(entry_date, 'EST', 'PST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 11:30 AM

Now we see not only the time converted, but also the time of day has gone from PM to AM.

Now let’s take a look at entry 6:

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/20/05 11:15 PM

We’ll again assume this timestamp is in US Eastern time, but let’s convert it this time to Greenwich Mean Time.

SELECT entry, to_char(new_time(entry_date, 'EST', 'GMT'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/21/05 04:15 AM

This shows not only the change in hours, but that the date of this entry is displayed properly for its time zone.

Of course the new_time function can be used on inserts in the same way. This is useful if you are allowing input from people in different geographical regions. Here we convert an entry made in Pacific Time to Eastern:

new_time(to_date('09/22/05 10:28 AM', 'MM/DD/YY HH:MI AM'), 'PST', 'EST'));

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=7;

7 09/22/05 01:28 PM

So we have converted 10:28 AM Pacific to 1:28 PM Eastern so all our entries in the table are consistent. Of course when performing the insert we need to put the to_date function within the new_time function so the text string is converted to a date format before we try to convert it.

oracle, sql, dba, database administration, database development

« Previous PageNext Page »