QUESTION 56

Which mandatory(强制性的) clause has to be added to the following statement to successfully create an external table called EMPDET? 
CREATE TABLE empdet (
   empno CHAR(2),
   ename CHAR(5),
   deptno NUMBER(4)
   ) 
ORGANIZATION EXTERNAL (
   LOCATION ('emp.dat')
   );
A. TYPE 
type 数据转换驱动器，oracle_loader为默认，也可以改换其他如databump
B. REJECT LIMIT 
REJECT LIMIT遇到错误退出，0为默认值,可以指定值或使用unlimited
C. DEFAULT DIRECTORY (right)
DEFAULT DIRECTORY工作目录，必须显式指定外部表的目录路径，不设定工作目录是无法创建外部表的
D. ACCESS PARAMETERS
转换参数，如列分割符，错误的设置不会妨碍对外部表的创建，但是访问外部表时会出错

Answer: C 

Explain:

• TYPE - specifies the type of external table. The two available types are theORACLE_LOADER type and theORACLE_DATAPUMP type. Each type of external table is supported by its own access driver.

  • The ORACLE_LOADER access driver is the default. It loads data from external tables to internal tables. The data must come from text data files. (TheORACLE_LOADER access driver cannot perform unloads; that is, it cannot move data from an internal table to an external table.)

  • The ORACLE_DATAPUMP access driver can perform both loads and unloads. The data must come from binary dump files. Loads to internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the binary dump files of the external table. TheORACLE_DATAPUMP access driver can write dump files only as part of creating an external table with the SQLCREATE TABLE AS SELECT statement. Once the dump file is created, it can be read any number of times, but it cannot be modified (that is, no DML operations can be performed).

• DEFAULT DIRECTORY - specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path. You must create the directory object before you create the external table; otherwise, an error is generated. See"Location of Data Files and Output Files" for more information.

• ACCESS PARAMETERS - describe the external data source and implements the type of external table that was specified. Each type of external table has its own access driver that provides access parameters unique to that type of external table. Access parameters are optional. See "Access Parameters".

• LOCATION - specifies the data files for the external table. The files are named in the formdirectory:file. Thedirectory portion is optional. If it is missing, then the default directory is used as the directory for the file.

file:///E:/11g官方文档/11g官方文档/E11882_01/server.112/e22490/et_concepts.htm#SUTIL011

Simple:

SQL> CREATE TABLE emp_load  2    (employee_number      CHAR(5),  3     employee_dob         CHAR(20),  4     employee_last_name   CHAR(20),  5     employee_first_name  CHAR(15),  6     employee_middle_name CHAR(15),  7     employee_hire_date   DATE)  8  ORGANIZATION EXTERNAL  9    (TYPE ORACLE_LOADER 10     DEFAULT DIRECTORY def_dir1 11     ACCESS PARAMETERS 12       (RECORDS DELIMITED BY NEWLINE 13        FIELDS (employee_number      CHAR(2), 14                employee_dob         CHAR(20), 15                employee_last_name   CHAR(18), 16                employee_first_name  CHAR(11), 17                employee_middle_name CHAR(11), 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy" 19               ) 20       ) 21     LOCATION ('info.dat') 22    ); Table created.