Struggling with Your Workload?
Get Assignment Help Now

Questions

a. Populating Tables with Data with sqlldr
Objectives:

1. Create sqlldr control files for data loading
2. Run sqlldr and populate your tables with data

Procedure:
We will be using the sqlldr utility to load data from flat files and populate our tables. In order to tell sqlldr where to find the data, and how it maps into the columns of a table, a control file is used. The control file (not to be confused with database control files) is passed as a parameter to the sqlldr utility, and sqlldr opens the data file described in the control file, and loads in the data.
Make sure you run the sqlldr command from the unix directory where you have all the data files and the control file which you will create.
You can create the control file in windows notepad and transfer it to unix using filezilla.
Here is a sample sqlldr control file:
OPTIONS (SKIP=3, LOAD=1000)
LOAD
INFILE customer.dat
REPLACE INTO TABLE customer
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
CUSTOMERID
,FIRSTNAME
,LASTNAME
,PHONENO
,ADDRESS
,ZIPCODE
)
And here is are the first few records in the data file.
--SOURCE CSMN666LAB SPRING 20055555,,,,
#NAME?,CUSTFNAME,CUSTLNAME,PHONENUM,ADDRESS,ZIPCODE
#NAME?,,,,,
1,DE CAIRE,Lloyd,6731192012,56 Saginaw Street,10000-0051
2,BAUER,Jacob,3774806550,38 Lachine Street,10000-0051
3,DENNIS,Edward,4415948226,53 Ossineke Court,10000-0051
4,GAGNON,Christine,3908139978,26 South Branch Street,10000-0100
The OPTIONS entry in the control file takes several parameters. The two that are present in the example are SKIP and LOAD. The SKIP option tells sqlldr to skip the first three records in the data file, since the first three lines contain descriptions of the data rather than the data itself. The LOAD parameter tells sqlldr to load at most 1000 records.
Following OPTIONS, there is a LOAD command. This is where we describe how the data is to be loaded. The INFILE parameter tells sqlldr the name of the file to read from. Then, REPLACE INTO TABLE customer tells sqlldr which table to read the data into. Next is the description of how the data is delimited. In this case, the file contains comma-delimited data, and each field can optionally be surrounded by double quotes. Lastly, the table columns corresponding to each field in the data file are listed.
To run sqlldr with a control file, invoke it like this:
StudentFirst> sqlldr control=customer.ctl
You will need to enter the username and password of the Oracle user who owns the table you are loading data into.
When sqlldr runs, it will create two files: a file ending in .log, and a file ending in .bad. The .log file will contain a log of the sqlldr run. You should check it after sqlldr runs in order to verify that the data was loaded successfully. The .bad file will contain a list of any records that did not match the format specified in the control file. If all the data file records appear in the .bad file, it means there is something wrong with the data format in the control file.
NOTE: If you have referential integrity constraints on your tables, you will need to consider the appropriate order in which to load the data in order to ensure that there are no referential integrity violations. If you have circular referential integrity constraints, or if you don't want to worry about the order in which you load the data, you can disable the constraints before loading the data, then enable them afterwards.
b) Task: Analyze Tables
Objectives:

1. Run the ANALYZE TABLE command against your tables
2. Give an example of doing the same using dbms_stats package
3. Verify that objects are valid

Procedure:
The ANALYZE TABLE command causes Oracle to collect statistics about database objects. Prior to Oracle 10g, using ANALYZE TABLE ... COMPUTE STATISTICS would collect statistics that would be used by the optimizer, but that function of the ANALYZE command is no longer active.
Sample:
Login as the owner of the tables, then run:
SQL>analyze table customer compute statistics;
SQL>analyze table order compute statistics;
We can also run a query to list all the user's objects and verify that they are valid:
SQL> select object_type, object_name, status from user_objects order by object_type, object_name;