What are moving data methods in oracle and how it works?

Here I will first load data using Sql * Loader utility and then by using external tables.

Sql * Loader loads data from external files into tables of an oracle database.It has powerful data parsing engine that puts little limitation on format of data in data file.                                      Input data files:  Sql * Loader reads data from one or more files that are specified in control file. A particular data can be in a fixed data format ,variable record format or streamed record format. The record file format is specified using INFILE parameter.If no record format is specified then default is stream record format. Here I loaded  data from Traffic_Violations.csv.  Location of file is specified in control file.

Control Files: A control file is a text file written in language that Sql * Loader understands.Control file indicates Sql * Loader where to find data,how to parse and interpret, where to insert . Control files has three sections

Global options such as input data file name and records to be skipped                                    INFILE: specifies location where the data is to be loaded.                                                            Data to be loaded.                                                                                                                                        Below is syntax to create a control file.Here I specified all the columns that I want to load from   Traffic_Violations.csv.  Make sure that You have a ready definition(table created) for Traffic_Violations  in database. Then I saved us_trafice_violation_mst.ctl.                                 I also created a blank file us_trafice_violation_mst.log which is loaded when the batch command is executed.

The batch command is as follows:

sqlldr username/password control=control filepath log= log filepath

The second section consists of INTO blocks which contains information about table.                Third section is optional and if present it contains input data.                                                            When Sql * Loader begins executiom it loads log files.Log files contains detailed summary of load,including description of errors occurred during load.

And all the data is loaded from Traffic_Violations.csv

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j

Latest Post by this Author

• PLSQLApril 26, 2020How effectively we can use temporary tables in Oracle?
• Big DataAugust 15, 2019How to analyze hadoop cluster?
• Big DataJuly 28, 2019How to setup Hadoop cluster using cloudera vm?
• Big DataMay 25, 2019How to configure parameters in Hadoop cluster?