External Table 1 (simple test)

An external table refers to a table that does not exist in the database. By providing Oracle with metadata that describes external tables, we can treat an operating system file (such as Excel, CSV, DAT, or TXT file) as a read-only database table, as if the data were stored in a common database table. An external table is an extension to a database table.
1. Use the SYS user to establish a logical directory and grant permissions to the specified user.

(to have read and write permissions to the directory in the operating system, directly using the Scott user, with insufficient permissions):

Sql> CreateDirectory TestDir1 as 'D:\oracletest\testDir';D Irectory created====Authorizing SQL> Grant Read  onDirectory TestDir1 toScott;GrantSucceededsql> GrantWrite onDirectory TestDir1 toScott;GrantSucceeded

===============
2. Prepare the data file:
Place the test data file TestEmp.txt file in the D:\oracletest\testDir directory. File contents:

7369SMITH Clerk7499ALLEN salesman7521WARD salesman7566JONES MANAGER7654MARTIN salesman7698BLAKE MANAGER7782CLARK MANAGER7788SCOTT ANALYST7839KING President7844TURNER salesman7876ADAMS Clerk7900JAMES Clerk7902FORD ANALYST7934MILLER Clerk

3. Create an external table testextemp

Sql> Create TableTestextemp (2Empno Number(4), enamevarchar2(Ten), Jobvarchar2(9))3Organization External (4Type Oracle_loader--Data Conversion Drive, Oracle_loader is the default, you can also change other, such as Oracle_datapump5 defaultDirectory TestDir1--specify the directory in which the external table resides6Access parameters (fields terminated bywhitespace)7Location'TestEmp.txt')--Specifying data Files8 );TableCreated

4. Query the external table:

Sql> Select *  fromtestextemp; EMPNO ename JOB----- ---------- ---------7369SMITH Clerk7499ALLEN salesman7521WARD salesman7566JONES MANAGER7654MARTIN salesman7698BLAKE MANAGER7782CLARK MANAGER7788SCOTT ANALYST7839KING President7844TURNER salesman7876ADAMS Clerk7900JAMES Clerk7902FORD ANALYST7934MILLER Clerk -Rows selected

5. Description
1) External tables are tables that are not in the database, such as a text file that is split in a certain format on the operating system, or other types of tables, that is, the data is organized outside the database and is an operating system file.
2) This external table is like a view for an Oracle database, and can be queried in the database like a view.
3) This view allows the user to run any SQL statement on the external data without first loading the data from the external table into the database.
4) Be aware that external data tables are read-only and cannot be changed.
5) The flags of the operating system files in the database are mapped by a logical directory.
6) You cannot run any DML operations on it, and you cannot create an index.
7) can query operation and connection, can operate in parallel.

External Table 1 (simple test)