Chegg
n.
A situation where there are two things, each of which are dependant upon the other in order to exist, thus causing the paradoxical question of which came (or must come) first. Derived from the question “Which came first, the chicken or the egg?”

Example: You need experience to get a job; however you need a job to get experience.

Origin: Jon Graton, Plymouth State University; Plymouth, NH

office lingo, buzzword, buzzwords, language, definitions

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

Pocket Religion
n.
A strong belief which arises in a small, isolated group. The strength of this belief is often caused and always enhanced by the isolation. Typically the idea is one which would be proven less significant or even false within a larger scope.

Origin: Ken Kochien, Plymouth State University; Plymouth, NH

office lingo, buzzword, buzzwords, language, definitions

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.

« Previous PageNext Page »