Oracle教程 2023-02-23

Oracle 视图 ALL_EXTERNAL_TABLES 官方解释，作用，如何使用详细说明

Oracle视图ALL_EXTERNAL_TABLES是一个只读的固有视图，用来查看当前用户所创建的所有外部表的基本信息，包括表名、定义、数据源等。

使用步骤：
1、查看当前用户创建的所有外部表：
SELECT * FROM all_external_tables;

2、查看默认数据源：
SELECT default_directory_name FROM all_external_tables
WHERE table_name = ‘表名’;

3、查看外部表中每一列信息：
SELECT column_name,column_type,column_precision,staging_column_name
FROM all_external_columns
WHERE table_name = ‘表名’;

ALL_EXTERNAL_TABLES describes the external tables accessible to the current user.

Related Views

DBA_EXTERNAL_TABLES describes all external tables in the database.
USER_EXTERNAL_TABLES describes the external tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(128)`	`NOT NULL`	Owner of the external table
`TABLE_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the external table
`TYPE_OWNER`	`CHAR(3)`		Owner of the implementation type for the external table access driver
`TYPE_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the implementation type for the external table access driver
`DEFAULT_DIRECTORY_OWNER`	`CHAR(3)`		Owner of the default directory for the external table
`DEFAULT_DIRECTORY_NAME`	`VARCHAR2(128)`	`NOT NULL`	Name of the default directory for the external table
`REJECT_LIMIT`	`VARCHAR2(40)`		Reject limit for the external table, or `UNLIMITED`
`ACCESS_TYPE`	`VARCHAR2(7)`		Type of access parameters for the external table: `BLOB` `CLOB`
`ACCESS_PARAMETERS`	`CLOB`		Access parameters for the external table
`PROPERTY`	`VARCHAR2(10)`		Property of the external table: `REFERENCED` – Referenced columns `ALL` – All columns
`INMEMORY`	`VARCHAR2(8)`		Indicates whether the In-Memory Column Store (IM column store) is enabled (`ENABLED`) or disabled (`DISABLED`) for this table
`INMEMORY_COMPRESSION`	`VARCHAR2(17)`		Indicates the compression level for the IM column store: `NO MEMCOMPRESS` `FOR DML` `FOR CAPACITY [ HIGH \| LOW ]` `FOR QUERY [ HIGH \| LOW ]` `NULL` This column has a value based on where the segments lie for a table. For example, if the table is partitioned and is enabled for the IM column store, the value is `NULL` for `ALL_EXTERNAL_TABLES` but non-`NULL` for `ALL_XTERNAL_TAB_PARTITIONS`.