Database Reference
In-Depth Information
Another multiuser consideration is that of the bad and log file names. What if you have many sessions
concurrently looking at the same external table or using parallel processing (which, in some respects, is a multiuser
situation)? It would be nice to be able to segregate these files by session, and fortunately you can do that. You may
incorporate the following special strings:
•
%p
: PID.
•
%a
: Parallel execution servers agent ID. The parallel execution servers have numbers 001, 002,
003, and so on assigned to them.
In this fashion, each session will tend to generate its own bad and log files. For example, let's say that you used
the following
BADFILE
syntax in the
CREATE TABLE
statement from earlier:
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1_%p.bad'
LOGFILE 'demo1.log_xt'
You would expect to find a file named similarly to the following if any records failed loading:
$ ls *.bad
demo1_7108.bad
However, you still might have issues over lengthy periods of time. The PIDs will be reused on most operating
systems. So the techniques outlined in dealing with errors may well still be relevant—you'll need to reset your bad file
or rename it if it exists if you determine this to be an issue.
Preprocessing
Preprocessing
is an external table feature that allows you to execute one or more operating system commands as the
first step when selecting from an external table. This feature was added in Oracle 11
g
Release 2, but was subsequently
back-ported to version 10.2.0.5 as well (so this works in Oracle 10
g
Release 2 and above). You invoke preprocessing by
specifying the
PREPROCESSOR
clause. The input of this clause can be an OS command or a shell script.
Sometimes the utility of preprocessing isn't intuitive. Consider the following use cases:
•
Dynamically display the output of an OS command (or a combination of commands) by
selecting from an external table
•
Search for files and filter columns and/or rows before displaying the data
•
Process and modify the contents of a file before returning the data
Examples of these scenarios follow.
Monitoring the File System Through SQL
In some of my customer's databases, the DBAs are using data files with automatic extension, but with many data files
sharing the same file system, such as the following and so on:
tablespace A, datafiles /u01/oradata/ts_A_file01.dbf autoextend unlimited
tablespace B, datafiles /u01/oradata/ts_B_file01.dbf autoextend unlimited
