mars wwwroot cslab Reference oracle Distributed Database OracleDistDB doc by oneforseven

VIEWS: 0 PAGES: 4

									\\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc
Last Revision 02/28/00 by rlw
Page 1 of 4, Printed 03/19/03

This exercise is designed to give you some basic information about Oracle Distributed Databases
and the referencing cnventions used in writing applications using Oracle Distributed Databases.

ORACLE DISTRIBUTED DATABASE CONCEPTS

General

A distributed database is a set of discrete databases stored on multiple networked computers in such a manner that
they may be treated as a single database by client applications. If the database servers are all running the same
database server software, the distributed database is known as homogeneous. In this exercise you will be working with
a homogenous distributed database consisting of Oracle 8i databases residing on two separate Windows

Global Database Names

Oracle is designed to accommodate distributed databases involving hundreds of database servers. In complex
networks, the Oracle global database naming convention provides a method for assigning unique names to each
database . Oracle Global Database Names use dot notation which look sort of like URL’s. For example
       ‘ SALES.US.AMERICAS.ACME_AUTO.COM ‘ ,
references a database named SALES in the US.AMERICAS.ACME_AUTO.COM domain. Client applications
performing operations on distributed databases reference database objects by appending their Global Database Names.

The Oracle domain in the lab is named US.ORACLE.COM. Since our lab network utilizes only 2 servers in a single
domain, the naming structure is simplified so that use of the domain name in identifying database objects is optional.

Network Service Names

Oracle databases and client applications communicate with each other using Oracle’s proprietary
networking software known as Net8. Net8 is platform independent, so that databases or clients running on
a variety of platforms including Windows, UNIX and LINUX can communicate seamlessly with each
other. In net8, databases are assigned Service Names which are mapped to network addresses and
database names to identify a specific database. These mappings are normally maintained by database
administrators. For this exercise the CS2 and CS6 service name mappings have been created for you.

Database Objects and Access Permissions

In Oracle every database object (table, view, stored procedure, etc.) has an owner. When an object is created, its
creator becomes its owner by default. Also by default, the owner is the only person with permissions to view or
change that object. If others are subsequently granted permissions to view or modify an object, they must reference
that object by the owners user id. For example, if your user id is SCOTT and you create a table named
DEPARTMENT and subsequently grant Mary permission to execute SELECT statements against it, Mary would
reference it by including the owners Oracle user id . For example, to execute a select against a table owned by user
SCOTT (who has give you select permissions) use
        SELECT * FROM SCOTT.DEPARTMENT;

Oracle Database Links

Oracle uses Database Links to manage connections between Oracle databases. Database links can be created by a user
with database administrator privileges and may be established with a variety of security options. Database links are
ordinarily given the same name as the remote database they are providing access to. The links that you will use in this
exercise have been established in the database CS2 (at network service location CS2) and the database CS6 (at
network service location CS6).

Referencing Database Objects in Distributed Databases

If you write an application which references an Oracle distributed database, you must qualify the names of objects on
remote databases by appending the @ symbol followed by name of the database link used to access the remote
database. For example, to reference the table EMP owned by user DERICK using a Database Link named
SALES.US.AMERICAS.ACME_AUTO.COM you could use
    SELECT * FROM DERICK.EMP@SALES.US.AMERICAS.ACME_AUTO.COM;
\\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc
Last Revision 02/28/00 by rlw
Page 2 of 4, Printed 03/19/03

As indicated earlier, we will be working with only one domain in the SPSU CS lab, so you can omit the Global
Database Domain when referencing a database object.

Distributed Database Security

Security for distributed transactions is managed by granting permissions to database objects and Database Links. The
authentication database may be centralized for an Oracle domain or decentralized. In decentralized situations, each
database maintains its authentication accounts and users must have identical accounts on each database to be accessed.
In the lab, we will use decentralized authentication, so you will need identical userid’s and passwords in both
databases. You will be responsible for seeing that your password is the same in both databases before you complete the
exercise that begins on page 2 of this handout.

