How to import data from a text file using an Oracle external table

Colleague recently in busy data consistency than work, need to different text files in the comparison of data, some files larger, more records, if the ordinary text editor open, it is obvious that will be very card, even can not open.

Based on this, the data of the text file can be imported into the database and compared at the level of the collection.

So how do you import data from a text file into a database? Here, the main advantage of Oracle's external table features.

Oracle External Tables support two types of drivers: one is Oracle_loader, the data for the external table must originate from the file file, the other is oracle_datapump, and the external table data must be a binary dump file, The dump file is a file that was previously populated with data from an Oracle internal table into an external table. Clearly, Oracle wants to keep the data inside the database for processing.

First, let's look at the size and record of the text file.

[Email protected] ~]$ du-sm p_20150626010000_2002371.0003479598 274    P_20150626010000_2002371.0003479598[[email Protected] ~]$ wc-l p_20150626010000_2002371.0003479598 2899265 p_20150626010000_2002371.0003479598

From the above output can be seen, the file 274M, there are 2,899,265 records.

Second, build the Create external table statement.

CREATE TABLE emp_load   (subsid number),    servnumber VARCHAR2 (All CHAR), Subsprodid number    ,    ProdID VARCHAR2 (+ char),    startdate date,    enddate  date,     owner VARCHAR2 (4 CHAR)) ORGANIZATION EXTERNAL   (TYPE oracle_loader    DEFAULT DIRECTORY tmp    ACCESS PARAMETERS      (RECORDS delimited by NEWLINE        Fields TERMINATED by "|"             (Subsid      Decimal EXTERNAL,               servnumber  char,               subsprodid  DECIMAL EXTERNAL,               prodid  Char (32 ),                startdate Date "Yyyymmddhh24miss",               enddate Date "Yyyymmddhh24miss",               owner   CHAR (4)               )      )    Location (' p_20150626010000_2002371.0003479598 ')   

Note that the directory TMP must exist because I am executing under the Scott user, so the Scott user must have read and write access to the path.

Third, execute the Build table statement under the Scott user.

Iv. See if there is a problem with the generated external table

Sql> Select COUNT (*) from Emp_load;      COUNT (*)------------------       2899265

The record matches the number of records viewed by the wc-l.

Note that the absence of an error during the construction of the table does not necessarily mean that the data has been successfully loaded in the external table. You must query the external table to determine if the data has been successfully loaded, and if you have an error message, see the log file generated in the current directory, in this case, Emp_load_2000.bad and Emp_load_2000.log.

Of course, data in external tables can only be queried, not DML operations, such as randomly deleting a single piece of data from a table

Sql> Delete from Emp_load where rownum=1;delete from Emp_load where Rownum=1            *error in line 1:ora-30657:operation Not supported on external organized table

If you want to perform DML operations on the external table data, you can first import the data from the external table into the internal table. The steps are as follows:

Sql> CREATE TABLE Test as SELECT * from Emp_load where 1=0; Table created. sql> INSERT/*+ APPEND * * To test SELECT * from emp_load;2899265 rows created. Elapsed:00:01:00.29sql> SELECT * FROM Test where rownum<=100;select * from test where rownum<=100              *error at Line 1:ora-12838:cannot read/modify A object after modifying it in parallelsql> commit; Commit complete. Sql> SELECT * FROM Test where rownum<=100;

Here, in order to save time, I used a direct path to insert, it can be seen that the insertion of nearly 3 million data, only 1 minutes or so, considering the database on my virtual machine, only to allocate 300M of memory, load efficiency is quite considerable.

Sql> Show parameter Memoryname                     TYPE     VALUE------------------------------------------------------------- ----------------hi_shared_memory_address         integer     0memory_max_target             Big integer 300mmemory_target                 Big integer 300mshared_memory_address             integer     0

Later, the time spent on traditional path insertion was tested, and in order to compare it with direct path insertion, eliminate the effects of SQL parsing, data buffer, and empty the shared pool and buffer cache, as follows:

Sql> conn/as sysdbaconnected.sql> alter system flush Shared_pool; System altered. Sql> alter system flush Buffer_cache; System altered. Sql> Conn scott/tigerconnected.sql> Set timing onsql> INSERT INTO Test select * from emp_load;2899265 rows Create d.elapsed:00:01:05.36

Insert with traditional path (that will produce redo log), take 1 minutes 5 seconds, compared to direct path insertion, the two efficiency is not small, it seems that the data volume is small, can not obviously reflect the advantages of direct path insertion speed.

Note: In Sql*plus, the Number field output defaults to 10, which results in a value of 937116510102250300, which may be displayed as 9.3712E+17, which can be set numwidth 18来 displays the value of the full Number field.

How to import data from a text file using an Oracle external table