Answers to Chapter 17

Reviewing It

1: What is the purpose of SQL*Loader?
A1: SQL*loader can load data from specifically formatted files (files with data fields in fixed locations), files where the fields have some kind of field separator such as a ':', data that is in its own definition file, or it can even load data via the Unix pipe command
2: Why would someone use External Tables instead of SQL*Loader?
A2: External tables gives the user the ability to run full SQL against the data, performing data conversions with DECODE, concatenations, and so on
3: Why would you want SQL*Loader to skip records?
A3: Perhaps there is some header information in the data file that is not to be loaded or the user is creating some test data from the middle of a data file
4: What are Export/Import used for?
A4: Export and Import can be used to move data from one Oracle instance to another, to backup a particular users data, to create test environments, and to provide incremental backup and recovery.

Checking It

1: Which of the following is proper SQL*Loader command line syntax:

  1. Sqlldr userid=scott/tiger control=foo.ctl

  2. Sqlldr scott/tiger foo

  3. Sqlldr

  4. All of the above

A1: d
2: Data will be inserted and any data in the table will remain with which mode:

  1. INSERT

  2. APPEND

  3. REPLACE

  4. TRUNCATE

A2: b
3: The table is expected to be empty with which mode:

  1. INSERT

  2. APPEND

  3. REPLACE

  4. TRUNCATE

A3: a
4: INTEGER EXTERNAL is used to define numbers with decimal points?

True/False

A4: False
5: A converted SQL*Loader control file to an External file puts the CREATE TABLE in a separate file with a .SQL suffix.

True/False

A5: False, it puts it in the log file
6: Which of the following is NOT valid EXPORT syntax:

  1. EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

  2. EXP

  3. EXP USERID=SCOTT/TIGER

  4. All are valid syntax

A6: c.

Applying It

Independent Exercise 1:

  • Export the SCOTT schema.

  • Drop the SCOTT.EMP table.

  • Import just the SCOTT.EMP table.

Independent Exercise 2:

  • Create a SQL*Loader control file for EMP.

  • Delete all the rows in EMP.

  • Use SQL*Loader to put data into the SCOTT.EMP table.

Independent Exercise 3:

  • Create a simple data file using ':' as field delimiters.

  • Create an External Table based on this simple data file.

  • Query your simple data using the External Table.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.237.3