STEP 1: Setting up Your Environment
Be sure you are connected to the DBM449_USER schema that was created in lab 1.
To begin this lab you will need to download the LAB3_DEPTS.SQL script file associated with the link and run the script in your DBM449_USER schema of your database instance. This script contains a single table and that you will be using to help pull data from each of the other two database instances. Notice that the DEPTNO column in this table is the PRIMARY KEY column and can be used to reference or link to the DEPTNO column in the other two database employee tables.
Now you need to create a couple of private database links that will allow you to connect to your other two regional databases. To accomplish this use the connection information listed above in the Lab Overview section. Name your links using your database instance name together with the region name as the name for the link. Separate the two with an underscore (example - DB1000_SEATTLE).
After creating both of your database links, query the USER_DB_LINKS view in the data dictionary to retrieve information about your database links. The output from your query should look similar to what you see below. You will need to set your linesize to 132 and format the DB_LINK and HOST columns to be only 25 bytes wide to get the same format that you see.
DB_LINK USERNAME HOST CREATED
------------------------- ------------------------------ ------------------------- ---------
DB1000_MIAMI MIAMI_USER miami 09-DEC-08
STEP 2: Testing your Database Links
Each of your remote databases has an employee data table. The tables are named SEATTLE_EMP and MIAMI_EMP respective to the database they are in. Using the appropriate database link, query each of the two tables to retrieve the employee number, name, job function, and salary. (HINT: you can issue a DESC command on each of the distributed tables to find out the actual column names just like you would for a table in your own instance.)
STEP 3: Connecting Data in the Seattle Database
Write a query that will retrieve all employees from the Seattle region who are salespeople working in the marketing department. Show the employee number, name, job function, salary, and department name (HINT: The department name is in the DEPT table) in the result set.
STEP 4: Connecting Data in the Miami Database
Write a query that will retrieve all employees from the Miami region who work in the accounting department. Show the employee number, name, job function, salary, and department name (HINT: The department name is in the DEPT table) in the results set.
STEP 5: Connecting Data in all Three Databases
Now we need to increase our report. Write a query that will retrieve employees from both the Seattle and Miami regions who work in sales. Show the employee number, employee name, job function, salary and location name in the result set (HINT: The location name is in the DEPT table).
STEP 6: Improving Data Retrieval from all Three Databases
Writing queries like the ones above can be fairly cumbersome. It would be much better to be able to pull this type of data as though it was coming from a single table, and in fact this can be done by creating a view.
Using the query written above as a guide, write and execute the SQL statement that will create a view that will show all employees in both the Seattle and Miami regions (you can use your own naming convention for the view name). Show all the employee number, name, job, salary, commission, department number and location name for each employee (HINT: The location name is in the DEPT table).
Now write a query that will retrieve all the data from the view just created.
--This may need to be edited to match your file location
alter database datafile 'F:\DBM\SPRINGB\DB????\ORACLE\ORADATA\ORACLE\USERS01.DBF' resize 10M;
set echo on
set pagesize 999
set linesize 999
Lab Number: 3
--Step 01 Setting up Your Environment
----Connect as dbm449_user
----Run lab 3 script