Experience in using external tables in Oracle databases

　External tableFor example, a text file or other types of tables separated by a certain format in the operating system. This external tableOracle DatabaseIt is like a view. You can perform queries and other operations in the database as you try. This attempt allows you to run any SQL statement on external data without loading the data in the External table into the database. However, the external data tables are read-only and cannot be changed. However, there are still many restrictions when using external tables.

Restriction 1: create a directory object first.

When using an external data table, you must first create a directory object. That is to say, to access operating system files in the Oracle database system (implementing control file multiplexing in Oracle), You must create a directory object pointing to the operating system file in the database, then, access the corresponding operating system file through the directory object, that is, the storage location of the operating file stored in the directory object. However, you must note that normal users of this directory object do not have the permission to create it. Generally, only privileged users or database administrators can create directory objects. If other users are allowed to create directory objects, they must be authorized to other users.

In fact, this directory object is the storage location of files or tables in the operating system. However, when creating an object, you must be careful that the Oracle database system will not confirm whether the directory actually exists. If the directory object is entered incorrectly, the External table may still be created normally, but data cannot be queried. Due to the lack of such self-check mechanism when creating directory objects, special attention is needed when assigning paths to this directory object. Note the case sensitivity of the path. In Windows, the path is case-insensitive. In Linux, the path must be case sensitive. Therefore, note the case differences when creating directory objects in different operating systems. It is best for database administrators to develop a habit of using lowercase directory names and file names in any operating system. In this case, different operating systems do not need to be subject to this case. Note that the directory object name must be unique. If the directory object name already exists, the system will prompt the error message "the name already has an existing object reference", instead of automatically overwriting the original object name settings.

Although this object name is easy to set, it is the only way for the database to find files on the operating system. Therefore, the directory object must be created accurately. Otherwise, the External table may not work properly.
The External table of the Oracle database is summarized here. The above is the experience summarized in my daily work. If you have anything wrong, please correct me, I am very happy to help you.