Bazy danych 1
1 Architektura bazy danych Oracle
 Instancja (ang. instance) [14] - zestaw procesów uruchomiony w pamięci komputera i udostępniający dane
i operacje bazy danych; całość procesów i plików składających się na pojedynczą bazę danych Oracle 11g.
Rodzaje wykorzystywanych plików [5]
" pliki danych,
" pliki dziennika powtórzeń (co najmniej dwa)  przechowują informacje dotyczące dodawania, modyfi-
kowanie i usuwania danych,
" archiwizowane pliki dziennika (tryb ARCHIVELOG),
" pliki sterujÄ…ce  zawierajÄ… informacje na temat fizycznej struktury samej bazy,
" pliki parametrów inicjujących,
" pliki alertów  przechowują informacje dotyczące działania całej instancji,
" dziennika śladu  zawierają informacje dotyczące procesów działających w tle,
" pliki kopii zapasowych,
" pliki haseł.
Struktury pamięci [5]
"  Globalny obszar systemu (ang. System Global Area  SGA) jest zbiorem struktur we wspólnej pamięci,
wykorzystywanych przez instancję Oracle i wspólnie przez użytkowników instancji bazy danych.
"  Bufor podręczny (ang. buffer cache) przechowuje bloki danych z dysk, które zostały ostatnio odczytane
dla potrzeb instrukcji SELECT lub zmodyfikowane bloki, które zostały zmienione lub dodane przez
instrukcje DML.
" Wspólny obszar (ang. shared pool) zawiera pamięci podręczne:
  Pamięć podręczna biblioteki (ang. library cache) przechowuje informacje o instrukcjach SQL
i PL/SQL wykonywanych w bazie danych.
  Pamięć podręczna słownika danych (ang. data dictionary cache) to kolekcja tabel bazy danych,
których właścicielem są schematy SYS i SYSTEM i które zawierają metadane na temat bazy
danych, jej struktur oraz uprawnień i ról użytkowników bazy danych.
 Pamięć podręczna serwera (ang. server result cache) zawiera dwie pamięci podręczne. Pierwsza
SQL query result cache służy do przechowywania wyników zapytań oraz fragmentów zapytań.
Druga pamięć podręczna PL/SQL function result cache przechowuje wyniki funkcji.
 Inne.
"  Bufor dziennika powtórzeń (ang. redo log buffer) przechowuje najnowsze zmiany dokonane w blokach
danych plików danych. Po zapełnieniu bufora dziennika powtórzeń w jednej trzeciej lub co trzy sekundy
wpisy dziennika powtórzeń zostają zapisane do plików dziennika powtórzeń.
"  Obszar dużych struktur pamięciowych (ang. large pool) to opcjonalny obszar SGA. Obszar ten używany
jest przez transakcje, które operują na co najmniej dwóch bazach danych, bufory komunikatów dla
procesów wykonujących zapytania równoległe oraz równoległe operacje tworzenia kopii zapasowych
i odtwarzania RMAN.
1
"  Obszar Java (ang. Java pool) używany jest przez Oracle JVM (Java Virtual Machine) do obsługi
wszystkich kodów języka Java oraz danych należących do sesji użytkownika. Kod Java i dane są przecho-
wywane w obszarze Java w sposób analogiczny do sposobu, w jaki kody SQL i PL/SQL przechowywane
są w pamięci podręcznej we wspólnym obszarze.
"  Obszar strumieni (ang. streams pool) przechowuje dane i struktury kontrolne służące do obsługi funkcji
Oracle Streams dostępnej w Oracle Enterprise Edition. Oracle Streams zarządza dzieleniem danych
i zdarzeń w środowisku rozproszonym.
"  Globalny obszar programów (ang. Program Global Area - PGA) to obszar pamięci zaalokowany i do-
stępny wyłącznie dla jednego procesu. Konfiguracja PGA zależy od sposobu konfiguracji połączenie
z bazÄ… danych Oracle:
 serwer współdzielony (ang. shared server)  wielu użytkowników współdzieli połączenie z bazą
danych, informacje na temat sesji użytkownika przechowywane są w SGA,
 serwer dedykowany (ang. dedicated server)  każdy proces użytkownika posiada własne połączenie,
pamięć sesji znajduje się w PGA.
Procesy drugoplanowe
 Procesy drugoplanowe (ang. background processes) [14]  procesy realizujące dostęp do wystartowanej
