Scribd Logo
Upload

Welcome to Scribd!

  • 8 views

    Lab 6 - n01530481

    Uploaded by

    smeet patel
    The document provides steps to create external tables in Oracle to load data from files located in a Linux directory into tables. It has the user create a new database user, directory object pointing to the Linux directory, and two sample data files to load. It then walks through creating two external tables to load the data files, checking the log and bad files, and querying the loaded data.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Lab 6 - n01530481

    Uploaded by

    smeet patel
    8 views12 pages
    The document provides steps to create external tables in Oracle to load data from files located in a Linux directory into tables. It has the user create a new database user, directory object pointing to the Linux directory, and two sample data files to load. It then walks through creating two external tables to load the data files, checking the log and bad files, and querying the loaded data.

    Original Title

    Lab 6_n01530481

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides steps to create external tables in Oracle to load data from files located in a Linux directory into tables. It has the user create a new database user, directory object pointing to the Linux directory, and two sample data files to load. It then walks through creating two external tables to load the data files, checking the log and bad files, and querying the loaded data.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    8 views12 pages

    Lab 6 - n01530481

    Uploaded by

    smeet patel
    The document provides steps to create external tables in Oracle to load data from files located in a Linux directory into tables. It has the user create a new database user, directory object pointing to the Linux directory, and two sample data files to load. It then walks through creating two external tables to load the data files, checking the log and bad files, and querying the loaded data.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 12

    Lab 6

    Step 1
    Execute the following command as the SYSTEM user.

    Connected as the system user in your vApp. You will execute a few administrative functions in this
    account.Create a user in your vApp for data warehouse. Call this user exercise6. Use the USERS tablespace
    and the TEMP tablespace.GRANT this user CONNECT and RESOURCE privilege.
    Step 2
    Open a terminal session in Linux to the oracle user account. You need to create a directory under
    /home/oracle called data. Make sure it is here. Call this directory data. You should now have a path of
    /home/oracle/data created with your new directory.

    Step 3
    Again as the system user you need to do a few things:
    SQL> CREATE OR REPLACE DIRECTORY ext_tab_data as ‘/home/oracle/data’;
    SQL> GRANT read, write on directory ext_tab_data to exercise6;

    data

    Create the following two files in the /home/oracle/data directory, use the file names that are given:
    Countries1.txt
    ENG,England,English
    SCO,Scotland,English
    IRE,Ireland,English
    WAL,Wales,Welsh

    Countries2.txt
    FRA,France,French
    GER,Germany,German
    USA,United States of America,English
    CAN,Canada,English
    Step 3A
    Create a connection in SQL Developer for your new user exercise6.

    Step 4
    Create the following two data files Counties1.txt and Countries2.txt.These two files must reside in the Linux
    Directory /home/oracle/data
    Step 5
    Enter the following code for the external table

    Step 6
    A log file and a bad file will be created in your defined directory. Show the contents of these two files. Check
    these for errors that occur during the loading process. You should see one line of error, and that will be the
    column headings that are embedded within the data file.

    Step 7
    Once your data is successfully loaded you may query the data. Answer the following questions.
    What countries is English spoken in? Show your answer and the query.
    Part B Try one on your own.
    Use the same user account you used previously, exercise6.
    Pre-Steps to Perform (on football.dat)
    In Lesson 7 of Blackboard there is a data file loaded called football.dat. Download this file in Blackboard.
    Place this file in your /home/oracle/data directory.
    Go to the directory where the data file is located and execute the following command.
    $ dos2unix football.dat
    Step 8 Create an external table called football_ext. View the data file to determine which columns you
    require. Write the SQL code for your external table.

    Column Data type and size


    Player_id Char 5
    Player_number Char 5
    Player_lname Char 50
    Player_fname Char 50
    Player_position Char 10
    Player_team Char 5
    Take a screen capture of your code and its successful creation.
    Step 9 Show the LOG file and the BAD file.
    Take a screen capture of the LOG FILE and the BAD FILE. If all goes correctly you may not have a bad file.

    Step 10 List the players that play for BUF. Take a screen capture of your query and its result.
    Step 11 List the Players that are running backs (RB). Show the team, player number, first name, last name and
    the position. Take a screen capture of your query and the result.

    You might also like