oracle_administracja_4

Report
<Insert Picture Here>
Administracja serwerem bazy danych Oracle 11g
Zarzą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?
posiada
Schemat JAN
Użytkownik
JAN
• 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
Kolumny
Tabela A
Bloki
Tabela B
Wiersze
Segment
Segment
Tabela
Przestrzeń tabel
Tablespace
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
CREATE GLOBAL TEMPORARY TABLE employees_temp
ON COMMIT PRESERVE ROWS
AS SELECT * FROM employees;
• 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)
Więzy integralności
DEPARTMENTS
JOB_HISTORY
EMPLOYEE_ID
(PK,FK)
START_DATE (PK)
END_DATE
JOB_ID (FK)
DEPARTMENT_ID (FK)
EMPLOYEES
EMPLOYEE_ID (PK)
FIRST_NAME
LAST_NAME
ESALARY
COMMISION_PCT
MANAGER_ID (FK)
MAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID (FK)
DEPARTMENT_ID (FK)
DEPARTMENT_ID (PK)
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID (FK)
LOCATIONS
LOCATION_ID (PK)
STREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID (FK)
COUNTRIES
JOBS
JOB_ID (PK)
JOB_TITLE
MIN_SALARY
MAX_SALARY
COUNTRY_ID (PK)
COUNTRY_NAME
REGION_ID (FK)
REGIONS
REGION_ID (PK)
REGION_NAME
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
ID
101
X
AGE
…
22
…
…
49
…
…
16
…
5
101
…
102
103
–30
Stany pracy więzów integralności
DISABLE
NOVALIDATE
DISABLE
VALIDATE
ENABLE
NOVALIDATE
ENABLE
VALIDATE
bez DML
Nowe dane
Istniejące dane
Weryfikacja więzów integralności
• Więzy są sprawdzane w momencie:
• Wykonania polecenia DML (IMMEDIATE)
• W momencie zatwierdzania transakcji( DEFERRED)
1
Wykonanie polecenia DML (I, U, D)
2
Sprawdzenie więzów
IMMEDIATE
3
Próba zatwierdzenia
transakcji - COMMIT
4
Sprawdzenie więzów DEFERRED
5
Zakończenie zatwierdzania
transakcji – COMMIT COMPLETE
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
… WHERE klucz = 22
Wskaźnik
Klucz wiersza
22
22
Indeks
Tabela
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
Wpis w liściu
Korzeń
Węzeł
Nagłówek
Liść
Długość klucza w kolumnie
Wartość klucza w kolumnie
ROWID
Indeks bitmapowy
Tabela
Plik 3
Blok 10
Blok 11
Indeks
Blok 12
Start Koniec
Klucz ROWID ROWID
Bitmap
<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>
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 AS
SELECT * 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
1
2
3
4
5
• 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’)
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+100
3 WHERE employee_id=100;
Transakcja 2
SQL> UPDATE employees
2 SET salary=salary*1.1
3 WHERE employee_id=100;
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+100
3 WHERE employee_id=100;
Transaction 2
SQL> UPDATE employees
2 SET salary=salary*1.1
3 WHERE employee_id=101;
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
Transakcja 1
SQL> UPDATE employees
2 SET salary=salary*1.1
3 WHERE employee_id= 107;
1 row updated.
Transakcja 2
SQL> UPDATE employees
2 SET salary=salary*1.1
3 WHERE employee_id= 106;
1 row updated.
• 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
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
Transakcja 1
Czas
Transakcja 2
UPDATE employees SET
9:00:00
salary=salary+100 WHERE
employee_id=100;
1 row updated.
UPDATE employees SET
salary=salary+100 WHERE
employee_id=101;
1 row updated.
UPDATE employees SET
COMMISION_PCT=2 WHERE
employee_id=101;
SELECT sum(salary) FROM
employees;
SUM(SALARY)
----------692634
9:00:05
Sesja oczekuje na
zatwierdzenie transkcji 2.
Sesja nadal oczekuje na
transakcję 2
16:30:00
Wykonywane jest wiele
operacji DML ale brak COMMIT
lub ROLLBACK
1 row updated.
16:30:01
commit;
Sesja wznawia prace
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:
1
SQL> SELECT SID, SERIAL#, USERNAME
FROM V$SESSION WHERE SID IN
(SELECT BLOCKING_SESSION FROM V$SESSION)
Result:
2
SQL> ALTER SYSTEM KILL SESSION '144,8982' IMMEDIATE;
• Następnie „zabić” blokującą sesje 
Deadlocks – wzajemne blokowanie
Transaction 1
UPDATE employees
SET salary = salary x 1.1
WHERE employee_id = 1000;
Transaction 2
9:00
UPDATE employees
SET manager = 1342
WHERE employee_id = 2000;
UPDATE employees
SET salary = salary x 1.1
WHERE employee_id = 2000;
9:15
UPDATE employees
SET manager = 1342
WHERE employee_id = 1000;
ORA-00060:
Deadlock detected while
waiting for resource
9:16
Dziękuję za uwagę i
zapraszam na ćwiczenia

similar documents