bazy danych Oracle 11g, a więc pełniące istotna rolę w implementacji bazy danych. Przy rozruchu bazy
danych uruchamiane są różnorakie procesy, działające potem niezależnie od siebie i wykonujące przeznaczone
im czynności, aż do zamknięcia bazy danych.
Procesy drugoplanowe [5]:
"  SMON to proces monitora systemu (ang. system monitor). W razie uszkodzenia systemu albo awarii
instancji na przykład z powodu przerwy w dostawie prądu albo uszkodzenia procesora proces SMON
przywraca stan instancji przez wykonanie wpisów z plików dziennika powtórzeń w plikach danych.
"  Jeżeli połączenie użytkownika zostanie zerwane albo w inny sposób ulegnie uszkodzeniu, PMON, znany
również jako monitor procesów (ang. process monitor), wykona zadania porządkowe. Proces czyści
bufor pamięci podręcznej bazy danych, a także wszystkie inne zasoby, z których korzystało połączenie
użytkownika.
"  RECO, czyli proces odtwarzania (ang. recovery process), obsługuje awarie rozproszonych transakcji
(to znaczy transakcji, które wykonują zmiany w tabelach należących do co najmniej dwóch baz danych).
"  DBWn Proces zapisujący do bazy (ang. database writer), znany we wcześniejszych wersjach Oracle
jako DBWR, zapisuje w plikach danych nowe lub zmienione bloki danych (tzn. bloki niezatwierdzone 
ang. dirty blocks), znajdujące się w buforze pamięci podręcznej.
"  LGWR, czyli proces zapisujÄ…cy do dziennika (ang. log writer ), odpowiada za zarzÄ…dzanie buforem
dziennika powtórzeń. Transakcja jest uznawana za zakończoną dopiero wówczas, gdy LGWR zapisze
w dzienniku powtórzeń dane powtórzeń, włączając w to rekord zatwierdzenia. Co więcej, DBWn nie mo-
że zapisać w plikach danych niezatwierdzonych buforów znajdujących się w buforze pamięci podręcznej,
dopóki LGWR nie zapisze danych powtórzenia.
"  Proces punktów kontrolnych (ang. checkpoint process), czyli CKPT, pomaga zmniejszyć ilość czasu
potrzebną do przywrócenia instancji.
"  ARCn Jeżeli baza danych znajuje się w trybie ARCHIVELOG, wówczas w momencie gdy dziennik
powtórzeń ulegnie zapełnieniu i dane powtórzeń zaczną być zapisywane w następnym dzienniku powtó-
rzeń w kolejce, proces archiwizujący (ang. archiver process) skopiuje dzienniki powtórzeń do co najmniej
jednego katalogu, urzÄ…dzenia lub sieciowej lokalizacji docelowe.
2
2 Oracle Net Services
 Oracle Net Services [14] to podsystem programowy, który obsługuje połączenia sieciowe z bazą danych
w korporacyjnych, heterogenicznych systemach komputerowych opartych na sieci. Częścią podsystemu jest
Oracle Net zawiadująca transferem danych pomiędzy aplikacjami zdalnymi a bazą danych Oracle; transfer
danych jest realizowany na bazie standardowego protokołu sieciowego, jak na przykład TCP/IP.
 Deskryptor połączenia [5] określa protokół komunikacyjny, nazwę serwera i nazwę usługi instancji uży-
wanÄ… podczas przetwarzania zapytania.
Przykładowy deskryptor połączenia:
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)))
 Użytkownicy [5] nie muszą wprowadzać deskryptora połączenia każdorazowo, gdy chcą uzyskać dostęp
do zdalnych danych. Administrator bazy danych może zdefiniować nazwy usług (lub aliasy), które odwołują
się do tych deskryptorów. Nazwy usług są przechowywane w pliku tnsnames.ora.
 Usługa katalogowa Oracle Internet Directory [5] ułatwia obsługę serwerów LDAP (ang. Lightweight
Directory Access Protocol), które w rozproszonym środowisku sieciowym Oracle zajmują się scentralizowanym
rozpoznawaniem nazw sieciowych.
Przykładowa zawartość pliku tnsnames.ora:
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)
 Każdy serwer bazodanowy [5] znajdujący się w sieci musi mieć plik listener.ora. Zawiera on nazwy i adresy
wszystkich procesów nasłuchujących komputera, a także wyszczególnia instancje, które są przez te procesy
obsługiwane.
Przykładowa zawartość pliku listener.ora:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
3
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\\...\\product\\11.2.0\\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\\...\\liborcl.dll")
)
)
ADR_BASE_LISTENER = C:\...
3 Użytkownicy, schematy i profile
Tworzenie użytkownika [8]:
CREATE USER user
IDENTIFIED { BY password
| EXTERNALLY [ AS  certificate_DN ]
| GLOBALLY [ AS  [ directory_DN ] ]
}
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| { QUOTA { size_clause | UNLIMITED } ON tablespace }...
EDO | PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| { QUOTA { size_clause | UNLIMITED } ON tablespace }...
| PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
]...
] ;
Przykład:
CREATE USER new_user IDENTIFIED BY 12345;
Usuwanie użytkownika [8]:
DROP USER user [ CASCADE ] ;
Przykład:
DROP USER new_user CASCADE;
Schemat użytkownika [5] posiada taką samą nazwę jak użytkownik. Schemat może zawierać różne rodza-
je obiektów np. tabele, widoki, indeksy, sekwencje itd. Właściciel schematu lub administrator może może
nadawać uprawnienia innym użytkownikom do tych obiektów.
 Profil bazy danych [5] to opatrzony nazwą zestaw informacji o ograniczeniach korzystania z zasobów,
