Data Loading and Unloading - Expert Oracle Database Architecture - page 706

Using an External Table to Load Different Files
A common need is to use an external table to load data from differently named files over a period of time. That is, this
week we must load file1.dat , and next week it will be file2.dat , and so on. So far, we've been loading from a fixed
file name, demo1.ctl . What if we subsequently need to load from a second file, demo2.ctl ?
Fortunately, that is pretty easy to accommodate. The ALTER TABLE command may be used to repoint the location
setting of the external table:
EODA@ORA12CR1> alter table SYS_SQLLDR_X_EXT_DEPT location( 'demo2.ctl' );
Table altered.
And that would pretty much be it—the very next query of that external table would have it accessing the file
demo2.ctl .
Multiuser Issues
In the introduction to this section, I described three situations where external tables might not be as useful as
SQLLDR. One of them was a specific multiuser issue. We just saw how to change the location of an external
table—how to make it read from file 2 instead of file 1 and so on. The problem arises when multiple users each try to
concurrently use that external table and have it point to different files for each session.
This cannot be done. The external table will point to a single file (or set of files) at any given time. If I log in and
alter the table to point to file 1 and you do the same at about the same time, and then we both query that table, we'll
both be processing the same file.
This issue is generally not one that you should encounter—external tables are not a replacement for database
tables; they are a means to load data, and as such you would not use them on a daily basis as part of your application.
They are generally a DBA or developer tool used to load information, either as a one-time event or on a recurring
basis, as in a data warehouse load. If the DBA has ten files to load into the database using the same external table,
she would not do them sequentially—that is, pointing the external table to file 1 and processing it, then file 2 and
processing it, and so on. Rather, she would simply point the external table to both files and let the database process
both of them:
EODA@ORA12CR1> alter table SYS_SQLLDR_X_EXT_DEPT
2 location( 'file1.dat', 'file2.dat' )
3 /
Table altered.
If parallel processing is required, then the database already has the built-in ability to do this, as demonstrated in
the last chapter.
So the only multiuser issue would be if two sessions both tried to alter the location at about the same time
(assuming they had the privilege to ALTER the table)—and this is just a possibility to be aware of, not something
I believe you'll actually run into very often.