9, 10, 11 The columns into which to insert the data.
This is a very simple example. The syntax of the controlfile can handle a wide
range of formats with intelligent parsing to fix any deviations in format such as length
or data types. In general you can assume that it is possible to construct a controlfile
that will understand just about any input datafile. However, do not think that it is
always easy.
TIP It may be very difficult to get a controlfile right, but once you have it, you
can use it repeatedly, with different input datafiles for each run. It is then the
responsibility of the feeder system to produce input datafiles that match your
controlfile, rather than the other way around.
External Tables
An external table is visible to SELECT statements as any other table, but you cannot
perform DML against it. This is because it does not exist as a segment in the database:
it exists only as a data dictionary construct, pointing toward one or more operating
system files. Using external files is an alternative to using SQL*Loader, and is often
much more convenient.
The operating system files of external tables are located through Oracle directory
objects. Directories are also a requirement for Data Pump, discussed later in this
chapter.
Directories
Oracle directories provide a layer of abstraction between the user and the operating
system: you as DBA create a directory object within the database, which points to a
physical path on the file system. Permissions on these Oracle directories can then be
granted to individual database users. At the operating system level, the Oracle user
will need permissions against the operating system directories to which the Oracle
directories refer.
Directories can be created either from a SQL*Plus prompt or from within Database
Control. To see information about directories, query the view DBA_DIRECTORIES.
Each directory has a name, an owner, and the physical path to which it refers. Note that
Oracle does not verify whether the path exists when you create the directory—if it does
not, or if the operating system user who owns the Oracle software does not have
P AR T III
permission to read and write to it, you will only get an error when you actually use the
directory. Having created a directory, you must give the Oracle database user(s) who
will be making use of the directory permission to read from and write to it, just as your
system administrators must give the operating system user permission to read from
and write to the physical path.
EXAM TIP Directories are always owned by user SYS, but any user to whom
you have granted the CREATE ANY DIRECTORY privilege can create them.
Figure 23-1 demonstrates how to create directories, using SQL*Plus. In the figure,
user SCOTT attempts to create a directory pointing to his operating system home
directory on the database server machine. This fails because, by default, users do not
have permission to do this. After being granted permission, he tries again. As the
directory creator, he will have full privileges on the directory. He then grants read
permission on the directory (and therefore any files within it) to all users, and read
and write permission to one user. The query against ALL_DIRECTORIES shows that
the directory (like all directories) is owned by SYS: directories are not schema objects.
This is why SCOTT cannot drop the directory, even though he created it.
Using External Tables
A common use of external tables is to avoid the necessity to use SQL*Loader to read
data into the database. This can give huge savings in the ETL (extract-transform-load)
cycle typically used to update a DSS system with data from a feeder system. Consider
the case where a feeder system regularly generates a dataset as a flat ASCII file, which
should be merged into existing database tables. One approach would be to use
SQL*Loader to load the data into a staging table, and then a separate routine to
merge the rows from the staging table into the DSS tables. This second routine cannot
start until the load is finished. Using external tables, the merge routine can read the
source data from the operating system file(s) without having to wait for it to be loaded.
Bạn đang xem 9, - OCA OCP ORACLE DATABASE 11G A LL IN ONE EXAM GUIDE P88 POT