Rozproszone i obiektowe systemy baz danych Dr inż. Robert Wójcik Wykład 5. Mechanizmy wspierające przezroczystość w rozproszonych bazach danych

Rozproszone i obiektowe systemy baz danych Dr inż. Robert Wójcik

Wykład 5. Mechanizmy wspierające przezroczystość w rozproszonych bazach danych

5.1. Charakterystyka systemu rozproszonych baz danych

5.2. Mechanizmy przezroczystości w Oracle 5.3. Łączniki bazodanowe w Oracle

5.4. Zastosowanie synonimów w Oracle 5.5. Zastosowanie perspektyw w Oracle

5.6. Migawki – perspektywy zmaterializowane

5.1. Charakterystyka systemu rozproszonych baz danych

[lit] Wrembel R., Bębel B., Oracle. Projektowanie rozproszonych baz danych, Helion, Gliwice, 2003.

• Scentralizowana kontra rozproszona baza danych

• Definicja rozproszonej bazy danych

• Cechy rozproszonego systemu baz danych - 12 reguł Date

• Przykładowe architektury rozproszonych systemów baz danych Na podstawie:

Wykład_01 ze strony:

http://wazniak.mimuw.edu.pl/index.php - systemy rozproszone, zaawansowane systemy baz danych.

• W typowych zastosowaniach systemów baz danych architektura systemu informatycznego ma strukturę scentralizowaną:

– scentralizowana baza danych, tj. system zarządzania bazą danych (SZBD) i wszystkie dane znajdują się w tym samym węźle sieci informatycznej;

– dostęp do bazy danych poprzez aplikacje klient-serwer lub w architekturze 3-warstwowej (wielowarstwowej).

• Istnieje wiele zastosowań, w których scentralizowane bazy danych nie zawsze oferują wymaganą funkcjonalność i zadowalającą efektywność W takich przypadkach, stosuje się tzw. rozproszone bazy danych (RBD).

Scentralizowana baza danych

Przykładowo, rozważmy sieć dużych warsztatów samochodowych w Polsce, z kilkoma oddziałami w każdym dużym mieście.

Gdyby zbudować system informatyczny dla tej sieci oparty o scentralizowaną bazę danych umieszczoną np. w Poznaniu, wówczas każde odwołanie do tej bazy z innego miasta wymagałoby transmisji sieciowej.

Przy sieci o niskiej przepustowości i dużej częstotliwości odwołań, poprawne wykorzystywanie systemu stałoby się niemożliwe.

Dodatkowo, taki system byłby znacznie bardziej podatny na awarie niż system rozproszony. Awaria serwera scentralizowanej bazy danych powodowałaby niemożliwość korzystania z systemu we wszystkich oddziałach firmy!

Rozproszona baza danych

Alternatywnym rozwiązaniem do przedstawionego poprzednio jest zastosowanie wielu lokalnych baz danych, np. po jednej w każdym dużym mieście, czyli tzw. systemu rozproszonych baz danych.

Każda z baz lokalnych przechowywałaby informacje o klientach z danego regionu, Krakowa, Wrocławia, Warszawy, Gdańska, Poznania.

Cechy rozproszonej bazy danych:

• każda lokalna baza danych działa na oddzielnym komputerze/serwerze (węzeł rozproszonej bazy danych);

• architektura typu serwer/serwer, w której bazy danych komunikują się ze sobą i współdzielą między sobą dane;

• z punktu widzenia użytkownika fizycznie odseparowane bazy lokalne stanowią logicznie, jedną, zintegrowaną bazę danych.

Integracja baz danych oraz przezroczysty dostęp do bazy możliwe dzięki:

- specjalizowanemu oprogramowaniu sieciowemu,

- mechanizmom dostępu do zdalnych baz zdefiniowanych w SQL (łączniki, synonimy, perspektywy, migawki).

Zalety rozproszonej bazy danych

• Dzięki umieszczeniu danych „blisko” ich użytkowników, skraca się opóźnienia transmisji sieciowej ponieważ dane specyficzne dla węzła są składowane i przetwarzane lokalnie.

• Zmniejsza się też ryzyko utraty wszystkich danych na skutek awarii systemu i wzrasta niezawodność całego systemu, ponieważ awaria jednej bazy danych np. w Krakowie nie ma wpływu na bazy danych w pozostałych miastach tak długo, dopóki żądania nie są kierowane do bazy w Krakowie.

Wady rozproszonej bazy danych

Architektura rozproszonych baz danych ma dwie podstawowe wady.

1) Rozproszenie danych utrudnia dostęp do pełnego-zintegrowanego zbioru danych pochodzących z różnych baz i ich analizę.

- Przykładowo, zarząd sieci warsztatów samochodowych będzie zainteresowany zestawieniami ilości sprzedaży i usług zrealizowanych w poszczególnych warsztatach. Uzyskanie takich informacji wymaga zintegrowania danych pochodzących ze wszystkich baz danych firmy.

2) Ponadto, wszystkie warsztaty korzystają z pewnego wspólnego zbioru informacji, tzw. słowników.

- Przykładem takiego słownika jest wykaz części znajdujących się w sprzedaży wraz z ich aktualnymi cenami.

- Gdyby dane słownikowe były przechowywane centralnie, tj. w jednej bazie danych, wówczas powstawałyby omówione wcześniej problemy architektury scentralizowanej.

- W związku z tym, informacje słownikowe są najczęściej powielane w każdej bazie danych firmy. Są to tzw. repliki.

Problem spójności zwielokrotnionych danych

W przypadku replik, występuje problem utrzymywania ich aktualnej zawartości (odświeżania), w przypadku, gdy oryginalne dane słownikowe ulegają modyfikacjom. Przykładowo, zmiana ceny opony w centrali firmy musi być propagowana do wszystkich oddziałów.

Komponenty architektury RBD

W skład systemu rozproszonej bazy danych wchodzą komponenty - sprzętowe,

- programowe.

• Do grupy komponentów sprzętowych zalicza się:

- tzw. węzły, czyli komputery, na których działają lokalne bazy danych;

- sieć komputerowa, dzięki której poszczególne węzły mogą się z sobą komunikować, a użytkownik z dowolnego węzła może sięgnąć do dowolnych innych węzłów systemu.

• Do grupy komponentów programowych zalicza się:

