Management and application of Oracle external tables

External tables as a table type of Oracle, although not as easy as normal library tables, but sometimes in the data migration or data loading, it also brings great convenience, sometimes more convenient than loading data with Sql*loader, the following will establish and apply external Table command and operation record as follows:

--Create directory objects that store files

Sqlplus Username/[email protected]

Create directory Ex_data as '/home/ex_data/';

--Create an external table

CREATE TABLE TAB1_EX
(
C1 VARCHAR2 (+ BYTE),
C2 VARCHAR2 (+ BYTE),
C3 VARCHAR2 (+ BYTE),
C4 VARCHAR2 (255 BYTE),
C5 VARCHAR2 (BYTE),
C6 VARCHAR2 (+ BYTE),
C7 VARCHAR2 (+ BYTE),
C8 VARCHAR2 (BYTE),
C9 VARCHAR2 (+ BYTE),
C10 VARCHAR2 (+ BYTE),
C11 VARCHAR2 (8 bytes),
C12 VARCHAR2 (8 bytes),
C13 VARCHAR2 (8 bytes),
C14 VARCHAR2 (8 bytes),
C15 VARCHAR2 (8 bytes),
C16 VARCHAR2 (8 bytes),
C17 VARCHAR2 (8 bytes),
C18 VARCHAR2 (8 bytes),
C19 VARCHAR2 (8 bytes),
C20 VARCHAR2 (+ BYTE),
C21 VARCHAR2 (8 bytes),
C22 VARCHAR2 (8 bytes)
)
ORGANIZATION EXTERNAL
(TYPE Oracle_loader
DEFAULT DIRECTORY Ex_data
ACCESS PARAMETERS
(Records delimited by newline
Fields terminated by ' | '
)
Location (ex_data: ' Tab1_ex.txt ')
);

--Apply External table

Select COUNT (*) from TAB1_EX;

--Note:

1. Although external tables can be queried like normal library tables, they cannot be indexed on your columns;

2. It is also not possible to insert into data inside the external table;

3. It is also not possible to update and delete data in external tables;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Management and application of Oracle external tables