Oracle creates external tables

Oracle external tables can quickly import large amounts of data into the database, the external table is created using the following steps:

　　

1 Create a directory: Users must be created with the SYS user to hold external data files.

Create directory Dir_data as '/home/oracle/oradata/in ';

Give the user permission to use the directory, or the user cannot access the directory and the files under it

Grant Write,read on directory Dir_data to Test;

2 The external data file to be imported, test_file.csv into the directory, note the delimiter for each field of the file, where the delimiter for each column in CSV is English semicolon ";" The following

Name;name_desc
Latiny1;latiny test File
Latiny2;latiny test File
Latiny3;latiny test File
Latiny4;latiny test File
Latiny5;latiny test File
Latiny6;latiny test File
Latiny7;latiny test File
Latiny8;latiny test File
Latiny9;latiny test File
Latiny10;latiny test File

3 Creating statements

CREATE TABLE Ext_test
(
Name Char (256),
Name_desc Char (256)
)
Organization External
(
Type Oracle_loader
Default directory Dir_ctrr_data
Access parameters
(
Records delimited by newline
Badfile dir_ctrr_data: ' Test. Bad
LogFile dir_ctrr_data: ' Test. Log
Discardfile dir_ctrr_data: ' Test. DSc
Skip 1
Fields terminated by '; '
Reject rows with all null fields
(
NAME char (256),
Name_desc Char (256)
)
)
Location (' Test.csv ')
)
Reject Limit Unlimited
Parallel

Parameter description:

1) Organization external --keyword, must have. To indicate that the table defined is an external table.
2) type--types of external tables
Oracle_loader- The default way to define an external table is to load the text data in a read-only manner.
Oracle_datapump- supports loading and unloading of data, the data file must be a binary dump file. You can extract data from an external table to an internal table, or you can unload data from an internal table as a binary file into an external table.

3) Default directory--MissingThe province's directory indicates the path to which the external file resides
4) location--Defines the location of the external table
5) Access Parameters -- describes how to access external tables
Records--keyword defines how data rows are recognized
delimited by ' xxx '--line break, commonly used newline to define line breaks, and to indicate a character set. Special characters need to be defined separately, such as special symbols, you can use the Ox ' 16-bit value ', for example, tab (/T) 16 bits is 9, then delimitedby0x ' ", CR (/R) 16 bits is D, then is delimitedby0x ' 0d '.
Skip X--Skip X rows of data, some files in the first row is a column name, you need to skip the line, skip 1 is used.
Fields --Keyword defines how fields are identified, commonly used as follows:
fields:terminated by ' x '--Field separator.
enclosed by ' x '--The field reference, the data contained within this symbol, is treated as a field. For example, a row of data formats such as: "ABC", "A" "B," "C,". Using the parameter terminated by ', ' enclosed by ' ", the system will read two fields, the first field value is ABC, the second field value is a" B, "C,.
Lrtrim--Remove the whitespace characters.
missing field values is null -- Some field vacancy values are set to NULL. For field lengths and delimiters that are indeterminate and ready to be used as external table files, you can use UltraEdit, EditPlus, and so on for analysis testing, and if the files are large, consider splitting the files into small files and extracting the data from them for testing.

6) fields terminated by "," --Description field Terminator
7) Reject limit unlimited --describes the number of errors allowed, which is unrestricted

4 Viewing data

SELECT * from Ext_test;

Oracle creates external tables