external table

来源:互联网 发布:淘宝鞋主页设计图片 编辑:程序博客网 时间:2024/03/29 01:44

external table

A read-only table whose metadata is stored in the database but whose data in stored in files outside the database. The database uses the metadata describing external tables to expose their data as if they were relational tables.


创建目录:CREATE DIRECTORY DIR_TEST AS 'D:/';    (删除目录用DROP DIRECTORY DIR_TEST)

赋予目录权限给相应用户:GRANT READ ON  DIRECTORY DIR_TEST TO HR;

查看系统已经定义的目录:SELECT * FROM DBA_DIRECTORIES;

查看外部表信息:SELECT * FROM DBA_EXTERNAL_TABLES;SELECT * FROM ALL_EXTERNAL_TABLES;SELECT * FROM USER_EXTERNAL_TABLES;


可以在查询语句中将 External Table 与数据库中其他表进行连接(Join),但是不能给 External Table 加上索引,并且不能插入/更新/删除数据,不能添加虚拟列,因为它不是真正的数据库表。另外,如果与数据库相关联的外部文件被改变或者被删除,这会影响到 External Table 返回查询结果。

OCP题库考察此知识点:
Which two operations can be performed on an external table? (Choose two.)
选项
A.Create a view on the table.
B.Create an index on the table.
C.Create a synonym on the table.
D.Add a virtual column to the table.
E.Update the table using the UPDATE statement.
F.Delete rows in the table using the DELETE command.

The answer is A 、C

http://blog.csdn.net/perfect_db/article/details/8899922

QUESTION 16
EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
A. UPDATE empdet
SET ename = 'Amit'
WHERE empno = 1234;
B. DELETE FROM empdet
WHERE ename LIKE 'J%';
C. CREATE VIEW empvu
AS
SELECT * FROM empdept;
D. CREATE INDEX empdet_idx
ON empdet(empno);
Answer: C

http://blog.csdn.net/perfect_db/article/details/8899922

QUESTION 26
Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an
external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the
database from an external table.
Answer: D

本题解释:A选项错误的原因是,该子句的默认值是0 而不是无限;

               D的翻译是:可用于常规的表在数据库中的数据卸载到外部表

               B错误是因为源数据是应存在于数据库中;

               C错误是因为他们的功能不一样,一个是load时用,一个是unload时用;

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
B. REJECT LIMIT
C. DEFAULT DIRECTORY
D. ACCESS PARAMETERS
Answer: C


我理解的是如果没有这个子句,就不知道到哪里去找“emp.dat”这个文件; 
a 选项可以省略,默认是oracle_loader
b和d也都不是必选项;


最后附上一幅官方对external table的图:


原创粉丝点击