Commits and Rollbacks

You may recall that when you are working in SQLplus, database record changes are initially written to a temporary
state in the database you are working with. The changes you make during a session are available to you, but not to
others until you commit them. By default the changes are committed when a session ends. Users or applications may
also commit changes with the command 'COMMIT' or erase all changes subsequent to the last COMMIT with the
command 'ROLLBACK';

A database transaction is defined as one or more sql statements bounded by COMMIT or ROLLBACK. In a
distributed environment, COMMITs and ROLLBACK are especially important to applications processing Distributed
Database transactions, because of the potential for network failures.

DISTRIBUTED DATABASE EXERCISE

Your company has a centralized database at the headquarters location in Atlanta, but has decided to build another
database in Seattle, where a new Sales location will be opening soon. Personnel records will be decentralized first to
gain experience before decentralizing order processing and other functions. In this exercise you will build the first
table in Seattle and use the SQLplus application to execute Distributed Database commands against the distributed
database..

Existing employee records reside in tables named EMP, DEPT and SALGRADE in a database named CS2 (service
name ‘CS2’) at the headquarters location in Atlanta. The owner of these tables is aaMARY. Related records in
Seattle will be maintained in a database named CS6 (Service Name ‘CS6’). You will be the owner of the tables
located in Seattle

You are located in Seattle and your charge is to set up a distributed database that will allow client applications at any
company location to manipulate the EMP, DEPT and SALGRADE tables. To do so, you will need to create public
database links in the Atlanta and Seattle databases. To summarize, you will be working with the following Oracle
database object identifiers.
                                                 Atlanta                              Seattle
Oracle Database Name                             CS2                                           CS6
Oracle Net8 Service Name                         CS2                                           CS6
Database Link Name                               CS2
Database Link Name                                                                             CS6

You begin by asking the Database Administrator in Seattle to open an account for you in the CS2 database with the
same userid that you use in the CS6 database. You then ask the Network Administrator to make the CS2 and CS6
service name mapping available to your workstation. Both of these activities have been completed for you by the lab
staff.

1. Create Database Links
In the lab you do not have the permissions necessary to create database links, so these have been created for you by the
lab staff. As information, the links were created with the following SQL commands.

in Atlanta      CREATE PUBLIC DATABASE LINK CS6 USING ‘CS6’;
in Seattle      CREATE PUBLIC DATABASE LINK CS2 USING ‘CS2’;

The PUBLIC key word is included to permit users other than the creator of the links to utilize them.

2. Get access to existing tables
\\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc
Last Revision 02/28/00 by rlw
Page 3 of 4, Printed 03/19/03


Recall that only the owner of a database object can grant access privileges to others. Since aaMARY is the owner of he
existing EMP, DEPT and SALGRADE tables in Atlanta, you must ask her to grant you the rights to execute SELECT
statements against them. She can do so the following command.
    GRANT SELECT ON EMP, DEPT, SALGRADE TO myuserid;
(where myuserid is the userid you are using in this exercise)
For this lab, the database administrator has used the following command to give everyone access to these tables.
    GRANT SELECT ON EMP, DEPT, SALGRADE TO public;
If you would like to try out the grant command, grant access to one of your lab mates.

3. Log into the database in Atlanta (at CS2) and review the existing HR tables

Using SQLplus, log in to to the Atlanta database with your userid and password and host string CS2. Since aaMARY
owns the tables you are interested in you must include her userid in your references to them. . Use the command
    SELECT * FROM aaMARY.EMP.
to view the contents of the EMP table. Use a similar commands to review the contents of aaMARY.DEPT and
aaMARY.SALGRADE.

2. Log into the CS6 database and synchronize your passwords

