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.