external table

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 describingexternal 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