Remember that your user id and password in both database must be identical in order for you to query. If you are
enrolled in the advanced database class, your instructor has made arrangements for you to have accounts in Atlanta (
database name CS2, Network Service Name CS2) and Seattle (database name CS6 and Network Service Name CS6)
with the same user id. You initial Oracle password in the CS labs is always ‘ temp00 ‘, but some of you will have
already changed your password at CS2. If you have done so, log into CS6 and make your password identical to what
you use on CS2. If your Oracle userid is myuserid and you wish to change your password to mynewpassword use
    GRANT CONNECT TO myuserid IDENTIFIED BY mynewpassword;

3. Create a copy of the Emp Table on CS6

While still logged in to CS6 use
     CREATE TABLE EMP AS
        SELECT * FROM aaMARY.EMP@CS2
        WHERE DEPTNO=200;
to clone the existing EMP table structure in Atlanta to a new table in Seattle. Since there is no DEPTNO 200, your
new table will not contain data. Then use the command
    DESCRIBE EMP;
to verify that the table has been created properly.

4. Create a primary key for the new table

When tables are created with 'AS SELECT' , constraints from the original table are not transferred to the new table.
Create a primary key for the new table with
   ALTER TABLE EMP ADD PRIMARY KEY(EMPNO);

5. Populate the Emp table at CS6

Add some data to the new table. While still logged into CS6, execute the following commands.
   INSERT INTO EMP VALUES (8001, 'HARRIS', 'SALESMAN', 8000, '01-DEC-99', 2000, 0, 30);
   INSERT INTO EMP VALUES (8002, 'MORRIS', 'SALESMAN', 8000, '01-DEC-99', 1500, 0, 30);
   INSERT INTO EMP VALUES (8003, 'GREENE', 'SALESMAN', 8000, '01-DEC-99', 1900, 0, 30);
   COMMIT;

Use SELECT * FROM EMP; to verify that data was correctly entered.

6. Grant the VP Personnel ( aaHARRY ) and aaMARY the right to execute selects against your new table;

While still logged into CS6, give aaHARRY permission to execute SELECT against the new table.
   GRANT SELECT ON EMP to aaHARRY;
   GRANT SELECT ON EMP TO aaMARY;

7. Perform a query against the distributed database from CS6
\\mars\wwwroot\cslab\Reference\oracle\Distributed Database\OracleDistDB.doc
Last Revision 02/28/00 by rlw
Page 4 of 4, Printed 03/19/03


You are now ready to perform your first distributed query. Suppose that you need to view EMPNO, ENAME, JOB
and SAL fields for all employees. While logged into CS6 with your own userid and password execute the following
command
    SELECT EMPNO, ENAME, JOB, SAL FROM EMP
       UNION
    SELECT EMPNO, ENAME, JOB, SAL FROM AAMARY.EMP@CS2;

Since you are logged in to the Seattle database, it is not necessary to include your own userid in the reference to the
local EMP table. To reference the remote table, however, you must include the Owners userid and append the name of
the Database Link used to connect to the remote database.

8 Verify that aaHarry can query the distributed databaase

Harry is located in Seattle and needs to list the ENAME, LOC and SAL for employees by their job title.. You decide to
write and test a query before giving him instructions on how to do this. Use SQLplus to log in at CS6 with userid
aaHARRY and password aaHARRY and execute a query that will return ENAME, LOC, and SAL for all employees
whose JOB is salesman.

SELECT ENAME, LOC, SAL
FROM AAMARY.DEPT@CS2, AAMARY.EMP@CS2
   WHERE AAMARY.EMP.JOB='SALESMAN'
   AND
   AAMARY.DEPT.DEPTNO=AAMARY.EMP.DEPTNO
UNION
SELECT ENAME, LOC, SAL
FROM AAMARY.DEPT@CS2, myuserid.EMP
   WHERE myuserid.EMP.JOB='SALESMAN'
   AND
   AAMARY.DEPT.DEPTNO=myuserid.EMP.DEPTNO ;

 9. Verify that other users in Seattle do not have access to the new table

Use SQLplus to log in at CS6 with userid SCOTT and password tiger. Execute the following
   SELECT * from myuserid.EMP@CS6;

You should receive a message to the effect that the table does not exist.

								
To top