- protokoły sieciowe np. TCP/IP, IPX/SPX, LU6.2, DEC Net;

- dedykowane oprogramowanie umożliwiające dostęp z jednej bazy danych do innej i przetwarzanie danych z innej bazy tak, jakby dane te były przechowywane lokalnie.

Reguły Date – własności systemu rozproszonej bazy danych

C.J.Date zaproponował 12 reguł, jakie powinien spełniać idealny system rozproszonej bazy danych.

1. Lokalna autonomia

2. Uniezależnienie od centralnego miejsca 3. Działanie ciągłe

4. Niezależność lokalizacji 5. Niezależność fragmentacji

6. Replikacja

7. Niezależność sprzętowa

8. Niezależność od systemu operacyjnego

9. Niezależność od systemu zarządzania bazą danych 10. Niezależność od sieci

11. Rozproszone zarządzanie transakcjami 12. Rozproszone przetwarzanie zapytań

1. Lokalna autonomia

- każdy węzeł należący do rozproszonej bazy danych jest zarządzany (administrowany) niezależnie od pozostałych węzłów;

- wszystkie operacje na danych w węźle są kontrolowane przez ten węzeł;

- działanie węzła X nie powinno zależeć od działania lub niedziałania innych węzłów;

- na każdym węźle działa niezależny system zarządzania bazą danych.

2. Uniezależnienie od centralnego węzła

- wszystkie węzły systemu rozproszonej bazy danych są traktowane jednakowo;

- nie ma wyróżnionego węzła oferującego usługi dla pozostałych węzłów, np.

przetwarzania zapytań;

Taki wyróżniony węzeł mógłby stanowić tzw. "wąskie gardło" całego systemu.

3. Działanie ciągłe

- awaria jednego węzła nie wpływa na pracę innych węzłów (jest to zagwarantowane przez autonomię węzłów);

- dzięki zastosowaniu mechanizmu replikowania danych do wielu węzłów, inny węzeł może udostępnić replikę oryginalnych danych w przypadku awarii węzła przechowującego dane oryginalne.

4. Niezależność lokalizacji

- sposób dostępu do danych przechowywanych w węzłach systemu RBD powinien być jednakowy, niezależny od fizycznego umiejscowienia danych i niezależny od ich fizycznego sposobu składowania;

- system powinien zapewniać tzw. przezroczystość lokalizacji (ang. location transparency), czyli ukrywać przed użytkownikiem fizyczne miejsce składowania danych;

- innymi słowy, przezroczystość lokalizacji gwarantuje, że użytkownik nie musi znać fizycznego umiejscowienia danych a dostęp do danych jest realizowany w taki sposób, jak gdyby dane były przechowywane lokalnie.

5. Niezależność fragmentacji

- dane, np. tabelę lub indeks, można dzielić na fragmenty;

- każdy fragment można niezależnie umieścić w innym węźle systemu RBD;

- użytkownik nie powinien być świadomym fizycznej lokalizacji fragmentów, często nie powinien też być świadomym istnienia fragmentów;

- dostęp do fragmentów powinien być jednakowy, niezależny od lokalizacji.

6. Replikacja

- Replikacja jest mechanizmem polegającym na tworzeniu kopii danych pochodzących z jednego węzła w innym węźle.

- Użytkownik może operować w taki sam sposób na danych oryginalnych (źródłowych), jak i na kopii danych.

- Podstawowym obiektem bazy danych, który się replikuje jest tabela, ale możliwe jest też replikowanie rekordów, instrukcji SQL, oraz innych obiektów bazy danych.

7. Niezależność sprzętowa

- ten sam system zarządzania bazą danych (pochodzący od jednego producenta, w jednej wersji) może zostać zainstalowany na różnych platformach sprzętowych, z których każda stanowi osobny węzeł.

- system zarządzania bazą danych (SZBD), działający na różnych platformach sprzętowych, może wejść w skład tego samego systemu RBD.

8. Niezależność od systemu operacyjnego

- ten sam system zarządzania bazą danych (pochodzący od jednego producenta, w jednej wersji) może zostać zainstalowany w różnych systemach operacyjnych i może wejść w skład tego samego systemu RBD;

- na przykład, SZBD Oracle10g Release 2 może zostać zainstalowany m.in.

w systemie operacyjnym:

# Solaris (x86-64),

# HP-UX Itanium,

# Linux,

# Microsoft Windows.

Każda z tych instalacji może wchodzić w skład tego samego systemu RBD.

9. Niezależność od SZBD

Niezależność od systemu zarządzania bazą danych oznacza, że:

- Po pierwsze, w skład systemu RBD mogą wchodzić bazy danych zarządzane przez różne systemy zarządzania bazami danych, np.

Oracle10g, IBM DB2, MS SQLServer, Sybase Adaptive Server Enterprise.

- Po drugie, sposób dostępu do tych baz danych powinien być jednolity.

- Oznacza to, że każdy z SZBD powinien dostarczać jednolity i ustandaryzowany interfejs dostępu do bazy danych.

10. Niezależność od sieci

- system RBD powinien pracować również w różnych architekturach sieciowych i z różnymi protokołami sieciowymi;

- dostęp do poszczególnych węzłów powinien być jednolity i niezależny od architektury sieciowej i niezależny od wykorzystywanych protokołów sieciowych.

11. Rozproszone zarządzanie transakcjami

- w systemie RBD można realizować transakcje rozproszone;

- transakcja rozproszona odwołuje się do wielu węzłów systemu;

- w przypadku tego typu transakcji system RBD powinien zagwarantować cztery cechy transakcji rozproszonej, tzn. jej

# trwałość,

# spójność,

# atomowość,

# izolację,

podobnie jak w przypadku standardowych transakcji scentralizowanych.

12. Rozproszone przetwarzanie zapytań

- Rozproszone przetwarzanie zapytań gwarantuje możliwość wykonania zapytania, które adresuje jednocześnie wiele węzłów systemu RBD.

- System powinien zagwarantować optymalny lub suboptymalny sposób wykonania takiego zapytania, zgodnie z przyjętym kryterium kosztu wykonania.

Podstawowa architektura systemu RBD

Jedną z podstawowych architektur implementacyjnych systemu RBD jest tzw. system sfederowanych baz danych, którego ogólną architekturę przedstawia rysunek poniżej.

