<Insert Picture Here>
Administracja serwerem bazy danych Oracle 11gZarządzanie obiekami bazy danych
Wykład nr 4
Michał Szkopiński
Obiekty w bazie danych Oracle
• Obiekty to struktury przechowujące, porządkujące lub operujące na danych• Tabele• Więzy integralności• Indeksy• Widoki• Widoki zmaterializowane• Sekwencje• Procedury• Linki bazodanowe
• Obiekty przechowywane są w schematach użytkowników
Co to jest Schemat w bazie?
Schemat JANUżytkownik
JAN
posiada
• Dostęp się do swoich obiektów bezpośrednio po nazwie
• SELECT * FROM T1
• Dostęp do obiektów innych użytkowników z przedrostkiem:
• SELECT * FROM JAN.T1
Dostęp do informacji o obiektach w EM
Ale przecież MY lubimy SQLPLUS-a
Konwencje nazewnicze obiektów
• Nazwy obiektów o rozmiarze od 1 do 30 bajtów z następującymi wyjątkami• Nazwa instancji bazy maksymalnie 8 bytów.• Nazy linków bazodanowych max. 128 bajtów.
• Nazwy bez apostrofów: • nie mogą być słowami zarezerwowanymi.• muszą zaczynać się od znaku.• są przechowywane jako wielkie litery
• Nazwy z apostrofami: • są wrażliwe na wielkość znaków• nie są zalecane!
Typy kolumn w tabelach
• Najczęściej używane typy kolumn:• CHAR(size [BYTE|CHAR]): Tekst o stałej długości
• Maksymalna długość 2000, minimalna i domyślna: 1• VARCHAR2(size [BYTE|CHAR]): Tekst o zmienne długości
• Maksymalna długość 4000• DATE: Data
• od 1-go stycznia 4712 (pne) do 31 grudnia, 9999 (ne)• NUMBER(p,s): Liczba całkowita lub zmiennoprzecinkowa
• z precyzją p – całkowitą ilością cyfr• ze skalą s – ilością cyfr po przecinku• dodatnie: 1 x 10-130 do 9.99...9 x 10125 z mak. 38 cyframi przed
przecinkiem• ujemne: -1 x 10-130 do -9.99...99 x 10125 z mak. 38 cyframi przed
przecinkiem
Typy binarne
•CLOB: zawartość binarna tekstowa•BLOB: zawartość binarna o dowolnej strukturze
• Maksymalny rozmiar: (4 GB – 1) * CHUNK• Chunk - jeden lub wielokrotność bloku (mak. 32 kB)
•BFILE: Link do pliku znajdującego się w systemie plików• Maksymalny rozmiar: 4GB
Struktura tabel w bazie Oracle
Przestrzeń tabelTablespace
Tabela A Tabela B
SegmentSegment
Wiersze
Kolumny
Tabela
Bloki
Wiersz Extent
Tworzenie tabeli i modyfikacje
CREATE TABLE SHOPOWNER.JOBS (
Job_id NUMBER(5),
Job_title VARCHAR2(30),
MIN_SALARY NUMBER(6),
MAX_SALARY NUMBER(6)
)
TABLESPACE USERS;
ALTER TABLE SHOPOWNER.JOBS ADD bonus NUMBER(6);
ALTER TABLE SHOPOWNER.JOBS
ADD CONSTRAINT PRIMARY KEY (JOB_ID);
Usuwanie tabeli
• Tabele usuwa się poleceniem: DROP TABLE T1• Razem z tabelą usuwane są:
• Definicja tabeli• Dane• Wyzwalacze• Indeksy związane z tą tabelą• Uprawnienia przydzielone do tej tabeli
• Opcje związane z poleceniem DROP TABLE:• CASCADE CONSTRAINTS: Wymagane jeśli z tabelą związane są
klucze obce• PURGE: bez tej opcji tabela jest przenoszona do KOSZA i może
być odzyskana. Z opcją PURGE usunięcie jest permanentne!
Usuwanie danych z tabeli• Polecenie DELETE
• Pozwala selektywnie usuwać wiersze• Zmienia segmenty wycofania• Automatycznie aktualizuje indeksy• Uruchamia wyzwalacze
• Polecenie TRUNCATE usuwa wszystkie wiersze z tabeli.• Jest poleceniem DDL więc wykonywany jest
automatyczny COMMIT• Przesuwa „Wskaźnik wysokiej wody” na początek
segmentu tabeli.• Nie zmienia segmentów wycofania• Nie uruchamia wyzwalaczy
Tabele tymczasowe
• Są obiektami przechowującymi dane na czas trwania sesji lub transakcji• ON COMMIT DELETE ROWS• ON COMMIT PRESERVE ROWS
• Dostarczają prywatnej przestrzeni na dane w trakcie sesji
• Są dostępne jednocześnie dla wszystkich sesji, nie ingerując w prywatną przestrzeń danych
Cechy tabel tymczasowych
• Tabele tymczasowe tworzymy klauzulą:• CREATE GLOBAL TEMPORARY TABLE
• Operacje DML nie zmieniają segmentów wycofania (undo data)• Są tworzone tylko w tymczasowej przestrzeni tabel• Na tabelach tymczasowych można tworzyć:
• Indeksy• Widoki• Wyzwalacze (Triggers)
CREATE GLOBAL TEMPORARY TABLE employees_temp
ON COMMIT PRESERVE ROWS
AS SELECT * FROM employees;
Więzy integralności
JOB_HISTORYEMPLOYEE_ID (PK,FK)START_DATE (PK)END_DATEJOB_ID (FK)DEPARTMENT_ID (FK)
EMPLOYEESEMPLOYEE_ID (PK)FIRST_NAMELAST_NAMEESALARYCOMMISION_PCTMANAGER_ID (FK)MAILPHONE_NUMBERHIRE_DATEJOB_ID (FK)DEPARTMENT_ID (FK)
DEPARTMENTSDEPARTMENT_ID (PK)DEPARTMENT_NAMEMANAGER_IDLOCATION_ID (FK)
JOBSJOB_ID (PK)JOB_TITLEMIN_SALARYMAX_SALARY
REGIONSREGION_ID (PK)REGION_NAME
COUNTRIESCOUNTRY_ID (PK)COUNTRY_NAMEREGION_ID (FK)
LOCATIONSLOCATION_ID (PK)STREET_ADDRESSPOSTAL_CODECITYSTATE_PROVINCECOUNTRY_ID (FK)
Rodzaje więzów integralności
• PRIMARY KEY - Klucz główny, unikalny i niepusty• UNIQUE – Unikalność wartości• FOREIGN KEY - Klucz obcy: związek między kolumnami
z dwóch różnych lub tej samej tabeli•NOT NULL – zawsze jakaś wartość•CHECK – pozytywnie zweryfikowany warunek na kolumnie
•Kiedy więzy integralności są sprawdzane:• Po wykonaniu operacji DML – IMMEDIATE• W momencie zatwierdzania transakcji - DEFERRED
Naruszenie więzów integralności
• Przykłady• Wprowadzenie istniejącej wartości klucza głównego• Usunięcie wiersza w tabeli nadrzędnej z wartością
wykorzystywaną w tabeli podrzędnej• Aktualizacja wartości poza zakresem
101 …
102 …
103 …101
X … 22
… 49
… 16
… 5
ID AGE
–30
Stany pracy więzów integralności
ENABLENOVALIDATE
ENABLEVALIDATE
Istniejące dane
Nowe dane
DISABLENOVALIDATE
DISABLEVALIDATE
bez DML
Weryfikacja więzów integralności
• Więzy są sprawdzane w momencie:• Wykonania polecenia DML (IMMEDIATE)• W momencie zatwierdzania transakcji( DEFERRED)
Sprawdzenie więzów IMMEDIATE
Próba zatwierdzenia transakcji - COMMIT
Sprawdzenie więzów DEFERRED
Zakończenie zatwierdzania transakcji – COMMIT COMPLETE
2
4
3
5
1 Wykonanie polecenia DML (I, U, D)
Przykłady tworzenia więzów integralności
ALTER TABLE countries
ADD (UNIQUE(country_name) ENABLE NOVALIDATE);
ALTER TABLE shopowner.jobs ADD CONSTRAINT job_pk
PRIMARY KEY (job_id);
CREATE TABLE emp (emp_no NUMBER PRIMARY
KEY,Last_name VARCHAR2(30), first_name
VARCHAR2(30), dept_no NUMBER, Mgr_no NUMBER,
hire_date date,salary NUMBER,
CONSTRAINT Mgr_FK FOREIGN KEY (mgr_no) REFERENCES
emp(emp_no), CONSTRAINT ck1 CHECK (salary > 0));
Indeksy
22
22
Indeks Tabela
KluczWskaźnik wiersza
… WHERE klucz = 22
Typy indeksów
• W bazie Oracle występują dwa najczęściej stosowane typy indeksów• Indeks typu B-tree
• Domyślny typ indeksu w postaci zbalansowanego drzewa• Indeks typu Bitmap:
• Posiada mapę bitową dla każdej unikalnej wartości • Każdy bit w mapie wskazuje na wiersz w tabeli• Wartość bitu wskazuje czy kolumna w wierszu ma wskazaną
wartość czy nie
Indeks B-Tree
Nagłówek
Długość klucza w kolumnie
Wartość klucza w kolumnie
ROWID
Korzeń
Węzeł
Liść
Wpis w liściu
Indeks bitmapowy
<Blue, 10.0.3, 12.8.3, 1000100100010010100>
<Green, 10.0.3, 12.8.3, 0001010000100100000>
<Red, 10.0.3, 12.8.3, 0100000011000001001>
<Yellow, 10.0.3, 12.8.3, 0010001000001000010>
KluczStartROWID
KoniecROWID Bitmap
Tabela
Indeks
Blok 10
Blok 11
Blok 12
Plik 3
Opcje Indeksów
• Indeks unikalny• Zapewnia unikalność wartości klucza w całej tabeli
• Indeks z odwróconym kluczem• Klucz jest przechowywany w odwrotnej kolejności binarnej
• Indeks złożony• Na więcej niż jednej kolumnie
• Indeks bazujący na funkcji• Kluczem jest wartość zwracana przez funkcję
• Indeks skompresowany• Z usuniętymi duplikatami kluczy i wartości z liści
Tworzenie indeksów
• Automatyczne• Przy zakładaniu więzów integralości
• klucza głównego PRIMARY KEY• Unikalności - UNIQUE
• Ręczne – poleceniami SQL• CREATE INDEX T1_IDX1 ON T1 (K1, K2);
• T1_IDX1 – nazwa indeksu• T1 – tabela na której zakładamy indeks• K1, K2 – dwie kolumny indeksu
Widoki
• Widok to zapisane zapytanie SQL• Zachowuje się prawie jak tabela
• W zapytaniach używamy widoku tak samo jak tabel• Przy spełnieniu określonych warunków można na widoku
używać poleceń DML (I, U, D)
• W definicji widoku można używać funkcji lub zmiennych np. USER, SYSDATE• Dane zwracane przez widok będą różne w zależności od
okoliczności np. kto wykonuje zapytanie czy czasu wykonania
• Widoki służą do:• Ukrywania złożoności zapytań SQL• Selektywnego udostępniania danych innym użytkownikom
Tworzenie widoków
CREATE VIEW V1 AS SELECT K1, K2 FROM T1 WHERE ...
CREATE VIEW MY_EMP_VIEW ASSELECT * FROM EMPLOYEES WHERE EMPLOYEE=USER
SELECT * FROM V1;SELECT * FROM MY_EMP_VIEW, T1 WHERE ...
Uprawnienia do Widoków
• Do tworzenia widoków wymagane jest uprawnienie CREATE VIEW
• Innym użytkownikom pozwalamy czytać z widoku, a nie z tabel
• Do czytania danych z widoków wymagane jest uprawnienie obiektowe SELECT lub SELECT ANY TABLE
Sekwencje
• Sekwencja generuje unikalne liczby całkowite• Posiada nazwę• Nie jest związana z żadną tabelą czy kolumną• Wartości mogą rosnąć lub maleć• Interwał między kolejnymi liczbami jest konfigurowalny• Sekwencja może być cykliczna• Maksymalna wartość sekwencji to 1027 a minimalna to -1026
• Wartości z sekwencji pobiera się funckją NEXTVAL• SELECT MY_SEQ.NEXTVAL FROM DUAL;• INSERT INTO T1 VALUES (MY_SEQ.NEXTVAL, ‘test’)
12
34
5
Tworzenie sekwencji
CREATE SEQUENCE MY_SEQ
START WITH 1000
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
NOCACHE
NOCYCLE;
Mechanizmy zapewniające spójność danych
• Lock – mechanizm blokujący aktualizacje tych samych danych w tym samym czasie przez wiele sesji użytkowników
• Blokowanie następuje na najniższym z dostępnych poziomów (wiersz, wiersze, blok, tabela itp)
• Blokowanie najczęściej jest wykonywane automatycznie, ale można też ręcznie
Transakcja 1
SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=100;
SQL> UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100;
Transakcja 2
Cechy mechanizmu blokowania
• Blokowanie na poziomach:• Wierszy dla operacji DML (IUD)• Brak blokowania dla zapytań
• Blokowanie jest utrzymywane aż do zakończenia transakcji COMMIT lub ROLLBACK
Transaction 1
SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=101;
SQL> UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100;
Transaction 2
Współbieżność operacji
Czas:
09:00:00
Transakcja 1 UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100;
Transakcja 2 UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101;
Transakcja 3 UPDATE hr.employees SET salary=salary+100 WHERE employee_id=102;
... ...
Transakcja x UPDATE hr.employees SET salary=salary+100 WHERE employee_id=xxx;
Typy blokad
• Każda operacja DML zakłada 2 blokady:• EXCLUSIVE - na wierszach które są zmieniane• ROW EXCLUSIVE – na tabeli w której są zmieniane wiersze
SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 106;1 row updated.
SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 107;1 row updated.
Transakcja 2Transakcja 1
Mechanizm kolejkowania
• Mechanizm kolejkowania zarządza:• Listą sesji które oczekują na założenie blokady• Typami blokad nałożonymi na obiekty bazy danych• Kolejnością zgłoszeń o założenie blokady
Konflikty
UPDATE employees SET salary=salary+100 WHERE employee_id=100;1 row updated.
9:00:00 UPDATE employees SET salary=salary+100 WHERE employee_id=101;1 row updated.
UPDATE employees SETCOMMISION_PCT=2 WHERE employee_id=101;Sesja oczekuje na zatwierdzenie transkcji 2.
9:00:05 SELECT sum(salary) FROM employees;SUM(SALARY)----------- 692634
Sesja nadal oczekuje na transakcję 2 16:30:00
Wykonywane jest wiele operacji DML ale brak COMMIT lub ROLLBACK
1 row updated.Sesja wznawia prace
16:30:01 commit;
Transakcja 1 Transakcja 2Czas
Przyczyny konfliktów
• Niezatwierdzone zmiany• Długo działające transakcje • Niepotrzebnie wysoki poziom blokowania
Rozwiązywanie konfliktów
• Najpierw należy zidentyfikować sesję która blokuje:
SQL> ALTER SYSTEM KILL SESSION '144,8982' IMMEDIATE;
SQL> SELECT SID, SERIAL#, USERNAMEFROM V$SESSION WHERE SID IN(SELECT BLOCKING_SESSION FROM V$SESSION)
Result:
1
2
• Następnie „zabić” blokującą sesje
Deadlocks – wzajemne blokowanie
Transaction 2Transaction 1
UPDATE employeesSET salary = salary x 1.1WHERE employee_id = 1000;
UPDATE employeesSET salary = salary x 1.1WHERE employee_id = 2000;
ORA-00060:Deadlock detected while waiting for resource
UPDATE employeesSET manager = 1342WHERE employee_id = 2000;
UPDATE employeesSET manager = 1342WHERE employee_id = 1000;
9:00
9:15
9:16
Dziękuję za uwagę i
zapraszam na ćwiczenia
Top Related