For some unknown reason, Oracle considers it necessary to distribute their UNIX software in .cpio files. Since this is the only time I ever use cpio, I can never remember the command and I always end up looking it up.

Well, for future reference, here is how you extract a .cpio file to the current directory on most platforms:

cpio -idmv < filename_to_extract.cpio

Some platforms, like AIX, may give errors like this with these options:

cpio: 0511-903 Out of phase!
cpio attempting to continue...

cpio: 0511-904 skipping 732944 bytes to get back in phase!
One or more files lost and the previous file is possibly corrupt!

cpio: 0511-027 The file name length does not match the expected value.

If you run into these you need to add the c option as the headers are stored in ASCII. The command should now look like this:

cpio -idcmv < filename_to_extract.cpio

For more information refer to the man page for cpio, but this is all I ever do with cpio. For a better UNIX archiving utility, consider tar.

Thanks to a recent product upgrade a sequence in one of our databases was reset to about 100,000 below its previous value. To reset it Oracle, and most other sites, tell me I need to drop the sequence and recreate it to change its current value.

To avoid dropping the sequence and invalidating all the triggers and anything else that is dependent on it I decided a different approach was in order.

I first determined the difference between the trigger and the maximum value in the table. I then changed the amount it increments with to the difference (plus a few to be save), selected nextval, then change it back to increment by 1.

The commands were something like this:

alter sequence id_sequence increment by 142900;

select id_sequence.nextval from dual;

alter sequence id_sequence increment by 1;

This method of course only works to increase the sequence. One additional risk is that something will increment the trigger while the increment is set high. In this case you’re stuck dropping the sequence and recreating it. Just remember, if it comes to this you’ll want to recompile all invalid objects so they won’t slow down the next time they run.

oracle, sql, dba, database administration, database development

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.

[SQL]CREATE OR REPLACE
FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
/**
* 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;

BEGIN

IF unixts > max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
‘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;

UNIXTS_TO
---------
02-SEP-04

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

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

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

« Previous PageNext Page »