Jest to architektura odniesienia zgodna ze standardem ANSI (ang. American National Standards Institute – instytucja ustalająca normy techniczne obowiązujące w USA).

• Każda z lokalnych baz danych BD1, BD2 i BD3 posiada swój schemat

implementacyjny lokalny (SIL1, SIL2, SIL3). Schemat implementacyjny określa w jakim implementacyjnym modelu danych są reprezentowane dane.

Przykładami takich modeli są: model relacyjny, obiektowy, obiektowo- relacyjny, semistrukturalny.

• Schemat implementacyjny lokalny jest niedostępny na zewnątrz węzła. Z tego względu, każdy węzeł udostępnia tzw. schemat zewnętrzny lokalny (SZL1, SZL2, SZL3).

Schemat ten, pełni dwie zasadnicze funkcje. Po pierwsze, umożliwia on dokonanie konwersji schematu implementacyjnego lokalnego do wspólnego modelu danych wykorzystywanego w systemie sfederowanych BD.

Najczęściej jest to model relacyjny. Po drugie, umożliwia udostępnienie nie całego schematu implementacyjnego lokalnego, ale jego fragmentu.

• Schemat implementacyjny lokalny jest odwzorowywany w schemat zewnętrzny lokalny z wykorzystaniem katalogu lokalnego. Przechowuje on m.in. odwzorowania nazw obiektów, informacje o prawach dostępu, procedury konwersji.

• Schematy zewnętrzne lokalne są integrowane w jeden schemat globalny (SG). Schemat ten zapewnia, że wszystkie lokalne bazy danych są widziane jako jedna, spójna baza.

Schematy zewnętrzne lokalne są odwzorowywane w SG z wykorzystaniem katalogu globalnego. Podobnie, jak katalog lokalny, katalog globalny przechowuje m.in. odwzorowania nazw obiektów, informacje o prawach dostępu, procedury konwersji.

• Na podstawie schematu globalnego, użytkownicy systemu sfederowanych BD tworzą własne schematy, tzw. schematy zewnętrzne globalne.

Umożliwiają one zawężenie danych udostępnianych przez SG do wycinka interesującego użytkowników.

Użytkownicy pracują z systemem poprzez swoje schematy zewnętrzne globalne (SZG).

System sfederowanych DB

• System sfederowanych baz danych to system składający się z co najmniej dwóch niezależnych, różnych systemów baz danych oraz odpowiedniego mechanizmu konsolidującego wszystkie ich komponenty.

• Ponadto, każdy system BD jest niezależnym i autonomicznym scentralizowanym SZBD, który ma swoich własnych lokalnych użytkowników.

System sfederowanych BD - przykład

Jako przykład systemu sfederowanych BD można podać hipotetyczny system kontroli opłat abonamentowych TVP.

Załóżmy, że w jego skład wchodzą trzy autonomiczne bazy danych należące do: Urzędu Miasta, sieci sklepów Saturn, Urzędu Radiofonii i TV.

• Pierwsza baza udostępnia dane meldunkowe obywateli, a jej zawartość jest niezbędna do wysyłania kar i wezwań do uiszczenia opłat abonamentowych.

• Druga baza udostępnia dane o sprzedaży odbiorników RTV, tj. rachunki lub faktury wystawiane kupującym sprzęt RTV. Jej zawartość jest niezbędna do zidentyfikowania kupujących sprzęt RTV. Trzecia baza udostępnia dane abonentów, którzy zarejestrowali odbiorniki RTV.

• W takim systemie sfederowanych baz danych, uprzywilejowany użytkownik mógłby wydać zapytanie o dane meldunkowe obywateli (baza Urzędu Miasta), którzy w ostatnim roku zakupili odbiorniki RTV (baza sieci Saturn), ale którzy nie płacą abonamentu, tj. nie zostali zarejestrowani w bazie danych Urzędu Radiofonii i TV.

5.2. Mechanizmy przezroczystości w Oracle Specjalizowane oprogramowanie sieciowe:

- umożliwia komunikację klient / serwer i zdalny dostęp do danych;

- wykorzystuje standardowe protokoły komunikacyjne: TCP/IP, IPX/SPX;

- moduł klienta: realizacja dostępu do zdalnej bazy danych w oparciu o nazwę bazy danych i odpowiedni protokół;

- moduł serwera: odbieranie żądań klientów kierowanych do zdalnej bazy danych, kierowanie żądań do odpowiedniej bazy danych, przesyłanie zwrotnie wyników operacji w bazie danych.

Łącznik bazy danych (ang. database link):

- jest obiektem bazy danych definiowanym za pomocą polecenia SQL, który umożliwia dostęp do zdalnej bazy danych użytkownikowi zdefiniowanemu w tej bazie;

- za pomocą łącznika można wykonywać operacje select i DML (insert, update, delete) w tabelach lub perspektywach tego użytkownika w zdalnej bazie danych; można też wywoływać znajdujące się tam procedury i

funkcje;

- definiuje ścieżkę sieciową do bazy danych.

Rysunek. Ilustracja zasady działania łącznika [lit]

Na rysunku widać bazy danych o nazwach BD1, BD2, BD3. Baza BD1 odwołuje się do tabeli rachunki poprzez łączniki o nazwach bd2 i bd3, tj.

insert into rachunki@bd3 ...;

update rachunki@bd2 ...;

Synonim (ang. synonym):

- obiekt bazy danych, definiowany poleceniem SQL, który wskazuje na inny obiekt, np. tabelę lub perspektywę;

- pozwala na odwołania do tej samej tabeli lub perspektywy za pomocą różnych nazw;

- synonimy stosuje się do ukrycia lokalizacji danych rozproszonych;

umożliwiają realizację przezroczystości lokalizacji;

- synonimy upraszczają odwołania do zdalnych obiektów.

Perspektywa (widok) (ang. view):

- zapytanie języka SQL, udostępniające użytkownikom określony podzbiór danych znajdujących się w bazie danych;

- umożliwiają integrację danych pochodzących z różnych źródeł;

- zastosowania obejmują ograniczanie dostępu do danych, upraszczanie schematu bazy danych, wprowadzanie dodatkowych ograniczeń

integralnościowych.

Migawka (ang. snapshot) lub perspektywa zmaterializowana (ang.

materialized view):

- obiekt bazy danych, definiowany poleceniem SQL, który stanowi kopię danych z wybranej tabeli lub grupy tabel;

