Next: Exporting databases with Data Up: SQL*Loader Previous: SQL*Loader Contents

Loading SYSLOG into a table

As an example, lets load something suffiently SA-ish into our database... syslog. We'll create a table with 3 columns: one for the timestamp, one for the system name, and one for the message. First, lets create the table. We'll use a real user this time now that we know how to create users.

bash-2.05$ sqlplus ben/passwd
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 8 13:04:21 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> create table sys_log_tbl (
  2  timestamp date,
  3  hostname varchar2(12),
  4  message varchar2(1024)
  5  );

Table created.
SQL>

Ok, tables ready. Now the control file for SQL*Loader to use.

bash-2.05$ cat syslog.ctl 
-- SQL*Loader Control File for Syslog
-- Oct  5 11:28:33 vixen su: [ID 810491 auth.crit]....
-- Oct  5 11:29:06 vixen last message repeated 4 times
-- 1-----  -0----| |--0| |--------------->
-- 1----------->15 17-21 23--->1024
LOAD DATA
INFILE 'messages.0'
APPEND
INTO TABLE sys_log_tbl
(timestamp      POSITION(01:15) DATE "Mon DD HH24:MI:SS",
 hostname       POSITION(17:21) CHAR,
 message        POSITION(23:1024) CHAR
)
bash-2.05$

The control file contains a number of directives that direct SQL*Loader to do the right thing. The one we're using is pretty minimalist. You'll notice that the whole thing looks like one long SQL statement, because it is. The statement, if we read it outload say: Load data from the input file 'messages.0' and append it into the table sys_log_tbl. The statements in paranthasis following read like a table column discription. These statements will translate the intput file to the column format in the destination table. In this case we're using fixed field proccessing using the POSITION keyboard. If we read this we get: the first field "timestamp" is a date occupying charrectors 1-15, hostname is string of charrectors occupying charrenctors 17-21 of the input line, and finally message is a string of charrectors that extends from the 23rd charrector out to 1024 charrectors. There are a variety of diffrent parsing methods avalible other than using fixed proccessing, but in this case it works well. We could tune this even more including the use of conditionals to better parse the input but thats beyond the scope of this book. And now we can actually run SQL*Loader using our config file.

bash-2.05$ sqlldr USERID=ben/passwd CONTROL=syslog.ctl LOG=syslog.log 

SQL*Loader: Release 10.1.0.2.0 - Production on Fri Oct 8 17:06:28 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 301
bash-2.05$

Now that it's done, lets look at the table to see if it all ready went where it was supposed to.

bash-2.05$ sqlplus ben/passwd

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 8 17:09:23 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from sys_log_tbl;
......
TIMESTAMP HOSTNAME
--------- ------------
MESSAGE
--------------------------------------------------------------------------------
unix: [ID 100000 kern.notice]

05-OCT-04 vixen
genunix: [ID 723222 kern.notice] 00000000fff63cc0 unix:sync_handler+12c 
(fff57618, 1000000, 1412d55, 0, f0055aa6, f997fe48)

05-OCT-04 vixen
genunix: [ID 179002 kern.notice]   %l0-3: 0000000000000001 0000000000000001 
000000000001 00000000fff789b8
.......

This should give you some good ideas about how to quickly load data into your database. SQL*Loader is generally found to be the fastest method avalible for loading data into Oracle, followed closely by Data Pump imports. For more information on SQL*Loader, please refer to Part II of the Oracle Database Utilities guide. http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825/toc.htm For reference while composing parsing translations in your control files keep a copy of the Oracle Database SQL Reference handy. http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/toc.htm

Next: Exporting databases with Data Up: SQL*Loader Previous: SQL*Loader Contents

2005-02-10