przypisywanych konkretnemu użytkownikowi.
4 Uprawnienia i role
 Uprawnienie systemowe [13] umożliwia użytkownikowi wykonywanie określonych czynności w bazie da-
nych, takich jak uruchamianie instrukcji DDL. Na przykład uprawnienie CREATE TABLE umożliwia użyt-
kownikowi utworzenie tabeli w jego schemacie.
 Uprawnienie obiektowe [13] pozwala użytkownikowi na wykonywanie określonych czynności na obiektach
bazy danych, takich jak uruchamianie instrukcji DML na tabelach.
 (...) role [5] to nazwana grupa uprawnień. Ponadto każdej roli można przypisać inne role. 
Nadanie uprawnień systemowych [8]:
4
GRANT
{ system_privilege
| role
| ALL PRIVILEGES
}
[, { system_privilege
| role
| ALL PRIVILEGES
}
]...
TO grantee_clause
[ WITH ADMIN OPTION ]
Przykład:
GRANT CREATE SESSION TO new_user;
Odebranie uprawnień systemowych [8]:
REVOKE { system_privilege
| role
| ALL PRIVILEGES
}
[, { system_privilege
| role
| ALL PRIVILEGES
}
]...
FROM grantee_clause
Przykład:
REVOKE CREATE SESSION FROM new_user;
Nadanie uprawnień obiektowych [8]:
GRANT
{ object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
[, { object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
]...
on_object_clause
TO grantee_clause
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
Przykład:
GRANT SELECT ON other_user.test TO new_user;
Odebranie uprawnień obiektowych [8]:
REVOKE { object_privilege | ALL [ PRIVILEGES ] }
[, { object_privilege | ALL [ PRIVILEGES ] } ]...
on_object_clause
FROM grantee_clause
[ CASCADE CONSTRAINTS | FORCE ]
Przykład:
REVOKE SELECT ON other_user.test FROM new_user;
5
5 Przykładowa treść laboratorium
Na podany temat każda grupa (2 osobowa) powinna przygotować następujące skrypty (*.sql):
1. Tworzący co najmniej 7 - 10 procedur oraz funkcji zapewniających pełną obsługę bazy danych.
2. UsuwajÄ…cy procedury oraz funkcje.
3. TworzÄ…cy co najmniej 5-10 wyzwalaczy.
4. UsuwajÄ…cy wyzwalacze.
Do wszystkich procedur, funkcji oraz wyzwalaczy powinien zostać dodany fragment kodu testujący ich
działanie. Wszystkie podprogramy powinny znalez
ć się w utworzonym pakiecie.
Podprogramy bardzo proste oraz skopiowane z wykładu lub instrukcji laboratoryjnych (po zmianie nazw
tabel) nie będą oceniane. W skryptach należy co najmniej raz użyć: instrukcji SELECT INTO, kursora,
kursora z co najmniej jednym parametrem, własnego wyjątku, błędu aplikacji oraz pakietu DBMS RANDOM.
Proszę przynieść wszystkie materiały stworzone podczas laboratorium nr 11 oraz 12.
Literatura
[1] Thomas Connolly, Carolyn Begg,  Systemy baz danych , Wydawnictwo RM, 2004
[2] Jeffrey D. Ullman, Jennifer Widom,  Podstawowy wykład z systemów baz danych , WNT, 2001
[3] Jeffrey D. Ullman, Jennifer Widom,  Systemy baz danych Pełny wykład , WNT, 2006
[4] Kevin Loney, Bob Bryla,  Oracle Database 10g Podręcznik administratora baz danych , Helion, 2008
[5] Scott Urman, Ron Hardman, Michael McLaughlin,  Oracle Database 10g Programowanie w języku
PL/SQL , Helion, 2008
[6] Ramez Elmasri, Shamkant B. Navathe,  Wprowadzenie do systemów baz danych , Helion, 2005
[7] Jose A. Ramalho,  Oracle 8i , Mikom, 2001
[8]  Oracle®Database SQL Language Reference 11g Release 1 (11.1) B28286-02 (b28286.pdf)
[9]  Oracle®Database SQL Language Quick Reference 11g Release 1 (11.1) B28285-02 (b28285.pdf)
[10] Joe Celko,  SQL Zaawansowane techniki programowania , PWN, 2008
[11] Jason Price,  Oracle Database 11g i SQL Programowanie , Helion, 2009
[12] Oracle®Database PL/SQL Language Reference 11g Release 1 (11.1) B28370-02 (B28370.pdf)
[13] Jason Price, Oracle Database 11g i SQL Programowanie, Helion, 2009
[14]  Oracle Database 11g. Przewodnik dla poczÄ…tkujÄ…cych , I. Abramson, M. Abbey, M. Corey, M. Malcher,
Helion, 2010
6