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

2 Obiekty w bazie danych OracleObiekty 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 Oracle Database 11g: Administration Workshop I

3 Co to jest Schemat w bazie?Schemat JAN Uż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 Oracle Database 11g: Administration Workshop I

4 Dostęp do informacji o obiektach w EMOracle Database 11g: Administration Workshop I

5 Ale przecież MY lubimy SQLPLUS-a

6 Konwencje nazewnicze obiektówNazwy 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! Oracle Database 11g: Administration Workshop I

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 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 do x z mak. 38 cyframi przed przecinkiem ujemne: -1 x do x z mak. 38 cyframi przed przecinkiem Oracle Database 11g: Administration Workshop I

8 Typy binarne CLOB: zawartość binarna tekstowaBLOB: 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 Oracle Database 11g: Administration Workshop I

9 Struktura tabel w bazie OracleKolumny Bloki Tabela A Tabela B Wiersze Segment Segment Tabela Przestrzeń tabel Tablespace Extent Wiersz Oracle Database 11g: Administration Workshop I

10 Tworzenie tabeli i modyfikacjeCREATE 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); Oracle Database 11g: Administration Workshop I

11 Usuwanie tabeli Tabele usuwa się poleceniem: DROP TABLE T1Razem 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! Oracle Database 11g: Administration Workshop I

12 Usuwanie danych z tabeliPolecenie 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 Oracle Database 11g: Administration Workshop I

13 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 Oracle Database 11g: Administration Workshop I

14 Cechy tabel tymczasowychTabele 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; Oracle Database 11g: Administration Workshop I

15 Więzy integralności DEPARTMENTS LOCATIONS JOB_HISTORY EMPLOYEESEMPLOYEE_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 DEPARTMENTS DEPARTMENT_ID (PK) DEPARTMENT_NAME MANAGER_ID LOCATION_ID (FK) JOBS JOB_ID (PK) JOB_TITLE MIN_SALARY MAX_SALARY REGIONS REGION_ID (PK) REGION_NAME COUNTRIES COUNTRY_ID (PK) COUNTRY_NAME REGION_ID (FK) LOCATIONS LOCATION_ID (PK) STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID (FK) Oracle Database 11g: Administration Workshop I

16 Rodzaje więzów integralnościPRIMARY 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 Oracle Database 11g: Administration Workshop I

17 Naruszenie więzów integralnościPrzykł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 101 … 102 103 … 22 49 16 5 –30 101 Oracle Database 11g: Administration Workshop I

18 Stany pracy więzów integralnościDISABLE NOVALIDATE DISABLE VALIDATE ENABLE NOVALIDATE ENABLE VALIDATE bez DML Nowe dane Istniejące dane Oracle Database 11g: Administration Workshop I

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

20 Przykłady tworzenia więzów integralnościALTER 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)); Oracle Database 11g: Administration Workshop I

21 Indeksy … WHERE klucz = 22 22 22 Indeks Tabela Wskaźnik wiersza KluczOracle Database 11g: Administration Workshop I

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 Oracle Database 11g: Administration Workshop I

23 Indeks B-Tree Wpis w liściu Korzeń Węzeł Nagłówek LiśćDługość klucza w kolumnie Wartość klucza w kolumnie ROWID Oracle Database 11g: Administration Workshop I

24 Indeks bitmapowy Plik 3 Tabela Blok 10 Blok 11 Indeks Blok 12 Klucz Klucz Start ROWID Koniec Bitmap Oracle Database 11g: Administration Workshop I

25 Opcje Indeksów Indeks unikalny Indeks z odwróconym kluczemZapewnia 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 Oracle Database 11g: Administration Workshop I

26 Tworzenie indeksów Automatyczne Ręczne – poleceniami SQLPrzy 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 Oracle Database 11g: Administration Workshop I

27 Widoki Widok to zapisane zapytanie SQL Zachowuje się prawie jak tabelaW 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ówDo 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ę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’) Oracle Database 11g: Administration Workshop I

31 Tworzenie sekwencji CREATE SEQUENCE MY_SEQ START WITH 1000 MINVALUE 1NOMAXVALUE INCREMENT BY 1 NOCACHE NOCYCLE; Oracle Database 11g: Administration Workshop I

32 Mechanizmy zapewniające spójność danychLock – 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; 2 SET salary=salary+100 3 WHERE employee_id=100; Transakcja 2 Oracle Database 11g: Administration Workshop I

33 Cechy mechanizmu blokowaniaBlokowanie na poziomach: Wierszy dla operacji DML (IUD) Brak blokowania dla zapytań Blokowanie jest utrzymywane aż do zakończenia transakcji COMMIT lub ROLLBACK Transaction 1 Transaction 2 SQL> UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100; SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=101; Oracle Database 11g: Administration Workshop I

34 Współbieżność operacjiCzas: 09:00:00 Transakcja 1 UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100; Transakcja 2 WHERE employee_id=101; Transakcja 3 WHERE employee_id=102; ... Transakcja x WHERE employee_id=xxx; Oracle Database 11g: Administration Workshop I

35 Typy blokad Każda operacja DML zakłada 2 blokady: Transakcja 1SQL> UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 107; 1 row updated. 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 Oracle Database 11g: Administration Workshop I

36 Mechanizm kolejkowaniaMechanizm 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 Oracle Database 11g: Administration Workshop I

37 Konflikty Transakcja 1 Czas Transakcja 2UPDATE 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; UPDATE employees SET COMMISION_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 Sesja wznawia prace 16:30:01 commit; Oracle Database 11g: Administration Workshop I

38 Przyczyny konfliktów Niezatwierdzone zmianyDługo działające transakcje Niepotrzebnie wysoki poziom blokowania Oracle Database 11g: Administration Workshop I

39 Rozwiązywanie konfliktówNajpierw 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  Oracle Database 11g: Administration Workshop I

40 Deadlocks – wzajemne blokowanieTransaction 2 Transaction 1 UPDATE employees SET salary = salary x 1.1 WHERE employee_id = 1000; UPDATE employees SET salary = salary x 1.1 WHERE employee_id = 2000; ORA-00060: Deadlock detected while waiting for resource UPDATE employees SET manager = WHERE employee_id = 2000; UPDATE employees SET manager = WHERE employee_id = 1000; 9: : :16 Oracle Database 11g: Administration Workshop I

41 zapraszam na ćwiczeniaDziękuję za uwagę i zapraszam na ćwiczenia