No Oracle geek should be without one of these.

If you’ve been an Oracle DBA for long you know the horrors of navigating through Oracle’s “data dictionary”, the tables and views which contain all the metadata about your database. While views like dba_objects and dba_tab_privs are their own level of hell, one thing is certain… the v$ views are far worse.

While the names of the v$ views are not terrible in themselves (e.g. v$session, v$datafile, v$tablespace) the true crux of the problem is you never use them. Any time your database is running fine, you would go to the dba_ tables and the like. Only when your database refuses to open after a fairly nasty crash and users are knocking on your door asking when you will have the database up do you finally need the v$ views, and then you can’t remember any of them.

v$viewsEnter tusc.com. I’m not sure how long they have been doing this, but they offer a free poster of the Oracle v$ views. I’ve seen these as far back as Oracle 8.0, and the company has been around since 1988, so clearly they know what they’re doing.

Well, they seem to be a little confused about “free”, but basically they want eight bucks to send you one of these and two bucks for each additional one. I figure they’re not making any money on this, just covering shipping and handling (and not in the same way eBay-ers need $50 to cover shipping and handling so they can sell something for cheap).

Still not convinced? Tusc has also been nice enough to offer the poster in PDF format. Download it, check it out, put it somewhere and forget about it until you have a database misbehaving then go frantically after it.

oracle, sql, dba, database administration, database recovery, database

There are a couple ways of tracking logon and logoff information within an Oracle database, however few offer the simplicity and flexibility of the use of an logon and logoff trigger. Here is how I built a simple set of triggers and accompanying table to track usernames, logon time, logoff time, machine connected from and program used.

I performed these steps as system. If you want to install these as another user you will need to assign the appropriate permissions first.

The first step is to create a table to store the information. This table may grow quickly if this is a very active system so we want to put it somewhere other than the system tablespace. For reference, mine currently has around 3000 records in it and is around 200k in size. Of course mileage may vary.

CREATE TABLE session_audit
(
user_id VARCHAR2(30),
session_id NUMBER,
host VARCHAR2(30),
program VARCHAR2(60),
logon_time DATE,
logoff_time DATE
)
TABLESPACE users;

This will be the table you query to determine login information. Of course you will probably want to purge data from here occasionally to keep it from getting too big.

Next we create the logon trigger which will populate all but the logoff_time of the session_audit table. This uses the sys_context function to lookup the host and session id (different from the SID) of the session. The program is retrieved by a subquery on the V$SESSION table.

CREATE OR REPLACE TRIGGER tr_session_audit_logon
AFTER LOGON ON DATABASE
DECLARE
session_id number;
BEGIN
select sys_context('USERENV','SESSIONID') into session_id from dual;
IF session_id != 0 --ignore internal connections
THEN
BEGIN
INSERT INTO session_audit (
user_id,
session_id,
host,
program,
logon_time,
logoff_time
)
VALUES(
user,
session_id,
sys_context('USERENV','HOST'),
(SELECT program FROM v$session
WHERE sys_context('USERENV','SESSIONID') = AUDSID),
sysdate,
NULL
);
END;
END IF;
END;

UPDATE: I have found that the old version of this resulted in an ORA-1427 error when someone made an internal connection. The problem also came up when dbms_jobs were run. The code now ignores internal connections (where the session id is 0).

Finally we create the logoff trigger to fill in the logoff_time.

CREATE OR REPLACE TRIGGER tr_session_audit_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
UPDATE session_audit
SET logoff_time = sysdate
WHERE sys_context('USERENV','SESSIONID') = session_id;
END;

That’s it. You can now search the session_audit table for logins durring a time window or all logins for a specific user. You could even check who is using a certain application.

I mentioned one possible issue, that you don’t want the session_audit table to fill up your system tablespace, however it is also important to mention that if your logon trigger fails for some reason people will not be able to log in, so watch the space, no matter where you put it.

Donald Burleson (who desperately needs a new portrait for his website) of Burleson Consulting offers these instructions for a similar trigger, however he is grabbing more information and fills most of it in at the end.

oracle, sql, dba, database administration, database development, database security, database, oracle security

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.

National Weather Service LogoThe National Weather Service has once again provided a wealth of information on Hurricane Wilma. Check out their page on Wilma for projected path, satellite images (updated every 30 minutes) and much more.

You won’t find any videos of the destruction, or suicidal news reporters out in 100 MPH winds, but you will find plenty of factual information about the storm.

Wondering what the next name is after Wilma? The list of names (maintained by the World Meteorological Orginization) does not use names starting with x, y, or z, so after Wilma we will move on to Hurricane Alpha, then Beta, Gamma, etc. throught the greek alphabet. For more information on hurricane naming, check out the National Hurricane Center’s page on the topic.

hurricane wilma, wilma, hurricane, weather, storm, tropical storm, national weather service, nws, noaa

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

« Previous PageNext Page »