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