1. Create two external files in 1.txt and 2.txt with only one data.
2. Create a logical directory and perform proper authorization:
SQL> create directory ztj1_dir as 'C: \ ztj1 \';
Directory created
Then, manually create the actual directory in the physical path, for example, create the ztj directory folder under drive c.
SQL> grant read on directory ztj1_dir to scott
Authorization successful
SQL> grant write on directory ztj1_dir to scott
Authorization successful
Note: After the logical directory is created, copy the flat file to the directory. Note that the file name should not be written incorrectly. For example, move the created external files 1.txtand 2.txt to the manually Created directory (c: \ ztj)
3. Create an External table
SQL> ED
Edit written files
Create table demo (
Emp_id number (4 ),
Ename varchar2 (12 ),
Job varchar2 (12 ),
Mgr_id number (4 ),
Salary number (8 ),
Comm number (8 ),
Dept_id number (2)
)
Organization external
(
Type Oracle_loader
Default directory ztj1_dir
Access parameters (records delimited by newline
Skip = 1 // skip the first line. If the first line is not the title, skip this parameter.
Fields terminated ",")
Location('1.txt', '2.txt', '3.txt ')
)
SQL>/
Table created
4. Perform the select Operation to check whether the operation is correct.
SQL> select * from demo;
To obtain information about the External table:
SQL> select owner, table_name, default_directory_name, access_parameters from dba_external_tables;
If the DBA wants to know the position of the flat file, use the following query:
SQL> select * from DBA_EXTERNAL_LOCATIONS;
Steps:
Free in http://linux.bkjia.com/
The username and password are both www.bkjia.com
Detailed description of the External table is successfully created in the download directory/June 1, 2013, June 11, January, and Oracle 10 Gb.