- możliwa jest automatyczna synchronizacja jej zawartości z zawartością tabel źródłowych;

- parametry odświeżania migawki są określane w jej definicji.

Nazwy globalne baz danych w Oracle

W systemie Oracle każda baza danych dostępna w sieci posiada unikalną nazwę globalną (ang. global database name). Nazwa ta składa się z dwóch członów:

- pierwszy z nich jest nazwą samej bazy (parametr konfiguracyjny bazy danych DB_NAME);

- drugi jest nazwą domeny (DB_DOMAIN).

Np. w domenie POZNAN.PL znajduje się baza ORA1,

o nazwie globalnej: ORA1.POZNAN.PL

W domenie PUT.POZNAN.PL znajduje się baza ORA2.PUT.POZNAN.PL, Natomiast w domenie CS.PUT.POZNAN.PL znajdują się dwie bazy danych ORA3.CS.PUT.POZNAN.PL oraz

ORA4.CS.PUT.POZNAN.PL.

Aktualna nazwa globalna bazy danych jest dostępna za pomocą perspektywy słownika bazy danych o nazwie GLOBAL_NAME.

SQL> select * from global_name ;

GLOBAL_NAME

--- ORA2.CS.PUT.POZNAN.PL

W tym przypadku:

DB_DOMAIN = CS.PUT.POZNAN.PL DB_NAME = ORA2

Nazwę globalną można zmienić na

LAB92.CS.PUT.POZNAN.PL

za pomocą polecenia

SQL> alter database rename global_name to LAB92;

Jeśli chcemy zmienić nazwę z uwzględnieniem innej domeny to piszemy:

SQL> alter database rename global_name to LAB92.PWR.WROC.PL ;

5.3. Łączniki bazodanowe w Oracle

Łącznik bazy danych – obiekt bazy danych, który umożliwia dostęp z lokalnej bazy danych do zdalnej bazy danych.

Operacje możliwe do wykonania za pomocą łącznika: select, insert, update, delete i lock table na tabelach lub perspektywach znajdujących się w zdalnej bazie danych.

Łącznik pozwala również wywoływać znajdujące się w bazie zdalnej procedury i funkcje.

Podział łączników:

• łącznik publiczny (ang. public database link) jest dostępny dla wszystkich użytkowników bazy danych;

• łącznik prywatny (ang. private database link) jest własnością użytkownika, który go utworzył; inni użytkownicy bazy danych nie mogą korzystać z łączników prywatnych innych użytkowników.

Definiowanie łączników prywatnych

Polecenie SQL:

SQL> create database link nazwa

connect to użytkownik_zdalny identified by hasło using 'nazwa_usługi';

Np.

Łącznik prywatny o nazwie lab92 do bazy danych określonej za pomocą nazwy usługi LAB92.II.PP zdefiniowanej w pliku konfiguracyjnym tnsnames.ora.

create database link lab92

connect to scott identified by tiger using 'LAB92.II.PP';

nazwa: jest nazwą łącznika;

użytkownik_zdalny: jest nazwą użytkownika istniejącego w zdalnej bazie danych;

hasło: jest hasłem użytkownika zdalnego;

nazwa_usługi: jest nazwą usługi zdefiniowaną w pliku tnsnames.ora

Polecenie tworzy łącznik o nazwie lab92 wskazujący na schemat użytkownika scott z hasłem tiger, znajdujący się w bazie danych określonej usługą o nazwie LAB92.II.PP.

W momencie tworzenia łącznika system nie sprawdza, czy istnieje usługa o podanej nazwie, ani czy wyspecyfikowana nazwa i hasło użytkownika są poprawne. Weryfikacja odbywa się dopiero w momencie odwołania się do zdalnej bazy za pomocą łącznika.

Używając nazwy usługi (LAB92.II.PP), sprawia się, że nazwy hosta (HOST) i instancji zdalnej bazy danych (SERVICE_NAME lub INSTANCE_NAME) pozostają transparentne dla użytkownika. Nazwy te są zamieniane na ich rzeczywiste wartości przy wykorzystaniu pliku tnsnames.ora lokalnego hosta.

Wpis w pliku tnsnames.ora:

