SQL*Loader-interview-question

SQL*Loader Interview-Question-Answer

Q1:-What is the SQL*Loader in Oracle?

Ans:- SQL*Loader is Oracle utility, used for loading data from flat files to Oracle Table/Tables.

Q2:-Can we load selected data from data files by SQL*Loader?

Ans:- Yes. By "When" clause ,we can do the same.

Q3:-What is control file in SQL*Loader ?

Ans:-Control File is a text file with extension of ctl.Control File has the all instructions

to load which data to table.

Q4:-How we can enhance the performance of SQL*Loader ?

Ans:-By below steps we can perform the performance of SQL*Loader

    1) Disable the Constraint and Indexes available in the loading Table.

    2) Use the DIRECT=TRUE option to use direct path load

    3) Use Fixed width data rather than delimited data.

    4) Try to aviod character set conversion

    5) Use READSIZE and BINDSIZE parameter.READSIZE will grab larger chunks of data per read system call while BINDSIZE parameter specifies the size of the bind array,the number of rows which will be loaded per batch.

 

Q5:-What is the difference between the conventional and direct path loader?

Ans:-Conventional path loader essentially loads the data by using standard INSERT statements while Direct path loader (DIRECT=TRUE) bypass the logic involved with that, and loads directly into the Oracle data files.

Q6:-Can we download data from Database Tables to flat files.

Ans:- No

Q7:-Can we load multiple data files by single control file ?

Ans:- Yes

Q8:-Can we load data from one data file to multiple Database Tables ?

Ans:- Yes 

Q9:-Can we restrict SQL*Loader to COMMIT only at the end of the load file?

Ans:- No

Q10:-Can we skip header or some records of file to load into Tables ?

Ans:-Yes. SKIP=n option is there . where n is numeric value to skip the n records.

Q11:-Can we modify data before loading into Tables ?

Ans:- Yes. We can use Arithmetic Operators as well as some Functions like UPPER, LOWER, CASE, DECODE, SUBSTR to modify data before loading into table.

Q12:-Can we load data from data file, having different column separator ?

Ans:-Yes.

Q13:- Write a code for sample control file ?

Ans :- Below is the sample control file

Load data

infile data.txt

insert/replace/truncate/append into table table_name

fields terminated by "," optionally enclosed by " "

(col1,col2,col3)

Q14:-Which files created by SQL*Loader after execution?

Ans:- Below files are created by SQL*Loader after execution

1) Log File

2) Bad File

3) Discard File