 |
|
Oracle - Capturing Date Information in UNIX
Oracle 11g New Features Tips by Burleson
Consulting |
Capturing date
information in UNIX
Advanced Oracle Utilities: The Definitive Reference by Rampant
TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following
is an excerpt from the book.
It is a common practice to capture the date from
the UNIX server. As with
Oracle, UNIX dates have a default date format that can be modified
according to the specified needs.
The default date output for UNIX is below:
Tue Sep 4
10:29:40
EDT 2001
To change the date display
format involves the use of a date format mask, very similar to using
the Oracle nls_date_format to change Oracle dates (Table 2.2).
|
Format
|
Meaning
|
|
%d
|
Day of
the month as a two-digit decimal number [01-31].
|
|
%e
|
Day of
the month as a two-character decimal number
|
|
%E
|
Combined Emperor/Era name and year.
|
|
%H
|
Hour
(24-hour clock) as a two-digit decimal number [00-23].
|
|
%I
|
Hour
(12-hour clock) as a two-digit decimal number [01-12].
|
|
%j
|
Day of
the year as a three-digit decimal number [001-366].
|
|
%m
|
Month
as a decimal two-digit number [01-12].
|
|
%M
|
Minute
as a decimal two-digit number [00-59].
|
|
%p
|
Equivalent of either AM or PM.
For example, PM.
|
|
%S
|
Second
as a two-digit decimal number
|
|
%t
|
Tab
character.
|
|
%u
|
Weekday as a one-digit decimal number [1-7].
|
|
%U
|
Week
number of the year (Sunday as the first day of the week) as a
two-digit decimal number [00-53].
|
|
%V
|
Week
number of the year (Monday as the first day of the week) as a
two-digit decimal number [01-53].
|
|
%w
|
Weekday as a one-digit decimal number [0-6]
|
|
%W
|
Week
number of the year (Monday as the first day of the week) as a
two-digit decimal number [00-53].
|
|
%x
|
Current date representation.
For example, 01/12/94.
|
|
%X
|
Current time representation.
For example, 19:45:58.
|
|
%y
|
Year
without century as a two-digit decimal number [00-99].
|
|
%Y
|
Year
with century as a four-digit decimal number [1970-2069].
For example, 1994.
|
|
%Z
|
Time
zone name (or no characters if time zone cannot be
determined). For
example, PST.
|
Table 2.2:
Formats
for the UNIX Date Command
This table shows that UNIX has far more date format
options than Oracle.
root> date "+DATE: %m/%d/%y%nTIME: %H:%M:%S"
DATE: 09/04/01
TIME: 09:37:49
The system date from a UNIX
server can be retrieved from inside a SQL*Plus script, as shown below.
Spool the SQL*Plus output to a file using the UNIX date
function:
gen.ksh
#!/bin/ksh
# First, we must set the
environment . . . .
# script is called with ORACLE_SID as argument
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut
-f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus -s /<<!
spool `hostname`_`date
+%d_%m_%y`.lst
select count(*) from dba_data_files;
spool off;
exit
!
Once the script has run, use
the UNIX ls command, to see that the spool file contains the UNIX
hostname and the UNIX date:
cheopsdb-02_04_09_01.lst
temp.ksh
get_files.ksh
Following is a look at how
the DBA can use UNIX commands to manage connected users.
UNIX user
identification
To see all users who are currently signed on to the
UNIX server, the “who” command can be used.
Note that the who command does not show Oracle users who have
connected using the Oracle Database Listener. Users logged into the
Oracle database using the Listener show up as Oracle processes.
root
ttyp1
Aug 31 19:09
tlmason
ttyp2
Sep 4 08:31
dbogstad ttyp3
Sep 4 06:33
clarson ttyp4
Sep 4 07:20
mgeske
ttyp5
Sep 4 06:35
vogden
ttyp6
Sep 4 06:45
crmoore ttyp7
Sep 4 06:45
yliu
ttyp8
Sep 4 06:47
mbell
ttyp9
Sep 4 06:54
acook
ttypa
Sep 4 06:58
rwestman ttypb
Sep 4 08:06
eboyd
ttypc
Sep 4 06:58
lhovey ttypd
Sep
4 07:00
mepeter
ttype
Sep 4 07:10
klong
ttypf
Sep 4 07:02
ldoolitt ttyq0
Sep 4 07:36
dwilken ttyq1
Sep 4 08:16
Options can be added to the who command to include
a count of all users on the Oracle server. In this example, there are
145 UNIX users connected to this Oracle server.
145
|
