Administracja serwerem bazy danych Oracle 11g Zarządzanie obiekami bazy danych Wykład nr 4

1. Administracja serwerem bazy danych Oracle 11gZarządzanie obiekami bazy danych Wykład nr 4 Michał Szkopiński

2. 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

3. Co to jest Schemat w bazie? 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 Schemat JAN Użytkownik JAN

4. Dostęp do informacji o obiektach w EM

5. Ale przecież MY lubimy SQLPLUS-a

6. Konwencje nazewnicze obiektów • Nazwy obiektów o rozmiarze od 1 do30 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!

7. 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 stycznia4712 (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-130do 9.99...9 x 10125z mak. 38 cyframi przed przecinkiem • ujemne: -1 x 10-130 do -9.99...99 x 10125z mak. 38 cyframi przed przecinkiem

8. 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

9. Struktura tabel w bazie Oracle Kolumny Bloki TabelaA TabelaB Wiersze Segment Segment Tabela Przestrzeń tabel Tablespace Extent Wiersz

10. 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 ADDbonus NUMBER(6); ALTER TABLE SHOPOWNER.JOBS ADD CONSTRAINT PRIMARY KEY (JOB_ID);

11. 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 poleceniemDROPTABLE: • CASCADECONSTRAINTS: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!

12. Usuwanie danych z tabeli • Polecenie DELETE • Pozwala selektywnie usuwać wiersze • Zmienia segmenty wycofania • Automatycznie aktualizuje indeksy • Uruchamia wyzwalacze • Polecenie TRUNCATEusuwa 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

13. Tabele tymczasowe • Są obiektami przechowującymi dane na czas trwania sesji lub transakcji • ONCOMMITDELETEROWS • ONCOMMITPRESERVEROWS • Dostarczają prywatnej przestrzeni na dane w trakcie sesji • Są dostępne jednocześnie dla wszystkich sesji, nie ingerując w prywatną przestrzeń danych

14. Cechy tabel tymczasowych • Tabele tymczasowe tworzymy klauzulą: • CREATE GLOBALTEMPORARY 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;

15. LOCATIONS LOCATION_ID (PK) STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID (FK) JOB_HISTORY EMPLOYEE_ID (PK,FK) START_DATE (PK) END_DATE JOB_ID (FK) DEPARTMENT_ID (FK) JOBS JOB_ID (PK) JOB_TITLE MIN_SALARY MAX_SALARY DEPARTMENTS DEPARTMENT_ID (PK) DEPARTMENT_NAME MANAGER_ID LOCATION_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) COUNTRIES COUNTRY_ID (PK) COUNTRY_NAME REGION_ID (FK) REGIONS REGION_ID (PK) REGION_NAME Więzy integralności

16. 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

17. 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 AGE X –30 101

18. Stany pracy więzów integralności DISABLENOVALIDATE ENABLENOVALIDATE ENABLEVALIDATE DISABLEVALIDATE bezDML Nowe dane Istniejące dane

19. Weryfikacja więzów integralności • Więzy są sprawdzane w momencie: • Wykonania polecenia DML (IMMEDIATE) • W momencie zatwierdzania transakcji( DEFERRED) Wykonanie polecenia DML (I, U, D) 1 Sprawdzenie więzów IMMEDIATE 2 Próba zatwierdzenia transakcji - COMMIT 3 Sprawdzenie więzów DEFERRED 4 Zakończenie zatwierdzania transakcji – COMMIT COMPLETE 5

20. Przykłady tworzenia więzów integralności ALTER TABLE countriesADD (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_datedate,salary NUMBER, CONSTRAINT Mgr_FK FOREIGN KEY (mgr_no) REFERENCES emp(emp_no),CONSTRAINT ck1 CHECK (salary > 0));

21. Indeksy … WHERE klucz= 22 Wskaźnik wiersza Klucz Indeks Tabela

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

23. Indeks B-Tree Wpis w liściu Korzeń Węzeł Nagłówek Długość klucza w kolumnie Liść Wartość klucza w kolumnie ROWID

24. Start ROWID Koniec ROWID Klucz 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> Indeks bitmapowy Plik3 Tabela Blok 10 Blok 11 Indeks Blok 12

25. 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

26. 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 INDEXT1_IDX1 ON T1 (K1, K2); • T1_IDX1 – nazwa indeksu • T1 – tabela na której zakładamy indeks • K1, K2 – dwie kolumny indeksu

27. 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

28. 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 ...

29. 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

30. 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’) 1 2 3 4 5

31. Tworzenie sekwencji CREATE SEQUENCE MY_SEQ START WITH 1000 MINVALUE 1 NOMAXVALUE INCREMENT BY 1 NOCACHE NOCYCLE;

32. 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 Transakcja1 Transakcja2 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;

33. Cechy mechanizmu blokowania • Blokowanie na poziomach: • Wierszy dla operacji DML (IUD) • Brak blokowania dla zapytań • Blokowanie jest utrzymywane aż do zakończenia transakcjiCOMMITlubROLLBACK Transaction 1 Transaction 2 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;

34. Współbieżność operacji

35. Typy blokad • Każda operacja DML zakłada 2 blokady: • EXCLUSIVE - na wierszach które są zmieniane • ROWEXCLUSIVE– na tabeli w której są zmieniane wiersze Transakcja1 Transakcja2 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.

36. 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

37. Konflikty Transakcja1 Czas Transakcja2

38. Przyczyny konfliktów • Niezatwierdzone zmiany • Długo działające transakcje • Niepotrzebnie wysoki poziom blokowania

39. Rozwiązywanie konfliktów • Najpierw należy zidentyfikować sesję która blokuje: SQL> select sid, serial#, username from v$session where sid in (select blocking_session from v$session) 1 Result: 2 SQL> alter system kill session '144,8982' immediate; • Następnie „zabić” blokującą sesje 

40. Deadlocks – wzajemne blokowanie Transaction 1 Transaction 2 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 9:009:159:16 UPDATE employeesSET manager = 1342WHERE employee_id = 2000; UPDATE employeesSET manager = 1342WHERE employee_id = 1000;

41. Dziękuję za uwagę i zapraszam na ćwiczenia