1 all powder board and ski oracle 9i workbook chapter 10: distributed databases jerry post copyright...
TRANSCRIPT
![Page 1: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/1.jpg)
1
All Powder Board and Ski
Oracle 9i WorkbookChapter 10: Distributed DatabasesJerry PostCopyright © 2003
![Page 2: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/2.jpg)
2
All Powder LAN
Offices/Managers
Checkout
Rental Desk
Network Switch
Server
![Page 3: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/3.jpg)
3
Create Target Database and Table
Create a new database if necessary:Start/All Programs/Oracle-OraHome92/Configuration and Migration Tools/ Database Configuration AssistantUse Net Manager to add a hostname entryLog in and create a new user/schema or use the system schemaCreate a small table
CREATE TABLE Customer( CustomerID INTEGER, LastName VARCHAR2(15), FirstName VARCHAR2(15), Constraint pk_Customer Primary Key (CustomerID));INSERT INTO Customer (CustomerID, LastName, FirstName) Values (1,'Smith', 'Adam');INSERT INTO Customer (CustomerID, LastName, FirstName) Values (2,'Keynes', 'John');INSERT INTO Customer (CustomerID, LastName, FirstName) Values (3,'Samuelson', 'Paul');INSERT INTO Customer (CustomerID, LastName, FirstName) Values (4,'Robinson', 'Joan');Commit;
![Page 4: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/4.jpg)
4
Link to Target Database
CREATE DATABASE LINK NewYork CONNECT TO RemoteUser IDENTIFIED BY t1 USING 'dbhostname';
SELECT * FROM Customer@NewYork;
Create a descriptive name for the link
User account and password on the target databaseDatabase hostname
in the network file
Table name@link name
![Page 5: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/5.jpg)
5
Database Replicas
Master Site
Materialized View (Replica)
Materialized View (Replica)
Materialized View (Replica)
Replication Group
Deployment Template
Link Schedule
Synchronize
![Page 6: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/6.jpg)
6
Create a Materialized View Replica
1. Start the Deployment Template1. Set up Master Sites2. Disconnect and log in as RepAdmin3. Create a Master Group4. Create the Template5. Generate the script
2. Create Materialized View logs on the master site for each table3. On the client, or build as PL/SQL to add to the script
1. Manually create a Materialized View Group creating a new Refresh Group matching name from template: CustomerTemplate
2. Select the Customer materialized view (or all)3. Add the materialized view to the refresh group
![Page 7: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/7.jpg)
7
Creating Replicas: Deployment Template-Master Site
Add the main database
Log in as SYSDBA
Open Advanced Replication and follow menu options
![Page 8: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/8.jpg)
8
Replica: Deployment: Master Group
Objects tab
Select Customer table
![Page 9: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/9.jpg)
9
Template: Create Views
Create template
Select tables
![Page 10: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/10.jpg)
10
Generate Template: Schema
![Page 11: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/11.jpg)
11
Create Materialized View
CREATE MATERIALIZED VIEW Powder.Customer FOR UPDATE ASSELECT * FROM Powder.Customer@host_database_link;
![Page 12: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/12.jpg)
12
Client Side MV and Refresh Group
Create refresh group
Add materialized views
Refresh
![Page 13: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/13.jpg)
13
PL/SQL on Web Pages
(0) Configure the Data Access Descriptor (DAD)
(1) Create the basic html form to get the SaleID GetSale.psp
(2) Create the page to display the results ListSales.psp
(3) loadpsp -replace -user powder/ski1@PostDB GetSale.psp
(4) loadpsp -replace -user powder/ski1@PostDB ListSales.psp
(5) http://PostDB/pls/simpledad/Powder.GetSale
Pages without Java Pages are stored as procedures within the database and use embedded PL/SQL to connect to the database
Two page example to enter a SaleID and lookup the matching data
![Page 14: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/14.jpg)
14
Configure DAD
http://server
Pick: Mod plsql Configuration Menu
Modify the SimpleDAD, or create a new one
Schema Name: Powder
UserName: Powder
Password:
Connect String: PostDB
Authentication Mode: Basic
Leave the other defaults
![Page 15: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/15.jpg)
15
GetSale.psp Form Page
<html><head><title>Get SaleID</title></head><body><p align=center>All Powder Board and Ski Shop Sales</p><form name='Form1' method='post' action='Powder.ListSales'><p>Enter a Sale ID value: <input type='text' name='SaleID' maxlength='20' /></p><input type='submit' name='submit' value='Submit' /></form></body></html>
![Page 16: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/16.jpg)
16
ListSales.psp Display Page
<%@ page language="PL/SQL" %><%@ plsql procedure="ListSales" %><%@ plsql parameter="SaleID" type="NUMBER" %><%! l_SaleID NUMBER := SaleID;%><HTML><HEAD><TITLE>List Sales Data</TITLE></HEAD><BODY><p>Sales data for SaleID: <%=SaleID%></p><table border=0><tr><th>Sale ID</th><th>Sale Date</th><th>CustomerID</th></tr><%
for item in (select * from sale where SaleID=l_SaleID order by SaleDate) loop%>
<tr> <td><%=item.SaleID%></td><td><%=item.SaleDate%></td>
<td><%=item.CustomerID%></td></tr>
<% end loop; %></table></BODY></HTML>
![Page 17: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/17.jpg)
17
Load Pages Into Oracle
(3) loadpsp -replace -user powder/ski1@PostDB GetSale.psp
(4) loadpsp -replace -user powder/ski1@PostDB ListSales.psp
Go to the command line (DOS)
You must include the database name
![Page 18: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/18.jpg)
18
Open the First Page in a Browser
http://PostDB/pls/simpledad/powder.getsale
The URL is very picky and must be complete (regardless of what the documentation says). And watch the dot!
http://WebServer/pls/DADName/Schema.Procedure
![Page 19: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/19.jpg)
19
Results
The example is simple to highlight the steps.You can retrieve more rows.You can use a better format.
![Page 20: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/20.jpg)
20
Create XML File with SQL
SET long 20000SET pages 100SPOOL D:\Students\AllPowder\Employee10.xmlSELECT XMLElement("Employee", XMLElement("LastName", LastName),
XMLElement("FirstName", FirstName))FROM EmployeeWHERE rownum<10;
SPOOL OFF
Eliminate line and page breaks
Send output to a file
Limit data to 9 rows to keep the file smaller for now
![Page 21: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/21.jpg)
21
XML File: Cleaned Up
<?xml version='1.0'?><Employees><Employee>
<LastName>Staff</LastName><FirstName></FirstName>
</Employee><Employee>
<LastName>Killy</LastName><FirstName>Jean-Claude</FirstName>
</Employee>…</Employees>
![Page 22: 1 All Powder Board and Ski Oracle 9i Workbook Chapter 10: Distributed Databases Jerry Post Copyright © 2003](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649f1a5503460f94c2f977/html5/thumbnails/22.jpg)
22
XML File in Internet Explorer