LAB92.II.PP = (DESCRIPTION = (ADDRESS =

(PROTOCOL = TCP) (HOST= M_LAB92 ) (PORT=1521)

(CONNECT DATA=

(SERVICE_NAME = LOC) ) )

Przykład użycia: SQL> select * from moja_tabela@lab92;

Uwaga:

jeśli parametr inicjalizacyjny GLOBAL_NAMES systemu Oracle został ustawiony na wartość true, tj.

GLOBAL_NAMES = true

wówczas nazwa łącza danych musi być taka sama jak globalna nazwa zdalnej bazy danych (domyślną wartością w Oracle 10g i 11g jest GLOBAL_NAMES = false).

Można również zdefiniować łącznik bazodanowy, który podczas podłączania do zdalnej bazy danych korzysta z danych uwierzytelniających bieżącego użytkownika, który jest aktualnie podłączony do bazy lokalnej (w odróżnieniu od ustalonego użytkownika o schemacie zdefiniowanym w zdalnej bazie danych).

Można wówczas użyć definicji łącznika wskazującego na bieżącego użytkownika:

create database link lab92 connect to current_user using 'LAB92.II.PP';

lub

create database link lab92 using 'LAB92.II.PP';

W zdalnej bazie danych musi istnieć taki sam użytkownik, jak korzystający z łącznika, i musi on posiadać identyczne hasło.

Informacje o prywatnych łącznikach danego użytkownika uzyskujemy z perspektywy słownika bazy danych USER_DB_LINKS:

SQL> select * from user_db_links ; (pełne informacje o linku) lub

SQL> select db_link from user_db_links ; (tylko nazwy łączników)

I

Wyświetlanie tylko nazw łączników

DB_LINK --- LAB92

Wyświetlanie pełnych informacji:

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED --- --- --- ---

LAB92 DEMO DEMO LAB92.II.PP 08/04/19

Znaczenie atrybutów perspektywy USER_DB_LINKS jest następujące:

DB_LINK jest nazwą łącznika;

USERNAME jest nazwą użytkownika wykorzystywanego w definicji łącznika, czyli użytkownika zdalnej bazy danych;

PASSWORD jest hasłem użytkownika zdalnej bazy danych;

HOST jest nazwą sieciowej usługi bazy danych wskazanej w klauzuli USING polecenia tworzącego łącznik;

CREATED zawiera datę utworzenia łącznika.

Informacje o wszystkich łącznikach, do których dany użytkownik ma dostęp, uzyskujemy za pomocą:

SQL> select * from all_db_links ;

Uprawnienia systemowe użytkownika, który będzie tworzył prywatne łączniki bazy danych:

CREATE DATABASE LINK

SQL> grant create databaselink to nazwa_użytkownika ;

Łącznik bazy danych staje się aktywny w momencie jego pierwszego użycia i pozostaje aktywny do końca sesji lub do momentu jego jawnego zamknięcia poleceniem:

SQL> alter session close database link nazwa_lacznika;

Prywatny łącznik usuwa się z bazy danych poleceniem:

SQL> drop database link nazwa_lacznika ;

Definiowanie łączników publicznych

Polecenie SQL:

SQL> create public database link nazwa

connect to użytkownik_zdalny identified by hasło using 'nazwa_usługi';

Np.

Łącznik publiczny o nazwie lab92 do bazy danych określonej za pomocą nazwy usługi LAB92.II.PP zdefiniowanej w pliku konfiguracyjnym tnsnames.ora.

create public database link lab92 connect to scott identified by tiger using 'lab92.ii.pp';

Do tworzenia łączników publicznych niezbędne jest posiadanie uprawnienia:

CREATE PUBLIC DATABASE LINK

Publiczny łącznik usuwa się z bazy danych poleceniem:

SQL> drop public database link nazwa_lacznika ;

Łączniki a nazwy globalne

W przypadku, gdy zdefiniujemy łącznik bazy danych bez nazwy domeny, zostanie ona automatycznie dodana do nazwy łącznika.

Na przykład jeśli nazwa domeny

DB_DOMAIN = II.PP

Wówczas definiując łącznik bez nazwy domeny

create public database link rw_lab92 connect to scott identified by tiger using 'lab92.ii.pp';

możemy realizować odwołania do tego łącznika specyfikując jego nazwę z domeną (rw_lab92.ii.pp) lub bez niej (rw_lab92).

Jeśli chcemy utworzyć łącznik z inną domeną niż rzeczywista domena bazy danych, to musimy ją wyspecyfikować jawnie w poleceniu:

create public database link test.ist.pwr.wroc.pl connect to scott identified by tiger

using 'lab92.ii.pp';

Wtedy odwołania z wykorzystaniem łącznika mają postać:

SQL> select * from moja_tab@test.ist.pwr.wroc.pl ;

Przykłady wykorzystania łącznika

delete from rachunki@lab92;

create table rachunki_kopia as

select * from rachunki@lab92;

Pierwsze polecenie wybiera wszystkie rekordy ze zdalnej tabeli rachunki,

drugie – usuwa zawartość zdalnej tabeli rachunki,

a trzecie – tworzy tabelę rachunki_kopia jako kopię zdalnej tabeli rachunki.

5.4. Zastosowanie synonimów w Oracle

Synonim w Oracle jest nazwą zastępczą - aliasem, nadanym obiektowi bazy danych.

Podczas definiowania synonimu powstaje odpowiedni wskaźnik do obiektu oryginalnego, a więc nie jest tworzony duplikat obiektu.

Użycie skróconej nazwy zastępczej (synonimu) pozwala:

• uprościć odwoływanie się do tabel i innych obiektów bazy danych;

• ukryć szczegółowe informacje o obiektach źródłowych oraz ich lokalizacji.

Można tworzyć synonimy do:

- tabel i łączników do tabel;

- widoków;

- migawek (widoków materializowanych);

- sekwencji;

- procedur i funkcji;

- pakietów;

- innych synonimów.

Cechy synonimów:

• nie alokują w bazie danych żadnych przestrzeni dla swoich potrzeb, jedynie w słowniku bazy danych pojawia się ich definicja;

• pozwalają na ukrycie tożsamości oraz lokalizacji wskazywanego obiektu, tj.

źródła danych (przeźroczystość dostępu);

• mogą być definiowane do obiektów znajdujących się w schematach innych użytkowników oraz obiektów wskazywanych przez łączniki bazodanowe;

• użycie synonimów obiektów w poleceniach SQL zapewnia przeźroczystość położenia, gdyż jeśli obiekt zostanie zmieniony lub przeniesiony wystarczy tylko zaktualizować synonim, a nie wszystkie odwołania do obiektu w poleceniach;

• umożliwiają skracanie długich nazw innych obiektów bazy danych, co prowadzi do uproszczenia poleceń.

Rodzaje synonimów:

- synonimy publiczne: są widoczne dla wszystkich użytkowników bazy danych; są zazwyczaj tworzone przez administratora bazy danych;

- synonimy prywatne: są definiowane w ramach schematu danego użytkownika i dostępne tylko w ramach tego schematu;

inny użytkownik chcąc skorzystać z takiego synonimu musi posiadać uprawnienia dostępu do obiektu wskazywanego przez synonim oraz poprzedzać nazwę synonimu nazwą właściciela schematu, w którym synonim się znajduje.

Definiowanie synonimu

Synonim definiuje się poleceniem create synonym w postaci:

create [or replace] [public] synonym nazwa_synonimu for obiekt_wskazywany ;

• Słowa kluczowe w nawiasach kwadratowych są opcjonalne.

• Słowo or replace jest używane jeśli chcemy zastąpić istniejącą definicję synonimu.

• Słowo kluczowe public umożliwia zdefiniowanie publicznego synonimu.

Jeśli zostanie ono pominięte, to utworzony zostanie synonim prywatny.

• Jeśli obiekt wskazywany znajduje się w schemacie innego użytkownika, to nazwę obiektu należy poprzedzić nazwą właściciela.

• Aby móc tworzyć synonimy użytkownik musi posiadać uprawnienie CREATE SYNONIM lub CREATE PUBLIC SYNONIM.

Przykłady

Synonim prywatny DT do tabeli DEPT w schemacie użytkownika scott.

create synonym dt for scott.dept;

Synonim prywatny DEMO_SYN do tabeli DEMO w zdalnej bazie danych, wskazywanej łącznikiem MLINK.

create synonym demo_syn for demo@mlink;

W momencie tworzenia synonimu system nie sprawdza istnienia obiektu przez niego wskazywanego, ani uprawnień dostępu do obiektu użytkownika, który utworzył synonim. Weryfikacja taka odbywa się w momencie pojawienia się odwołania do synonimu.

Np.

Użytkownik cindy uzyskuje uprawnienie obiektowe SELECT do tabeli DEPT w schemacie użytkownika scott.

SQL> connect scott/haslo_s ;

SQL> grant select on scott.dept to cindy ;

Nadanie uprawnienia create synonym użytkownikowi cindy.

SQL> connect system/haslo_s;

SQL> grant create synonym to cindy ;

Utworzenie synonimu dt do tabeli dept użytkownika scott.

SQL> connect cindy/haslo_c;

SQL> create synonym dt for scott.dept;

Wydruk zawartości tabeli wskazywanej przez synonim dt.

SQL> select * from dt ;

Zmiana nazwy synonimu dt na dept1 przez cindy:

SQL> create or replace synonym dept1 for dt;

Po zmianie nazwy synonimu dt na dept1 użytkownik cindy zachowuje uprawnienia otrzymane do synonimu dt.

SQL> select * from dept1 ;

W podobny sposób zadziała zmiana nazwy synonimu:

SQL> rename dt to dept1;

SQL> select * from dept1 ;

W ramach schematu określonego użytkownika synonimy muszą mieć unikalne nazwy, różne od nazw innych obiektów.

Różni użytkownicy mogą tworzyć synonimy o takich samych nazwach, o ile nie są to synonimy publiczne.

Użytkownik scott tworzy synonim prywatny dt do tabeli demo w schemacie cindy. Obaj użytkownicy scott i cindy będą mieli zdefiniowane w swoich schematach synonimy o nazwie dt.

SQL> connect scott/haslo_s;

SQL> create synonym dt for cindy.demo;

Użytkownicy mogą wzajemnie korzystać ze swoich synonimów, podając przed nimi nazwę użytkownika. Operacje zostaną wykonane jeśli użytkownicy posiadają odpowiednie uprawnienia.

Użytkownik scott może skorzystać z synonimu prywatnego dept1 użytkownika cindy, podając nazwę schematu (użytkownika) przed nazwą aliasu:

SQL> select * from cindy.dept1;

Usuwanie synonimów

Synonim usuwamy za pomocą polecenia DROP.

drop [public] synonym nazwa_synonimu;

Przykład

SQL> drop synonym dept1;

Synonim powiązany z usuwanym obiektem nie jest automatycznie usuwany, czyli usunięcie tabeli nie powoduje automatycznego usunięcia synonimu do tej tabeli.

Wszelkie próby odwołania się do synonimu, który został usunięty, powodują błędy.

Informacje słownikowe

Informacje o synonimach prywatnych użytkownika można uzyskać za pomocą perspektywy (widoku) USER_SYNONYMS.

Natomiast informacje o wszystkich synonimach, do których ma dostęp dany użytkownik, można uzyskać za pomocą perspektywy ALL_SYNONYMS.

SQL> select * from user_synonyms;

create synonym dt for scott.dept;

create synonym demo_syn for demo@mlink;

gdzie

domena dla łącznika mlink: pwr.pl

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ---

DT SCOTT DEPT

DEMO_SYN DEMO MLINK.PWR.PL

SYNONYM_NAME - nazwa synonimu;

TABLE_OWNER - nazwa właściciela tabeli dla synonimu tabeli, lub puste dla synonimu łącznika;

TABLE_NAME - nazwa tabeli wskazywanej synonimem;

DB_LINK – nazwa łącznika wskazywanego synonimem.

5.5. Zastosowanie perspektyw w Oracle

Perspektywa (ang. view) jest wirtualną tabelą, utworzoną za pomocą poleceń SQL-a, które mają na celu wyświetlenie zawartości jednej lub wielu tabel bazowych lub innych perspektyw bazowych.

Podstawowe własności:

• Perspektywa jest logicznie powiązana ze swoimi tabelami lub perspektywami bazowymi. W momencie wykonania perspektywy jest sprawdzana możliwość wykonania tworzących ją poleceń, a także ograniczenia integralnościowe nałożone na tabele bazowe. Dzięki temu zmiany w tabelach bazowych lub skasowanie tabeli bazowej, a także przekroczenia ograniczeń integralnościowych są wykrywane.

• Perspektywa może też posłużyć do modyfikowania zawartości tabel bazowych, na których jest oparta. Możliwość wykonywania operacji DML w postaci insert, update i delete jest jednak ograniczona do perspektyw, które składają się z kolumn pojedynczej tabeli. Istnieje możliwość eliminacji tych ograniczeń, ale trzeba wówczas zdefiniować za pomocą klauzuli

„instead of” własny algorytm obsługi (wyzwalacz) każdej z operacji DML kierowanej do perspektywy.

• Perspektywa zawsze odzwierciedla stan aktualny bazy i tym różni się od migawki, która odzwierciedla stan przeszły.

• Jeśli w poleceniu SQL występuje nazwa perspektywy, to system Oracle automatycznie zastępuję tę nazwę zapytaniem, na którym dana perspektywa została oparta.

• Perspektywa nie alokuje fizycznej przestrzeni dyskowej. Jest ona produktem zapytań wczytujących odpowiednie dane z tabel bazowych.

• Perspektywa tym różni się od zwykłej tabeli, że nie posiada własnych danych, co oznacza że na atrybutach perspektywy nie można definiować indeksów.

• Informacja o perspektywach jest przechowywana w słowniku bazy danych.

Przykład.

Tabela bazowa PRAC

PNO PNAZ STAN GR ZATR DOD PENSJA NDZ

7329 Smith Clerk 7902 17-12-99 300.00 800.00 20 7499 Allen Salesman 7698 20-02-99 300.00 1600,00 30 7521 Ward Salesman 7698 22-02-99 5.00 1250.00 30 7566 Jones Manager 7839 02-04-99 100.00 2975.00 20

Perspektywa KADRA – powstała przez wybór kolumn PNO, PNAZ, STAN, GR i NDZ z tabeli PRAC.

SQL>

create view KADRA as select PNO, PNAZ, STAN, GR, NDZ from PRAC;

Wydruk danych z wykorzystaniem perspektywy:

SQL> select * from KADRA;

KADRA:

PNO PNAZ STAN GR NDZ

7329 Smith Clerk 7902 20 7499 Allen Salesman 7698 30 7521 Ward Salesman 7698 30 7566 Jones Manager 7839 20

Korzyści ze stosowania perspektyw:

• możliwość ograniczania danych udostępnianych użytkownikom do wybranych wierszy i kolumn (autoryzacja dostępu do danych); tabele mogą być podzielone na wiele różnych perspektyw; np. wybrani użytkownicy mogą mieć dostęp do danych finansowych;

• uproszczenie dostępu do danych poprzez ukrycie złożonych zapytań w definicji perspektywy oraz wprowadzanie skróconych nazw tabel;

• możliwość prezentowania tych samych danych w różnej formie poprzez umieszczenie w definicji perspektywy funkcji konwersji danych lub wyrażeń arytmetycznych;

• zapewnienie logicznej niezależności aplikacji od danych; dzięki utworzeniu interfejsu pomiędzy aplikacjami a tabelami bazy danych, w przypadku modyfikacji schematu tabel bazowych, należy zmienić tylko definicję perspektyw, a aplikacje nie będą wymagały żadnych modyfikacji;

• możliwość wprowadzenia dodatkowych ograniczeń integralnościowych w oparciu o klauzulę „with check option”;

• możliwość integracji w jednym miejscu danych pochodzących z tabel znajdujących się w różnych, fizycznie rozproszonych bazach danych;

• uniezależnienie aplikacji od fizycznej lokalizacji danych poprzez ukrycie w definicji perspektywy informacji o lokalizacji danych.

Składnia polecenia definiującego perspektywę

create [or replace] [force | noforce] view nazwa_persp as

select pole/pola from tabela/tabele where warunek

[with read only]

[with check option [constraint nazwa_ograniczenia]];

Opcjonalna klauzula “or replace” oznacza, że definicja istniejącej perspektywy zostanie nadpisana przez nową definicję.

Opcjonalna klauzula „force” umożliwia utworzenie perspektywy nawet jeśli nie istnieją jej tabele bazowe. Domyślnie stosowana jest klauzula „no force”, która zabrania tworzenia perspektywy jeśli nie istnieją jej tabele bazowe.

Parametry „pole/pola” są atrybutami występującymi w tabelach bazowych.

Parametr „warunek” definiuje ograniczenia dotyczące wyboru danych z tabel.

Opcjonalna klauzula „with read only” zabrania kierowania do perspektywy poleceń DML.

Opcjonalna klauzula „with check option” umożliwia zdefiniowanie ograniczenia integralnościowego za pomocą klazuli where perspektywy.

Można wykonywać operacje DML (np. Insert) na perspektywie tylko, jeśli w ich efekcie będzie spełnione ograniczenie zadane klauzulą where (rekord będzie można wyświetlić za pomocą perspektywy).

Np.

perspektywa udostępniająca informacje o pracownikach z tabeli PRAC, którzy zarabiają ponad 1000 zł; tylko te rekordy, które spełniają ograniczenie integralnościowe where mogą być przetwarzane i wyświetlane.

SQL> create view pond_tys as

select pnaz, pensja from PRAC where pensja > 1000

with check option constraint ;

SQL> select * from ponad_tys;

PNAZ PENSJA Allen 1600,00 Ward 1250.00 Jones 2975.00

Lub na podstawie:

http://nuijten.blogspot.com/2010/10/inline-view-check-option.html

SQL> create table temp (x int);

Table created.

SQL> insert into temp values (1);

1 row created.

SQL> insert into temp values (100);

1 row created.

SQL> create view vw_temp as select * from temp where x <= 10;

View created.

SQL> select * from vw_temp;

X --- 1

SQL> insert into vw_temp values (2);

1 row created.

SQL> insert into vw_temp values (102);

1 row created.

SQL> select * from vw_temp;

X --- 1 2

Wartość 102 nie jest wyświetlana,

choć została wstawiona do tabeli temp (atrybut X).

Aby zabezpieczyć się przed wstawianiem danych, które nie mogą być wyświetlone przez perspektywę dodajemy with check option;

create view vw_temp 2 as

3 select * 4 from temp 5 where x <= 10 6 with check option 7 ;

View created.

SQL>

SQL> select * from vw_temp;

X --- 1 2

SQL> insert into vw_temp values (2);

1 row created.

SQL> insert into vw_temp values (102);

insert into vw_temp values (102) *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

Łączenie danych z kilku tabel

Utworzenie perspektywy z tabel LOK i DEPT.

SQL> select * from LOK;

LOK:

POZ_ID GRUPA 122 New York 124 Dallas 123 Chicago 167 Boston

SQL> select * from DEPT;

DEPT:

D_ID NAZWA POZ_ID

10 Account 122

20 Res 124

30 Sales 123

40 Oper 167

12 Res 122

13 Sales 122

14 Oper 122

23 Sales 124

24 Oper 124

34 Oper 123

43 Sales 167

W definicji perspektywy przed nazwą kolumny powinna występować nazwa tabeli bazowej.

SQL> create view gp as

select dept.nazwa, dept.poz_id, lok.grupa from dept, lok where dept.poz_id = lok.poz_id;

GP:

NAZWA POZ_ID GRUPA

Account 122 New York

Res 124 Dallas

Sales 123 Chicago

Oper 167 Boston

Res 122 New York

Sales 122 New York

Oper 122 New York

Sales 124 Dallas

Oper 124 Dallas

Oper 123 Chicago

Sales 167 Boston

Zamiast każdorazowo podawać nazwę tabeli bazowej i nazwę wchodzącej w jej skład kolumny, możemy utworzyć aliasy poszczególnych tabel, tym samym zmniejszając rozmiar polecenia definiującego perspektywę.

SQL> create view gp as

select d.nazwa, d.poz_id, k.grupa from dept d, lok k where d.poz_id = k.poz_id;

Można również dokonać integracji zawartości tabel np. OSOBY, znajdujących się w różnych lokalizacjach Poznań, Warszawa, Kraków, opisanych łącznikami link_poz, link_warsz, link_krak.

SQL> select * from osoby_poznan@linkpoz;

OSOBY_POZNAN

K_ID NAZW MIASTO

1234 Smith Poznan

2456 Nowak Poznan

7893 Adamski Poznan

SQL> create view v_osoby as

select * from osoby_poznan@link_poz union

select * from osoby_warszawa@link_warsz union

select * from osoby_krakow@link_krak;

Kasowanie perspektywy

Do usuwania perspektywy stosujemy polecenie DROP.

SQL> drop view v_osoby;

Modyfikowanie tabel za pomocą perspektyw

Do perspektywy składającej się z kolumn pojedynczej tabeli można wprowadzać praktycznie dowolne zmiany. Wszystkie modyfikacje zostaną automatycznie przeniesione do tabeli bazowej, pod warunkiem, że nie zostały przekroczone ograniczenia integralnościowe nałożone na tabele bazowe.

Tabela bazowa PRAC

PNO PNAZ STAN GR ZATR DOD PENSJA NDZ

7329 Smith Clerk 7902 17-12-99 300.00 800.00 20 7499 Allen Salesman 7698 20-02-99 300.00 1600,00 30 7521 Ward Salesman 7698 22-02-99 5.00 1250.00 30 7566 Jones Manager 7839 02-04-99 100.00 2975.00 20

SQL> create view KADRA as select PNO, PNAZ, STAN, GR, NDZ from PRAC;

SQL> update kadra set ndz = ndz*2

where stan = ’Salesman’;

SQL> select * from kadra where stan=’Salesman’;

PNO PNAZ STAN GR NDZ

7499 Allen Salesman 7698 60 7521 Ward Salesman 7698 60

SQL> select * from prac;

PNO PNAZ STAN GR ZATR DOD PENSJA NDZ

7329 Smith Clerk 7902 17-12-99 300.00 800.00 20 7499 Allen Salesman 7698 20-02-99 300.00 1600,00 60 7521 Ward Salesman 7698 22-02-99 5.00 1250.00 60 7566 Jones Manager 7839 02-04-99 100.00 2975.00 20

W przypadku perspektyw, zawierających kolumny kilku tabel, należy liczyć się z dużymi ograniczeniami co do możliwości realizowania operacji DML (update, insert, delete) na tabelach bazowych poprzez perspektywę.

Ograniczenia w stosowaniu poleceń DML na tabelach bazowych za pośrednictwem perspektywy można ominąć, definiując własny algorytm obsługi każdej operacji DML kierowanej do perspektywy. Wykorzystuje się w tym celu wyzwalacz „instead of” definiowany dla perspektywy.

W szczególności, w celu umożliwienia modyfikowania tabel bazowych, poprzez perspektywę v_osoby,

OSOBY_POZNAN

K_ID NAZW MIASTO

1234 Smith Poznan

2456 Nowak Poznan

7893 Adamski Poznan

OSOBY_WARSZAWA

K_ID NAZW MIASTO

1235 Kowalski Warszawa

2449 Doren Warszawa

7888 Lukocki Warszawa

OSOBY_KRAKOW

K_ID NAZW MIASTO

2567 Low Krakow

2445 Dera Krakow

7899 Marko Krakow

SQL> create view v_osoby as

select * from osoby_poznan@link_poz union

select * from osoby_warszawa@link_warsz union

select * from osoby_krakow@link_krak;

za pomocą polecenia „insert”, należy zdefiniować dla niej wyzwalacz v_os_modyf w postaci:

SQL> create trigger v_os_modyf instead of insert on v_osoby begin

if :new.miasto=’Poznan’

then insert into osoby_poznan@link_poz

values (:new.k_id, :new.nazw, :new.miasto);

elsif :new.miasto=’Warszawa’

then insert into osoby_warszawa@link_warsz

values (:new.k_id, :new.nazw, :new.miasto);

elsif :new.miasto=’Krakow’

then insert into osoby_krakow@link_krakow

values (:new.k_id, :new.nazw, :new.miasto);

end if;

end;

Odczyt kolumn perspektywy, które mogą być modyfikowane

W przypadku perspektywy łączącej kolumny z kilku tabel można odczytać, które z kolumn mogą być modyfikowane, korzystając z perspektywy USER_UPDATABLE _COLUMNS:

SQL> select column_name, updatable from user_updatable_columns where table_name = ’KADRA’;

Atrybut column_name zawiera nazwy tabel oraz perspektyw użytkownika.

Dla każdego atrybutu tabeli lub perspektywy (column_name) podawana jest informacja o możliwości modyfikowania wartości danego atrybutu (UPDATABLE) , wstawiania rekordu, zawierającego wartość tego atrybutu (INSERTABLE), oraz usuwania rekordu, zawierającego wartość tego atrybutu (DELETABLE). Wartością atrybutów updatable, insertable, deletable jest YES lub NO.

Przeglądanie definicji perspektyw

Definicje perspektyw użytkownika są zapisane w perspektywie słownika danych o nazwie USER_VIEWS, natomiast wszystkie perspektywy, z których użytkownik może korzystać są dostępne za pomocą ALL_VIEWS.

SQL> describe all_views;

SQL> describe user_views;

Najważniejsze atrybuty obu perspektyw to:

VIEW_NAME - nazwa perspektywy;

TEXT_LENGTH – długość w bajtach tekstu definiującego perspektywę;

TEXT – tekst definicji perspektywy.

SQL> select view_name, text from user_views where view_name = ’KADRA’;

VIEW_NAME TEXT

--- KADRA select PNO, PNAZ, STAN, GR, NDZ from PRAC;

5.6. Migawki – perspektywy zmaterializowane

Migawka jest mechanizmem wykorzystywanym do replikowania danych.

Jest to obiekt bazy danych, definiowany poleceniem SQL, który stanowi kopię danych z wybranej tabeli lub grupy tabel.

Ponieważ migawka jest fizycznym obiektem, rodzajem tabeli, której jest przydzielona pamięć może ona być indeksowana i partycjonowana, podobnie jak tabela, a także można dla niej zdefiniować parametry składowania oraz przestrzeń tabel.

Migawka zapamiętuje stan z danej chwili. Wynika stąd potrzeba jej odświeżania w celu synchronizacji jej zawartości z zawartością tabel źródłowych.

Parametry odświeżania migawki są określane w jej definicji.

Sposób definiowania migawek w środowisku Oracle zostanie omówiony podczas prezentacji zagadnień dotyczących fragmentacji oraz replikacji danych.