777

Materiały szkoleniowe

Administracja serwerem Oracle

0x08 graphic
Prowadzący

Anna Pijanowska - Kuśnierz

Spis treści

Konwencje typograficzne

Konwencje typograficzne

Aby ułatwić wyszukiwanie w materiałach szkoleniowych ważniejszych informacji oraz poszczególnych elementów, zastosowano ikony o następującym znaczeniu:

0x01 graphic
— ikona „Wskazówka”; sygnalizuje fragment opisujący ułatwienia wykonania czynności lub „skrót” pozwalający wykonać ją szybciej i sprawniej;

0x01 graphic
— ikona „Uwaga”; sygnalizuje informacje ważne dla prawidłowego wykonania czynności;

0x01 graphic
— ikona „Ćwiczenia”; rozpoczyna zestaw ćwiczeń;

Wprowadzenie

Cele Kursu

Po zakończeniu tego kursu powinieneś potrafić:

Opisać architekturę serwera Oracle.
Uruchomić i zamknąć bazę danych.
Utworzyć kompletną bazę danych.
Opisać operacje dostępu i aktualizacji danych.
Opisać zasady współbieżności transakcji i spójności odczytu.
Dopasować bazę danych przez zmienianie parametrów inicjalizacji.
Zarządzać strukturą bazy danych przez przestrzenie tabel.
Zarządzać przydziałem przestrzeni i wzrostem segmentów bazy danych.
Zarządzać przestrzenią bazy danych przez tabele, indeksy, klastry indeksowe i klastry haszujące.
Zarządzać segmentami wycofania i segmentami tymczasowymi.
Zapewniać integralność danych.
Tworzyć, monitorować oraz usuwać użytkowników.
Kontrolować zasoby systemowe użytkowników bazy danych.
Przydzielać, monitorować i kontrolować uprawnienia w bazie danych.
Wykonywać zadania związane z obsługą języków narodowych (NLS)
Opisać, włączyć i wyłączyć śledzenie.
Opisać zaawansowaną architekturę serwera.
Opisać procedury bazy danych i opcje dodatkowe.
Zarządzać bazą danych używając narzędzia Serwer Manager.

Obowiązki Administratora Bazy Danych

System baz danych Oracle może być całkiem duży i obsługiwać wielu użytkowników.

Często więc niezbędne jest aby jedna osoba lub grupa ludzi była odpowiedzialna za zarządzanie systemem bazy danych. Tą osobą jest administrator bazy danych (DBA).

Obowiązki DBA

Pierwsza instalacja i instalowanie nowych wersji serwera Oracle i narzędzi aplikacyjnych.
Utworzenie podstawowych struktur bazy danych i podstawowych obiektów.
Przydział przestrzeni systemowej i planowanie przyszłych wymagań przestrzeni systemu bazy danych.
Modyfikowanie struktury bazy danych.
Zakładanie użytkowników.
Kontrolowanie i monitorowanie dostępu użytkowników do bazy danych.
Archiwizacja i odtwarzanie bazy danych.
Zakładanie użytkowników.
Kontrolowanie i monitorowanie dostępu użytkowników do bazy danych.
Archiwizacja i odtwarzanie bazy danych.
Zapewnienie bezpieczeństwa systemu.
Monitorowanie i optymalizacja efektywności bazy danych.

W niektórych systemach baz danych może być wielu różnych DBA o różnych zadaniach.

Na przykład inni DBA mogą być odpowiedzialni za tworzenie użytkowników, monitorowanie użytkowników, monitorowanie zasobów systemu i zapewnienie bezpieczeństwa bazy danych.

W przeciwieństwie do obowiązków DBA, projektant aplikacji w bazie danych może być odpowiedzialny za projektowanie i implementację aplikacji bazodanowych, projektowanie obiektów bazy danych oraz strojenie aplikacji.

I. Przegląd architektury Oracle

Cele

W tej lekcji przedstawiamy architekturę serwera Oracle, w szczególności struktury fizyczne, struktury pamięci i procesy tła.

Po zakończeniu tej lekcji powinieneś potrafić:

Rozpoznawać składniki architektury Oracle.
Określić przeznaczenie każdego z typów składników.

Przegląd

Aby efektywnie zarządzać bazą danych, administrator bazy danych (DBA od. ang. DataBase Administrator) powinien dokładnie znać i rozumieć architekturę serwera ORACLE.

DBA musi rozumieć następujące elementy:

Procesy.
Struktury pamięci.
Pliki.

Globalny Obszar Systemowy (SGA)

Przy każdym starcie serwera w części pamięci operacyjnej rezerwowana jest na Globalny Obszar Systemowy (SGA od ang. System Global Area). Jest to grupa dzielonych struktur pamięci zawierająca informacje kontrolne pojedynczego systemu bazy danych Oracle.

SGA powinien być alokowany w pamięci rezydentnej (nie stronicowej, nie wirtualnej).

Jeśli podłączonych do bazy jest wielu użytkowników, dane w SGA są „dzielone” przez wszystkich użytkowników.

SGA można także tłumaczyć jako „Shared Global Area” - dzielony obszar globalny.

SGA nie powinien zajmować więcej niż 50% dostępnej pamięci operacyjnej.

0x01 graphic

Obszar Dzielony

Obszar dzielony jest fragmentem SGA zawierającym między innymi takie konstrukcje jak dzielone obszary SQL i bufor słownika danych.

Obszary Dzielone SQL

Każdy z obszarów dzielonych AQL (shared SQL area) zawiera informacje używane przy wykonaniu pojedynczego polecenia SQL. Procesy wykonujące identyczne polecenia SQL używają tej samej informacji.

Polecenie SQL umiejscawiane jest w dzielonym obszarze SQL za pomocą algorytmu haszowania zastosowanego do tego polecenia. Stąd też tylko absolutnie identyczne polecenia mogą być skierowane w ten sam obszar pamięci.

Obszary dzielone SQL bywają też nazywane buforem bibliotecznym (library cache).

Bufor Słownika Danych

Słownik danych (data dictionary) jest zbiorem tabel i perspektyw bazy danych zawierających informacje o bazie danych, jej strukturach i użytkownikach. Wśród danych przechowywanych w słowniku danych znajdują się:

Nazwy wszystkich tabel i perspektyw bazy danych,
Nazwy i typy danych kolumn tabel bazy danych,
Uprawnienia wszystkich użytkowników Oracle.

Bufor słownika bazy danych nazywany jest czasem buforem wierszowym (row cache).

Zawartość Obszaru Dzielonego

Tekst polecenia SQL lub PL/SQL,
Analiza składniowa polecenia SQL lub PL/SQL,
Plan wykonania poleceń SQL i PL/SQL,
Bufor słownika danych zawierający wiersze słownika danych.

0x01 graphic

Bufor Bazy Danych

Bufor bazy danych zawiera kopie bloków danych odczytanych z dysku. Wszyscy użytkownicy podłączeni aktualnie do systemu korzystają ze wspólnego bufora bazy danych.

Dostęp do Danych

cache miss (brak w buforze)

Przy pierwszym dostępie procesu użytkownika do wskazanego fragmentu danych, proces ten, zanim uzyska do niego dostęp, musi przepisać dane z dysku do bufora.

cache hit (trafienie w bufor)

Jeśli proces żąda dostępu do danych, które już znajdują się w buforze, może odczytywać dane bezpośrednio z pamięci operacyjnej.

Dostęp do danych przy trafieniach w bufor jest szybszy niż przy sytuacji braków w buforze. Ponieważ rozmiar bufora jest ograniczony, nie wszystkie dane z dysku mieszczą się w buforze. Kiedy bufor jest pełen, kolejne braki w buforze powodują, że Oracle zapisują dane z bufora na dysk w celu zwolnienia miejsca dla nowych danych. Wtedy kolejne odwołania do przeniesionych na dysk danych powodują sytuację braku w buforze.

Bufor Dziennika Powtórzeń

Bufor dziennika powtórzeń (redo log buffer) to bufor cykliczny zawierający informacje o zmianach dokonanych w bazie danych. Dziennik powtórzeń:

Rejestruje wszystkie zmiany dokonane w bazie danych.
Odtwarza zmiany dokonane w bazie danych i segmentach wycofania podczas odtwarzania bazy .
Może być pominięty przez użycie słowa kluczowego UNRECOVERABLE (nieodtwarzalne) w poleceniach CREATE TABLE i CREATE INDEX.
Może być pominięty przez ładowanie danych z pomocą Oracle Loader.

Procesy Tła

W celu maksymalizacji efektywności wykonania i umożliwienia pracy wielu użytkownikom, system Oracle używa specjalnych procesów Oracle zwanych procesami drugoplanowymi lub procesami tła.

System Oracle, w zależności od konfiguracji, może składać się z wielu procesów tła. Oto ich nazwy:

Database Writer (DBWR) - sekretarz bazy danych,
Log Writer (LGWR) - sekretarz dziennika powtórzeń,
Checkpoint (CKPT) - menadżer punktów kontrolnych,
System Monitor (SMON) - monitor systemu,
Proces Monitor (PMON) - monitor procesów,
Archiver (ARCH) - archiwizator,
Recoverer (RECO) - odtwarzacz,
Lock (LCKn) - kontroler blokad,
Snapshot Refresh (SNPn) - odświeżacz migawek,
Shared Serwer (Snnn) - serwer dzielony,
Dispatcher (Dnnn) - proces rozdzielający (dyspozytor),
Parallel Query (Pnnn) - kontroler zapytań równoległych.

Procesy PMON i SMON

Procesy PMON i SMON zwalniają zasoby bazy danych nie używane już przez użytkownika.

PMON

Porządkuje systemy po awaryjnym przerwaniu podłączenia,
Wycofuje niezatwierdzone transakcje,
Zwalnia blokady nałożone przez przerwany proces,
Zwalnia zasoby SGA zajęte przez przerwany proces,
Uruchamia ponownie przerwane procesy serwera dzielonego i dyspozytora.

SMON

Wykonuje automatyczne odtwarzanie instalacji,
Zwalnia obszar zajęty przez nieużywane już segmenty tymczasowe,
Łączy obszary wolnej przestrzeni plików danych w spójny obszar.

Cztery Obowiązkowe Procesy

Procesy PMON, SMON, DBWR i LGWR są niezbędnymi dla działania instalacji procesami. Pozostałe procesy są opcjonalne.
Procesy PMON, SMON, DBWR i LGWR nie mogą być konfigurowane przez parametry inicjalizacji.

0x01 graphic

Jeśli którykolwiek z tych czterech procesów zostanie przerwany, następuje awaria instancji i trzeba uruchomić ją ponownie.

Procesy użytkownika

Proces użytkownika jest tworzony kiedy użytkownik uruchamia program aplikacji.

Proces użytkownika

Uruchamia narzędzie / aplikację, jest postrzegany jako klient. Przykładem może być Serwer Manager, Oracle Forms, lub program w Pro*C.
Przekazuje polecenia SQL do procesu serwera i odbiera wyniki.

Procesy Serwerów

Przed uzyskaniem dostępu do danych, dane muszą być umieszczone w buforze danych.

Dokonuje tego proces serwera. W celu wykonania polecenia SQL proces serwera używa pamięci dzielonej w SGA.

Zadania Procesu Serwera

Analiza i wykonanie poleceń SQL,
Odczyt bloków danych z dysku do dzielonych buforów danych w SGA,
Zwrócenie wyników poleceń SQL do procesu użytkownika.

Proces serwera analizuje, wykonuje i sprowadza dane dla każdej aplikacji użytkownika.

Zadanie	Opis
Analiza (parse)	Sprawdzanie poprawności składni, uprawnień, identyfikacja obiektów i optymalizacja (budowa drzewa analizy). Podczas analizy używane są obszary dzielone SQL, stąd też procesy mogą używać wspólnych drzew analizy.
Wykonanie (execute)	Realizacja działań z drzewa analizy na danych, fizyczny odczyt i zmiana danych, według potrzeb.
Sprowadzanie (fetch)	Przekazuje dane wynikowe polecenia SELECT

0x01 graphic

Uwaga: Istnieją też inne możliwe konfiguracje dotyczące serwerów, jednak nie będą one omawiane podczas lekcji.

0x01 graphic

Instancja Oracle

Instancję Oracle stanowi kombinacja SGA i procesów tła bazy danych. Kiedy instancja jest uruchamiana, przydzielane są bufory pamięci SGA i startują procesy tła.

Należy odróżnić pojęcia „baza danych Oracle” i „instancja Oracle”. Mówimy, że „instancja jest uruchomiona” (pamięć jest przydzielona, procesy tła uruchomione), zaś baza danych (plik danych) „jest zamontowana” w instalacji.

Procesy serwera i procesy użytkownika nie zawierają się w definicji instancji Oracle.

0x01 graphic

Baza Danych Oracle

Baza danych Oracle składa się z jednego lub wielu plików kontrolnych (control file), plików danych (datafile) i plików dziennika powtórzeń (redo log files) wyspecyfikowanych w pliku kontrolnym.

Struktura fizyczna	Definicja
Plik danych (datafile)	Zawiera wszystkie dane bazy danych. Struktury logiczne, takie jak tabele, indeksy, przechowywane są fizycznie w plikach danych.
Pliki dziennika powtórzeń (redo log files)	Przechowują zapis wszystkich zmian dokonanych w bazie danych w celu ewentualnego odtwarzania.
Pliki kontrolne (control files)	Każdy z nich zawiera opis struktury fizycznej i statusu bazy danych.

Pliki Dziennika Powtórzeń

Pliki dziennika powtórzeń (redo log files) zawierają zapis wszystkich zmian dokonanych w bazie danych używany podczas odtwarzania danych. Jeśli pliki dziennika powtórzeń są zwielokrotnione, ta sama informacja dziennika powtórzeń zapisywana jest jednocześnie do plików dziennika powtórzeń.

Pliki Dziennika Powtórzeń

Pliki dziennika powtórzeń zapisywane są w sposób cykliczny.
Muszą istnieć co najmniej dwie grupy plików dziennika powtórzeń.
Pliki dziennika powtórzeń powinny być przechowywane na najszybszym i najmniej obciążonym operacjami wejścia/wyjścia urządzeniu. Urządzenia RAID nie są zalecane.

0x01 graphic

Zwielokrotnione Pliki Dziennika Powtórzeń

Zalecana konfiguracja plików dziennika powtórzeń to przynajmniej po dwa elementy (redo log member) w każdej grupie, a każdy element na innym dysku.
Zwielokrotnione pliki powtórzeń dziennika zabezpieczają przed utratą dziennika powtórzeń.
Wszystkie elementy grupy plików dziennika powtórzeń zawierają te same informacje i mają identyczny rozmiar.
Elementy grupy są aktualizowane jednocześnie.
Każda grupa powinna zawierać taką samą liczbę elementów.Przykład: Zwielokrotnione Pliki Dziennika Powtórzeń

0x01 graphic

Dysk A i B reprezentują różne dyski na tej samej maszynie. Każdy element grupy musi być tego samego rozmiaru. Każdy element grupy powinien być przechowywany na innym dysku aby lepiej zabezpieczyć się przed awarią. Każdy element grupy zapisywany jest w tym samym czasie.

Domyślna instalacja tworzy dwie grupy dzienników powtórzeń z jednym elementem w każdej grupie i domyślnym rozmiarem 500 KB. Często warto dodać więcej elementów na innych dyskach, a także zwiększyć rozmiar plików dziennika.

Pliki Kontrolne

Plik kontrolny (control file) to niewielki plik binarny opisujący strukturę bazy danych.

Własności Pliku Kontrolnego

Identyfikuje wszystkie niezbędne pliki danych i pliki dziennika,
W pliku kontrolnym zapamiętana jest nazwa bazy danych,
Plik kontrolny jest niezbędny do zamontowania, otwarcia i używania bazy danych,
Plik kontrolny przechowuje informację synchronizującą, potrzebną przy odtwarzaniu,
Zalecana konfiguracja to minimum dwa pliki kontrolne przechowywane na różnych dyskach,
Parametr CONTROL_FILES identyfikuje pliki kontrolne.

Plik kontrolny musi być dostępny do zapisu dla serwera Oracle podczas otwierania bazy danych.

Domyślna nazwa pliku kontrolnego jest zależna od systemu operacyjnego.

Plik Parametrów

Plik parametrów definiuje instancję. Aby uruchomić instancję, Oracle musi odczytać plik parametrów init<SID>ora. Plik parametrów jest plikiem tekstowym zawierającym listę parametrów konfiguracyjnych instancji. Ustawiając te parametry na określone wartości zarządzamy przydziałem pamięci i ustawieniem procesów instancji Oracle. Plik parametrów decyduje między innymi:

Ile pamięci ma być przeznaczonej na struktury pamięci w SGA,
Co robić z zapełnieniami w trakcie pracy plikami dziennika powtórzeń,
Jakie są nazwy i położenie plików kontrolnych bazy danych.

Rozmiar SGA

Parametr	Definicja
SHARED_POOL_SIZE	Rozmiar pamięci (w bajtach) przeznaczonej na obszar dzielony poleceń SQL i PL/SQL.
DB_BLOCK_SIZE	Rozmiar pojedynczego bloku (w bajtach), a tym samym bufora bloku w bazie danych.
DB_BLOCK_BUFFERS	Liczba utworzonych w SGA buforów bazy danych, z których każdy ma rozmiar DB_BLOCK_SIZE. (Całkowita wielkość buforów bazy danych w SGA to iloczyn DB_BLOCK_SIZE i DB_BLOCK_BUFFERS)
LOG_BUFFER	Liczba bajtów przeznaczonych na bufor dziennika powtórzeń

Pliki Śladu i Plik Ostrzeżeń

Po wystąpieniu błędu w czasie działania instancji Oracle do pliku ostrzeżeń (alert file) zapisywany jest komunikat . Jeżeli błąd został wykryty przez serwer lub proces tła, ta informacja zapisywana jest w odpowiednim pliku śladu (trace file).

Plik Ostrzeżeń

Plik ostrzeżeń (alert file) bazy danych to chronologiczny zapis komunikatów i błędów, wśród których znajdować się mogą:

Błędy wewnętrzne (ORA-600), błędy zniszczenia bloku (ORA-1578), błędy zakleszczenia (ORA-60).
Działania administracyjne (DDL) i polecenia sterowania serwerem (STARTUP, SHUTDOWN, ARCHIVE LOG i RECOVER).
Wszystkie inne niż domyślne wartości parametrów inicjalizacji z chwili startu instancji.

Oracle stosuje plik ostrzeżeń zamiast wyświetlania takich komunikatów na konsoli operatora.

Plik ostrzeżeń jest umieszczany w miejscu określonym przez parametr BACKGROUND_DUMP_DEST.

Ważne jest, aby regularnie (codziennie) sprawdzać plik ostrzeżeń, aby wykryć problem, zanim sytuacja stanie się poważna, dopóki dysponujemy poprawną kopią zapasową bazy (akcja będzie zależna od natury problemu)

Pliki Śladu

Po wykryciu przez serwer lub proces tła błędu wewnętrznego, informacja jest zapisywana na odpowiedni plik śladu. Pliki śladu umieszczone są tam, gdzie wskazuje parametr BACKGROUND_DUMP_DEST jeśli jest to komunikat procesu tła lub tam, gdzie wskazuje parametr USER_DUMP_DEST, jeśli komunikat zapisywany jest przez proces serwera.

Parametr SQL_TRACE

Zapis śladu może być włączony i wyłączony parametrem inicjalizacji SQL_TRACE. Jego wartość to TRUE lub FALSE.

Można też włączyć zapis do pliku śladu tylko dla bieżącej sesji wykonując polecenie:

SQL<ALTER SESSION SET sql_trace = TRUE

Podsumowanie

DBA powinien pamiętać, że:

Globalny Obszar systemowy (SGA) to grupa następujących dzielonych struktur w pamięci:
Obszar dzielony (shared pool),
Bufory bazy danych (database buffer cache)
Bufor dziennika powtórzeń (redo log buffer)
Procesy tła wykonują asynchroniczne różne zadania dla wszystkich użytkowników bazy danych.
Do przetwarzania poleceń SQL procesy serwerów używają pamięci dzielonej.
Procesy użytkownika uruchamiają narzędzie lub aplikację i przekazują przetwarzanie poleceń SQL procesom serwerów.
Instancja Oracle składa się z SGA oraz procesów tła
Baza danych Oracle składa się ze wszystkich plików danych, plików dziennika powtórzeń i plików kontrolnych.
Pliki dziennika powtórzeń zapisują wszystkie zmiany dokonane w bazie danych i są używane przy odtwarzaniu.
Plik kontrolny opisuje strukturę bazy danych i jest niezbędny do montowania i otwierania bazy danych.
Plik parametrów jest używany do ustawienia rozmiarów SGA i zlokalizowania plików kontrolnych w trakcie uruchamiania instancji Oracle.
Plik ostrzeżeń bazy danych jest chronologicznym rejestrem komunikatów i błędów.

II. Uruchamianie i zamykanie instancji

Cele

W tej lekcji omówimy kroki, jakie musi wykonać DBA przy uruchamianiu i zamykaniu instancji Oracle w odpowiednim trybie, właściwym dla różnych sytuacji.

Po przerobieniu tej lekcji powinieneś potrafić:

Wybrać metodę identyfikacji.
Uruchomić instancję w odpowiednim trybie.
Zamknąć instancję w odpowiednim trybie.
Zmienić tryb bazy danych.
Ograniczyć podłączenia do bazy.

Metody Identyfikacji

W zależności od tego, czy DBA jest administratorem lokalnym bazy danych (na tym samym komputerze, gdzie znajduje się baza danych), czy też DBA administruje wieloma różnymi bazami danych z pojedynczego odległego komputera, możemy wybrać pomiędzy identyfikacją przez system operacyjny lub identyfikacją administratorów przez pliki haseł.

Identyfikacja przez System Operacyjny

Ustaw identyfikację użytkownika w systemie operacyjnym.
Ustaw REMOTE_LOGIN_PASSWORDFILE=NONE
Podłączenie przez polecenie CONNECT / AS sysdba.

Identyfikacja przez Plik Haseł

Utwórz plik haseł narzędziem ORAPWD
Ustaw REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.
Nadaj uprawnienia sysoper lub sysdba administratorom w bazie danych (polecenie GRANT).
Podłączenie przez polecenie CONNECT użytkownik AS sysdba lub sysoper.

Uruchamianie Instancji

Przy uruchamianiu instancji trzeba podać stan w jakim ma znaleźć się baza danych.

Stany Uruchomienia

Stan Uruchomienia	Opis
NOMOUNT	Używany przy tworzeniu bazy danych.
MOUNT	Używany przy modyfikowaniu struktury plików i zmianach znaczników w pliku kontrolnym.
OPEN	Udostępnia bazę danych wszystkim użytkownikom.

0x08 graphic
Procedura Uruchamiania

Wystartowanie instancji.
Zamontowanie bazy danych.
Otwarcie bazy danych.

Na diagramie stan SHUTDOWN reprezentuje punkt startowy polecenia STARTUP.

Stan NOMOUNT, MOUNT i OPEN wzajemnie się wykluczają, tj. baza może być w danym momencie tylko w jednym z tych stanów. Aby przejść za stanu SHUTDOWN do otwartej bazy danych, należy wykonać polecenie STARTUP.

Po wykonaniu STARTUP NOMOUNT następuje wystartowanie instancji.
Po wykonaniu STARTUP MOUNT następuje wystartowanie instancji i otwarcie pliku kontrolnego. Baza danych może być zamontowana lecz nie otwarta np. dla zmiany istniejącej struktury plików. Przykładem zmiany struktury plików może być zmiana nazwy pliku dziennika lub pliku danych, albo też rozpoczęcia odtwarzania.
Po wykonaniu STARTUP OPEN następuje wystartowanie instancji, otwarcie pliku kontrolnego, a następnie otwarcie wszystkich plików wyspecyfikowanych w pliku kontrolnym.

Aby otworzyć bazę danych po poleceniu STARTUP MOUNT wykonujemy polecenie ALTER DATABASE OPEN. Wtedy baza danych jest dostępna dla wszystkich użytkowników.

0x01 graphic

Uwaga: Polecenie STARTUP można wykonać dla danej instancji tylko raz.

Kroki Serwera

Uruchomienie instancji powoduje, że serwer Oracle:

Odczytuje parametry z pliku init<SID>.ora.
Przydziela pamięć na SGA.
Uruchamia procesy tła.
Otwiera pliki śladu i plik ostrzeżeń.

Nazwa bazy danych musi być podana w parametrze DB_NAME w pliku init<SID>.ora lub w poleceniu STARTUP.

Tryby Uruchomienia

Opcja NOMOUNT nie jest zwykle podawana w poleceniu STARTUP jako, że jest ona używana jedynie dla wystartowania instancji, która ma utworzyć bazę danych.
Opcja MOUNT jest używana, gdy musimy wykonać odtwarzanie, lub konieczne jest wykonanie pewnych akcji związanych z plikami danych lub plikami dziennika.
W przeważającej liczbie przypadków używana jest opcja OPEN pozwalająca wszystkim na używanie bazy danych.

Opcje Montowania

Opcje PARALLEL używamy tylko jeśli wiele instancji jednoczenie ma mieć dostęp do bazy danych. W przeciwnym razie używamy (domyślnej) opcji EXCLUSIVE. Opcja SHARED jest synonimem opcji PARALLEL. Opcja RETRY jest dostępna tylko dla instancji pracujących w trybie PARALLEL.

Opcje Uruchamiania

Opcja FORCE jest używana do ponownego uruchomienia już działającej instancji. Nie należy używać tej opcji jeśli da się zamknąć bazę poleceniem SHUTDOWN. Po wykonaniu polecenia STARTUP FORCE baza wymaga odtwarzania.
Opcja RESTRICT służy do zablokowania możliwości podłączenia się do bazy zwykłym użytkownikom.

Elementy SGA

Pewna część przestrzeni o stałym rozmiarze jest zarezerwowana na ogólne informacje bazy danych i instancji. Nie są tzn. przechowywane żadne dane użytkowników.
Część o zmiennym rozmiarze przeznaczona na obszar dzielony, obszary sortowania i inne dane związane z danymi użytkownika.

Składnia:

0x01 graphic

gdzie:

baza danych określa nazwę bazy danych

OPEN udostępnia bazę danych użytkownikom

MOUNT montuje bazę danych na potrzebę pewnych czynności DBA, nie udostępnia bazy użytkownikom

NOMOUNT tworzy SGA i uruchamia procesy tła, nie udostępnia bazy danych użytkownikom

EXCLUSIVE pozwala jedynie bieżącej instancji na dostęp do bazy danych

PARALLEL pozwala na dostęp do bazy danych wielu instancji (uruchamia Serwer Równoległy Oracle).

SHARED inny termin na PARALLEL

RETRY wskazuje, aby instancja równoległa usiłowała się ponownie uruchamiać w pięciosekundowych odstępach.

PFILE=parfile pozwala na użycie innego niż domyślny pliku parametrów dla ustalenia konfiguracji instancji

FORCE przed wykonaniem normalnego uruchomienia przerywa działającą instancję

RESTRICT udostępnia bazę danych tylko użytkownikom posiadającym uprawnienie RESTRICTED SESSION

RECOVER po uruchomieniu bazy rozpoczyna odtwarzanie po awarii nośników.

Przykład

Start instancji bazy danych ORCL z użyciem pliku parametrów initORCL.ora. Wykonujemy polecenie:

SVRMGR > STARTUP NOMOUNT PFILE=initORCL.ora.

Montowanie Bazy Danych

Aktywacja Plików Kontrolnych

Podczas montowania bazy danych:

Wyspecyfikowane w pliku parametrów pliki kontrolne zostają odnalezione i otwarte.
Pliki kontrolne są odczytywane w celu znalezienia nazw i statusów plików danych i plików dziennika powtórzeń.

Zamontować bazę danych można też poleceniem ALTER DATABASE

Składnia:

0x01 graphic

gdzie:

baza danych nazwa bazy, która ma być zmieniona.

MOUNT montuje bazę danych na potrzeby pewnych czynności DBA, nie udostępnia bazy użytkownikom

EXCLUSIVE pozwala jedynie bieżącej instancji na dostęp do bazy

PARALLEL pozwala na dostęp do bazy danych wielu instancji (uruchamia Serwer Równoległy Oracle).

OPEN udostępnia bazę danych użytkownikom

Uruchomianie Odtwarzania

Baza danych musi być zamontowana, lecz nie otwarta w celu

Włączenia lub wyłączenia archiwizacji dzienników umożliwiających odtwarzanie nośników (przełączenie pomiędzy trybami ARCHIVELOG i NOARCHIVELOG).
Wykonania pełnego odtwarzania nośników (odbudowanie pełnej bazy z kopii zapasowej)
Wykonania niepełnego odtwarzania bazy danych.

Opcja RESETLOGS jest omawiana na kursie Archiwizacja i Odtwarzanie. Powinna być stosowana bardzo ostrożnie.

Opcje MOUNT i OPEN są dostępne w menu narzędzia Instance Manager oraz w Server Manager w trybie linii poleceń.

Otwieranie Bazy Danych

Otwieramy bazę danych w celu umożliwienia normalnej pracy użytkownikom.

Przykład

Otwieranie zamontowanej uprzednio bazy danych.

SVRMGR> ALTER DATABASE OPEN;

Statement processed.

Argumentu RESTRICTED SESSION używamy w celu udostępnienia bazy danych tylko użytkownikom DBA.

Jeśli którykolwiek plik danych lub plik dziennika powtórzeń jest niedostępny lub nie może być otwarty, serwer Oracle zwraca błąd i nie otwiera bazy danych.

Sesje Zastrzeżone

Stan bazy danych może być zmieniony tak, aby w przyszłe podłączenia mieli prawo wykonywać inni użytkownicy mający prawo do otwierania sesji zastrzeżonych (uprawnienie RESTRICTED SESSION). Służy do tego polecenie ALTER SYSTEM.

Składnia:

0x01 graphic

gdzie:

ENABLE RESTRICTED pozwala na wykonywanie podłączeń tylko użytkowników z uprawnieniami RESTRICTED SESSION

DISABLE RESTRICTED pozwala na podłączenie się wszystkim użytkownikom

Zamykanie bazy danych

Po zamknięciu bazy danych i zatrzymaniu instancji baza danych jest niedostępna dla użytkowników.

Składnia:

0x01 graphic

gdzie:

NORMAL oczekuje aż wszyscy podłączeni użytkownicy normalnie zakończą swoje sesje.

TRANSACTIONAL oczekuje, aż wszyscy użytkownicy zakończą swoje transakcje, rozłącza użytkowników po zakończeniu transakcji

IMMEDIATE kończy bieżąco wykonywane polecenie SQL i wycofuje nie zatwierdzone transakcje.

ABORT najszybsze możliwe zakończenie. Nie czeka na zakończenie operacji ani na odłączenie użytkowników. Nie wycofuje nie zatwierdzonych transakcji.

Zamknięcie Normalne (shutdown normal)

Nie pozwala na nowe podłączenia. Serwer Oracle odwleka zamknięcie bazy aż do odłączenia się wszystkich użytkowników. Następne uruchomienie nie wymaga odtwarzania instancji. NORMAL jest domyślnym trybem zamykania.

Zamknięcie Transakcyjne (shutdown transactional)

Bieżące polecenie SQL klientów nie są kończone. Wszystkie nie zatwierdzone transakcje kontynuują pracę. Po zakończeniu transakcji sesje użytkowników są kończone. Polecenie SHUTDOWN TRANSACTIONAL zamyka i demontuje bazę danych i zatrzymuje instancję. Baza danych jest spójna, następne uruchomienie nie wymaga odtworzenia instancji.

Zamknięcie Natychmiastowe (shutdown immediate)

Bieżące polecenie SQL klientów nie są kończone. Wszystkie nie zatwierdzone transakcje są wycofywane. Serwer Oracle nie czeka na odłączenie się wszystkich podłączonych do bazy danych użytkowników. Polecenie SHUTDOWN IMMEDIATE zamyka i demontuje bazę danych i zatrzymuje instancję. Baza danych jest spójna, następne uruchomienie nie wymaga odtworzenia instancji.

Zamknięcie Awaryjne (shutdown abort)

Przerywa bieżąco wykonywane przez serwer Oracle polecenia SQL klientów. Nie zatwierdzone transakcje nie są wycofywane. Serwer Oracle nie czeka na odłączenie się podłączonych do bazy danych użytkowników. SHUTDOWN ABORT nie zamyka ani nie demontuje bazy danych, zatrzymuje jedynie instancję. Następne uruchamianie wymaga odtwarzania instancji (co odbywa się automatycznie).

Zamykamy bazę danych w celu zrobienia kopii zapasowej wszystkich struktur fizycznych offline z poziomu systemu operacyjnego lub w celu modyfikacji parametrów inicjalizacji.

Aby sprawdzić, czy są jacyś użytkownicy podłączeni do bazy oglądamy perspektywę V$SESSION.

Przykłady

Zamknięcie bazy danych pozwalające użytkownikom zakończyć ich sesje.

SVRMGR> CONNECT / AS SYSDBA

Connected

SVRMGR> SHUTDOWN NORMAL

Database closed

Database dismounted

Oracle instance shut down

Zamknięcie bazy danych zamontowanej, ale nie otwartej.

SVRMGR> CONNECT / AS SYSDBA

Connected

SVRMGR> SHUTDOWN NORMAL

ORA-01109 : database not open

Database dismounted

ORACLE instance shut down

Zamknięcie bazy danych w stanie NOMOUNT

SVRMGR> CONNECT / AS SYSDBA

Connected

SVRMGR> SHUTDOWN NORMAL

ORA-01507 : database not mounted

ORACLE instance shut down

Podsumowanie

Aby baza danych była dostępna dla użytkowników, należy wystartować (uruchomić) instancję.

Procedura Uruchamiania

Wywołaj narzędzie Oracle Enterprise Manager lub Server Manager.
Podłącz się jako administrator (polecenie CONNECT użytkownik AS sysdba)
Uruchom bazę danych (polecenie STARTUP)
Start instancji.
Zamontowanie bazy danych.
Otwarcie bazy danych

Po zamknięciu instancji baza danych jest niedostępna dla użytkowników.

Procedura Zamykania

Wywołaj narzędzie Oracle Enterprise Manager lub Server Manager.
Podłącz się jako administrator (polecenie CONNECT użytkownik AS sysdba)
Zamknij instancję (polecenie SHUTDOWN).

Poleceniami ALTER SYSTEM ENABLE/DISABLE RESTRICTED SESSION ograniczamy dostęp jedynie dla użytkowników DBA lub też pozwalamy na podłączenie wszystkim użytkownikom.

0x01 graphic

Zadania

Podłącz się do bazy jako internal

Zamontuj bazę

Obejrzyj perspektywę V$SGA

Obejrzyj perspektywę DBA_USERS

Otwórz bazę

Zamknij bazę w trybie IMMEDIATE

Otwórz bazę poleceniem STARTUP OPEN

Zobacz, czy teraz da się obejrzeć perspektywę DBA_USERS

Zablokuj dostęp dla użytkowników

Spróbuj zalogować się jako DEMO/DEMO

Zamknij bazę w trybie TRANSACTIONAL

W Instance Managerze zamontuj bazę i otwórz, a następnie zamknij w trybie IMMEDIATE

III. Tworzenie bazy danych

Cele

W tej lekcji omówimy kroki jakie musi podjąć DBA przy tworzeniu nowej bazy danych Oracle.

Po przerobieniu tej lekcji powinieneś potrafić:

Omówić związek między logicznym diagramem encji i związków a związanymi z nimi obiektami bazy danych.
Utworzyć bazę danych.
Zbudować słownik danych.

Przegląd

Tworzenie bazy danych to pierwszy krok w organizowaniu i zarządzaniu systemu baz danych.

Zadania Związane z Tworzeniem Bazy Danych

Organizacja zawartości bazy danych poprzez przestrzenie tabel.
Zaprojektowanie struktury bazy danych optymalizującej efektywność i minimalizującej fragmentację.
Przygotowanie środowiska systemu operacyjnego do tworzenia bazy danych.
Edycja pliku parametrów.
Wystartowanie instancji.
Wykonanie polecenia CREATE DATABASE.
Zapewnienie bezpieczeństwa bazy danych przez zwielokrotnienie plików dziennika powtórzeń i plików kontrolnych.
Utworzenie pliku haseł.
Zdefiniowanie tabel i perspektyw słownika danych do zarządzania bazą danych.

Planowanie Przestrzeni Tabel

Baza danych Oracle jest podzielona na mniejsze logiczne części nazywane przestrzeniami tabel.

Przestrzenie Tabel (tablespace)

Każda przestrzeń tabel składa się z jednego lub wielu plików systemu operacyjnego.
Przestrzenie tabel mogą być włączane (przełączane w stan online) w czasie pracy bazy.
Z wyjątkiem przestrzeni tabel SYSTEM, przestrzenie tabel mogą być wyłączane (przełączane w stan offline) bez przerywania pracy bazy danych.
Każdy obiekt znajduje się w pojedynczej przestrzeni tabel.

Przestrzeń tabel zawierająca aktywny segment wycofania nie może być wyłączona.

Przestrzeń tabel SYSTEM jest zawarta w każdej instancji serwera Oracle. Jako uzupełnienie przestrzeni tabel SYSTEM zaleca się zbudowanie dodatkowych przestrzeni tabel:

Przestrzeń tabel	Opis
TEMP	Zawiera segmenty tymczasowe używane np. przy sortowaniu
RBS	Zawiera dodatkowe segmenty wycofania. (Nie można utworzyć przestrzeni tabel o nazwie rollback ponieważ słowo ROLLBACK jest słowem kluczowym).
TOOLS	Przeznaczona na tabele używane przez narzędzia serwera Oracle.
APPLI_DATA	Zawiera dane produkcyjne, używane przez aplikacje. Może istnieć wiele przestrzeni tabel dla danych produkcyjnych, co będzie omawiane w dalszej części lekcji.
APPLI_INDEX	Przeznaczona na indeksy związane z danymi produkcyjnymi z przestrzeni tabel APPLI_DATA. Może istnieć wiele indeksowych przestrzeni tabel

Charakterystyka Danych

Przed zaprojektowaniem struktury przestrzeni tabel w bazie danych należy rozważyć własności przechowywanych danych.

Pozwoli to na:

Minimalizację fragmentacji.
Zmniejszenie efektu rywalizacji o dysk.
Oddzielenie segmentów.

Na niektórych platformach program instalacyjny Oracle utworzy pewną liczbę predefiniowanych przestrzeni tabel.

Minimalizacja Fragmentacji

W celu minimalizacji fragmentacji oddzielamy grupy obiektów z różną charakterystyką fragmentacji umieszczając je w innych przestrzeniach tabel.

Typ Segmentu	Charakterystyka fragmentacji
Segmenty słownika danych	Brak skłonności do fragmentacji, nigdy nie dzielą wolnej przestrzeni.
Segmenty danych aplikacji	Mała skłonność do fragmentacji, ponieważ tabele mają określoną przestrzeń zgodną z wymaganiami projektu.
Pomocnicze segmenty aplikacyjne	Średnia skłonność do fragmentacji.
Segmenty wycofania	Średnia skłonność do fragmentacji
Segmenty tymczasowe	Średnia skłonność do fragmentacji

Oddzielanie Segmentów

Rozdzielanie grupy segmentów reprezentujące obiekty z inną charakterystyką operacyjną umieszczając je w różnych przestrzeniach tabel.

Kiedy Oddzielać Grupy

Oddzielamy grupy:

Segmentów o różnych wymaganiach archiwizacji.
Segmentów o innych wymaganiach dotyczących dostępu.
Segmentów o innej częstości ich używania.
Segmenty o różnych wymaganiach przestrzeni.

Korzyści z Oddzielania Grup

Archiwizacja i odtwarzanie.

Archiwizacja i odtwarzanie jest prostsza kiedy dane związane z określoną aplikacją są oddzielone od danych aplikacji.

Archiwizacja jest szybsza jeśli dotyczy przestrzeni tabel tylko do odczytu.

Planowanie rozmiarów.

Szacowanie i pomiar przyrostu danych w projekcie jest prostsze jeśli dane związane z określonym projektem są oddzielone od danych innego projektu.

Bezpieczeństwo

Zapewnienie bezpieczeństwa jest prostsze w implementacji jeśli manipulacja uprawnieniami do bazy danych użytkowników dotyczy grup obiektów.

Usuwanie

Archiwizacja lub usuwanie danych związanych z zakończonym projektem jest łatwiejsze jeśli dane projektu przechowywane są niezależnie od danych innych projektów.

Umiejscowienie Plików Bazy Danych

Zabezpieczamy bazę danych maksymalizując jednocześnie pojemność i wydajność.

Umiejscowienie Plików Bazy Danych

Zachowuj przynajmniej dwie aktywne kopie pliku kontrolnego bazy danych na przynajmniej dwóch urządzeniach fizycznych.
Używaj zwielokrotnionych plików dziennika powtórzeń umieszczając elementy grupy na różnych dyskach.
Umieszczaj przestrzenie tabel, których dane mogą powodować rywalizację o dysk na różnych fizycznych dyskach (stripping).

Tworzenie Bazy Danych

W celu utworzenia bazy danych Oracle podajemy następujące kroki:

Wybierz unikalną nazwę instancji, rozmiar bloku, zestaw znaków bazy danych, maksymalną liczbę plików dziennika.
Przygotuj plik parametrów (init<SID>.ora.).
Ustaw odpowiednie zmienne systemu operacyjnego.
Uruchom Serwer Manager w trybie linii poleceń i podłącz się jako sysdba.
Wystartuj instancję (STARTUP NOMOUNT)
Utwórz bazę danych

Powyższe kroki wykonywane są zwykle przez skrypt instalacyjny. Następnie DBA powinien zainstalować słownik danych, perspektywy importu/eksportu i dodatkowe opcje swojej licencji.

Wybór Nazwy Instancji

Musimy wybrać unikalną nazwę dla instancji. Na pewnych platformach, takich jak UNIX, nazwa instancji jest zawarta w nazwie pliku parametrów.

Przykład

Wyświetlić pliki parametrów w katalogu dbs.

% ls $ORACLE_HOME/dbs/init*.ora

/home/oraclev7/dbs/init.ora

/home/oraclev7/dbs/initEDUC7.ora

/home/oraclev7/dbs/initDBA01.ora

Domyślnie nazwa pliku parametrów instancji o nazwie TEST w systemie UNIX to initTEST.ora umieszczona w katalogu $ORACLE_HOME/dbs.

Konkretna metoda sprawdzenia jakie instancje są uruchomione w systemie zależy od systemu operacyjnego.

W systemie UNIX wyświetlamy wszystkie procesy wprowadzając następujące polecenie:

ps -ef lub

ps -ax

W ten sposób wyświetlamy uruchomione procesy drugoplanowe wszystkich instancji bazy danych Oracle. Nazwa instancji będzie zawierać się w nazwie jej procesów drugoplanowych.

Przygotowanie Pliku Parametrów

Plik parametrów, nazywany zwykle plikiem init<SID>.ora to plik tekstowy, który możemy dopasować do swoich potrzeb używając standardowego edytora systemu operacyjnego.

Plik parametrów jest odczytywany tylko w momencie startu instancji. Jeśli został zmieniony, aby zmiany odniosły skutek trzeba zamknąć i ponownie uruchomić instancję.

Powody Modyfikowania Pliku Parametrów

Parametry w pliku init<SID>.ora mają istotny wpływ na efektywność bazy danych, lub też muszą zostać dopasowane do wymagań produkcyjnych bazy danych:

Rozmiary składników Globalnego Obszaru Systemowego (SGA).
Wartości domyślne dla bazy danych i instancji.
Ograniczenia bazy danych.
Definicja (istotna tylko przy tworzeniu) różnych cech fizycznych bazy danych, takich jak rozmiar bloku bazy danych.
Specyfikacja plików kontrolnych, archiwizowanych plików dziennika i plików śledzenia.
Optymalizacja efektywności przez dopasowanie rozmiarów struktur pamięci.
Definicja różnych parametrów funkcjonalnych.

Cechy pliku parametrów:

Wartości parametrów mogą być liczbami całkowitymi, napisami lub wartościami logicznymi.
Linie komentarzy rozpoczynają się od symbolu #.
Większość parametrów posiada wartości domyślne.

Edycja Pliku Parametrów

Podczas przygotowywania nowej bazy danych powinniśmy określić ustawienia pewnych parametrów. Wartości innych mogą pozostać domyślne.

Parametry Które Powinny Być Ustawione

Parametr	Opis
DB_NAME	Maksymalnie ośmioznakowy identyfikator bazy danych. Jedyny parametr obowiązkowy podczas tworzenia nowe bazy danych.
CONTROL_FILES	Nazwy plików kontrolnych
DB_BLOCK_SIZE	Rozmiar (w bajtach) bloku danych bazy danych Oracle. Domyślnie 2048
SHARED_POOL_SIZE	Rozmiar (w bajtach) obszaru dzielonego. Domyślnie 3500000
BACKGROUND_DUMP_DEST	Miejsce tworzenia plików śladu procesów tła.
USER_DUMP_DEST	Miejsce tworzenia plików śladu użytkowników.
DB_BLOCK_BUFFERS	Liczba bloków buforowych w SGA. Domyślnie 32

Wskazówki Ustawienia Parametrów Init<SID>.ora

Zawsze podaj co najmniej dwie nazwy plików kontrolnych w pliku parametrów, umieszczone, jeśli to możliwe, na różnych dyskach.
Jeśli jest ustawione DB_NAME, wartość musi być zgodna z nazwą bazy danych zapisaną w plikach kontrolnych.

Najczęściej Modyfikowane Parametry

Parametr	Opis
AUDIT_TRAIL	Włącza lub wyłącza zapis do dziennika obserwacji
IFILE	Nazwa innego pliku parametrów jaki ma być użyty przy starcie.
LOG_BUFFER	Liczba bajtów zarezerwowana na bufor dziennika powtórzeń w SGA.
LOG_ARCHIVE_START	Włącza lub wyłącza automatyczną archiwizację dziennika jeśli baza jest w trybie ARCHIVELOG.
LOG_ARCHIVE_FORMAT	Domyślny format archiwizowanych plików dziennika
LOG_ARCHIVE_DEST	Miejsce archiwizowania plików dziennika powtórzeń
LOG_CHECKPOINT_INTERVAL	Ustawienie częstotliwości punktów kontrolnych
LOG_CHECKPOINT_TIMEOUT	Okresowe wykonywanie punktów kontrolnych
MAX_DUMP_FILE_SIZE	Maksymalny rozmiar (wyrażony w blokach systemu operacyjnego) plików śladu.
OPEN_CURSORS	Maksymalna liczba kursorów otwieranych jednocześnie przez użytkownika.
PROCESSES	Maksymalna liczba procesów systemu operacyjnego podłączonych jednocześnie do tej instancji.
ROLLBACK_SEGMENTS	Nazwy segmentów wycofania utworzonych dla tej instancji.
SQL_TRACE	Włącza lub wyłącza narzędzie śledzenia SQL dla wszystkich sesji użytkownika.
TIMED_STATISTICS	Włącza lub wyłącza pomiar czasów w śladach i w ekranach Performance Monitora.

0x01 graphic

Uwaga: Wszystkie te parametry są opcjonalne. Jeśli któryś z nich nie ma przypisanej wartości, stosowana jest wartość domyślna.

Przykład:

db_name=ORCL

# db_files = 1024 # INITIAL

# db_files = 80 # SMALL

db_files = 400 # MEDIUM

# db_files = 1000 # LARGE

control_files = (E:\database\ctl1orcl.ora, E:\database\ctl2orcl.ora)

db_file_multiblock_read_count = 8 # INITIAL

# db_file_multiblock_read_count = 8 # SMALL

# db_file_multiblock_read_count = 16 # MEDIUM

# db_file_multiblock_read_count = 32 # LARGE

db_block_buffers = 200 # INITIAL

# db_block_buffers = 100 # SMALL

# db_block_buffers = 550 # MEDIUM

# db_block_buffers = 3200 # LARGE

#shared_pool_size = 10000000 # INITIAL

shared_pool_size = 2530000 # SMALL

# shared_pool_size = 5000000 # MEDIUM

# shared_pool_size = 9000000 # LARGE

log_checkpoint_interval = 10000

processes = 59 # INITIAL

# processes = 50 # SMALL

# processes = 100 # MEDIUM

# processes = 200 # LARGE

log_buffer = 8192 # INITIAL

# log_buffer = 8192 # SMALL

# log_buffer = 32768 # MEDIUM

# log_buffer = 163840 # LARGE

audit_trail = true # if you want auditing

timed_statistics = true # if you want timed statistics

max_dump_file_size = 10240 # limit trace file size to 5 Meg each

# log_archive_start = true

# log_archive_dest = e:\kurs\database\db1

# log_archive_format = "%%ORACLE_SID%%T%TS%S.ARC"

# rollback_segments = (name1, name2)

background_dump_dest=E:\database\trace

user_dump_dest=E:\database\trace

db_block_size = 2048

remote_login_passwordfile = exclusive

Ustawienie Identyfikatora Systemowego

Identyfikator systemowy SID (od ang. System IDentifier) to zmienna środowiska używana przez serwer Oracle do określenia instancji, do jakiej użytkownik się podłącza.

Ustawienie Zmiennej

Przed wystartowaniem instancji sprawdzamy poprawność ustawienia SID. Jeśli SID nie jest ustawiony poprawnie, należy tego dokonać używając odpowiedniego polecenia systemu operacyjnego.

Przykłady

Ustawienia wartości ORACLE_SID na TEST pod systemem UNIX i następnie sprawdzanie wartości.

$ ORACLE_SID=TEST;export ORACLE_SID

$ echo $ORACLE_SID

TEST

Wartość SID może mieć do 8 znaków w UNIXie, do 4 znaków w Windows NT.

Zmieniając wartość identyfikatora systemowego możemy podłączyć się do różnych baz danych.

Uuchomienie Instancji

Instancję uruchamiamy używając polecenia STARTUP, tak jak pokazano poniżej.

Przykład

Wystartowanie instancji w trybie NOMOUNT.

SVRMGR> STARTUP NOMOUNT

Uruchamianie Instancji

Klauzula PFILE=init<SID>.ora może być niezbędna w poleceniu STARTUP jeżeli plik parametrów nie znajduje się w domyślnym miejscu.
Zostaną uruchomione cztery procesy tła: DBWR, LGWR, SMON oraz PMON.

Tworzenie Bazy Danych

Baza danych Oracle składa się z plików danych przechowujących tabele i indeksy, plików dziennika powtórzeń stanowiących część systemu odtwarzania serwera Oracle oraz plików kontrolnych zawierających informacje niezbędne do uruchomienia i obsługi bazy danych.

Składnia:

0x01 graphic

gdzie:

baza danych nazwa danych jaka ma być tworzona.

spec.pliku specyfikacja pliku danych lub dziennika w formacie: nazwa [SIZE n] [K lub M] [REUSE]

CONTROLFILE REUSE żąda ewentualnego zamazania istniejącego pliku kontrolnego wyspecyfikowanego w pliku parametrów.

LOGFILE GROUP określa nazwy plików dziennika jakie mają być używane i grupę do której należą.

MAXLOGFILES jest maksymalną liczbą plików dziennika tworzonych w bazie danych.

MAXLOGMEMBERS jest maksymalną liczbą elementów w grupie plików dziennika powtórzeń.

MAXLOGHISTORY jest maksymalną liczbą archiwów dziennika powtórzeń w automatycznym odtwarzaniu nośników w Oracle Parallel Serwer.

DATAFILE spec. pliku określa pliki danych jakie mają być użyte.

MAXDATAFILES jest maksymalną liczbą plików danych tworzonych w bazie danych.

MAXINSTANCES maksymalna liczba instancji jakie jednocześnie mogą zamontować i otworzyć bazę danych.

ARCHIVELOG żąda, aby pliki dziennika powtórzeń były przed ich powtórnym użyciem archiwizowane.

NOARCHIVELOG stwierdza, że dzienniki powtórzeń mogą być ponownie zapisywane przed archiwizacją ich zawartości.

EXCLUSIVE po utworzeniu montuje bazę danych w trybie wyłącznym.

CHARACTERSET zestaw znaków używanych w bazie do przechowywania danych.

Jeżeli w specyfikacji pliku podano opcję REUSE, plik taki musi już istnieć. W przeciwnym przypadku konieczna jest opcja SIZE, a podany plik istnieć nie może.

Przykłady

Utworzenie bazy danych o nazwie TEST z jednym plikiem danych o nazwie system.dbf i rozmiarze 10 MB, a także dwoma plikami dziennika o nazwie log1a.rdo oraz log2a.rdo, każda rozmiaru 500 KB. Zestaw znaków pozostanie domyślny, czyli US7ASCII.

SVRMGR> CREATE DATABASE test

2> DATAFILE `/u02/Oracle/DBA1/system.dbf' SIZE 10M

3> LOGFILE

4> GROUP 1 `/u01/Oracle/DBA1/log1a.rdo' SIZE 500K,

5> GROUP 2 `/u01/Oracle/DBA1/log1a.rdo' SIZE 500K;

Utworzenie bazy danych z 8-bitowym zestawem znaków i zwielokrotnionymi dziennikami powtórzeń.

SVRMGR> CREATE DATABASE test

2> DATAFILE `/u02/Oracle/DBA1/system.dbf' SIZE 10M

3> LOGFILE GROUP 1

4> (`/u01/Oracle/DBA1/log1a.rdo',

5> `/u02/Oracle/DBA1/log1a.rdo') SIZE 500K,

6> GROUP 2 (`/u01/Oracle/DBA1/log1a.rdo',

7> `/u02/Oracle/DBA1/log1a.rdo') SIZE 500K

8> CHARACTER SET WE8ISO8859P1;

Wskazówki :

Jeśli nie wyspecyfikowano pełnych nazw dziennika pliki będą utworzone w katalogu domyślnym systemu.

Polecenie CREATE DATABASE może trwa pewien czas z powodu zadań jakie wykonuje w tle.

Dodawanie Pliku Kontrolnego

Plik kontrolny jest odczytywany w chwili uruchamiania bazy danych. Jeśli nie zostanie znaleziony, uruchomienie się nie powiedzie. Zapewniamy, że przynajmniej dwa pliki kontrolne opisują naszą bazę danych wykonując następujące kroki:

Procedura Dodawania Pliku Kontrolnego

1. Zamknij bazę danych.

2. Skopiuj istniejący plik kontrolny w nowe miejsce.

3. Zmodyfikuj plik parametrów tak, aby zawierał nazwę nowej kopii.

4. Uruchom bazę danych ze zmodyfikowanym plikiem parametrów.

Powyższe kroki zakładają, że istnieje tylko jeden plik kontrolny. Są one zbędne, jeśli od razu utworzymy bazę danych ze zwielokrotnionymi plikami kontrolnymi. Należy także zapewnić, aby serwer Oracle miał prawa odczytu i zapisu do nowoutworzonego pliku kontrolnego.

Przykład (unix)

SVRMGR> SHUTDOWN

SVRMGR> HOST ed init.ora

CONTROL_FILES=(path/file1, path/file2)

SVRMGR> CONNECT / AS SYSDBA

SVRMGR> STARTUP PFILE=init.file

Jeśli plik kontrolny zostanie uszkodzony, to trzeba go stworzyć na nowo poleceniem CREATE CONTROLFILE (przy otwartej instancji i niezamontowanej bazie danych).

Dodawanie Elementu Dziennika Powtórzeń

Pliki dziennika powtórzeń nie muszą być punktem krytycznym dla bezpieczeństwa bazy danych Oracle. Unikamy tego problemu przez zapewnienie, aby każda grupa plików dziennika miała więcej niż jeden element.

Możemy dodać element do grupy dziennika powtórzeń poleceniem ALTER DATABASE.

Składnia:

0x01 graphic

gdzie:

baza danych nazwa bazy jaka jest zmieniana

plik nazwa pliku w systemie operacyjnym

GROUP number numer grupy plików dziennika, gdzie ma być dodany element

Jeżeli użyjemy opcji REUSE, wskazany plik musi istnieć, w przeciwnym przypadku zostanie on utworzony w takim rozmiarze, jak inne elementy jego grupy.

W celu sprawdzenia bieżącego stanu plików dziennika sprawdzamy perspektywę słownika danych V$LOG. W V$LOGFILE oglądamy pełne nazwy plików dziennika powtórzeń. Złączenie V$LOG i V$LOGFILE pozwoli na obejrzenie jednocześnie pełnej nazwy i statusu.

Przykład

Modyfikacja bazy danych TEST polegająca na dodaniu pliku dziennika o nazwie log1b.rdo do grupy 1 oraz weryfikacja efektów.

SVRMGR> ALTER DATABASE test

2> ADD LOGFILE MEMBER `/u02/Oracle/ DBA01/log1b.rdo'

3> TO GROUP 1;

SQL> SELECT * FROM v$logfile;

GROUP # STATUS MEMBER

-------- -------- ---------------------------------------

1 /u01/Oracle/ DBA01/1og1a.rdo

2 /u01/Oracle/ DBA01/1og2a.rdo

1 /u02/Oracle/ DBA01/1og1b.rdo

0x01 graphic

Uwaga: Po dodaniu nowego elementu do plików dziennika perspektywa V$LOGFILE może pokazywać, że STATUS pewnych plików jest niepoprawny (INVALID), co oznacza że nie zostały one jeszcze zapisane przez LGWR. To jest normalny efekt, pliki staną się poprawne jak tylko instancja zacznie ich używać.

Aby instancja użyła nowo dodane pliki dziennika powtórzeń należy wymusić przełączenie grup dziennika poleceniem ALTER SYSTEM SWITCH LOGFILE.

Zmiana Rozmiaru Elementów Grupy Dziennika Powtórzeń

Nie ma bezpośredniego sposobu na zmianę elementów grupy dziennika powtórzeń. Zamiast tego należy utworzyć nowe grupy o innym rozmiarze po czym usunąć stare grupy.

Tworzenie Pliku Haseł

Jeśli zdecydujemy się na używanie pliku haseł do identyfikacji użytkowników pełniących funkcje administracyjne, musimy dokonać następujących czynności:

Tworzenie Pliku Haseł

Utwórz plik haseł używając narzędzia ORAPWD:
ORAPWD FILE=orapwdSID PASSWORD=secret ENTRIES=30
Plik haseł zostanie utworzony w katalogu $ORACLE_HOME/dbs./
Ustaw wartość parametru inicjalizacji REMOTE_LOGIN_PASSWORDFILE na EXCLUSIVE.
Podłącz się jako SYDBA i uruchom ponownie bazę danych, aby odczytała nowe ustawienie parametru REMOTE_LOGIN_PASSWORDFILE.
Nadaj uprawnienia SYSDBA lub SYSOPER administratorom bazy danych.
GRANT SYSDBA TO administrator.
Jeśli chcesz administrować bazą danych z oddalonej maszyny klienta, za pomocą FTP prześlij tam initSID.ora.
Aby teraz administrować bazą danych należy uruchomić Server Manager na maszynie klienta i podłączyć się jako SYSDBA.

Jacy użytkownicy mają przyznane uprawnienia systemowe SYSDBA i SYSOPER możemy sprawdzić wykonując zapytanie na perspektywie bazy danych V$PWFILE_USERS.

Słownik Danych

Jedną z najważniejszych części danych Oracle jest słownik danych. Słownik danych to zestaw tabel i perspektyw używanych tylko do odczytu w celu uzyskania informacji o bazie danych. Słownik bazy danych jest tworzony przez plik poleceń sql.bsq.

Zawartość Słownika Danych

Nazwy użytkowników serwera Oracle.
Informacja o uprawnieniach i rolach nadanych każdemu użytkownikowi.
Nazwy i definicje obiektów w schematach.
Warunki integralności.
Przydział pamięci obiektom bazy danych.
Ogólna struktura bazy danych.
Informacje kontroli i śledzenia.
Pamiętane procedury i wyzwalacze bazy danych.

Tworzenie Dodatkowych Perspektyw Słownika Danych

Niektóre spośród plików rozkazowych SQL (inaczej skryptów) Oracle wykonują pewne funkcje na słowniku danych i powinny być uruchomione podczas lub po utworzeniu bazy danych.

Skrypty Uruchamiane Po Utworzeniu Bazy Danych

Skrypt

Zadanie

catalog.sql

Tworzenie często używanych perspektyw słownika danych

Catproc.sql

Uruchomienie wszystkich skryptów wymaganych do używania PL/SQL po stronie serwera.

Użytkownicy Słownika Danych

Słownik bazy danych jest centralnym źródłem informacji dla serwera Oracle i użytkowników bazy danych.

Użytkownicy Słownika Danych

Administratorzy bazy danych
Użytkownicy bazy danych
Aplikacje
Serwer Oracle

Nigdy nie używa się poleceń DML, takich jak INSERT, UPDATE, czy DELETE do bezpośredniej modyfikacji tabel bazowych słownika danych. Słownik jest modyfikowany niejawnie przez polecenia DDL. Sprawdzamy zawartość słownika danych przez wykonywanie zapytań na odpowiednich tabelach i perspektywach.

Użytkownicy Słownika Danych

Kategorie Słownika Danych

Słownik danych składa się z zestawu tabel bazowych i ze związanych z nimi perspektyw, które mogą być podzielone na następujące kategorie:

Kategoria	Opis
USER_xxx	Perspektywy dostępne dla każdego użytkownika dostarczające informacji o jego własnych obie.
ALL_xxx	Perspektywy dostępne dla każdego użytkownika dostarczające informacji o wszystkich obiektach do których ten użytkownik ma dostęp.
DBA_xxx	Perspektywy dostępne tylko dla DBA, dostarczające informacji o definicjach wszystkich obiektów w bazie danych.
zgodne z ANSI	Synonimy takie jak MYPRIVS czy ACCESSIBLE_TABLES utworzone dla zachowania zgodności z ANSI.

Cechy Słownika Danych

Wszystkie tabele i perspektywy słownika danych należą do użytkowników sys.
Dla perspektyw ALL_xxx i USER_xxx są utworzone publiczne synonimy.
Przyrostki nazw są zgodne między trzema pierwszymi kategoriami perspektyw serwera Oracle.
Perspektywy USER_xxx nie mają kolumny OWNER (właściciel)
Wiele perspektyw DBA_xxx nie ma odpowiednika w perspektywach USER_xxx
i ALL_xxx.
Nazwy wszystkich perspektyw słownika danych możemy uzyskać z perspektywy DICTIONARY.

Przeglądanie Perspektyw Słownika Danych

Przykłady

Wyświetlanie informacji DBA o kontach w serwerze Oracle z użyciem definicji kolumn takich jak w poprzednim przykładzie.

SQL> SELECT username, user_id, password, default_tablespace,

temporary_tablespace

FROM dba_users

USERNAME USER_ID PASSWORD DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

------------- ----- ------------------ ------------------ -----------------------

SYS 0 15C146A39C91814D SYSTEM SYSTEM

TUTORIAL15 115 565CD49962C3CCCA USER_DATA TEMPORARY_DATA

TUTORIAL16 116 5B61BB5091C4FDBB USER_DATA TEMPORARY_DATA

SYSTEM 5 87C4ECFB800A8C3D USER_DATA TEMPORARY_DATA

CTXSYS 22 EXTERNAL USER_DATA USER_DATA

DEMO 21 EXTERNAL USER_DATA TEMPORARY_DATA

SCOTT 20 F894844C34402B67 USER_DATA TEMPORARY_DATA

ZOLNIERZ 28 9546719E1D5CBD6B USER_DATA TEMPORARY_DATA

REP 46 EA472EF9486DC053 REPOSITORY_DATA TEMPORARY_DATA

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Identyfikacja wszystkich segmentów wycofania i ich statusu.

SQL>SELECT segment_name, status

FROM dba_rollback_segs

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

RB1 ONLINE

RB2 ONLINE

RB3 ONLINE

RB4 ONLINE

RB5 ONLINE

RB6 ONLINE

RB7 ONLINE

RB8 OFFLINE

RB9 OFFLINE

RB10 OFFLINE

RB11 OFFLINE

RB12 OFFLINE

Wyświetlanie informacji o definicjach przestrzeni tabel.

SQL> SELECT TABLESPACE_NAME, INITIAL_EXTENT,

NEXT_EXTENT, STATUS, CONTENTS

FROM dba_tablespaces

TABLESPACE_NAME INITIAL_EX NEXT_EXTEN STATUS CONTENTS

------------------------------ ---------- ---------- --------- ---------

SYSTEM 10240 10240 ONLINE PERMANENT

USER_DATA 10240 10240 ONLINE PERMANENT

ROLLBACK_DATA 10240 10240 ONLINE PERMANENT

TEMPORARY_DATA 10240 10240 ONLINE PERMANENT

REPOSITORY_DATA 10240 10240 ONLINE PERMANENT

INDEX_DATA 10240 10240 ONLINE PERMANENT

Wyświetlanie nazw plików danych należących do naszej bazy danych.

SQL> SELECT file_name, file_id, tablespace_name, bytes, blocks, status

FROM dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS

----------------------------- --- ----------------- ---------- ------ ---------

D:\ORANT\DATABASE\USR1ORCL.ORA 2 USER_DATA 115343360 56320 AVAILABLE

D:\ORANT\DATABASE\RBS1ORCL.ORA 3 ROLLBACK_DATA 36700160 17920 AVAILABLE

D:\ORANT\DATABASE\TMP1ORCL.ORA 4 TEMPORARY_DATA 17825792 8704 AVAILABLE

D:\ORANT\DATABASE\SYS1ORCL.ORA 1 SYSTEM 167772160 81920 AVAILABLE

D:\ORANT\DATABASE\REPORCL.ORA 5 REPOSITORY_DATA 2097152 1024 AVAILABLE

D:\ORANT\DATABASE\IND1ORCL.ORA 6 INDEX_DATA 55267328 26986 AVAILABLE

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Wyświetlanie informacji o nieużywanych obszarach w bazie danych.

SQL> SELECT * FROM dba_free_space;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS

------------------------------ ---------- ---------- ---------- ----------

SYSTEM 1 75101 40960 20

SYSTEM 1 49111 61440 30

SYSTEM 1 77666 8714240 4255

SYSTEM 1 70531 40960 20

USER_DATA 2 19822 204800 100

USER_DATA 2 5392 112640 55

USER_DATA 2 10632 112640 55

USER_DATA 2 31913 81920 40

ROLLBACK_DATA 3 16186 3553280 1735

TEMPORARY_DATA 4 1861 14016512 6844

REPOSITORY_DATA 5 312 30720 15

INDEX_DATA 6 26964 47104 23

Podsumowanie

Bazę danych tworzymy w 8 krokach:

Zaprojektowanie struktury bazy danych optymalizującej efektywność i minimalizującej fragmentację.
Przygotowanie środowiska systemu operacyjnego do tworzenia bazy danych.
Utworzenie pliku parametrów.
Wystartowanie instancji.
Wykonanie polecenia CREATE DATABASE.
Zapewnienie bezpieczeństwa bazy danych przez zwielokrotnienie plików dziennika powtórzeń i plików kontrolnych.
Utworzenie pliku haseł.
Zdefiniowanie tabel i perspektyw słownika danych do zarządzania bazą danych.

0x01 graphic

Zadania

Obejrzyj perspektywę V$CONTROLFILE i sprawdź, ile masz plików kontrolnych

Dodaj nowy plik kontrolny (ctl2adm#.ora) i sprawdź, czy jest używany

Napisz polecenia dodające do każdej grupy po jednym pliku dziennika powtórzeń (e:\kurs\database\db#\lg3adm#.ora i e:\kurs\database\db#\lg4adm#.ora) i spraw, by obydwa nowe pliki zostały użyte

Utwórz nowego użytkownika ADMIN/ADMIN poleceniem CREATE USER ADMIN IDENTIFIED BY ADMIN i nadaj mu uprawnienia SYSDBA i SYSOPER poleceniem GRANT SYSDBA, SYSOPER TO ADMIN. Co się stało?

Zmień ustawienia w pliku parametrów tak, aby można było nadać użytkownikowi ADMIN uprawnienie SYSDBA i SYSOPER i zrestartuj bazę. Spróbuj ponownie nadać uprawnienia.

Sprawdź perspektywę V$PWFILE_USERS, aby sprawdzić czy nadanie praw się powiodło

Obejrzyj perspektywę DICTIONARY

Obejrzyj perspektywę DBA_TABLESPACES, DBA_DATA_FILES i DBA_FREE_SPACE. O czym świadczy wynik zapytania na DBA_FREE_SPACE?

IV. Wybieranie i aktualizacja danych

Cele

W tej lekcji wyjaśniamy procesy zachodzące przy wybieraniu i aktualizacji danych oraz szczegółowy przebieg operacji SQL.

Po przerobieniu tej lekcji powinieneś potrafić:

Wyjaśnić kroki przetwarzania polecenia SQL.
Rozpoznać korzyści z obszaru dzielonego SQL Oracle.
Rozpoznać funkcje i zawartość buforów bazy danych.
Opisać sposób w jaki serwer pobiera dane do buforów bazy danych.
Rozpoznać zadanie procesu sekretarza bazy danych (DBWR).
Rozpoznać zdarzenia jakie powodują aktywację sekretarza bazy danych.

Przegląd

Ta lekcja wyjaśnia jak jest przetwarzane w pamięci polecenie SQL i w jaki sposób dokonuje się modyfikacji danych.

Polecenie SQL są przetwarzane przez proces serwera.
Identyczne polecenia SQL korzystają ze wspólnego obszaru SQL.
Bufory bazy danych przechowują wcześniejszy obraz zmodyfikowanych danych w blokach wycofania a późniejszy obraz zmodyfikowanych danych w blokach danych.
Sekretarz bazy danych (DBWR) zapisuje wszystkie zmienione bufory na dysk.

Bufory Bazy Danych

Kiedy użytkownik żąda dostępu do danych proces serwera sprawdza w buforach bazy danych, czy nie ma tam już odpowiednich danych.

Bloki Danych

Jeśli brak danych w buforach, proces serwera odczytuje odpowiednie bloki z pliku danych i umieszcza je w buforach bazy danych, z których każdy ma rozmiar taki jak rozmiar bloku danych Oracle (DB_BLOCK_SIZE).

Jeśli użytkownik zmodyfikuje otrzymane dane, zmiany pojawiają się w blokach danych w buforach. Nowa wersja danych nazywana jest obrazem późniejszym (after image), ponieważ jest obrazem danych po dokonaniu zmian.

Bloki Wycofania

Oracle zachowuje oryginalną wersję danych w bloku wycofania. Ta wersja danych nazywana jest obrazem wcześniejszym (before image), ponieważ zawiera zachowane dane sprzed dokonania zmian przez użytkownika. Jeśli użytkownik zmieni swoją decyzję przed zatwierdzeniem zmiany w bazie danych, transakcja może być wycofana dzięki informacji zachowanej w bloku wycofania.

Bufor Dziennika Powtórzeń

Podczas przetwarzania transakcji, proces serwera zapisuje obraz wcześniejszy i obraz późniejszy w buforze dziennika powtórzeń. Ta informacja używana jest przy odtwarzaniu.

Kolejka (Lista LRU)

Bufory bazy danych zorganizowane są w dwóch listach: lista brudna (dirty list) oraz kolejka (LRU od Last Recently Used). Lista brudna zawiera zmienione bufory, które nie zostały zapisane na dysk.

Zawartość Listy LRU

Typ bufora	Opis
Wolne bufory (free buffers)	Bufory, które nie były zmodyfikowane i są gotowe do użycia
Bufory zaczepione (pinned buffers)	Bufory obecnie używane.
Bufory brudne (dirty buffers)	Bufory zmodyfikowane, które trzeba zapisać na dysk.

Kiedy proces używa bufora, przenosi go na początek (do ostatnio używanych) listy LRU. Brudne bufory stopniowo przesuwają się na koniec (do najpóźniej używanych) listy LRU.

Żądania Użytkowników

Wszystkie polecenia SQL przetwarzane są przez proces serwera, który otrzymuje żądania wprost od procesu użytkownika. Proces serwera stosuje trzy główne fazy przetwarzania: analiza (parse), wykonanie (execute) i sprowadzanie (fetch).

Proces serwera

Kiedy proces serwera musi odczytać blok danych z dysku do bufora bazy danych to:

Przegląda listę LRU.
Poszukuje wolnego bufora.
Przesuwa brudne bufory do brudnej listy.

Proces serwera przerywa poszukiwania kiedy znajduje wolny bufor lub kiedy przejrzy określoną liczbę buforów bez natrafienia na wolny.

Konfiguracja PGA

Obszar Globalny Programu (PGA od Program Global Area) to obszar pamięci zawierający dane i informacje kontrolne pojedynczego użytkownika lub procesu serwera. PGA jest rezerwowane przez serwer Oracle, kiedy proces użytkownika podłącza się do bazy danych Oracle i jest otwierana sesja.

Charakterystyka PGA

Obszar stosu (stack space) to pamięć zarezerwowana na przechowanie zmiennych i tablic sesji.
Dane sesji użytkownika (user session data) to dodatkowa pamięć dla sesji użytkownika.
PGA jest zapisywalny i nie jest dzielony.

Przetwarzanie Polecenia SQL

Analiza (parsing) to jeden z kroków przetwarzania polecenia SQL. Podczas wykonywania polecenia SQL Oracle wykonuje:

Analizę (Parse)
Wykonanie (Execute)
Sprowadzanie (Fetch)

Analiza

Sprawdza składnię oraz poprawność semantyczną.
Przegląda słownik danych w celu rozpoznania obiektów, uprawnień i znalezienia najefektywniejszej ścieżki wyszukiwania (nazywanej drzewem analizy lub planem wykonania - parse tree lub execution plan).
Rezerwuje prywatne obszary SQL dla polecenia.

Drzewo analizy jest przechowywane w obszarze dzielonym. Wiele procesów serwera może korzystać z tego samego drzewa analizy.

Serwer Oracle wykorzystuje pewną przestrzeń pamięci zwaną kursorem do zapisu informacji o statusie każdego polecenia.

Wykonanie

Zastosowanie metod z drzewa analizy do buforów danych.
Wykonanie fizycznych lub logicznych odczytów i logicznych zapisów.
Sprawdzenie warunków integralności.
Modyfikacja danych, jeśli zachodzi taka potrzeba.

Sprowadzanie

Pobiera wiersze danych dla poleceń SELECT

Obszary Dzielone SQL

Oracle automatycznie zauważa kiedy aplikacja wysyła do bazy danych identyczne polecenia SQL. Jeżeli wykonywane są dwa identyczne polecenia, obszar SQL w którym wykonywane jest pierwsze z nich jest też wykorzystywany przez drugie.

Stąd też, zamiast używać wielu obszarów SQL dla identycznych poleceń SQL, istnieje tylko jeden obszar SQL dla identycznych poleceń SQL.

Identyczne Polecenia SQL

Polecenia SQL są uważane za identyczne jeżeli:

Ich zapis jest absolutnie identyczny, z dokładnością do małych i wielkich liter, separatorów itp.
Odwołują się do samych obiektów.
Typy i nazwy zmiennych są identyczne.

Operacja SELECT

Operacja SELECT wymaga aby blok danych używany w kryteriach wyszukiwania znajdował się w buforze bazy danych.

Operacja SELECT

Jeśli blok znajduje się w pamięci wykonuje odczyt logiczny.
Jeśli bloku nie ma w pomięci, wykonuje odczyt fizyczny.

Bloki nagłówków obiektów umieszczone w buforach bazy danych nie są umieszczane na tym i następnych diagramach, jako że nie są chwilowo dla nas istotne.

Kiedy to możliwe (to znaczy przy pełnym przeglądaniu tabel), serwer Oracle dokonuje odczytów wieloblokowych (multiblock reads) do pomięci.

Operacja UPDATE

Wszystkie operacje UPDATE wymagają segmentu wycofania do zapewnienia spójności odczytu, możliwości odtwarzania i wycofania.

Operacja Update

Ściąga bloki danych do buforów bazy danych.
Ściąga bloki wycofania do buforów bazy danych.
Nakłada blokady wyłączne na wiersze przeznaczone do modyfikacji.

Dokonuje zapisów w buforze dziennika powtórzeń identyfikujących obraz wcześniejszy.

Zapamiętuje dane wycofania w buforze bloku segmentu wycofania.
Dokonuje zmian w buforze bloku danych.
Dokonuje zapisów w buforze dziennika powtórzeń identyfikujących obraz późniejszy.

Własności Operacji UPDATE

Segment wycofania jest obiektem używanym do zapamiętywania danych wycofania.
Serwer Oracle wszystkim odczytującym dane dostarcza spójnego obrazu danych.

Operacja SELECT nazywana jest często operacją ODCZYTU. Odczytujący nie powodują blokowania zapisujących, zapisujący nie blokują odczytujących. Serwer Oracle osiąga ten efekt dzięki zastosowaniu segmentów wycofania.

Proces Sekretarza Bazy Danych (DBWR)

DBWR zarządza buforami bazy danych w ten sposób, aby procesy serwera zawsze mogły znaleźć wolne bufory.

Proces DBWR

Zapisuje wszystkie zmodyfikowane bufory do plików danych.
Używa algorytmu LRU, aby ostatnio używane bloki pozostawały w pamięci.
Optymalizuje I/O dzięki opóźnianiu zapisów.

DBWR zapisuje brudne bufory na dysku kiedy:

Brudna lista osiągnie limit długości.
Proces przeszuka wskazaną liczbę buforów na liście LRU bez znalezienia wolnego bufora.
Wystąpi time-out procesu DBWR
Wystąpi punkt kontrolny.

Podsumowanie

Podczas odczytywania i modyfikacji danych:

Wszystkie polecenia SQL są przetwarzane przez proces serwera, który otrzymuje żądania bezpośrednio od procesu użytkownika.
Kroki przetwarzania polecenia SQL to analiza, wykonanie i sprawdzanie.
Identyczne polecenia SQL wykorzystują ten sam obszar dzielony SQL.
Odczytywane oraz niezatwierdzone zmodyfikowane dane przechowywane są w blokach danych blokach wycofania w buforach bazy danych w pamięci.
Bloki wycofania przechowują obraz wcześniejszy modyfikowanych danych używany podczas odtwarzania.
Podstawowym zadaniem DBWR jest zapis wszystkich zmienionych buforów do pliku danych.

V. Zapewnianie spójności i współbieżności transakcji

Cele

Ta lekcja wyjaśnia zasady rejestrowania danych i obsługi współbieżnego dostępu do danych z zachowaniem spójności odczytu na poziomie polecenia.

Po przerobieniu tej lekcji powinieneś:

Rozpoznawać rolę Sekretarza dziennika (LGWR) w rejestrowaniu zatwierdzonych transakcji w plikach dziennika powtórzeń.
Identyfikować składniki i przeznaczenie dziennika powtórzeń.
Rozumieć cel punktów kontrolnych.
Rozumieć jak Oracle zarządza współbieżnym dostępem do danych i spójnością odczytu na poziomie polecenia.

Przegląd

W bazie danych Oracle dane są zawsze najpierw tworzone i modyfikowane w pamięci a następnie zapisywane na trwałe w plikach danych.

Ponieważ użytkownicy tworzą i aktualizują dane w buforach bazy danych, Sekretarz Bazy Danych, proces DBWR, okresowo zapisuje zmienione bloki w bezpieczne miejsce do pliku danych. DBWR dokonuje zapisów często, ale nie za każdym razem, kiedy użytkownik zatwierdza transakcję. Stąd też, dane zatwierdzone przez użytkownika mogą pozostawać w buforach bazy danych, gdzie są narażone na awarię instancji, co powoduje utratę zapisów znajdujących się w pamięci.

Mechanizm zabezpieczenia danych w buforach bazy danych stanowią pliki dziennika powtórzeń. Pliki dziennika powtórzeń przechowują wszystkie zmiany dokonane na danych buforach bazy danych.

Transakcje: Przegląd

Serwer Oracle traktuje sekwencję poleceń SQL jako pojedynczą logiczną jednostkę pracy, nazywaną transakcją.

Kiedy Zaczyna się Transakcja?

Na początku sesji.
Na końcu poprzedniej transakcji.

Kiedy Kończy się Transakcja?

Zatwierdzenie transakcji

Wydano polecenie COMMIT
Polecenie SQL spowodowało niejawne zatwierdzenie.
Poprawne wyjście z programu użytkownika z odłączeniem od serwera Oracle.

Przerwanie transakcji

Wydano polecenie ROLLBACK
Zakończenie na żądanie użytkownika
Awaryjne wyjście z programu użytkownika bez odłączenia się od serwera Oracle.
Awaria procesora.
Awaria dysku.

Zakończenie transakcji może być jawne, wymuszone przez polecenia COMMIT lub ROLLBACK albo niejawne, spowodowane przez inną operację. Wśród poleceń SQL, które powodują zatwierdzenie znajdują się polecenia DDL (Data Definition Language) takie jak CREATE, ALTER czy DROP oraz polecenia DCL (Data Control Language) takie jak GRANT czy REVOKE.

Transakcje w Pamięci

W bazie danych Oracle dane są zawsze najpierw tworzone i modyfikowane w pamięci a następnie zapisywane na trwałe w plikach danych.

Dziennik Transakcji

Serwer Oracle zapisuje wszystkie dokonane w bazie danych zmiany w buforze dziennika powtórzeń. Jeden z procesów tła Sekretarz Dziennika (LGWR od Log Writer) zapisuje informacje z bufora dziennika powtórzeń na dysk. Inny proces tła, Archiwizator (ARCH) może być uruchomiony do archiwizowania informacji dziennika powtórzeń.

Rejestracja prowadzona jest w celu umożliwienia odtwarzania.
Dziennik powtórzeń zapisuje zmiany dokonane przez transakcję.
Bufor dziennika powtórzeń jest buforem cyklicznym (okrężnym) zawierającym informację o zmianach dokonanych w bazie danych.

Bufor Dziennika Powtórzeń

Zapisuje wszystkie zmiany dokonane w bazie danych.
Kiedy wymagane jest odtwarzanie, potrafi zrekonstruować zmiany dokonane w bazie danych i zapisy w segmentach wycofania.
Może zostać pominięty przez użycie słowa kluczowego UNRECOVERABLE w poleceniach CREATE TABLE oraz CREATE INDEX.
Może pozostać pominięty przez narzędzie ładowania danych Oracle (Oracle Loader).

Zadania Plików Dziennika Powtórzeń

Mechanizm ochrony danych w buforach bazy danych to pliki dziennika powtórzeń.

Zawierają one zapis wszystkich zmian dokonanych w danych buforach bazy danych.

Jeżeli nastąpi awaria instancji, pliki dziennika powtórzeń są wykorzystywane do odtworzenia zmodyfikowanych danych, jakie znajdowały się w pamięci. Te pliki używane są tylko do tego celu.

Proces Sekretarza Dziennika (LGWR)

Proces Sekretarza Dziennika (LGWR od Log Writer) zapisuje bufor dziennika powtórzeń na dysk.

LGWR zapisuje pozycje z bufora dziennika powtórzeń do plików dziennika powtórzeń kiedy:

Wystąpi zdarzenie zatwierdzenia transakcji.
Bufor dziennika powtórzeń zostanie zapełniony w jednej trzeciej.
DBWR zakończy oczyszczanie buforów bloków przy punkcie kontrolnym.
Wystąpi time-out procesu LGWR.

Własności Procesu LGWR

W instancji zawsze istnieje dokładnie jeden sekretarz dziennika.
Potwierdzenie dokonania zatwierdzenia nie jest wysyłane zanim transakcja nie zostanie zapisana w pliku dziennika powtórzeń.
Zatwierdzania wykonywane przez użytkowników mogą zapisać pozycje dziennika innych użytkowników zanim sami postanowią to wymusić dokonując zatwierdzenia. W ten sposób uzyskuje się średnią poniżej jednej operacji I/O na zatwierdzenie.
Podczas długotrwałych transakcji obszar bufora dziennika powtórzeń może wypełnić się bardziej niż w jednej trzeciej zanim LGWR dokona zapisu do pliku dziennika powtórzeń.

Zatwierdzenia w Bazie Danych

Przez zatwierdzanie transakcji dokonane zmiany stają się trwałe.

Operacja COMMIT

Użytkownik wysyła COMMIT.
LGWR zapisuje bufor dziennika powtórzeń do bieżącej grupy dziennika.
Użytkownik otrzymuje komunikat, że transakcja została zatwierdzona.
Blokady zasobów dotyczące bloków danych i bloków wycofania są zdejmowane.
DBWR ewentualnie zapisze bloki bazy danych na dysk (w tym także bloki segmentów wycofania i bloki indeksów).

Własności COMMIT

LGWR zapisuje na trwałe transakcje pozwalając DBWR na opóźnienie zapisów co z kolei pozwala na zredukowanie czasu I/O wymaganego na fizyczny zapis zmodyfikowanych w pamięci buforów bloków bazy danych.
Zapisy dziennika powtórzeń zawierające zmienione bajty i informację identyfikującą są krótkie.
Zatwierdzenia w bazie danych zapisują w pojedynczej operacji pozycje dziennika powtórzeń z wielu transakcji żądających zatwierdzenia w tym samym czasie.
Jeżeli nie wystąpi przepełnienie bufora dziennika powtórzeń, wymagany jest tylko jeden synchroniczny zapis na transakcję (średnio może być nawet mniej).
Rozmiar transakcji nie ma wpływu na czas oczekiwania na wykonanie bieżącego polecenia COMMIT.
Zapis, jeżeli to możliwe, jest wieloblokowy.

Przełączanie Dziennika, Numer Sekwencyjny Dziennika

Przełączanie Dziennika (Log Switches)

Przełączenie dziennika następuje kiedy LGWR kończy zapisywać jedną grupę dziennika powtórzeń i rozpoczyna zapisy w drugiej.
Przełączenie dziennika ma miejsce gdy LGWR zapełnił jedną grupę plików dziennika.
Przy przełączeniu dziennika, bieżącej grupie dziennika powtórzeń zostaje przypisany numer sekwencyjny dziennika (log sequence number) identyfikujący informację zapisaną w tej grupie dziennika powtórzeń i używany później do synchronizacji.
Przełączenie dziennika może być wymuszone przez DBA poleceniem ALTER SYSTEM SWITCH LOGFILE.
Przy przełączeniu dziennika automatycznie pojawia się punkt kontrolny.
Przetwarzanie może trwać dopóki dostępny jest przynajmniej jeden z elementów grupy. Po stwierdzeniu zniszczenia lub braku dostępu do elementu, do pliku śladu LGWR i do pliku alertów wpisywany jest komunikat.

Numer Sekwencyjny Dziennika (Log Sequence Number)

Każdy plik dziennika powtórzeń jest jednoznacznie identyfikowany przez jego numer sekwencyjny dziennika.
Podczas odtwarzania Oracle stosuje odpowiednie pliki dziennika powtórzeń według wzrastającego numeru sekwencyjnego dziennika.
Bieżący numer sekwencyjny dziennika jest zapisany w pliku kontrolnym.

Punkty Kontrolne (Checkpoints)

Podczas punktu kontrolnego DBWR zapisuje wszystkie brudne bufory z buforów bazy danych na dysk, przez co gwarantuje, że wszystkie zmodyfikowane od czasu ostatniego punktu kontrolnego bloki danych będą zapisane na dysku.

Kiedy Pojawia się Punkt Kontrolny?

Przy każdym przełączeniu dziennika (obowiązkowo).
Po określonej liczbie sekund od ostatniego punktu kontrolnego (parametr LOG_CHECKPOINT_TIMEOUT)
Kiedy określona liczba bloków dziennika powtórzeń zostanie zapisana na dysk od ostatniego punktu kontrolnego (parametr LOG_CHECPOINT_INTERVAL).
Przy zamykaniu instancji (chyba, że z opcją ABORT).
Jeżeli jest wymuszony przez DBA (ALTER SYSTEM CHECKPOINT).
Kiedy wyłączana (przełączana w stan offine) jest przestrzeń tabel a przynajmniej jeden z jej plików jest w stanie online.

Synchronizacja

Przy każdym punkcie kontrolnym modyfikowany jest numer sekwencyjny punktu kontrolnego w nagłówkach każdego z plików bazy danych i w pliku kontrolnym.

Plik kontrolny w chwili uruchomienia instancji sprawdza czy wszystkie pliki mają ten sam numer sekwencyjny punktu kontrolnego.

Parametr LOG_CHECKPOINT_TIMEOUT określa odstęp czasu między kolejnymi punktami kontrolnymi.

Parametr LOG_CHECKPOINT_INTERVAL określa liczbę nowozapisanych bloków pliku dziennika powtórzeń, jaka powoduje inicjalizację punktu kontrolnego.

Proces Punktów Kontrolnych (CKPT)

Punkty kontrolne bazy danych zapewniają, że wszystkie zmodyfikowane bufory bazy danych zostaną zapisane do plików bazy danych. Pliki bazy danych są oznaczone jako pochodzące z bieżącej chwili, punkt kontrolny odnotowany jest w pliku kontrolnym.

Punkty Kontrolne

DBA może wymusić punkt kontrolny poleceniem ALTER SYSTEM CHECKPOINT.

Proces CKPT

Proces punktów kontrolnych jest uruchamiany przez ustawienie parametru CHECKPOINT_PROCESS = TRUE. Wartością domyślną jest FALSE.
Jeśli jest uruchomiony, proces ten przejmuje zadania LGWR dotyczące modyfikacji plików w momencie punktu kontrolnego.
Po zakończeniu punktu kontrolnego aktualizowane są nagłówki plików danych.
Częstsze punkty kontrolne zmniejszają czas potrzebny na odtwarzanie w przypadku awarii instancji, ale mogą zmniejszać efektywność.
Proces CKPT poprawia efektywność baz danych posiadających wiele plików danych.

Proces Archiwizatora (ARCH)

Proces Archiwizatora (ARCH) kopiuje na bieżąco pliki dziennika powtórzeń na wskazane urządzenie za każdym razem kiedy LGWR przełącza się na inną grupę.

Proces Archiwizatora

Kopiuje pliki dziennika powtórzeń na taśmę lub dysk dla zapewnienia możliwości odtwarzania po awarii nośnika.
Aktywowany tylko po wystąpieniu przełączenia dziennika.
Jest opcjonalny, potrzebny tylko w trybie ARCHIVELOG.
Może zapisywać na taśmę lub na dysk.

0x01 graphic

Spójność Odczytu

W czasie przetwarzania polecenia serwera Oracle posługuje się spójnym obrazem danych tabeli z chwili rozpoczęcia polecenia.

Zasada spójności odczytu stanowi, że dane jakie ogląda lub zmienia użytkownik muszą pozostawać niezmienione dopóki użytkownik nie zakończy ich przetwarzania.

Niezatwierdzone zmiany powinny być widoczne tylko dla użytkownika, który dokonał zmian. Użytkownik wykonujący zapytanie powinien widzieć tylko dane zatwierdzone przed rozpoczęciem zapytania (domyślnie) lub zatwierdzone przed rozpoczęciem transakcji (wymuszane poleceniem SET TRANSACTION READ ONLY).

Przykład Wykorzystania Modułu Spójności Odczytu

Model spójności odczytu wykorzystywany przy podzapytaniach skorelowanych.

SQL> SELECT first_name, dept_id, salary

2> FROM s_emp

3> WHERE salary>(SELECT AVG (salary) FROM s_emp

4> WHERE b.dept_id = a.dept_id;

Serwer gwarantuje, że w trakcie przetwarzania powyższego zapytania widoczne będą te same wartości, nawet jeśli inny użytkownik zatwierdzi jednocześnie zmiany w S_EMP.

Serwer Oracle zwróci zatwierdzone dane lub zmiany dokonane wcześniej przez bieżącego użytkownika, jak to ilustruje następujący przykład:

Czas	Użytkownik A	Użytkownik B
0	UPDATE_emp....
1		SELECT...FROM s_emp b
2	COMMIT
3		SELECT...FROM s_emp a

Model Blokowania

Serwer Oracle pozwala na współbieżny dostęp do danych zapewniając integralność danych przez blokady danych i słownika danych.

Model Blokowania

Odczytujący nie blokują zapisujących.
Zapisujący nie blokują odczytujących.
Spójność odczytu jest zapewniona przez użycie zapisów w segmentach wycofania.
Zapewnione jest blokowanie na poziomie wiersza (domyślnie) a także na poziomie tabel.
Blokowanie jest automatyczne, może też być wymuszane jawnie.
Można jawnie zażądać blokad dzielonych i blokad wyłączonych.
Blokowanie na poziomie wiersza jest dostępne dla każdego wiersza w bazie danych.
Serwer Oracle nigdy nie zwiększa poziomu blokady (na przykład z poziomu wiersza do poziomu strony czy tabeli).

Podsumowanie

DBA powinien rozumieć zasady działania bufora dziennika powtórzeń, procesu Sekretarza Dziennika i Archiwizatora:

Serwer Oracle zapisuje wszystkie dokonane w bazie danych zmiany do bufora dziennika powtórzeń.
Pliki dziennika powtórzeń używane są do odtwarzania w strukturach pamięci zmodyfikowanych danych po awarii instancji.
LGWR zapisuje pozycje dziennika powtórzeń na dysk.
Punkty kontrolne zapewniają, że wszystkie zmodyfikowane bufory bazy danych zostaną zapisane do plików bazy danych.
Proces ARCH kopiuje na bieżąco pliki dziennika powtórzeń na wskazane urządzenie pamięci masowej przy każdym przełączeniu się LGWR na nową grupę dziennika powtórzeń.
Odczytujący nie blokują zapisujących.
Zapisujący nie blokują odczytujących.

VI. Zarządzanie strukturą bazy danych

Cele

Tematem tej lekcji są informacje o dopasowaniu bazy danych

Po przerobieniu tej lekcji powinieneś być w stanie:

Wyjaśnić zasady przydziału przestrzeni w bazie danych.
Dopasować strukturę bazy danych.
Przygotować potrzebne przestrzenie tabel.
Opisać różne typy segmentów.

Przegląd

Serwer Oracle przydziela przestrzeń bazy danych dla wszystkich danych w bazie.

Definicje Obiektów

Termin	Definicja
Baza danych	Logiczny zbiór danych dzielonych składowanych w przestrzeniach tabel.
Plik	Fizyczny plik danych należący do określonej przestrzeni tabel.
Przestrzeń Tabel	Logiczny skład na fizycznie zgrupowane dane.
Segment	Zbiór jednego lub wielu ekstentów zawierających wszystkie dane określonego obiektu w ramach przestrzeni tabel.
Ekstent	Zbiór spójnych (sąsiednich) bloków w pliku danych.
Blok	Wielokrotność fizycznych bloków pliku zarezerwowana w istniejącym pliku danych.

0x01 graphic

Przydział Przestrzeni

Przestrzeń jest zarezerwowana podczas tworzenia obiektu i później, kiedy obiekt rośnie.
Przestrzeń jest przydzielana w spójnych ciągach bloków bazy danych nazywanych ekstentami.
Obiekty bazy danych wykorzystujące przestrzeń przechowywane są w pojedynczej przestrzeni tabel przez cały czas swojego istnienia.
Pliki danych należą do pojedynczej przestrzeni tabel.

Struktura Logiczna Bazy Danych

Rozpatrując bazę danych z logicznej perspektywy łatwiej można zrozumieć zasady przydziału przestrzeni serwera Oracle.

Baza danych Oracle dzieli się logicznie na osobne przestrzenie tabel.

Przestrzeń tabel SYSTEM jest konieczna do pracy bazy danych.

Przestrzenie tabel zawierają segmenty bazy danych.

Przestrzenie Tabel

Baza danych Oracle dzieli się na mniejsze logiczne fragmenty przestrzeni nazywane przestrzeniami tabel (ang. TABLESPACEs).

Przestrzenie Tabel

Każda przestrzeń tabel składa się z jednego lub wielu plików systemu operacyjnego.
Przestrzenie tabel mogą być włączane w trakcie pracy bazy danych.
Przestrzenie tabel (z wyjątkiem przestrzeni tabel SYSTEM i przestrzeni tabel zawierających aktywny segment wycofania) mogą być wyłączane bez przerywania pracy bazy danych.
Status przestrzeni tabel może być przełączany pomiędzy tylko-do-odczytu i odczyt-zapis.
Obiekty utworzone w przestrzeni tabel nigdy nie mogą rezerwować przestrzeni spoza ich oryginalnej przestrzeni tabel.

Zastosowanie Przestrzeni Tabel

Kontrola przydziału przestrzeni i ograniczenia ilościowe przestrzeni dla użytkowników.
Kontrola dostępu do danych przez indywidualne włączenia/wyłączenie przestrzeni tabel.
Rozproszenie składowania danych na urządzenia w celu poprawienia efektywności I/O i minimalizacji rywalizacji o pojedynczy dysk.
Wykonywanie operacji częściowej archiwizacji i częściowego odtwarzania.
Utrzymywanie wielkich ilości danych statycznych na urządzeniach tylko-do-odczytu.

Baza danych składa się z co najmniej jednej przestrzeni tabel SYSTEM. Dodatkowe przestrzenie tabel dodaje się w celu zwiększenia możliwości kontroli i długoterminowej obsługi.

Ogólnie, serwer Oracle rozróżnia dwa typy przestrzeni tabel składające się na bazę danych: przestrzeń systemowa i przestrzenie niesystemowe.

Przestrzeń tabel SYSTEM

Wymagana do uruchomienia każdej bazy danych.
Zawiera informacje słownika danych, definicje pamiętanych procedur, pakietów i wyzwalaczy bazy danych.
Zawiera segment wycofania SYSTEM.
Może, choć nie powinna, zawierać dane użytkowników.

Niesystemowe Przestrzenie Tabel

Pozwalają na bardziej elastyczną administrację bazą danych.
Zawierają segmenty wycofania, segmenty tymczasowe, dane aplikacji, dane indeksów i przestrzeń użytkowników.

Przestrzeń tabel dla segmentów wycofania

Jest niesystemową przestrzenią tabel o specjalnym przeznaczeniu.
Jeśli zawiera używany na bieżąco segment wycofania (status in-use),nie może być wyłączona ani też przełączona w tryb tylko-do-odczytu. Musi być odtwarzana tak jak przestrzeń tabel SYSTEM, to znaczy przy wyłączonej w celu odtworzenia całej bazie danych.

Fizyczna Struktura Bazy Danych

Całkowita wielkość fizycznej przestrzeni rezerwowanej dla obiektów bazy danych Oracle zależy od rozmiaru plików systemu operacyjnego utworzonych na potrzeby każdej przestrzeni tabel.

Opis

Każda logiczna przestrzeń tabel fizycznie zbudowana jest z jednego lub wielu plików systemu operacyjnego.
Każdy segment (np. segment danych) może być rozłożony na wielu plikach, pod warunkiem, że pliki te należą do tej samej przestrzeni tabel.

0x01 graphic

Tworzenie Przestrzeni Tabel

Przestrzeń tabel możemy utworzyć za pomocą polecenia CREATE TABLESPACE.

Składnia:

0x01 graphic

gdzie:

przestrzeń_tabel to nazwa przestrzeni tabel jaka ma być utworzona.

spec_pliku jest nazwą pliku danych.

Nazwa pliku jest nazwą pliku danych.

SIZE określa rozmiar pliku w bajtach (można też użyć jednostek K lub M).

REUSE pozwala aby serwer Oracle wykorzystał istniejący plik.

DATAFILE specyfikuje plik lub pliki danych z jakich ma się składać przestrzeń tabel.

DEFAULT STORAGE określa domyślne parametry zarządzania przestrzenią dla wszystkich obiektów tworzonych w tej przestrzeni tabel.

ONLINE sprawia, że przestrzeń tabel jest dostępna użytkownikom posiadającym do niej uprawnienia niezwłocznie po utworzeniu.

OFFLINE sprawia, że przestrzeń tabel nie jest dostępna natychmiast po utworzeniu.

PERMANENT wskazuje, że przestrzeń tabel będzie używana do przechowywania trwałych obiektów. Tak też jest domyślnie.

TEMPORARY wskazuje, że przestrzeń tabel będzie używana do przechowywania obiektów tymczasowych. Na przykład segmentów używanych przez niejawne sortowanie przy realizacji klauzuli ORDER BY.

0x01 graphic

Wskazówki

Jeśli pominiemy opcje ONLINE oraz OFFLINE, serwer Oracle domyślnie utworzy przestrzeń tabel włączoną. Perspektywa słownika danych DBA_TABLESPACES wskazuje status każdej przestrzeni tabel.
Jeżeli w specyfikacji pliku użyjemy REUSE, plik taki musi już istnieć. W przeciwnym przypadku wymagana jest opcja SIZE, a plik istnieć nie może.

Składnia DEFAULT STORAGE:

0x01 graphic

gdzie:

INITIAL określa rozmiar (w bajtach) pierwszego ekstentu rezerwowanego dla obiektu.

NEXT określa rozmiar (w bajtach) następnego ekstentu rezerwowanego dla obiektu.

MINEXTENTS określa całkowitą liczbę ekstentów przydzielaną podczas tworzenia obiektu.

MAXEXTENTS określa całkowitą liczbę ekstentów (wliczając też pierwszy), jaka może być przydzielona dla obiektu.

PCTINCREASE określa procent o który każdy następny (po drugim) ekstent będzie większy w stosunku do poprzedniego.

0x01 graphic

Wskazówki:

Wartości INITIAL i NEXT są zaokrąglane do następnej wielokrotności rozmiaru bloku danych (wskazywanego przez parametr inicjalizacji DB_BLOCK_SIZE).

Pierwszy blok ekstentu INITIAL jest traktowany jako blok nagłówka segmentu.

Przykład

Tworzenie przestrzeni tabel RBS z pojedynczym plikiem o nazwie rbs01.dbf i rozmiarze 3MB. Przestrzeń tabel ma być natychmiast gotowa do użycia.

SQL>CREATE TABLESPACE rbs

2> DATAFILE `/u01/Oracle/D/rbs 01.dbf ' SIZE 3M

3> DEFAULT STORAGE (INITIAL 50K NEXT 50K

4> MINEXTENTS 10 MAXEXTENTS 121

5> PCTINCREASE 0) ;

PCTINCREASE ma domyślną wartość 50, lecz zwykle zmieniamy ją na 0 dla lepszej kontroli wzrostu segmentu.

Modyfikacja Przestrzeni Tabel

Przestrzeń tabel modyfikujemy, aby zmienić jej domyślne parametry zarządzania przestrzenią, w celu jej wyłączenia lub wyłączenia, dodania kolejnych plików danych, zmiany nazwy istniejących plików danych, przełączania trybu między odczyt-zapis oraz tylko-do-odczytu, lub też w celu wykonania kopii zapasowej.

Dokonujemy tego za pomocą polecenia ALTER TABLESPACE.

0x01 graphic

Składnia:

0x01 graphic

gdzie:

przestrzeń_tabel określa nazwę przestrzeni tabel jaka ma być zmieniona.

ADD DATAFILE dodaje wyspecyfikowany plik do przestrzeni tabel.

RENAME DATAFILE zmienia nazwę jednego lub wielu plików przestrzeni tabel.

DEFAULT STORAGE określa nowe domyślne parametry zarządzania przestrzenią dla obiektów tworzonych następnie w przestrzeni tabel

ONLINE włącza przestrzeń tabel (status online)

OFFLINE wyłącza przestrzeń tabel (status offline).

NORMAL wykonuje punkt kontrolny na wszystkich plikach danych przestrzeni tabel.

TEMPORARY wykonuje punkt kontrolny jedynie na włączonych plikach danych przestrzeni tabel.

IMMEDIATE nie upewnia się, że pliki danych są dostępne i nie wykonuje punktu kontrolnego.

BEGIN BACKUP przygotowuje przestrzeń tabel do archiwizacji online.

END BACKUP przywraca normalny status przestrzeni tabel.

READ WRITE pozwala na tworzenie, modyfikacje i usunięcia obiektów

READ ONLY zapobiega zmianom.

Usuwanie Przestrzeni Tabel

Kiedy przestrzeń tabel i jej zawartość nie jest już potrzebna, możemy usunąć ją z bazy danych posługując poleceniem DROP TABLESPACE.

Składnia:

0x01 graphic

gdzie:

przestrzeń_tabel określa nazwę usuwanej przestrzeni tabel.

INCLUDING CONTENTS usuwa całą zawartość przestrzeni tabel.

CASCADE CONSTRAINTS usuwa wszystkie referencyjne warunki integralności z tabel spoza usuwanej przestrzeni, które odnoszą się do kluczy głównych lub unikalnych tabel z usuwanej przestrzeni.

Usuwanie Przestrzeni Tabel

Przestrzeń tabel w której ciągle znajdują się dane może być usunięta tylko z opcją INCLUDING CONTENTS.
Kiedy przestrzeń tabel zostanie usunięta, jej danych nie ma już w bazie danych.
Podczas usuwania przestrzeni tabel likwidowane są jedynie wskaźniki do plików w pliku kontrolnym bazy danych. Jednak pliki bazy danych ciągle istnieją i muszą być jawnie usunięte z poziomu systemu operacyjnego.
Przestrzeń tabel, podobnie jak jej segmenty, może być usunięta nawet jeśli jest w trybie tylko-do-odczytu. Jest to spowodowane tym, że polecenie DROP modyfikuje jedynie słownik danych (który musi być w trybie odczyt-zapis) a nie fizyczne pliki z których składa się przestrzeń tabel.

Tymczasowe Przestrzenie Tabel

Wszystkie operacje sortowania wykonywane w instancji używają wspólnego segmentu sortowania (sort segment) z tymczasowej przestrzeni tabel.

Segment tymczasowy:

Nie może zawierać żadnego trwałego obiektu.
Jest zarządzany przez DBA.

Aby wskazać, czy przestrzeń tabel jest tymczasowa, używamy polecenia CREATE TABLESPACE lub ALTER TABLESPACE z odpowiednią opcją.

Składnia

0x01 graphic

gdzie:

PERNAMENT wskazuje, że przestrzeń tabel będzie używana do przechowywania obiektów trwałych. Jest to opcja domyślna.

TEMPORARY wskazuje, że przestrzeń tabel będzie używana jedynie do przechowywania obiektów tymczasowych, na przykład segmentów używanych przy przetwarzaniu klauzuli ORDER BY.

0x01 graphic

Uwaga: Przydział i zwolnienie przestrzeni w segmencie sortowania tymczasowej przestrzeni tabel możemy obserwować w perspektywie V$SORT_SEGMENTS.

Zmiana Rozmiaru Plików Danych

Rozmiar pliku danych może zmieniany automatycznie jeśli użyjemy opcji AUTOEXTEND lub ręcznie za pomocą polecenia ALTER DATABASE.

Automatyczna zmiana Plików Danych

Polecenie AUTOEXTEND włącza lub wyłącza automatyczne rozszerzanie plików danych. Opcję automatycznego rozszerzania pliku możemy podać przy specyfikacji plików następujących poleceniach SQL.

CREATE DATABASE
ALTER DATABASE
CREATE TABLESPACE
ALTER TABLESPACE

Składnia Opcji Autoextend

Składnia:

0x01 graphic

gdzie:

OFF wyłącz automatyczne rozszerzanie plików danych. NEXT i MAXSIZE są ustawione na zero. Wartości te muszą być ponownie podane w kolejnych poleceniach ALTER TABLESPACE AUTOEXTEND.

ON włącza automatyczne rozszerzanie plików danych.

NEXT przestrzeń dysku przydzielana do pliku kiedy potrzeba więcej ekstentów.

MAXSIZE maksymalna przestrzeń dysku dopuszczalna dla tego pliku danych.

UNLIMITED znosi limity przydziału przestrzeni dysku dla pliku danych.

Przykłady

Dodanie nowego pliku do przestrzeni tabel USERS.

SVRMGR> ALTER TABLESPACE users

2> ADD DATAFILE `users02' SIZE 1OM

3> AUTOEXTEND ON

4> NEXT 521K

5> MAXSIZE 250M;

Włączenie automatycznego rozszerzenia istniejącego pliku danych za pomocą polecenia ALTER DATABASE.

SVRMGR> ALTER DATABASE DATAFILE `users 02'

2> AUTOEXTEND ON NEXT 1M MAXSIZE 100M;

Ręczna Zmiana Rozmiaru Plików Danych

Zwiększamy lub zmniejszamy rozmiar pliku danych jawnie za pomocą polecenia ALTER DATABASE.

Ponieważ możemy zmienić rozmiar pliku danych, możemy dodać więcej przestrzeni w bazie danych bez tworzenia dodatkowych plików. Możemy także poprawić błędy szacowania wymagań przestrzeni żądając zwolnienia nieużywanej przestrzeni bazy danych.

Przykład

Zmiana rozmiaru pliku users02.

SVRMGR> ALTER DATABASE DATAFILE `users02'

2> RESIZE 100M;

Parametr COMPATIBLE musi być ustawiony na wersję 7.2.0 lub wyższą.

Przestrzenie Tabel Tylko-Do-Odczytu

Zanim DBA przełączy przestrzeń tabel w stan tylko-do-odczytu, muszą być spełnione następujące warunki (dobrze jest w tym celu uruchomić instancję w trybie restricted):

Przestrzeń tabel musi być włączona (online).
Nie może być aktywnych transakcji.
Przestrzeń tabel nie może zawierać aktywnego segmentu wycofania.
Przestrzeń tabel nie może aktualnie podlegać archiwizacji online.

Zaleca się usunięcie segmentów wycofania przed przełączeniem przestrzeni tabel na tylko-do-odczytu. Z tego też powodu przestrzeń tabel SYSTEM nigdy nie może stać się przestrzenią tabel tylko-do-odczytu.

Przykłady

Zmiana przestrzeni tabel na tylko-do-odczytu za pomocą polecenia ALTER TABLESPACE.

SQL> ALTER TABLESPACE user_tablespace READ ONLY

Zmiana przestrzeni tabel na odczyt-zapis za pomocą polecenia ALTER TABLESPACE.

SQL> ALTER TABLESPACE user_tablespace READ WRITE;

Aby przestrzeń tabel przywrócić w stan odczyt_zapis, wszystkie pliki danych przestrzeni tabel muszą być online. W tym calu można posłużyć się opcją DATAFILE ONLINE polecenia ALTER DATABASE. Bieżący status plików danych sprawdzamy w perspektywie V$DATAFILE lub DBA_DATA_FILES.

Zasady Zarządzania Przestrzeniami Tabel

Istnieją trzy zasady zarządzania przestrzeniami tabel.

Używanie Wielu Przestrzeni Tabel

Użycie wielu przestrzeni tabel pozwala na większą elastyczność w wykonywaniu operacji na bazie danych.

Oddzielaj dane użytkowników od danych słownika danych.
Oddzielaj dane różnych aplikacji.
Umieszczaj różne pliki przestrzeni tabel na oddzielnych urządzeniach dyskowych aby zredukować rywalizację o I/O.
Oddzielaj segmenty wycofania od segmentów danych chroniąc się przed utratą danych przez awarię pojedynczego dysku.
Wyłączaj poszczególne przestrzenie tabel pozostawiając inne włączone.
Zarezerwuj przestrzenie tabel w zależności od typu użycia danych, uwzględniając np. wysoką częstotliwość modyfikacji, dane tylko do odczytu czy przestrzeń na segmenty tymczasowe.
Archiwizuj poszczególne przestrzenie tabel.

Określaj Parametry Zarządzania Przestrzenią w Przestrzeniach Tabel

Określ domyślne parametry zarządzania przestrzenią dla obiektów tworzonych w przestrzeni tabel.
Ustaw domyślne parametry zarządzania przestrzenią w przestrzeni tabel na podstawie szacunków dotyczących rozmiaru typowego obiektu tworzonego w przestrzeni tabel.

Przypisz Użytkownikom Ograniczenia Przestrzeni

Przydzielaj ograniczenia wykorzystania przestrzeni tabel użytkownikom według potrzeb.

Segmenty

Segment to zbiór jednego lub wielu ekstentów zawierających wszystkie dane określonego typu należącego do struktur logicznych przechowywanych

Typ Segmentu	Definicja
Segment wycofania (rollback segment)	Zbiór ekstentów zawierający dane wycofania używane do wycofania, spójności odczytu lub odtwarzania.
Segment danych (data segment)	Zbiór ekstentów zawierający całość danych tabeli lub klastra.
Segment indeksu (index segment)	Zbiór ekstentów zawierający całość danych określonego indeksu.
Segment tymczasowy (temporary segment)	Zbiór ekstentów zawierający dane należące do obiektu tymczasowego.
Segment Cache (cache segment)	Ekstent zawierający definicje słownikowe tabel słownikowych, ładowanych podczas otwierania bazy danych. Nie wymaga żadnej obsługi przez administratora bazy danych.

Co To Jest Segment ?

Segment to logiczna struktura, jaka może być utworzona, zajmuje wtedy przestrzeń, może się rozrastać. Segmenty czasem nazywane są po prostu obiektami bazy danych.
Segmenty nie mogą być podzielone na wiele przestrzeni tabel.
Ekstent to zbiór spójnych (sąsiednich) bloków bazy danych.
Do zarządzania przestrzenią segmentów tymczasowych zawsze używane są domyślne ustawienia parametrów w przestrzeni tabel - nie mogą one być ustawione dla obiektu tymczasowego jawnie.

Segmenty Wycofania

Segment wycofania (rollback segment) to fragment bazy danych sprzed wykonania modyfikacji przez transakcję, pozwalający na wycofanie zmian.

Transakcja (transaction) jest jednostką operacji na bazie danych powodującą zmiany i blokady wierszy. Każda transakcja ma przydzielony unikalny identyfikator i dokładnie jeden segment wycofania. Segment wycofania jest obiektem cyklicznym, w którym każda transakcja dokonuje wielu zapisów. Transakcja może używać następnego ekstentu, jeżeli brak w nim aktywnych zapisów.

Jeśli następny ekstent, jaki ma być użyty posiada aktywne zapisy, do segmentu wycofania zostanie dodany nowy ekstent o ile nie osiągnięto jeszcze maksymalnej liczby ekstentów.

Charakterystyka Segmentów Wycofania

Pozwalają na wycofanie całej transakcji lub w przypadku pomyłki operatora, do wskazanego punktu zachowania.
Umożliwiają spójność odczytu.
Wykorzystywane przy odtwarzaniu bazy danych.
Mogą zawierać dane podlegające zmianom pochodzące z dowolnej przestrzeni tabel (z wyjątkiem segmentu wycofania SYSTEM).
Mogą zawierać zmiany wielu transakcji.
W przestrzeni tabel SYSTEM zawsze istnieje segment wycofania o nazwie SYSTEM.
Jeżeli używamy wielu przestrzeni tabel, konieczny jest przynajmniej jeszcze jeden segment wycofania.

Segment Danych i Indeksów

Segmenty danych (data segments) zawierają dane wstawiane do tabel. Segmenty indeksów (index segments) zawierają indeksy, struktury służące do poprawiania efektywności wyszukiwania danych.

Cechy Segmentów

Typ Segmentu	Funkcja
Tabela	Zawiera wiersze pojedynczej tabeli.
Klaster indeksowy	Zawiera wiersze jednej lub wielu tabel zgrupowane według wartości wskazanej kolumny tabeli.
Klaster haszujący	Zawiera wiersze tabeli rozmieszczone za pomocą algorytmu haszowania.
Indeks	Zawiera indeksy utworzone na jednej lub wielu kolumnach tabeli. Służą do zwiększenia efektywności wyszukiwania danych.

Co To Są Klastry?

Klaster to struktura przestrzeni danych. Każdy klaster zawiera jedną lub wiele tabel.
Klastry haszowane także mogą zawierać więcej niż jedną tabelę, ale są to rzadkie przypadki.
Tabele z klastra używane są przez użytkownika w ten sam sposób co zwykłe tabele.

Segmenty Tymczasowe

Segmenty tymczasowe (temporary segments) dostarczają przestrzeni potrzebnej serwerowi przy sortowaniu danych.

Charakterystyka Segmentów Tymczasowych

Segmenty tymczasowe są:

Tworzone automatycznie przez serwer Oracle przy wykonywaniu złożonych operacji takich jak złączenia, grupowanie sortowanie, tworzenie indeksów i innych operacji wymagających sortowania.
Tworzone w przestrzeni tabel jeśli brak miejsca w pamięci wewnętrznej.
Przypisane do transakcji.
Zwalniane na końcu transakcji (przez SMON).
Nie chronione przez dziennik powtórzeń, ponieważ nie są konieczne dla zachowania spójności danych.

Segmenty Tymczasowe w Nietymczasowej Przestrzeni Tabel

Każda przestrzeń tabel może zawierać segmenty tymczasowe.
Przydział przestrzeni jest określony przez klauzulę DEFAULT STORAGE przestrzeni tabel segmentu.
Dynamiczny wzrost tabel tymczasowych może powodować fragmentację.
DBA może zdecydować która przestrzeń tabel jest używany przez każdego użytkownika do przechowywania segmentów tymczasowych, stąd też możliwe jest utworzenie specjalnej przestrzeni tabel na segmenty tymczasowe.
Ponieważ przydział i zwalnianie segmentów tymczasowych odbywa się często, warto utworzyć specjalną przestrzeń tabel na segmenty tymczasowe, co jest możliwe od wersji 7.3.

Segment Bootstrap

Segment bootstrap zawiera definicje struktury tabel słownika danych. Jest ładowany podczas otwierania bazy danych.

Segment bootstrap jest:

Często nazywany segmentem cache.
Niedostępny dla użytkowników.
Utworzony w przestrzeni tabel SYSTEM i należy do użytkownika sys.
Nie wymaga żadnych akcji ze strony DBA.

Podsumowanie

Dopasowujemy bazę danych przez

Zaprojektowanie logicznej i fizycznej i fizycznej struktury
Tworzenie dodatkowych, niesystemowych przestrzeni tabel.
Tworzenie segmentów wycofania.
Tworzenie segmentów indeksów.
Tworzenie segmentów tymczasowych.

0x01 graphic

Zadania

Utwórz przestrzeń tabel USER_DATA o parametrach: plik danych e:\kurs\database\db#\usradm#.ora o rozmiarze 1M, ekstent inicjalny 10K, ekstent następny 10K, pctincrease 0, automatyczne zwiększanie rozmiaru pliku bazy danych, w trybie offline.

Obejrzyj perspektywę DBA_TABLESPACES, zwróć uwagę na status stworzonej przestrzeni tabel

Przełącz przestrzeń USER_DATA w tryb online i obejrzyj to na perspektywie DBA_TABLESPACES

Dodaj plik o wielkości 1MB do przestrzeni USER_DATA, obejrzyj to na perspektywie DBA_DATA_FILES i sprawdź, które pliki danych mają ustawioną opcję AUTOEXTEND

Zmień ustawienia w bazie tak, aby przestrzeń SYSTEM mogła się rozszerzać o 1M za każdym razem, gdy zajdzie taka potrzeba i sprawdź czy się udało

Utwórz tymczasową przestrzeń tabel TEMPORARY_DATA na pliku o rozmiarze 1M i nazwie e:\kurs\database\db#\tmpadm#.ora z domyślnymi parametrami i sprawdź poprawność utworzenia przestrzeni

Zmień rozmiar pliku dla przestrzeni TEMPORARY_DATA na 2MB, sprawdź, czy się udało

VII. Zarządzanie przydziałem przestrzeni

Cele

W tej lekcji omawiamy jak efektywnie zarządzać i kontrolować przydział przestrzeni w bazie danych.

Po przerobieniu tej lekcji powinieneś potrafić:

Przydzielać ekstenty obiektom bazy danych.
Wyjaśnić parametry zarządzania przydziałem przestrzeni bazy danych.
Kontrolować zużycie przestrzeni.
Wykorzystywać parametry używania przestrzeni.
Wyświetlić informacje o przestrzeni bazy danych.

Przegląd

Serwer Oracle przydziela przestrzeń bazy danych wszystkim danym w bazie.

Termin	Definicja
Baza danych	Logiczny zbiór dzielonych danych przechowywanych w przestrzeniach tabel.
Plik	Fizyczny plik danych należący do pojedynczej przestrzeni tabel.
Przestrzeń tabel	Logiczne repozytorium fizycznie zgrupowanych danych.
Segment	Zbiór złożony z jednego lub z wielu ekstentów zawierający wszystkie dane określonej struktury przechowywania w przestrzeni tabel.
Ekstent	Zbiór spójnych (sąsiednich) bloków bazy danych w pliku danych.
Blok	Wielokrotność fizycznych bloków istniejącego pliku danych.

0x01 graphic

Parametry Zarządzania Przestrzenią

INITIAL
NEXT
MAXEXTENTS
MINEXTENTS
PCTINCREASE
OPTIMAL
FREELISTS
FREELISTS

Parametry Wykorzystania Przestrzeni Bloku

PCTFREE
PCTUSED
INITRANS
MAXTRANS

Ekstenty

Extent to ciągła sekwencja bloków danych. Ekstenty są przydzielane obiektom bazy danych w miarę ich wzrostu.

Ekstenty są przydzielane kiedy:

Segment jest tworzony (INITIAL EXTENT).
Segment rośnie (NEXT EXTENT).
Tabela lub klaser jest modyfikowana w celu przydziału ekstentów.

Ekstenty są zwalniane kiedy:

Segment lub klaster jest usuwany.
Segment lub klaster jest obcinany (polecenie TRUNCATE).
Segment jest większy niż zadeklarowany rozmiar optymalny a zawiera wolne ekstenty (dotyczy tylko segmentów wycofania).

Charakterystyka Ekstentów

Każdy segment w bazie danych jest złożone z co najmniej jednego ekstentu w którym przechowuje dane.
Pierwszy ekstent nazywa się ekstentem inicjalnym (initial extent).
Następne ekstenty są nazywane ekstentami przyrostowymi (incremental extent).
Obiekt zarezerwuje następny ekstent jeżeli wszystkie jego dotychczasowe ekstenty są już zapełnione.
Częste zwalnianie ekstentów może prowadzić do fragmentacji przestrzeni tabel.
Inicjalny ekstent jest zarezerwowany na zapas fragmentem przestrzeni bazy danych. Kiedy ekstent inicjalny zostanie zapełniony, rezerwowany jest następny.
Dla serwera Oracle bloki z kolejnymi identyfikatorami stanowią bloki spójne (sąsiednie). Niekoniecznie muszą to być bloki sąsiadujące ze sobą na dysku mimo, że podczas tworzenia pliku serwer Oracle próbuje alokować ciągłe obszary dysku.

Kontrola Przydziału Ekstentów

Możemy wpływać na przydział ekstentów segmentom. Ustawiamy parametry przydziału przestrzeni poszczególnym obiektom oraz domyślne parametry przestrzeni tabel.

Parametry Zarządzania Przestrzenią

INITIAL
NEXT
MAXEXTENTS
MINEXTENTS
PCTINCREASE
OPTIMAL
FREELISTS
FREELIST GROUPS

Ustawiamy parametry zarządzania przestrzenią dla:

Tabeli
Klastrów
Indeksów
Segmentów wycofania
Przestrzeni tabel (ustawienie wartości domyślnych)

Zasady Pierwszeństwa

Każdy parametr zarządzania przestrzenią określony na poziomie obiektu przesłania odpowiednią opcję zdefiniowaną na poziomie przestrzeni tabel.
Jeśli parametr zarządzania przestrzenią nie jest podany jawnie na poziomie obiektu, stosują się wartości parametrów z poziomu przestrzeni tabel.
Jeśli parametry zarządzania przestrzenią nie są jawnie podane na poziomie przestrzeni tabel, serwer Oracle stosuje domyślne wartości systemowe.
Jeśli parametry zarządzania przestrzenia zostaną zmodyfikowane, zmiany stosują się tylko do nowoprzydzielanych ekstentów.
Opcja OPTIMAL może być specyfikowana tylko dla segmentów wycofania.
Parametry FREELISTS oraz FREELIST GROUPS nie mogą być specyfikowane jako domyślne dla przestrzeni tabel.

Parametr	Opis
INITIAL	Rozmiar w bajtach pierwszego ekstentu przydzielanego dla segmentu. Wartość domyślna to odpowiednik pięciu bloków danych.
NEXT	Rozmiar w bajtach następnego ekstentu przydzielanego dla segmentu. Wartość domyślna to odpowiednik pięciu bloków danych.
MAXEXTENTS	Określa całkowitą liczbę ekstentów (wliczając pierwszy) jaką Oracle ma prawo przydzielić obiektowi. Wartość minimalna to 1. Wartość domyślna zależy od wybranego rozmiaru bloku i od Systemu operacyjnego.
UNLIMITED	Określa, że ekstenty mają być automatycznie dodawane w miarę potrzeb. Nie powinno się używać tej opcji w przypadku segmentów wycofania ani tabel słownikowych.
MINEXTENTS	Całkowita liczba ekstentów rezerwowana podczas tworzenia segmentu. Wartość domyślna to pojedynczy ekstent, z wyjątkiem segmentów wycofania, gdzie niezbędne są dwa.
PCTINCREASE	Procent o jaki rośnie każdy kolejny ekstent w stosunku do poprzedniego. Domyślnie to 50 procent, z wyjątkiem segmentów wycofania, które nie używają tego parametru.
OPTIMAL	Określa optymalny rozmiar segmentu wycofania w bajtach. Wartością domyślna jest null.
FREELISTS	Liczba list wolnych bloków stosowanych przy wstawianiu do tabeli. Domyślnie jest jedna.
FREELIST GROUPS	Liczba oddzielnych grup list wolnych bloków używanych przez oddzielne instancje w konfiguracji Parallel Serwer. Domyślna jest jedna.

0x01 graphic

Uwaga: Podanie MINEXTENTS pozwala na zarezerwowanie dużej przestrzeni już podczas tworzenia segmentu.

Parametry przydziału przestrzeni zmieniamy przez ustawienie wszystkich parametrów przydziału przestrzeni w przestrzeni tabel.

Efekty Zmiany NEXT

Następny ekstent przyrostowy ma rozmiar NEXT. Kolejne ekstenty rosną jak zwykle, stosując parametr PCTINCREASE.

Efekty Zmiany PCTINCREASE

Wartość NEXT jest obliczana z użyciem następującego wzoru:

NEXT = NEXT * (1 + nowe PCTINCREASE/100)

zaokrąglone do następnego bloku Oracle.

Efekty Jednoczesnej Zmiany NEXT oraz PCTINCREASE

Następny ekstent ma przypisaną nową wartość NEXT. Od tej pory wartość NEXT będzie obliczana na podstawie PCTINCREASE zwykłą metodą.

Zmiana wartości domyślnych w przestrzeni tabel stosuje się tylko do nowotworzonych obiektów w tej przestrzeni tabel.

Parametry INITIAL oraz MINEXTENTS nie mogą być modyfikowane.

Przy określaniu parametrów zarządzania przestrzenią kierujemy się zasadą maksymalnego wykorzystania spójnej wolnej przestrzeni i ochroną wolnego miejsca przestrzeni tabel przed fragmentacją.

Fragmentacja wolnej przestrzeni może spowodować zmniejszenie efektywności.

Fragmentację ograniczamy przez:

Dopasowanie ekstentu INITIAL do wielkości segmentu.
Właściwe ustawienie PCTINCREASE zwiększające wielkość ekstentów przyrostowych.

Bloki Bazy Danych

Bloki bazy danych serwera Oracle są najmniejszą jednostką przestrzeni używaną w bazie danych.

Własności Bloków Bazy Danych

Bloki Bazy Danych odpowiadają jednemu lub wielu fizycznym blokom dyskowym.
Rozmiar jest definiowany przy tworzeniu bazy danych przez parametr DB_BLOCK_SIZE i jest identyczny dla wszystkich plików danych.
DB_BLOCK_SIZE także określa rozmiar każdego bufora bazy danych w SGA.
Bloki bazy danych są także nazywane blokami logicznymi lub blokami serwera Oracle.
Kiedy już baza danych Oracle zostanie utworzona, parametr DB_BLOCK_SIZE nie może już być zmieniony.
Rozmiar bloku bazy danych w serwerze Oracle to zwykle 2 KB, 4 KB, lub 8 KB. Wartość domyślna zależy od systemu operacyjnego.
Zaleca się aby rozmiar blok serwera Oracle był równy lub był wielokrotnością rozmiaru bloku w systemie operacyjnym.
Na pewnych platformach rozmiar bloku może być bardzo duży (np. 32 KB). Opcja ta nazywana jest Big Oracle Blocks.
Wszystkie operacje I/O są wykonywane na bloku lub serii bloków. Blokady serwera Oracle zakładane są jednak nie na poziomie bloków, lecz wierszy.

Format bloku serwera Oracle jest podobny niezależnie od tego, czy blok zawiera dane tabeli, indeksu czy klasera.

Części Bloku Bazy Danych

Fragment Bloku	Opis
Nagłówek (header)	Zawiera ogólne informacje o bloku takie jak adres bloku, typ segmentu.
Słownik tabel (table directory)	Zawiera informacje o tabelach w przypadku tabel w klaserze.
Słownik wierszy (row directory)	Zawiera informacje o wierszach zawartych w bloku Wymagany narzut to dwa bajty na każdy wiersz.
Wolne miejsce (free space)	Składa się z bajtów bloków nadal dostępnych na wstawienia lub aktualizację wierszy lub też na dodatkowe informacje o transakcjach.
Wiersze danych (row data)	Przechowuje dane tabeli lub indeksu.

0x01 graphic

Wolna przestrzeń w blokach danych tabeli, klastra lub indeksu może również zawierać zapisy transakcji. Zapis transakcji w bloku jest wymagany dla każdej transakcji używającej jednego lub wielu wierszy z tego bloku. Zapisy transakcji, konieczne dla blokad na poziomie wierszy, umieszczane są w wolnej przestrzeni bloku.

Kontrola Wykorzystania Przestrzeni

Wykorzystanie przestrzeni przez operację wstawienia, modyfikacji i usuwania wierszy w bloku bazy danych kontrolujemy przez ustawienie odpowiednich wartości parametrów kontroli wykorzystania przestrzeni.

Parametry Wykorzystania Przestrzeni

Parametry PCTFREE oraz PCTUSED pozwalają na kontrolowanie wykorzystania wolnej przestrzeni w operacjach wstawiania i modyfikacji wierszy w bloku bazy danych. Każdy z tych parametrów może być zdefiniowany dla tabeli, klastra i migawki w poleceniach CREATE i ALTER. PCTFREE może być także definiowany dla indeksów.

Suma PCTFREE i PCTUSED musi być mniejsza lub równa 100.

Parametr	Definicja
PCTFREE	Określa procent przestrzeni każdego bloku danych zarezerwowany na przyszłe modyfikacje wierszy tabeli. Wartością domyślną PCTFREE jest 10 procent.
PCTUSED	Określa minimalny procent użytej przestrzeni jaki jest obliczany przez Oracle dla każdego bloku tabeli. Blok staje się kandydującym
INITRANS	Określa na liczbę zapisów transakcji rezerwowanych inicjalnie w nagłówku bloku.
MAXTRANS	Określa maksymalną liczbę transakcji jakie mogą odwoływać się jednocześnie do pojedynczego bloku.

Zarezerwowany (pozostawiamy wolny) fragment przestrzeni bloku na przyszłe modyfikacje umieszczonych już w bloku wierszy ustawiamy za pomocą parametru PCTFREE.

PCTFREE

PCTFREE definiuje procent dostępnej przestrzeni bloku zarezerwowany na możliwe modyfikacje wierszy zawartych w tym bloku. Domyślnie jest to 10 procent.
Po osiągnięciu PCTFREE, blok jest uznawany za pełny i nie jest dostępny dla operacji wstawiania nowych wierszy.
Parametr PCTFREE może być również określany przy tworzeniu lub modyfikacji indeksów.
Wolna przestrzeń jest zapełniona przez wstawienia nowych wierszy, rozrost istniejących wierszy i rozrost nagłówka bloku danych.
Blok jest usuwany z listy wolnych kiedy rozmiar wolnej przestrzeni spadnie poniżej granicy PCTFREE.
PCTFREE wyspecyfikowane dla indeksu rezerwuje miejsce zarówno dla modyfikacji jak i wstawiania pozycji. Wtedy wolna przestrzeń jest obliczana jako procent od DB_BLOCK_SIZE minus rozmiar nagłówka.

Procent wolnej przestrzeni w blokach danych możemy obliczyć używając następującego wzoru:

PCTFREE =
* 100

0x01 graphic

Uwaga: Zjawiska migracji wierszy i wierszy w łańcuchu nadal mogą mieć miejsce, nawet jeśli obliczymy PCTFREE stosując powyższy wzór.

Parametr PCTUSED ustawiamy aby kontrolować moment, kiedy blok będzie brany pod uwagę podczas wstawiania nowych wierszy. Nowe wiersze będą wstawiane do bloku dopóki blok znajduje się na liście wolnych. Blok jest usuwany z listy wolnych po osiągnięciu granicy PCTFREE i pozostaje poza nią dopóki jest wypełniony bardziej niż PCTUSED.

PCTUSED

PCTUSED jest granicą decydującą kiedy blok staje się dostępny dla wstawienia nowych wierszy.
Kiedy procent użytej przestrzeni bloku spada poniżej PCTUSED, albo w wyniku usuwania wierszy, albo w wyniku modyfikacji redukujących rozmiar kolumn, blok ponownie jest dostępny dla wstawienia nowych wierszy.
Domyślną wartością systemową dla PCTUSED jest 40.

Serwer Oracle dodaje blok do listy wolnych kiedy stwierdzi, że używana przestrzeń bloku spadła poniżej granicy PCTUSED. Lista wolnych to lista wskaźników do bloków, które mogą być wykorzystywane przy wstawianiu nowych wierszy. Bloki są dodawane i usuwane z listy wolnych kiedy używana przestrzeń odpowiednio spada poniżej PCTUSED i wzrasta powyżej PCTFREE.

Przez parametry PCTFREE i PCTUSED możemy poprawić efektywność i wykorzystanie przestrzeni.

Mała Wartość Parametru PCTFREE

Pozwala na pełniejsze zapełnienie bloków operacjami wstawienia.
Może zmienić dane w mniejszej liczbie bloków.
Może zwiększyć koszty przetwarzania jeśli serwer Oracle często dokonuje reorganizacji bloków.
Może spowodować migrację wierszy.

Duża Wartość Parametru PCTFREE

Rezerwuje więcej przestrzeni dla przyszłych modyfikacji.
Może wymagać większej liczby bloków na przechowanie danych.
Obniża koszty przetwarzania ponieważ bloki rzadziej wymagają reorganizacji.
Zmniejsza efekt wierszy w łańcuchach (chained rows).

Mała Wartość PCTUSED

Zmniejsza koszty przetwarzania, ponieważ bloki rzadziej są uznawane za wolne.
Zwiększa nieużywaną przestrzeń.

Duża Wartość PCTUSED

Zwiększa koszty przetwarzania, ponieważ bloki częściej są uznawane za wolne.
Poprawia wykorzystanie przestrzeni.

Kiedy procent wolnej przestrzeni w bloku danych osiąga PCTFREE, żaden nowy wiersz nie może być wstawiony do bloku dopóki procent wykorzystanej przestrzeni nie spadnie poniżej ustawienia PCTUSED. Stąd, jeśli wartość PCTUSED jest niska, blok nie będzie rejestrowany jako wolny zbyt często i koszt przenoszenia bloku na listę wolnych jest zredukowany. Z drugiej strony, jeśli PCTUSED jest wysokie, blok będzie postrzegany jako wolny dużo częściej i koszt przetwarzania wzrośnie.

Kompromisem wykorzystania przestrzeni i efektywności I/O jest suma PCTFREE i PCTFREE różna od 100 o procent dostępnej przestrzeni bloku jaką zajmuje przeciętnie jeden wiersz. Na przykład dla bloku o rozmiarze 2048 po odjęciu 100 bajtów narzutu pozostaje 1948 bajtów dostępnych dla danych. Jeśli przeciętny wiersz zajmuje 195 bajtów, czyli 10% z 1948, suma PCTUSED i PCTFREE powinna wynosić 90%.

Liczbę aktywnych transakcji operujących na tym samym bloku określamy przez parametry INITRANS i MAXTRANS.

INITRANS

INITRANS to inicjalna liczba zapisów współbieżnych transakcji rezerwowana w każdym nagłówku bloku podczas tworzenia bloku (domyślnie 1, minimalnie 1, maksymalnie 255). Każdy zapis transakcji zajmuje około 23 bajtów (jest to zależne od systemu operacyjnego).

MAXTRANS

MAXTRANS to maksymalna liczba współbieżnych transakcji dopuszczalna na bloku (maksymalnie 255).
Domyślna wartość MAXTRANS jest zależna od systemu od systemu operacyjnego, ale zwykle wynosi 255.
Każda transakcja zajmuje około 23 bajtów przestrzeni bloku. Jeśli brak wolnej przestrzeni bloku, transakcja jest zawieszona w oczekiwaniu na dostęp do bloku.

Łańcuchy i Migracja

Aby uniknąć pogorszenia się efektywności monitorujemy zjawiska wierszy w łańcuchach (row chaining) i migrację wierszy (migration).

Łańcuchy (Chaining)

Wiersz, który nie mieści się w pojedynczym bloku zajmuje łańcuch złożony z wielu bloków.
Początkowy odcinek wiersza pozostaje w bloku w którym wiersz był umieszczony inicjalnie.
Wszystkie dodatkowe części umieszczone są w dołączonym do łańcucha bloku.

Migracja (Migration)

Wiersz migrujący jest przeniesiony w całości z oryginalnego bloku do bloku dołączonego do łańcucha. Jednak nagłówek wiersza pozostaje w bloku, gdzie wiersz został utworzony początkowo.
Wiersz migruje kiedy modyfikacja wymaga więcej miejsca niż jest aktualnie dostępne w bloku.
Odpowiednio duża wartość PCTFREE pozwala na uniknięcie efektu migracji wierszy.

Końcowe kolumny NULL (czyli te z końca wiersza) nie są przechowywane jeśli zostały dodane do struktury tabeli poleceniem ALTER TABLE.

Parametry PCTFREE i PCTUSED optymalizują wykorzystanie przestrzeni w blokach danych.

Zwiększając parametr PCTFREE redukujemy zjawisko migracji wierszy.

Łańcuchy i Migracja Wierszy

Łańcuch pojawia się jeśli wiersze tabeli są długie. Wtedy dane z wielkiego wiersza tabeli nie mogą zmieścić się w pojedynczym bloku. Dane wiersza są przechowywane w łańcuchu bloków danych.
Migracja występuje kiedy wiersz w bloku danych jest modyfikowany tak, że jego długość przekracza wielkość wolnej przestrzeni w bloku. Wtedy dane tego wiersza są przenoszone do nowego bloku danych (o ile cały wiersz może zmienić się w nowym bloku).

Wpływ na Efektywność

Efektywność I/O spada przy odczycie wierszy w łańcuchach lub wierszy przeniesionych.
Serwer Oracle musi przejrzeć więcej niż jeden blok danych aby wyszukać informacje z takiego wiersza.

Usuwanie Migracji Wierszy

Sprawdź występowanie w obiektach wierszy w łańcuchach lub przeniesionych za pomocą polecenia ANALYZE.
Zmień PCTFREE dla obiektów z takimi wierszami.
Wykonaj export, usuń obiekt i zaimportuj go ponownie.

Identyfikujemy występowanie wierszy w łańcuchach i wierszy przeniesionych za pomocą polecenia ANALYZE. Polecenie ANALYZE dokonuje analizy wskazanej tabeli.

Składnia:

0x01 graphic

Użycie Polecenia ANALYZE

Polecenie ANALYZE zapamiętuje identyfikatory wierszy (rowid) i inne informacje w tabeli CHAINED_ROWS w Twoim schemacie. Identyfikatory wierszy reprezentują blok początkowy zarówno wierszy w łańcuchach jak i przeniesionych.

Przez podanie polecenia

ANALYZE TABLE tabela1 LIST CHAINED ROWS INTO tabela2

możemy spowodować zapisanie informacji w tabeli o nazwie innej niż CHAINED_ROWS.

Do utworzenia tabeli CHAINED_ROWS można użyć skryptu SQL serwera Oracle o nazwie UTLCHAIN.SQL, (ewentualnie po modyfikacji kopii pliku w celu zmiany nazwy tabeli).

Wyświetlanie Informacji o Ekstentach i Segmentach

Aby obejrzeć ile ekstentów zostało przydzielonych i jak wiele istnieje segmentów piszemy zapytania oparte na perspektywach słownika danych.

Perspektywy Słownika Danych

USER/DBA_EXTENTS
USER/DBA_FREE_SPACE
USER/DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES

Przykłady

Lista wszystkich kolumn perspektywy DBA_TABLESPACES.

SQL> DESCRIBE dba_tablespaces

Nazwa kolumny Wartość Typ

------------------------------ -------- ----

TABLESPACE_NAME NOT NULL VARCHAR2(30)

INITIAL_EXTENT NOT NULL NUMBER

NEXT_EXTENT NOT NULL NUMBER

MIN_EXTENTS NOT NULL NUMBER

MAX_EXTENTS NOT NULL NUMBER

PCT_INCREASE NOT NULL NUMBER

STATUS NOT NULL VARCHAR2(9)

CONTENTS NOT NULL VARCHAR2(9)

Lista nazw i statusów wszystkich przestrzeni tabel.

SQL> SELECT tablespace_name, status, contents

2> FROM dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS

------------------------------ --------- ---------

SYSTEM ONLINE PERMANENT

USER_DATA ONLINE PERMANENT

ROLLBACK_DATA ONLINE PERMANENT

TEMPORARY_DATA ONLINE PERMANENT

REPOSITORY_DATA ONLINE PERMANENT

INDEX_DATA ONLINE PERMANENT

Lista kolumn w DBA_DATA_FILES

SQL> DESCRIBE dba_data_files

Nazwa kolumny Wartość Typ

------------------------------ -------- ----

FILE_NAME VARCHAR2(513)

FILE_ID NOT NULL NUMBER

TABLESPACE_NAME NOT NULL VARCHAR2(30)

BYTES NOT NULL NUMBER

BLOCKS NOT NULL NUMBER

STATUS NOT NULL VARCHAR2(9)

AUTOEXTENSIBLE VARCHAR2(3)

MAXBYTES NOT NULL NUMBER

MAXBLOCKS NUMBER

INCREMENT_BY NUMBER

Lista ogólnych informacji o plikach danych należących do poszczególnych przestrzeni tabel.

SQL> SELECT file_name, file_id, tablespace_name, bytes

2> FROM dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME BYTES

------------------------------- -------- -------------------------- ------------

D:\ORANT\DATABASE\USR1ORCL.ORA 2 USER_DATA 115343360

D:\ORANT\DATABASE\RBS1ORCL.ORA 3 ROLLBACK_DATA 36700160

D:\ORANT\DATABASE\TMP1ORCL.ORA 4 TEMPORARY_DATA 7825792

D:\ORANT\DATABASE\SYS1ORCL.ORA 1 SYSTEM 167772160

D:\ORANT\DATABASE\REPORCL.ORA 5 REPOSITORY_DATA 2097152

D:\ORANT\DATABASE\IND1ORCL.ORA 6 INDEX_DATA 55267328

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Wyświetlanie obszarów w wolnej przestrzeni w poszczególnych przestrzeniach tabel.

SQL> SELECT *

2> FROM dba_free_space

3> ORDER BY file_id, block_id:

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_F

--------------------------- ---------- ---------- ---------- --------- ----------

SYSTEM 1 1213 10240 5 1

SYSTEM 1 48896 204800 100 1

SYSTEM 1 49111 61440 30 1

SYSTEM 1 69136 30720 15 1

SYSTEM 1 69251 92160 45 1

SYSTEM 1 70531 40960 20 1

USER_DATA 2 4417 51200 25 2

USER_DATA 2 5392 112640 55 2

USER_DATA 2 29848 81920 40 2

USER_DATA 2 38984 61440 30 2

USER_DATA 2 55872 30720 15 2

USER_DATA 2 56127 327680 160 2

ROLLBACK_DATA 3 16186 3553280 1735 3

TEMPORARY_DATA 4 1861 14016512 6844 4

REPOSITORY_DATA 5 312 30720 15 5

REPOSITORY_DATA 5 632 163840 80 5

INDEX_DATA 6 26964 47104 23 6

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Ta sama informacja o wolnej przestrzeni z wykorzystaniem nazw plików zamiast ich identyfikatorów.

SQL> SELECT free.tablespace_name, bloc_id, free.bytes,

2> free.blocks, df.file_name

3> FROM dba_free_space free, dba_data files df

4> WHERE free. File_id = df.file_id

5> ORDER BY 1 , 2;

TABLESPACE_NAME BLOCK_ID BYTES BLOCKS FILE_NAME

---------------- -------- ---------- -------- -----------------------------------

INDEX_DATA 26964 47104 23 D:\ORANT\DATABASE\IND1ORCL.ORA

REPOSITORY_DATA 312 30720 15 D:\ORANT\DATABASE\REPORCL.ORA

REPOSITORY_DATA 632 163840 80 D:\ORANT\DATABASE\REPORCL.ORA

ROLLBACK_DATA 16186 3553280 1735 D:\ORANT\DATABASE\RBS1ORCL.ORA

SYSTEM 1213 10240 5 D:\ORANT\DATABASE\SYS1ORCL.ORA

SYSTEM 48896 204800 100 D:\ORANT\DATABASE\SYS1ORCL.ORA

SYSTEM 49111 61440 30 D:\ORANT\DATABASE\SYS1ORCL.ORA

SYSTEM 69136 30720 15 D:\ORANT\DATABASE\SYS1ORCL.ORA

SYSTEM 69251 92160 45 D:\ORANT\DATABASE\SYS1ORCL.ORA

SYSTEM 70531 40960 20 D:\ORANT\DATABASE\SYS1ORCL.ORA

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Lista kolumn w DBA_SEGMENTS

SQL> DESCRIBE dba_segments

Nazwa kolumny Wartość Typ

------------------------------ -------- ----

OWNER VARCHAR2(30)

SEGMENT_NAME VARCHAR2(81)

PARTITION_NAME VARCHAR2(30)

SEGMENT_TYPE VARCHAR2(17)

TABLESPACE_NAME VARCHAR2(30)

HEADER_FILE NUMBER

HEADER_BLOCK NUMBER

BYTES NUMBER

BLOCKS NUMBER

EXTENTS NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NUMBER

MAX_EXTENTS NUMBER

PCT_INCREASE NUMBER

FREELISTS NUMBER

FREELIST_GROUPS NUMBER

Ogólna informacja o wszystkich segmentach w bazie danych.

SQL> SELECT owner, segment_name, extents, max_extents

2> FROM dba_segments

3> ORDER BY owner, segment_name;

OWNER SEGMENT_NAME EXTENTS MAX_EXTENT

--------- ------------------------ ---------- ----------

SYS ACCESS$ 9 2147483645

SYS AQ$_QUEUE_STATISTICS 1 121

SYS AUD$ 1 121

SYS AUDIT$ 1 121

SYS AUDIT_ACTIONS 1 121

...

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Informacja podsumowująca grupy segmentów w poszczególnych przestrzeniach tabel.

SQL> SELECT TABLESPACE_NAME, COUNT (*) AS SEGMENTS,

2> sum (bytes) AS BYTES

3> FROM dba_segments

4> GROUP BY tablespace_name;

TABLESPACE_NAME SEGMENTS BYTES

------------------------------ ---------- ----------

INDEX_DATA 320 55218176

REPOSITORY_DATA 158 1900544

ROLLBACK_DATA 16 33144832

SYSTEM 235 158472192

TEMPORARY_DATA 80 3807232

USER_DATA 2899 113655808

Lista kolumn w DBA_EXTENTS.

SQL> DESCRIBE dba_extents

Nazwa kolumny Wartość Typ

------------------------------ -------- ----

OWNER VARCHAR2(30)

SEGMENT_NAME VARCHAR2(81)

PARTITION_NAME VARCHAR2(30)

SEGMENT_TYPE VARCHAR2(17)

TABLESPACE_NAME VARCHAR2(30)

EXTENT_ID NOT NULL NUMBER

FILE_ID NOT NULL NUMBER

BLOCK_ID NOT NULL NUMBER

BYTES NOT NULL NUMBER

BLOCKS NOT NULL NUMBER

Informacja o ekstentach przydzielonych w bazie danych.

SQL>SELECT * FROM dba_extents;

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BLOCK_ID BYTES BLOCKS

------ ------------- ------------ --------------- --------- -------- ----- ------

SYS FILE$ TABLE SYSTEM 0 162 10240 5

SYS UNDO$ TABLE SYSTEM 0 157 10240 5

SYS BOOTSTRAP$ TABLE SYSTEM 0 352 51200 25

SYS CON$ TABLE SYSTEM 5 47711 61440 30

SYS CON$ TABLE SYSTEM 6 48751 92160 45

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Zwalnianie Niewykorzystanej Przestrzeni

Czasami zdarza się, że zarezerwujemy przestrzeń dla segmentu, a potem stwierdzimy, że nie jest ona używana. Warto wtedy zwolnić nieużywaną przestrzeń, aby można było udostępnić ją innym segmentom.

Maksymalną w całej historii segmentu wielkość wykorzystanej przestrzeni wskazuje tzw. znacznik wysokiej wody (high water mark). Nawet po usunięciu wierszy znacznik ten nie jest przesuwany.

Przestrzeń poniżej znacznika wysokiej wody nie może być zwolniona.
Znacznik wysokiej wody może być znaleziony za pomocą procedury pakietowej DBMS_SPACE.UNUSED_SPACE.
Jeśli segment jest całkowicie pusty, przestrzeń może być zwolniona za pomocą polecenia TRUNCATE DROP STORAGE.

0x01 graphic

Do zwalniania niewykorzystanej pamięci służy polecenie ALTER TABLE.

Składnia:

0x01 graphic

SQL> ALTER TABLE big_emp DEALLOCATE UNUSED;

0x01 graphic

Uwaga: Po zwolnieniu przestrzeni znacznik wysokiej wody pozostaje niezmieniony.

Jeżeli nie chcemy zwolnić całej nieużywanej przestrzeni używamy klauzuli KEEP (zachowaj)
Efekt zwolnienia przestrzeni możemy sprawdzić w perspektywie DBA_FREE_SPACE; kluczami do odpowiednich tabel są EXTENT_ID, FILE_ID oraz BLOCK_ID.

Łączenie Wolnej Przestrzeni

Przydział przestrzeni dla segmentów umieszczonych w przestrzeniach tabel odbywa się za pomocą ekstentów zbudowanych z ciągłych sekwencji bloków danych, Jeśli pewien wolny obszar przestrzeni jest fragmentowany, podzielony na kilka mniejszych obszarów, wolne obszary przestrzeń tabel mogą być złączone (coalesced). Jest to domyślnie wykonywane przez proces monitora systemu (SMON), ale może być także spowodowane wykonaniem polecenia ALTER TABLESPACE z klauzulą COALESCE.

Przykład

SVRMGR> ALTER TABLESPACE users COALESCE;

Statystyki o ekstentach jakie mogą być złączone w przestrzeni tabel możemy obejrzeć w perspektywie DBA_FREE_SPACE_COALESCED. Zapytania na tej perspektywie pozwalają stwierdzić czy obszary przestrzeni tabel wymagają łączenia.

Po każdych ośmiu złączeniach transakcja łącząca zatwierdza zmiany i inne transakcje mogą wtedy dokonywać rezerwacji lub zwalniania przestrzeni. Oznacz to, że być może trzeba będzie wykonać polecenie ALTER TABLESPACE wielokrotnie. Sprawdzaj perspektywę DBA_FREE_SPACE_COALESCED.

0x01 graphic

Walidacja Struktur

Walidacja sprawdza integralność struktur indeksów, tabel i klastrów.

Walidacja Indeksów

Serwer Oracle sprawdza integralność każdego bloku danych i sprawdza czy blok nie jest zniszczony.
Serwer Oracle nie sprawdza, czy każdy wiersz tabeli ma odpowiadającą pozycję w indeksie ani czy każda pozycja indeksu wskazuje na pewien wiersz w tabeli.

Walidacja Tabel

Serwer Oracle sprawdza integralność każdego bloku danych.
Jeżeli użyto opcji CASCADE sprawdzana jest też struktura wszystkich indeksów tabeli i odpowiedniość między tabelą a jej indeksami.

Walidacja Klastrów

Serwer Oracle sprawdza integralność każdego wiersza w klaserze.
Serwer Oracle dokonuje walidacji struktur wszystkich tabel klastra.
Użycie opcji CASCADE powoduje walidację struktur wszystkich indeksów tabel klastra, w tym indeksu klastra.

Jeżeli została użyta opcja CASCADE w celu sprawdzenia odpowiedniości indeksów, sprawdzane jest czy:

Każda wartość indeksowanej kolumny tabeli pasuje do odpowiedniej wartości kolumny w pozycji indeksu. Odpowiadająca pozycja indeksu musi ponadto identyfikować wiersz tabeli przez poprawny identyfikator wiersza rowid.
Każda pozycja w indeksie identyfikuje pewien wiersz w tabeli. Wartość indeksowanej kolumny w pozycji indeksu musi odpowiadać wartości ze wskazywanego wiersza.

Składnia

0x01 graphic

gdzie:

indeks jest nazwą indeksu jaki ma być analizowany. Jeśli pominięto schemat, serwer Oracle zakłada istnienie indeksu w bieżącym schemacie.

tabela jest nazwą tabeli jaka ma być analizowana. Jeśli pominięto schemat, serwer Oracle zakłada istnienie tabeli w bieżącym schemacie.

klaster jest nazwą klastra jaki ma być analizowany. Jeśli pominięto schemat, serwer Oracle zakłada istnienie klastra w bieżącym schemacie.

VALIDATE STRUCTURE powoduje walidację struktury analizowanego obiektu. Jeżeli użyjemy tej opcji dla klastra, serwer Oracle automatycznie dokona walidacji wszystkich tabel z klastra.

CASCADE powoduje walidację struktury związanych z tabelą lub klastrem indeksów.

Jeśli serwer Oracle dokona pomyślnej walidacji struktury, zwraca komunikat potwierdzający walidację. Jeśli zauważono zniszczenie struktury obiektu, zwracany jest błąd. W takim przypadku należy usunąć i ponownie utworzyć obiekt. Po użyciu polecenia ANALYZE...VALIDATE STRUCTURE do analizy obiektu tabela INDEX_STATS będzie zawierać zapis informacji statystycznej.

Przykłady

Walidacja struktury indeksu PARTS_INDEX.

SQL> ANALYZE INDEX parts_index

2> VALIDATE STRUCTURE;

Walidacja struktury klastra ORDER_CUST, wszystkich jego tabel i indeksów, w tym indeksu klastra.

SQL> ANALYZE CLUSTER order_cust

2> VALIDATESTRUCTURE

3> CASCADE;

Monitorowanie Wykorzystania Przestrzeni

Powinniśmy kontrolować wykorzystanie przestrzeni przez segmenty, zwłaszcza jeśli są one modyfikowane.

Monitorujemy średnią efektywność wykorzystania przestrzeni przez segment przez wykonywanie następujących kroków.

Monitorowanie Wykorzystania Przestrzeni

Analiza obiektu za pomocą polecenia ANALYZE.
Zanotowanie procentowego wykorzystania przestrzeni.

Wykonujemy kroki 1 i 2 w regularnych odstępach czasu i porównujemy wyniki. Jeśli wykorzystywana przez obiekt przestrzeń maleje poniżej jego przeciętnej, możemy wyeksportować, usunąć i zaimportować ten obiekt.

Indeksy kontrolujemy przez wykonanie polecenia ANALYZE lub wykonując zapytania wprost na perspektywach słownika danych odnoszących się do indeksów. Indeksy mogą być usuwane i tworzone ponownie.

Wszystkie ekstenty pozostają zarezerwowane aż do czasu obcięcia lub usunięcia segmentu.

Monitorujemy wykorzystanie przestrzeni przez indeks za pomocą polecenia ANALYZE STRUCTURE i przeglądania tabeli INDEX_STATS.

Podsumowanie

Serwer Oracle przydziela przestrzeń bazy danych wszystkim danym w bazie.

Termin	Definicja
Baza danych	Logiczny zbiór dzielonych danych przechowywanych w przestrzeniach tabel.
Plik	Fizyczny plik danych należący do pojedynczej przestrzeni tabel.
Przestrzeń tabel	Logiczne repozytorium fizycznie zgrupowanych danych.
Segment	Zbiór złożony z jednego lub z wielu ekstentów zawierający wszystkie dane określonej struktury przechowywanej w przestrzeni tabel.
Ekstent	Zbiór spójnych (sąsiednich) bloków bazy danych w pliku danych.
Blok	Wielokrotność fizycznych bloków istniejącego pliku danych.

Parametry Zarządzania Przestrzenią

INITIAL
NEXT
MAXEXTENTS
MINEXTENTS
PCTINCREASE
OPTIMAL
FREELISTS

Parametry Wykorzystania Przestrzeni Bloku

PCTFREE
PCTUSED
INITRANS
MAXTRANS

0x01 graphic

Zadania

Napisz zapytanie na DBA_SEGMENTS, które wypisze nazwę segmentu, jego bajty, bloki i liczbę dopuszczalnych dodatkowych ekstentów

Napisz zapytanie na DBA_FREE_SPACE, które znajdzie ilość wolnej przestrzeni w każdej z przestrzeni

VIII. Zarządzanie segmentami wycofania

Cele

W tej lekcji nauczymy się zarządzać segmentami wycofania Oracle, w tym tez wpływać na ich rozmiar.

Po przerobieniu tej lekcji powinieneś potrafić:

Opisać i zarządzać funkcjami segmentów wycofania.
Tworzyć segmenty wycofania o właściwym rozmiarze.
Opisać kompromisy między wykorzystaniem pamięci a efektywnością.
Określić liczbę segmentów wycofania.

Przegląd

Segment wycofania to zbiór ekstentów zawierających danie wycofania wykorzystywane przy wycofaniu transakcji, zapewnianiu spójności odczytu i odtwarzaniu bazy danych.

Użycie wielu segmentów wycofania.
Wybór pomiędzy publicznymi i prywatnymi segmentami wycofania.
Właściwe ustawienie rozmiaru segmentu wycofania.
Monitorowanie segmentu wycofania w narzędziu Serwer Manager.
Ustawienie optymalnej liczby ekstentów dla każdego segmentu wycofania.

Segmenty Wycofania

Segment wycofania (rollback segment) to fragment bazy danych przeznaczony na zapis danych sprzed zmodyfikowania ich przez transakcję, co pozwala na wycofanie zmian w pewnych warunkach.

Transakcja (transaction) jest jednostką operacji na bazie danych powodującą zmiany i blokady wierszy. Każda transakcja ma przydzielony unikalny identyfikator i dokładnie jeden segment wycofania. Segment wycofania jest obiektem cyklicznym, w którym każda transakcja dokonuje wielu zapisów. Transakcja może używać następnego ekstentu, jeżeli brak w nim aktywnych zapisów.

0x01 graphic

Jeśli następny ekstent, jaki ma być użyty posiada aktywne zapisy, do segmentu wycofania zostanie dodany nowy ekstent o ile nie osiągnięto jeszcze maksymalnej liczby ekstentów.

0x01 graphic

Charakterystyka Segmentów Wycofania

Pozwalają na wycofanie całej transakcji lub, w przypadku pomyłki operatora, do wskazanego punktu zachowania.
Umożliwiają spójność odczytu.
Wykorzystywane przy odtwarzaniu bazy danych.
Mogą zawierać dane podlegające zmianom pochodzące z dowolnej przestrzeni tabel (z wyjątkiem segmentu wycofania SYSTEM).
Mogą zawierać zmiany wielu transakcji.
W przestrzeni tabel SYSTEM zawsze istnieje segment wycofania o nazwie SYSTEM.
Jeżeli używamy wielu przestrzeni tabel, konieczny jest przynajmniej jeszcze jeden segment wycofania.

Zapisy w segmentach wycofania używane przy wycofaniu, spójności odczytu i odtwarzaniu, dokonywane są w sposób cykliczny.

Własności Segmentów Wycofania

Składają się z wielu zapisów wycofania wielu transakcji.
Przechowują informację o bloku taką jak ID pliku i bloku oraz dane takie jakie były przed modyfikacją.
Mogą rosnąć z powodu wielkich lub długotrwałych transakcji.
Mogą być przypisane do transakcji automatycznie lub jawnie.
Można wymusić ich zmniejszenie wykonując polecenie ALTER ROLLBACK SEGMENT <rbs> SHRINK TO <rozmiar>.
Jeżeli się zwiększą, zmniejszenie do OPTIMAL jest automatyczne.

Serwer Oracle wymaga istnienia włączonego (online) segmentu wycofania o nazwie SYSTEM podczas otwierania bazy danych.

Segment wycofania SYSTEM jest zakładany podczas tworzenia bazy danych.

Kiedy transakcja wymaga kontynuacji zapisów informacji wycofania w następnym ekstencie segmentu wycofania, serwer Oracle porównuje bieżący rozmiar segmentu wycofania z rozmiarem optymalnym segmentu. Jeśli segment wycofania jest większy niż jego rozmiar optymalny a ekstent następujący bezpośrednio za właśnie zapełnionym jest nieaktywny, serwer Oracle zwalnia nieaktywne ekstenty segmentu wycofania dopóki całkowity rozmiar segmentu wycofania nie jest równy lub najbliższy nie mniejszy jego rozmiarowi optymalnemu.

Transakcja może zażądać przydzielenia wskazanego segmentu wycofania poleceniem SET TRANSACTION USE ROLLBACK SEGMENT.

Dla segmentów wycofania nie można ustawić parametru PCTINCREASE.

Właściwa wartość OPTIMAL może być znaleziona przez monitorowanie V$ROLLSTAT, lub przez wykorzystanie monitora wycofań z narzędzia Serwer Manager/GUI.

Określamy charakterystyki wykorzystania przestrzeni przez segmenty wycofania.

Parametry Wykorzystania Przestrzeni Segmentów Wycofania

INITIAL
NEXT
MINEXTENTS
MAXEXTENTS
OPTIMAL

Optymalne Parametry

Parametr OPTIMAL określa optymalny rozmiar (w bajtach) segmentu wycofania.
Serwer Oracle zwalnia ekstenty kiedy rozmiar segmentu wycofania jest większy niż OPTIMAL.
Parametr OPTIMAL nie może być mniejszy niż suma bajtów pierwszych MINEXTENTS ekstentów, najlepiej równy tej sumie.
Dla segmentów wycofania nie podaje się parametru PCTINCREASE, jest on zawsze równy zero.
Parametr MINEXTENTS musi być przynajmniej równy dwa, ponieważ ekstenty segmentu wycofania są wykorzystywane w porządku cyklicznym.
Dla segmentu wycofania parametr MAXEXTENTS nie powinien być nigdy ustawiany na UNLIMITED, ponieważ może to spowodować utratę nad nim kontroli i zarezerwowanie przez segment wycofania całej dostępnej przestrzeni dysku.

Istnieją trzy typy segmentów wycofania.

Typ	Funkcja
Prywatny (private)	Aby był rozpoznany przez system musi być uwzględniony w wartości parametru ROLLBACK_SEGMENTS w pliku parametrów
Publiczny (public)	Tworzy pulę segmentów wycofania, które mogą być przydzielone i wykorzystywane w każdej instancji żądającej dodatkowego segmentu wycofania. Użyteczne tylko w środowisku Parallel Serwer, ale wtedy też zwykle lepiej wykorzystywać prywatne segmenty wycofania.
Opóźniony (deferred)	Tworzony kiedy przestrzeń tabel jest wyłączona (offline) tak, że transakcje nie mogą być wycofane natychmiast. Tworzony zawsze w przestrzeni tabel SYSTEM.

Zasady

Tylko segmenty wycofania publiczne i prywatne są tworzone przez DBA.
Prawo do tworzenia segmentów wycofania mają użytkownicy Oracle z uprawnieniami systemowymi CREATE ROLLBACK SEGMENT.
Przydział publicznych segmentów wycofania odbywa się na podstawie obliczenia TRANSACTIONS/ TRASACTIONS_PER_ROLLBACK_SEGMENT, które określa liczbę segmentów wycofania pobieranych z puli publicznej.
Publiczne segmenty wycofania są czasem używane przez Oracle Parallel Serwer, ale nie są wymagane przez konfigurację. Zwykle preferowane są prywatne segmenty wycofania.

Tworzenie Segmentu Wycofania

Możemy stworzyć segment wycofania używając polecenie CREATE ROLLBACK SEGMENT.

Składnia:

0x01 graphic

gdzie:

segment_wycofania jest nazwą tworzonego segmentu wycofania.

PUBLIC określa, że segment wycofania należy do puli publicznej.

przestrzeń_tabel specyfikuje nazwę przestrzeni tabel gdzie ma być utworzony segment wycofania.

klauzula_przestrzeni określa zasady przydzielania przestrzeni segmentowi wycofania.

OPTIMAL specyfikuje optymalny rozmiar w bajtach segmentu wycofania. Opcja OPTIMAL należy do klauzuli przestrzeni.

Minimalną wartością MINEXTENTS dla segmentów wycofania jest 2. Można jeszcze zwiększyć wartość MINEXTENTS przy tworzeniu segmentu wycofania np. kiedy przestrzeń bazy danych jest pofragmentowana a chcemy zagwarantować wystarczającą ilość przestrzeni do przechowania wszystkich danych tabeli. W segmentach wycofania PCTINCREASE jest zawsze zero i nie może być podawany.

Przykład

Utworzenie segmentu o nazwie RBS01 w przestrzeni tabel RBS przez polecenie CREATE ROLLBACK SEGMENT. Weryfikacja poprawności utworzenia segmentu wycofania w słowniku danych.

SQL> CREATE ROLLBACK SEGMENT rbs01

2> TABLESPACE rbs

3> STORAGE (INITIAL 10K NEXT 10K

4> MINEXTENTS 20 MAXEXTENTS 121

5> OPTIMAL 200K);

SQL> SELECT * FROM dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS --------------------- ------ ------------------- ---------- ---------- ----------SYSTEM SYS SYSTEM 0 1 ONLINE

RBS01 PUBLIC RBS 2 3 OFFLINE

0x01 graphic

Uwaga: Format powyższego wydruku został poprawiony.

Modyfikacja Segmentu Wycofania

Przełączamy segment wycofania w tryb offline aby usunąć ten segment lub przestrzeń tabel, która go zawiera.

Przełączenie segmentu wycofania na online udostępnia segment transakcjom.

Wyłączamy i włączamy segmenty wycofania poleceniem ALTER ROLLBACK SEGMENT.

Składnia:

0x01 graphic

gdzie:

segment_wycofania to nazwa zmienianego segmentu wycofania.

ONLINE przełącza segmenty wycofania na online.

OFFLINE przełącza segmenty wycofania na offline.

SHRINK usiłuje zmniejszyć segment wycofania do podanego lub optymalnego rozmiaru.

Przełączenie segmentu wycofania na online:

Natychmiast udostępnia segment wycofania transakcjom.
Zmienia status na ONLINE.
Pozwala na użycie segmentu wycofania.

Przełączenie segmentu wycofania na offline:

Jeśli zawiera aktywne transakcje, zapobiega używaniu segmentu przez przyszłe transakcje.
Jeśli nie ma aktywnej transakcji, przełącza segment wycofania na offline.
Ustawia status na OFFLINE.
Pozwala na usunięcie segmentu wycofania o ile status faktycznie zmienił się na OFFLINE.

Ustawienia MAXEXTENTS na liczbę nieco mniejszą niż absolutne maksimum pozwala DBA na obsługę sytuacji kiedy segment wycofania jest bliski osiągnięcia maksymalnego rozmiaru.

Przykład

Udostępnienie segmentu wycofania RBS01, utworzonego w poprzednim przykładzie i weryfikacja statusu ONLINE w słowniku danych.

SQL> SELECT segment_name, status

2> FROM dba_rollback_segs;

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

RBS01 OFFLINE

RBS02 ONLINE

RBS03 ONLINE

SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE;

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

RBS01 ONLINE

RBS02 ONLINE

RBS03 ONLINE

Zasady Używania Segmentów Wycofania

Dopasowujemy segment wycofania do poziomu aktywności i liczby transakcji zapisujących do segmentu wycofania przez ustawianie parametrów klauzuli przestrzeni (w tym parametru OPTIMAL) podczas tworzenia i strojenia segmentów wycofania.

Rywalizacja o Segmenty Wycofania

Każda transakcja jest przypisana do pewnego segmentu wycofania.
Istnieje tylko skończona liczba miejsc na zapis transakcji w nagłówku segmentu wycofania.
Transakcje dla których zabrakło miejsca w nagłówku zostaną zakończone.
Im więcej jest użytkowników, tym więcej potrzeba segmentów wycofania.

Przykład

SELECT n. Name, round (100 * s. waits/ s.gets) ”%Count”

FROM v$rollname n, v$rollstat s

WHERE n.usn = s. usn;

Jeśli uzyskana wartość jest większa niż 1%, potrzeba większej ilości segmentów wycofania. Można rozważyć też wykorzystanie polecenia SET TRASACTION USE ROLLBACK SEGMENT.

W poleceniach ALTER ROLLBACK SEGMENT oraz CREATE ROLLBACK SEGMENT definiujemy optymalny rozmiar w bajtach dla każdego segmentu wycofania.

Cechy Dynamicznego Przydziału ekstentów

Segment wycofania może mieć zdefiniowany rozmiar optymalny. Rozmiar optymalny (w bajtach) definiujemy podczas tworzenia lub modyfikacji segmentu.
Segment wycofania jest automatycznie zmniejszany do rozmiaru optymalnego kiedy spada liczba aktywnych transakcji.

Zasady Określania Parametru OPTIMAL

Segmenty wycofania dla długotrwałych transakcji powinny mieć duży rozmiar OPTIMAL, aby uniknąć kosztownego przydzielania i zwalniania ekstentów.
Dla długotrwałych zapytań należy zapewnić, aby segmenty wycofania używane w transakcjach modyfikujących dane miały odpowiednio duży rozmiar OPTIMAL. Inaczej otrzymamy błąd „snapshot too old” (ORA-01555).
Segmenty wycofania używane w krótkich transakcjach i zapytaniach powinny mieć niewielki rozmiar OPTIMAL dla poprawienia buforowania segmentu wycofania.

Usuwanie Segmentu Wycofania

Segmenty wycofania często są usuwane z powodu powodowanej przez nie fragmentacji dysku lub ponieważ są przenoszone do innej przestrzeni tabel. Segment wycofania usuwany za pomocą polecenia DROP ROLLBACK SEGMENT.

Składnia:

gdzie:

segment_wycofania jest nazwą usuwanego segmentu.

Usuwać można tylko segmenty wycofania znajdujące się w stanie OFFLINE.

Podsumowanie

Segment wycofania to zbiór ekstentów zawierających dane wycofania wykorzystywane przy wycofaniu transakcji, zapewnieniu spójności odczytu i odtwarzaniu bazy danych.

Użycie wielu segmentów wycofania.
Wybór pomiędzy publicznymi i prywatnymi segmentami wycofania.
Właściwe ustawienie rozmiaru segmentu wycofania.
Monitorowanie segmentu wycofania w narzędziu Serwera Manager.
Ustawienie optymalnej liczby ekstentów dla każdego segmentu wycofania.

0x01 graphic

Zadania

Utwórz tabelę TEST w przestrzeni tabel USER_DATA (skrypt s:\create_test.sql)

Dodaj wiersz do tabeli TEST poleceniem INSERT INTO TEST VALUES ('TEST'). Co się stało?

Utwórz przestrzeń tabel RBS na pliku e:\kurs\database\db#\rbsadm#.ora o rozmiarze 1MB, ekstencie inicjalnym 20K, ekstencie następnym 20K, minimalną ilością ekstentów równą 2, pctincrease 0

Utwórz publiczny segment wycofania RBS01 w przestrzeni tabel RBS

Obejrzyj perspektywę DBA_ROLLBACK_SEGS, jaki status na utworzony segment wycofania?

Przełącz segment wycofania RBS01 w tryb online

Spróbuj ponownie dodać wiersz do tabeli TEST

Sprawdź perspektywę v$rollstat

Z drugiej sesji spróbuj przełączyć segment RBS01 w stan offline. Sprawdź w perspektywie DBA_ROLLBACK_SEGS i v$rollstat, jaki teraz jest stan tego segmentu wycofania. Spróbuj usunąć segment RBS01.

Zatwierdź transakcję i spróbuj ponownie usunąć segment RBS01. Czy teraz się udało?

Utwórz publiczny segment wycofania RBS02 z maksymalną liczbą ekstentów równą 3.

Przełącz segment RBS02 w tryb online.

Dodaj do tabeli TEST 10 wierszy, gdzie name ma długość 30 znaków: INSERT INTO TEST VALUES ('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');

W przestrzeni tabel USER_DATA utwórz tabelę TT jako kopię tabeli TEST poleceniem CREATE TABLE TT AS SELECT * FROM TEST;

Powtarzaj polecenie "INSERT INTO TT SELECT * FROM TT" aż do uzyskania błędu. Jaki to błąd ?

Zatwierdź transakcje i sprawdź ile jest wierszy w tabeli TT poleceniem SELECT COUNT(*) FROM TT. Dlaczego?

Spróbuj usunąć wszystkie wiersze z tabeli TT poleceniem DELETE FROM TT. Co się stało?

Zmień w segmencie RBS02 maksymalną liczbę ekstentów na 100 i ponownie spróbuj usunąć wiersze z tabeli TT Spróbuj zatwierdzić transakcję

Spróbuj zmienić w segmencie RBS02 maxextents na 2 . Co się stało?

Zmień w segmencie RBS02 maksymalna ilość ekstentów na 300.

Usuń tabelę TT

Obetnij tabelę TEST poleceniem TRUNCATE TABLE TEST

W sesji nr 1 wstaw wiersz do tabeli TEST,

W sesji nr 2 przełącz przestrzeń tabel USER_DATA w tryb offline.

W sesji nr 2 obejrzyj perspektywę DBA_SEGMENTS i znajdź opóźniony segment wycofania

W sesji nr 2 włącz przestrzeń tabel USER_DATA

W sesji nr 1 zatwierdź transakcję

W sesji nr 2 zobacz ile wierszy na tabela TEST

Usuń tabelę TEST

IX. Obsługa segmentów tabel i indeksów

Cele

Lekcja ta wyjaśnia, w jaki sposób obsługiwać w Oracle segmenty tabel i indeksów, również jak dobierać ich rozmiary.

Na końcu tej sekcji powinieneś potrafić

Tworzyć tabele i indeksy odpowiednich rozmiarów.
Rozumieć kompromisy związane z zajętością miejsca i wydajnością.
Przeglądać wykorzystanie przestrzeni w bazie.

Przegląd

Segmenty zawierają całość danych dla specyficznej struktury wewnątrz przestrzeni tabel. Segment składa się z jednego lub wielu obszarów (extent)

Własności segmentu

Typ segmentu

Opis

Data

Zawiera dane

Index

Dostarcza efektywnej struktury do lokalizacji w tabeli specyficznych wierszy.

Tabele

Segment danych dla tabeli zawiera wiersze tablicy. Dane tabel przechowywane są w segmentach danych.

Własności tabel

Przechowują dane użytkownika.
Przechowują dane systemowe (słownik danych).
Tworzone poleceniem CREATE TABLE.
Blok nagłówka zawiera listę wolnych bloków (free list) i informacje o ekstentach.
Mogą być utworzone na podstawie innych tabel oraz do przetwarzania równoległego (CREATE TABLE AS SELECT).
Mogą być tworzone w trybie UNRECOVERABLE (CREATE TABLE AS SELECT)

Przykład: Polecenie CREATE TABLE

Utworzenie tabeli ORDERS i podanie parametrów przechowywania danych (storage parameters) w tabeli.

SQL> CREATE TABLE orders (

2> orderid NUMBER (3) NOT NULL,

3> orderdate DATE,

4> shipdate DATE,

5> client VARCHAR2 (3) NOT NULL,

6> amount_due NUMBER (10,2),

7> amount_paid NUMBER (10,2))

8> PCTFREE 5 PCTUSED 65

9> STORAGE (

10> INITIAL 5M

11> NEXT 5M

12> PCTINCREASE 0

13> MINEXTENTS 2

14> MAXEXTENTS 50)

15> TABLESPACE users;

0x01 graphic

Uwaga: Parametry sterujące z wykorzystaniem przestrzeni nie są częścią klauzuli przestrzeni.

Dane z tabel przechowywane są jako wiersze. Wiersz jest kombinacją wartości, którą czytamy zwykle poziomo i która zawiera unikatową kombinację informacji opisującą relacje pomiędzy kawałkami podobnych danych.

Własności wierszy

Nie ma limitu wierszy dla tabeli.
Wiersz nie będący w klastrze, zawarty w całości w jednym bloku, posiada co najmniej 3- bajtowy nagłówek.
Wiersze mogą rozciągać się na wiele bloków, oznacza to tworzenie łańcucha (chained rows).
Wiersze przechowywane są w sposób ciągły w formacie zmiennej lub stałej (dla kolumn typu CHAR i DATE) długości.
Wartości kolumn przechowywane są obok siebie.
Rozmiar kolumny wskazuje na liczbę bajtów potrzebnych do przechowywania wartości kolumny. Potrzeba 1 bajtu (nagłówka) przy przechowywaniu do 250 bajtów lub też 3 bajtów dla kolumn dłuższych niż 250.
Długość kolumny równa 0 oznacza null.

0x01 graphic

Uwaga: Każdy wiersz używa dwóch bajtów w katalogu wierszy w nagłówku bloku danych.

Alokowanie przestrzeni dla tabeli

Dobrze utworzone tabele utworzą wydajniejszą bazę danych.

Parametry klauzuli przestrzeni

Jeśli to możliwe, należy przechowywać tabelę w jednym obszarze (extent).
Jeśli tabela bardzo rośnie należy ustawić dużą wartość PCTINCREASE.
Jeśli nie jest dostępna jedna duża wolna przestrzeń na dane, można utworzyć wiele małych ekstentów za pomocą odpowiedniego ustawienia MINEXTENTS.

Parametry wykorzystywania przestrzeni (Space Utilization Parameters)

Jeśli wiersze nie będą mocno modyfikowane, ustawiamy niskie PCTFREE.
Jeśli dla tabeli będzie dużo operacji wstawienia i usuwania, ustawiamy niskie PCTUSED.
PCTFREE plus PCTUSED nie mogą przekroczyć 100 %.

Tworzenie tabel

Składnia:

0x01 graphic

gdzie:

schemat oznacza właścicieli tabel

tabela jest nazwą tabeli

kolumna jest nazwą kolumny

typ_kulumny jest typem danych w kolumnie

PCTFREE jest ilością miejsca zarezerwowaną w blokach (jako procent całego dostępnego - nie licząc nagłówka bloku - miejsca w bloku) dla wierszy, które powiększą się.

PCTUSED określa limit wykorzystanego miejsca dla bloku (po jak zapełni się do PCTFREE) zanim stanie się on dostępny dla wstawienia wierszy.

INITRANS określa liczbę pozycji przygotowanych dla transakcji nagłówku bloku. Domyślnie 255.

MAXTRANS limit pozycji transakcji, jakie mogą być zaalokowane dla każdego bloku. Domyślnie 255.

TABLESPACE określa przestrzeń tabel, w której ma być tabela

STORAGE określa klauzulę przestrzeni, która determinuje w jaki sposób tablicy będą przydzielane ekstenty.

RECOVERABLE określa, że utworzenie tabeli ma zostać odnotowane w dzienniku powtórzeń. Domyślnie.

UNRECOVERABLE określa, że utworzenie tabeli nie może być odnotowane w dzienniku powtórzeń.

CLUSTER określa, że tabela jest częścią klastra.

PARALLEL określa charakterystykę dostępu równoległego do tabeli. Patrz opisy DEGREE i INSTANCES.

DEGREE Liczba całkowita specyfikująca domyślny stopień paralelizmu dla tabeli; domyślnie jest to obliczane przy uwzględnieniu przypuszczalnego rozmiaru tabeli i wartości parametru inicjalizującego.

ENABLE włącza warunek integralności.

DISABLE wyłącza warunek integralności.

AS podzapytanie wstawia do tabeli wiersze będące wynikiem podzapytania.

CACHE określa, iż bloki tej tabeli są po wczytaniu podczas wykonywania pełnego przejścia przez tabelę (full table scan)umieszczane jako te ostatnio użyte w liście LRU obsługującej bufory danych.

NOCACHE określa, iż bloki wczytane przy przeglądaniu całej tabeli (full table scan) nie są umieszczane jako ostatnio użyte na liście LRU obsługującej bufory danych.

Składnia klauzula_przestrzeni:

0x01 graphic

gdzie:

INITIAL jest rozmiarem pierwszego ekstentu w bajtach.

NEXT jest rozmiarem następnego ekstentu w bajtach.

MINEXTENTS jest liczbą ekstentów tworzonych od razu.

MAXEXTENTS jest liczbą możliwych do zaalokowania ekstentów

PCTINCREASE jest współczynnikiem o jaki powiększany jest każdy następnie przydzielany ekstent.

FREELISTS jest liczbą list wolnych bloków.

FREELIST GROUPS jest liczbą grup list wolnych bloków dla instancji Serwera Równoległego.

Modyfikacja tabel

Do modyfikacji definicja tabeli używa się polecenia ALTER TABLE.

Składnia:

0x01 graphic

gdzie:

ALLOCATE EXTENT ręcznie dodaje nowy ekstent.

SIZE jest rozmiarem nowego ekstentu w bajtach.

DATAFILE jest nazwą pliku danych w przestrzeni tabel.

INSTANCE jest nr instancji w środowisku bazy danych MPP.

DEALLOCATE UNUSED jawnie zwalnia niewykorzystaną przestrzeń na końcu tabeli i udostępnia ją dla innych segmentów.

KEEP określa liczbę bajtów powyżej znacznika wysokiej wody (high water mark), jaka ma pozostać w tabeli po dealokacji.

ENABLE enable_clause włącza sprawdzanie pojedynczego constrainta lub wszystkich wyzwalaczy powiązanych z tabelą.

ENABLE TABLE LOCK pozwala na blokady DML i DDL na tabeli w środowisku serwera równoległego.

DISABLE disable-clause wyłącza sprawdzanie pojedynczego constrainta lub wszystkich wyzwalaczy powiązanych z tabelą.

DISABLE TABLE LOCK uniemożliwia blokady DML i DDL na tabeli w środowisku Serwera Równoległego.

Polecenia ALTER TABLE można użyć do kontroli alokacji przestrzeni dla przyszłych bloków.

Przykłady

SQL> ALTER TABLE reserwe

2> STORAGE(MAXEXTENTS 121

3> PCTINCREASE100);

Table created

SQL> ALTER TABLE too_small

2> ALLOCATE EXTENT

3> (SIZE 200K

4> DATAFILE `/u02/Oracle/test/user1 . dbs');

Table altered

Istniejące bloki i obszary nie są przez polecenie ALTER zmienione.
Rozmiar obszaru NEXT - jaki ma być przydzielony w następującej kolejności, obliczany na podstawie rozmiaru ostatniego zaalokowania obszaru i PCTINCREASE, nie zmienia się, gdy ekstent zostanie dodany ręcznie za pomocą opcji ALLOCATE EXTENT.

Usuwanie tabel

Tabelę i jej zawartość usuwa się poleceniem DROP TABLE.

Składnia

0x01 graphic

gdzie:

schemat jest schematem zawierającym tabelę. Jeśli pominie się schemat Serwer Oracle zakłada, iż chodzi o schemat wydającego polecenie.

tabela jest nazwą tabeli, która ma być usunięta.

CASCADE CONSTRAITS usuwa wszystkie więzy integralności, jakie odnoszą się kluczy Primery i unique w usuwanej tabeli. Jeśli pominie się tą opcję, a istnieje taki klucz obcy, Serwer Oracle zwróci błąd i nie usunie tabeli.

Segmenty Indeksów

Jeśli na tabeli zostanie utworzony indeks, jeśli dla niego tworzony segment indeksowy (index segment). Segment taki jest fizycznie oddzielony od segmentów danych tabeli czy klastra.

0x01 graphic

Charakterystyka drzewa B*Tree

Wyważone drzewa pozwalają na szybką lokalizację wartości kluczy.
Adres ROWID (hexadecymalny) daje bezpośredni dostęp do wiersza w tabeli.
Zapytania odnoszące się tylko do poindeksowanych kolumn mogą zostać rozwiązane wewnątrz indeksu (bez odwoływania się do tabeli).
Przeszukiwanie indeksu (index search) jest alternatywą dla przejrzenia całej tabeli (full table scan); mogą one oszczędzić operacji We/Wy na dysku.
Automatycznie wykorzystywane i utrzymywane przez Serwer Oracle Server.

Własności segmentu indeksu

Niezależny od tabeli czy klastra
Każdy indeks może mieć swoje własne parametry i przestrzeń tabel.
Indeksy są zwykle mniejsze niż poindeksowane tabele.
Mogą być tworzone równolegle.
Mogą być tworzone z opcją UNRECOVERABLE.
Powinny być dla optymalizacji przechowywane w osobnej przestrzeni tabel.
Mogą być albo typu unique albo non-unique.
Mogą być albo proste (jedna kolumna), albo złożone (znane również jako skonkatenowane).

Przykład

Utworzenie indeksu na tabeli EMP.

SQL> CREATE INDEX emp_ename /*Index name*/

2> ON emp(ename) /*Table and column name*/

3> STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0)

4> TABLESPACE user_data;

Index created

Indeksy bitmapowe

Indeksy bitmapowe są atrakcyjną alternatywą dla normalnych indeksów typu B*-Tree w sytuacji, gdy:

Tabele są bardzo duże (miliony wierszy).
Tabele mają niską kardynalność - tj. kolumnę, w której liczba różnych wartości jest mała (np. płeć, wiek, stan cywilny, kod pocztowy).
Zapytania mają indeksy bitmapowe na wszystkich kolumnach zwykle używanych w klauzulach WHERE.
Zapytania mają klauzule WHERE z warunkami spełnianymi przez tysiące wierszy.

Koncepcja indeksów bitmapowych

Mapa bitowa dla każdej wartości

Kolumna = REGION
`cast'	`central'	`west'
1	0	0
0	1	0
0	0	1
0	0	1
0	1	0
0	1	0

Efektywna kombinacja bitmapów korzystająca z operacji logicznych (AND,OR itd.).
Znaczna redukcja zużycia miejsca w porównaniu z innymi technikami indeksowania.
Dramatyczne zyski wydajności dla niektórych zapytań.

Kiedy korzystać z indeksów bitmapowych

Indeksy bitmapowe powinny znaleźć zastosowanie w następujących sytuacjach:

W zapytaniach zawierających:
Kolumny z niską kardynalnością
Kombinację wielu warunków WHERE
Każdy produkt daje dużą liczbę wierszy
Jest to alternatywa dla indeksów skonkatenowanych dająca
Lepsze wykorzystanie przestrzeni
Bez zależności od kolejności kolumn
Jeśli działania polegają głównie na odczycie danych, a mało jest modyfikacji
Indeksy bitmapowe są bardzo kosztowne przy modyfikacjach

Indeksy bitmapowe nie są dostępne przed wersją 7.3.3.

Indeksy typu unique

Indeksów typu unique można użyć by zapewnić, iż w tabeli nie będzie dwóch wierszy z tymi samymi wartościami w kolumnach, na których zbudowany jest indeks.

Nie ma duplikatów wartości

Unikatowość kolumny należy zapewni raczej za pomocą constrainta UNIQUE, a nie indeksu typu unique. Taka niepowtarzalność jest koncepcją stricte logiczną i powinna być ujęta przy definicji tabeli.
Indeksy typu unique są tworzone automatycznie dla każdego klucza głównego i klucza unique.

Indeksy typu Non-Unique

Indeksy typu Non-unique pozwalają na powtarzanie wartości w indeksowanej kolumnie.

Indeksy skonkatenowane

Indeksy złożone lub skonkatenowane tworzone są na wielu kolumnach tabeli.

Charakterystyka

Kolumny w indeksach złożonych nie muszą być w tej samej kolejności co kolumny w tabeli, nie muszą to być też kolumny sąsiednie.
Maksymalną liczbą kolumn tworzącą klucz indeksu złożonego jest 16. Jednak prawdziwe maksimum zależy od rozmiaru bloku danych. Całkowity rozmiar klucza indeksu w bajtach nie może przekroczyć połowy zdefiniowanego bloku danych.

Tworzenie indeksów

Indeksy tworzy się poleceniem CREATE INDEX.

0x01 graphic

gdzie:

UNIQUE określa, czy wartość w indeksowanej kolumnie ma być unikatowa.

BITMAP określa, że ma zostać utworzony indeks bitmapowy.

schemat jest schematem, które ma zawierać indeks lub tabelę.

indeks jest nazwą tworzonego indeksu.

tabela jest nazwa tabeli, na której zbudowany ma być indeks.

kolumna jest nazwą kolumny w tabeli. Maksymalnie można podać 16 kolumn.

ASC/DESC są obsługiwane ze względu na kompatybilność ze składnią DB2, jednak indeksy tworzone są zawsze z porządkiem rosnącym.

CLUSTER określa klaster, dla którego ma zostać zbudowany indeks.

INITRANS określa liczbę pozycji dla transakcji przygotowanych w nagłówkach bloków danych segmentu. Domyślnie 2.Każda pozycja dla transakcji zajmuje 23 bajty.

MAXTRANS ogranicza liczbę transakcji, które mogą mieć równoczesny dostęp do bloku. Domyślnie 255.

TABLESPACE określa przestrzeń tabel, w której ma zostać umieszczony indeks.

STORAGE patrz składnia klauzuli przestrzeni.

PCTFREE jest ilością miejsca (w procentach) rezerwowanego na dodatkowe pozycje indeksu.

NOSORT mówi Serwerowi Oracle, że wiersze tabeli zostały posortowane w porządku rosnącym.

RECOVERABLE określa, iż utworzenie indeksu zostanie odnotowane w dzienniku powtórzeń. Domyślnie tak się właśnie dzieje.

UNRECOVERABLE określa, iż utworzenie indeksu nie ma być odnotowane w dzienniku powtórzeń.

PARALLEL określa, że indeks ma być tworzony równolegle.

Składnia klauzuli przestrzeni:

0x01 graphic

gdzie:

INITIAL określa rozmiar pierwszego ekstentu segmentu.

NEXT określa rozmiar drugiego ekstentu segmentu.

MINEXTENTS określa liczbę ekstentów, jakie mają zostać zaalokowane w czasie tworzenia indeksu.

MAXEXTENTS określa maksymalną liczbę ekstentów.

PCTINCREASE określa w procentach o ile kolejny ekstent ma być większy od poprzedniego.

FREELISTS określa liczbę list wolnych bloków dla każdej grupy takich list. Domyślnie 1.

FREELIST GROUPS określa liczbę list wolnych bloków dla indeksu. Domyślnie 1.

Przykłady

Poniższe przykłady używają domyślnej przestrzeni tabel twórcy indeksów. Korzystają one również z domyślnych parametrów klauzuli przestrzeni dla tej przestrzeni tabel - zakładając, że tabela nie ma wierszy.

SQL> CREATE INDEX ind_emp_empno ON EMP (EMPNO)

Niżej parametry klauzuli przestrzeni podane są jawnie.

SQL> CREATE INDEX ind_emp_empno ON EMP(EMPNO)

2> TABLESPACE user

3> STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 75);

Indeks skonkatenowany oparty na dwóch kolumnach tabeli PARTS. Jest on przechowywany w osobnej przestrzeni tabel przeznaczonej na wszystkie indeksy w bazie.

SQL> CREATE INDEX ind_parts ON PARTS (set_no, part_no)

2> TABLESPACE tbs_ind

3> STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) PCTFREE 0;

0x01 graphic

Wskazówki:

Najpierw należy utworzyć tabelę a następnie ją indeksować.
Jeśli kolumna zawiera wartości posortowane w porządku rosnącym, należy podać opcję NOSORT. W ten sposób pominięta zostanie przy tworzeniu indeksu faza sortowania.
Przy sortowaniu wartości indeksu Serwer Oracle korzysta z segmentów tymczasowych i przestrzeni do sortowania określonej parametrem inicjalizacyjnym SORT_AREA_SIZE.
Indeksy mogą być tworzone jako UNRECOVERABLE, tak iż nie jest generowana informacja dla segmentów wycofania i dziennika powtórzeń. Do momentu zrobienia kopii zapasowej, nie będzie możliwe odtworzenie takich indeksów.

Obsługa indeksów

Gdy poprawiana jest poindeksowana tabela, poprawiony lub utrzymany musi zostać również indeks.

Kompromis wydajności

Przy korzystaniu z indeksów wyważamy wydajność pomiędzy

Prędkością pobierania danych z tabeli.
Prędkością modyfikowania tabeli.

Kiedy do tabeli wstawia się wiersz lub gdy wiersze są z niej usuwane, powiązane z nią indeksy również muszą zostać poprawione. Im więcej indeksów zbudowanych na tabeli, tym większy będzie narzut. Dlatego, jeśli tabela używana jest głównie do odczytu, istnienie wielu indeksów może pomóc. Jednak jeśli tabela jest często modyfikowana, lepiej mieć na niej mniej indeksów.

Indeksy pomagają, gdy modyfikuje się stosunkowo niewiele wierszy. Jeśli wykonywane jest wiele wstawień i usunięć, lepiej mieć mniej indeksów.

Indeksy są tworzone w domyślnej przestrzeni tabel, lecz przy ich budowaniu można podać inną przestrzeń. Umieszczenie indeksów i tabel, na których są one budowane w różnych przestrzeniach tabel - najlepiej na różnych urządzeniach - poprawia wydajność, ponieważ umożliwia równoległy dostęp do nich.

Kiedy indeks zostanie utworzony, segment zawierający go jest tworzony automatycznie w podanej lub domyślnej przestrzeni tabel.

Parametry klauzuli przestrzeni dla indeksów

INITIAL
NEXT
MAXEXTENTS
MINEXTENTS
PCTINCREASE

Lepiej jest przechowywać indeksy w mniejszej liczbie większych obszarów, niż w wielu małych ekstentach, ponieważ wtedy informacje w indeksie są z większym prawdopodobieństwem rozłożone równomiernie i w sposób ciągły.

Jeśli tabela istnieje w czasie tworzenia indeksu, wszystkie bloki indeksu zostają zapełniane do wartości PCTFREE. Gdy do tabeli zostanie dodany nowy wiersz, odpowiadający mu zapis w indeksie musi zostać umieszczony w odpowiednim bloku (indeksu).

Wartość PCTFREE

Powinno być duże, jeśli do poindeksowanej tabeli będzie wstawianych wiele wierszy.
Powinno być małe, jeśli do poindeksowanej tabeli nie będzie wstawianych zbyt wiele wierszy.

Jeśli PCTFREE zostanie ustawione ze zbyt małym zapasem i nowego zapisu zabraknie miejsca w blokach indeksu, blok indeksu zostaje rozbity na wiele bloków

Jeśli PCTFREE jest zbyt duże, w blokach indeksu będzie dużo zmarnowanego miejsca.

Wartość INITRANS i MAXTRANS

Wartość dla INITRANS i MAXTRANS zależą od rozmiaru zapisów w indeksie i liczby równoczesnych transakcji, jakie będą korzystały z indeksu.

Niskie wartości INITRANS i MAXTRANS

Wartości dla INITRANS i MAXTRANS mogą być niskie jeśli

Zapisy w indeksie są dużego rozmiaru.
Mało użytkowników będzie równocześnie korzystało z poindeksowanej tabeli, a więc i z indeksu.

Jeśli wielu użytkowników wyda polecenie, które korzysta z indeksu, większa wartość INITRANS zapewni, że będzie dosyć miejsca na przeprowadzenie transakcji i wyeliminuje narzut potrzeby na tworzenie nowych pozycji dla transakcji. Większe MAXTRANS zapewni, iż użytkownicy nie będą musieli oczekiwać na zwolnienie się miejsca potrzebnego transakcji.

Jeśli parametry klauzuli przestrzeni nie zostaną podane jawnie, przyjmowane są domyślne wartości dla docelowej przestrzeni tabel.

Monitorowanie Indeksów

Jeśli wartości kluczy są często modyfikowane, dobrze jest kontrolować wykorzystanie przestrzeni indeksu.

Monitorowanie średniej efektywności wykorzystania przestrzeni indeksu można osiągnąć powtarzając następujące kroki:

Procedura monitorowania indeksów

Przeanalizuj indeks.
Zanotuj procent zajętej przestrzeni.

Wykonuj kroki 1 i 2 regularnie, porównaj wyniki. Jeśli wykorzystanie przestrzeni w indeksie spadnie poniżej średniej, powinieneś skondensować przestrzeń indeksu poprzez usunięcie i ponowne utworzenie.

W celu przeanalizowania indeksu, skorzystaj z polecenia ANALYZE i bezpośrednio sprawdź zawartość perspektyw słownika danych dotyczących indeksów.

Wszystkie obszary pozostają zarezerwowane, aż do usunięcia indeksu.

Indeks może zostać usunięty jawnie bądź niejawnie poprzez usunięcie tabeli.

Obcięcie tabeli obcina również jej indeksy.

Po pierwsze, korzystamy z polecenia ANALYZE.

Składnia:

gdzie:

indeks identyfikuje indeks do przeanalizowania. Jeśli pominie się schemat Serwer Oracle zakłada, iż indeks jest w schemacie aktualnego użytkownika.

VALIDATE STRUCTURE sprawdza strukturę indeksu, generuje statystyki do perspektywy INDEX_STATS.

Przykład

SQL> ANALYZE INDEX tab_index VALIDATE STRUCTURE;

Index analyzed.

Zauważamy procent zużytej przestrzeni. W tym celu wykonujemy zapytania na perspektywach słownika danych.

Przyklad

Po przeanalizowaniu indeksu TAB_INDEX, dane dotyczące jego wykorzystania przestrzeni pojawiają się w perspektywie INDEX_STATS.

SQL> SELECT btree_space, used_space, pct_used

2> FROM index_stats

3> WHERE name = `TAB_INDEX';

BTREE_SPACE USED_SPACE PCT_USED

-------------------- ---------------------- --------------

1891 196 11

0x01 graphic

Wskazówki:

BTREE_SPACE jest to przestrzeń zarezerwowana dla indeksu. USED_SPACE jest to przestrzeń wykorzystana.
PCT_USED nie jest tym samym co parametr PCTUSED użyty przy tworzeniu tabeli - jest to obliczana statystyka. Parametr ten jest nielegalny przy tworzeniu indeksu. PCT_USED jest procentem przydzielonej przestrzeni, która jest używana.
Statystyki dostępne są również poprzez perspektywy USER_INDEXES i USER_IND_COLUMNS.

Modyfikacja indeksów

Parametry klauzuli przestrzeni dla indeksu oraz jego parametry dla transakcji można zmienić poleceniem ALTER INDEX.

Składnia:

0x01 graphic

gdzie:

schemat jest nazwą schematu, do którego należy indeks. Domyślnie - schemat aktualnego użytkownika.

PCFREE zmienia wartość PCTFREE.

INITRANS/MAXTRANS ustawia nowe wartości tych parametrów

STORAGE ustawia nowe wartości parametrów klauzuli przestrzeni.

ALLOCATE EXTENT jawnie alokuje następny ekstent dla indeksu.

SIZE określa rozmiar ekstentu w bajtach.

DATAFILE określa jeden z plików danych przestrzeni tabel indeksu jako ten, gdzie ma zostać umieszczony nowy ekstent.

INSTANCE czyni nowy ekstent dostępnym dla podanej instancji. Parametr ten wykorzystuje się jedynie w Serwer Oracle działających w trybie równoległym z opcją Serwera Równoległego.

Za pomocą polecenia ALTER INDEX, można zwolnić niewykorzystaną przestrzeń (DEALLOCATE UNUSED) lub szybko przebudować indeks (REBUILD).

Składnia cd.:

0x01 graphic

gdzie:

DEALLOCATE UNUSED wymusza zwolnienie nie wykorzystanej przestrzeni i pozwala na jej wykorzystanie w innych segmentach.

KEEP określa liczbę bajtów powyżej znacznika wysokiej wody (high water mark), jakie maja pozostać w indeksie po dealokacji.

REBUILD tworzy nowy indeks korzystając ze starego.

PARALLEL do zbudowania nowego indeksu ma zostać użyte numer równoległych procesów.

NOPARALLEL do tworzenia nowego indeksu nie mają być wykorzystane procesy równoległe. Jest to zachowanie domyślne.

RECOVERABLE określa, że tworzenie indeksu zostanie odnotowane w dzienniku powtórzeń. Jest to zachowanie domyślne.

UNRECOVERABLE określa, że tworzenie indeksu nie ma być odnotowane w dzienniku powtórzeń.

TABLESPACE określa przestrzeń tabel, gdzie indeks ma zostać ponownie zbudowany.

Przykłady

Zmiana wartości MAXTRANS na 5.

SQL>ALTER INDEX ind_emp_empno

2> MAXTRANS 5;

Index ALTERed

Nowe ekstenty nie będą większe od swych poprzedników.

SQL> ALTER INDEX ind_emp_empno

2> STORAGE (PCTINCREASE 0);

Index ALTERed

Zmiany w INITRANS odnoszą się jedynie do kolejnych bloków.
Zmiany w MAXTRANS odnoszą się do wszystkich bloków.
Zmiany w parametrach klauzuli przestrzeni odnoszą się do kolejnych ekstentów.

Ponowne tworzenie indeksu

Możliwe jest ponowne utworzenie indeksu przy wykorzystaniu istniejącego indeksu jako źródła danych. Tworzenie indeksu w ten sposób pozwala na zmianę charakterystyki przechowywania indeksu, przeniesienie go do innej przestrzeni tabel. Można również w ten sposób usunąć fragmentację. Faktycznie, w porównaniu z usunięciem i ponownym utworzeniem indeksu za pomocą polecenia CREATE INDEX taki sposób tworzenia indeksu jest wydajniejszy.

Usuwanie indeksu

Indeks usuwa się poleceniem DROP INDEX. Indeks należy usunąć gdy

Nie jest on dłużej potrzebny.
Indeks stał się niepoprawny i przed ponownym zbudowaniem musi zostać usunięty.
Chcemy przenieść indeks do innej przestrzeni tabel.
Będą wykonywane duże operacje DML dotyczące tabeli.

Składnia:

Przykład

Usunięcie indeksu IND_PARTS.

SQL> DROP INDEX ind_parts;

Index dropped.

Kiedy usuwa się tabelę, zbudowane na niej indeksy są również usuwane.

Podsumowanie

Segmenty zawierają wszystkie dane specyficznej struktury wewnątrz przestrzeni tabel. Segment składa się z jednego lub więcej ekstentów.

Własności segmentu

Typ segmentu

Opis

Data

Zawiera dane

Index

Dostarcza efektywnej drogi wyszukiwania wierszy w tabeli.

0x01 graphic

Zadania

Utwórz przestrzeń tabel INDEX_DATA o rozmiarze 1MB z pctincrease = 0

Połącz się jako DEMO, skopiuj do katalogu z:\sql skrypt s:\create_tables.sql, zmodyfikuj go tak aby tabele EMP i DEPT zostały utworzone z następującymi parametrami: ekstent inicjalny 6K, następny 6K, pctincrease=0, pctfree=10 pctused=70, początkowo 2 ekstenty, maksymalnie 30, w przestrzeni tabel USER_DATA i uruchom skrypt ;

Połączony jako DEMO utwórz indeks non-unique EMP_JOB na tabeli EMP oparty na kolumnie job w przestrzeni tabel INDEX_DATA

Połączony jako DEMO wykonaj polecenie ANALYZE, aby zebrać dane o wykorzystaniu przestrzeni przez indeks EMP_JOB ;

Połączony jako DEMO obejrzyj odpowiednie perspektywy i określ jak wiele miejsca zajmuje indeks EMP_JOB. Jakie inne indeksy są utworzone w schemacie DEMO? Skąd się tam wzięły?

Połączony jako DEMO dodaj do tabeli EMP dane wykonując skrypt s:\more_emp.sql

Połączony jako DEMO usuń i utwórz na nowo indeks EMP_JOB, zbierz statystykę i określ miejsce jakie zajmuje indeks. Co się stało?

Połączony jako DEMO usuń indeks

Połączony jako DEMO skopiuj skrypt s:\create_tables.sql do katalogu z:\sql, zmodyfikuj go tak, by utworzył tabelę EMP z parametrami initial=2K, next=2K, pctincrease=0, PCTUSED=80, PCTFREE=10 i uruchom go, a następnie uruchom skrypt s:\more_emp.sql

Połączony jako DEMO zmodyfikuj skrypt z:\sql\create_tables.sql tak, by utworzył tabelę EMP1 z parametrami initial=2K, next=2K, pctincrease=0, PCTUSED=50, PCTFREE=40 i uruchom go, a następnie skopiuj skrypt s:\more_emp do z:\sql i zmodyfikuj go tak, by dodawał wiersze do tabeli EMP1

Sprawdź wielkość segmentów tabel EMP i EMP1. Czy tabele utworzone w ćwiczeniu 67 różnią się wielkością od tabel utworzonych w ćwiczeniu 68?

Usuń tabele EMP, DEPT, EMP1, DEPT1

W bazie potrzebna jest tabela, w której będą przechowywane dane statyczne i która będzie bardzo często przeglądana. W celu rozłożenia operacji I/O na kilka urządzeń utworzymy tę tabelę w przestrzeni tabel rozłożonej na trzech dyskach

Podłączony jako sysdba utwórz przestrzeń tabel STRIPE rozłożoną na trzech plikach, każdy po 110K o następujących nazwach e:\kurs\database\db#\s1adm#.ora, e:\kurs\database\db#\s2adm#.ora, e:\kurs\database\db#\s3adm#.ora i upewnij się, czy jest włączona

Jaka jest w tym momencie ilość wolnego miejsca w przestrzeni tabel STRIPE?

W przestrzeni tabel STRIPE utwórz tabelę BUSY z pojedynczym pierwszym ekstentem o rozmiarze 300K. Co się stało?

W przestrzeni tabel STRIPE utwórz tabelę BUSY, tak aby tabela powstała i ilość miejsca zarezerwowana na początku wynosiła 300K ;

Ile zostało wolnego miejsca w przestrzeni tabel SPRIPE?

Usuń przestrzeń tabel STRIPE.

Obejrzyj perspektywę DBA_FREE_SPACE? Skąd wzięło się tyle ekstentów?

Sprawdź stopień pofragmentowania przestrzeni tabel w perspektywie DBA_FREE_SPACE_COALESCE. Które przestrzenie tabel trzeba zdefragmetować?

Wykonaj defragmentację wszystkich przestrzeni tabel, które tego wymagają, a następnie sprawdź ponownie perspektywę DBA_FREE_SPACE_COALESCE.

X. Obsługa klastrów

Cele

W tej lekcji będziemy omawiać zarządzanie segmentami klastrowymi Oracle, w tym sposoby określania rozmiaru klastrów i klastrów haszujących.

Po przerobieniu tej lekcji powinieneś być w stanie:

Opisać zalety i wady stosowania klastrów.
Utworzyć klaster.
Opisać metodę haszowania.
Utworzyć klaster haszujący.

Ta lekcja zawiera materiał specyficzny dla pewnych konfiguracji lub implementacji Oracle. Instruktor może ograniczyć omawianie tego tematu w zależności od potrzeb słuchaczy. Pełny tekst, ćwiczenia i rozwiązania mogą być wykorzystane w samodzielnej nauce.

Przegląd

Segmenty danych zawierają dane wstawiane do tabel.

Cechy Segmentu Danych

Typy Segmentu Danych

Funkcja

Klaster indeksowy

(index cluster)

Zawiera wiersze jednej lub wielu tabel pogrupowane fizycznie na podstawie wartości pewnej kolumny tabeli.

Klaster haszujący

(hash cluster)

Zawiera wiersze pewnej tabeli pogrupowane fizycznie przez zastosowanie algorytmu haszowania.

Klastry definiują strukturę przechowywania. Każdy klaster zawiera jedną lub wiele definicji tabel.
Klastry haszujące mogą zawierać więcej niż jedną tabelę, ale są to rzadkie przypadki.
Tabele w klastrze są wykorzystywane przez użytkowników w ten sam sposób co tabele samodzielne.

Klastry Indeksowe

Utworzenie klastra (cluster) to opcjonalna metoda przechowywania tabel, która może poprawić wydajność I/O i zmniejszyć narzut wykorzystania przestrzeni. Klucz klastra (cluster key) stanowią kolumny wspólne dla sklastrowanych tabel. Indeks klastra (cluster index) jest indeksem utworzonym na kluczowych kolumnach klastra.

0x01 graphic

Tabele w klastrze:

Mają wspólne kolumny.
Często używane razem.
Przechowywane w tych samych blokach danych.

Zalety użycia klastrów

Zredukowane operacje dyskowe I/O, ponieważ powiązane ze sobą wiersze są przechowywane w tych samych blokach danych.
Poprawione czasy dostępu przy złączeniach sklastrowanych tabel.
Zredukowana przestrzeń indeksów - zwłaszcza kiedy klucz klastra ma wiele powtarzających się wartości - ponieważ klucz klastra przechowywany jest w indeksie tylko raz.

Teoretycznie maksymalna liczba kolumn klucza klastra wynosi 16. Praktycznie zależy ona od rozmiaru bloku danych, ponieważ rozmiar wartości klucza klastra nie może przekraczać jednej trzeciej rozmiaru bloku danych.

Klastry mogą zwiększyć efektywność zapytań, ale mogą zmniejszyć efektywność operacji wstawiania, modyfikacji i usuwania oraz pełnego przeglądania tabeli.

Przechowywanie tabeli w klastrze wymaga większej liczby bloków danych w porównaniu do tabeli samodzielnej, ponieważ bloki danych w klastrze są dzielone. Jeśli w klastrze jest tylko jedna tabela, ten narzut jest jednak nieznaczny. Stąd właśnie potencjalne zmniejszanie efektywności poleceń DML. Ogólnie, czasami nie warto przechowywać w klastrze bardzo często aktualizowanej tabeli.

Aplikacje używają tabel sklastrowanych automatycznie. Istnienie klastrów jest niewidoczne dla użytkowników i aplikacji.

Jeśli sklastrowanych jest wiele tabel, pełny przegląd tabeli staje się wolniejszy, ponieważ muszą być przeglądane wszystkie rekordy wszystkich tabel. Klaster z pojedynczą tabelą nie ma tej wady.

Zarządzanie Klastrami

Pewne tabele dobrze nadają się do klastrowania.

Tabele Kandydujące do Klastra

Przede wszystkim przeszukiwane, rzadko aktualizowane.
Zawierające powtarzające się wartości w kolumnie.
Tabele często łączone.

Kluczem klastra jest kolumna lub grupa kolumn wspólna dla klastrowanych tabel. Każda tabela w klastrze musi mieć kolumnę lub kolumny o typie i rozmiarze pasującym do klucza klastra.

Klucz Klastra

Dobrymi kluczami klastra są kolumny:

Zawierające szeroki zakres wartości.
Używane do złączeń wielu tabel.

Mniej odpowiednimi kolumnami dla klucza klastra są kolumny:

Z niewieloma różnymi wartościami.
Często aktualizowane.

Zasady

Klucz klastra nie może zawierać kolumn LONG oraz LONG RAW.
Ogólnie, kryteria wyboru dobrego klucza klastra są takie jak kryteria wyboru kolumn do indeksu.
Ponieważ wartość klucza klastra w wierszu może być zmieniana, zmiana ta oznacza, że wiersz musi być przeniesiony. Ponieważ wiersze o tej samej wartości klucza klastra są przechowywane razem, zmiana wartości klucza klastra w wierszu może przenieść wiersz do innego bloku danych. Stąd też kolumny często aktualizowane nie są dobrym wyborem dla klucza klastra.
Jeśli istnieje wiele wierszy dla każdej wartości klucza klastra, wiersze te będą przechowywane w blokach w łańcuchu. Nie należy na przykład używać jako klucza klastrowego kolumny oznaczającej płeć, ponieważ w dużej organizacji ta wartość nie będzie wprowadzała wystarczającego rozróżnienia.
Jeśli jest za mało wierszy z każdą wartością klucza klastra, przestrzeń bloków danych będzie marnowana. Aby tego uniknąć ustawiamy parametr SIZE na małą wartość.

Indeks klastra (cluster index) to indeks utworzony dla klastra zdefiniowany na kolumnach jego klucza.

Indeks klastra

Używany do szybkiego znalezienia wartości klucza klastra.
Wskazuje bezpośrednio na blok danych zawierający wartość klucza klastra.
Pozwala na dostęp do wiersza w minimalnie dwóch operacjach I/O.

Indeks klastra różni się od indeksu tabeli tym, że:

Klucze puste (null) są zarejestrowane w indeksie klastra.
Dostęp do danych z klastra odbywa się tylko przez indeks klastra.
Indeks klastra musi być utworzony przed wstawianiem danych.
Zawiera tylko zapis na klucz.

Zasady

Tworzymy indeks klastra po utworzeniu klastra.
Indeks klastra nie może być unikalny, ani zawierać kolumn typu LONG ani LONG RAW.

Zarządzanie Klastrami

Klaster i jego indeks mogą być przechowywane w różnych przestrzeniach tabel.

Klaster i jego indeks powinny być tworzone w oddzielnych przestrzeniach tabel i na różnych urządzeniach fizycznych tak, aby dane tabel i indeksu mogły być jednocześnie dostępne przy minimalnej rywalizacji o dysk.

Bloki danych klastra mają taki sam format jak niesklastrowane bloki danych z wyjątkiem dodatkowych danych na słownik tabel.

Przydział i użycie przestrzeni przez dane klastra kontrolujemy za pomocą parametrów wykorzystania przestrzeni.

Parametry Przestrzeni

INITRANS
MAXTRANS
PCTUSED
PCTFREE
SIZE
INITIAL
NEXT
MAXEXTENTS
MINEXTENTS
PCTINCREASE

Parametry przestrzeni PCTFREE oraz PCTUSED stosują się do całego klastra (a nie do poszczególnych tabel).

Parametr SIZE wyznacza maksymalną liczbę wartości klucza klastra, które mogą być przechowywane w pojedynczym bloku danych.

Parametr SIZE:

Jest argumentem opcjonalnym.
Określa przybliżoną liczbę bajtów potrzebną na przechowywanie przeciętnego klucza klastra i wszystkich związanych z nim wierszy.
Wyznacza liczbę kluczy klastra przechowywanych w pojedynczym bloku.

Domyślnie serwer Oracle przechowuje jeden klucz klastra i związane z nim wiersze w jednym bloku danych.

Mimo, że parametr SIZE wyznacza maksymalną liczbę kluczy klastra przechowywanych w pojedynczym bloku, w bloku może być mniej kluczy klastrowych. Na przykład jeśli z jednym z kluczy związanych jest wiele wierszy, w bloku może być przechowywane tylko jeden klucz klastra.

Przy wyborze wartości parametru wykorzystania przestrzeni SIZE powinniśmy rozważyć dwa warunki:

Jeśli parametr wykorzystania pamięci SIZE będzie zbyt duży, marnujemy przestrzeń (zbyt mało kluczy przechowywanych w bloku klastra).
Jeśli parametr wykorzystania pamięci SIZE będzie za mały, wtedy rekordy z tą samą wartością klucza przechowywane będą w różnych blokach klastra (przy próbie wstawienia do bloku klastra nie mieszczących się rekordów).

Tworzenie klastra

Do tworzenia klastra służy polecenie CREATE CLUSTER.

Składnia:

0x01 graphic

gdzie:

schemat schemat, który ma zawierać klaster. Domyślnie schemat bieżącego użytkownika.

klaster jest nazwa klastra.

SIZE określa rozmiar przestrzeni (w bajtach, kilobajtach lub megabajtach) na umieszczenie wszystkich wierszy z określoną wartością klucza.

INDEX tworzy klaster indeksowy.

0x01 graphic

Uwaga: Inne opcje były już omawiane.

Po utworzeniu klastra, mogą w nim być tworzone tabele. Jednakże zanim zostaną wstawione wiersze do sklastrowanych tabel, trzeba utworzyć indeks klastra. Użycie klastrów nie pozbawia nas możliwości stosowania dodatkowych indeksów na tabelach w klastrze - mogą one być tworzone i usuwane w zwykły sposób.

Tworzenie Indeksu Klastra

Najpierw tworzymy klaster a następnie indeks klastra.
Do tworzenia indeksu klastra zbudowanego na klucze klastra służy polecenie CREATE INDEX.

Składnia:

0x01 graphic

gdzie:

schemat to schemat gdzie ma być indeks.

indeks to nazwa tworzonego indeksu.

klaster określa klaster, na którym tworzony jest indeks.

Przy tworzeniu indeksu klastra stosujemy te same zasady co dla zwykłych indeksów.

Przykłady

Utworzenie klastra o nazwie CLUSTER_T1_T2 z kolumną DEPTNO stanowiącą klucz klastra. Klaster ma być umieszczony w przestrzeni tabel TBS_DATA. Na przechowanie grupy wierszy ma być przeznaczone 400 bajtów, pierwszy ekstent ma mieć 30 KB.

SQL> CREATECLUSTER cluster_T1_T2 (dept NUMBER (3))

2> SIZE 400

3> TABLESPACE tbs_data

4> STORAGE (INITIAL 30K);

SQL>CREATE INDEX i_clu_T1_T2

2> ON CLUSTER cluster_T1_T2

3> TABLESPACE tbs_index;

SQL> CREATE TABLE T1 (name VARCHAR2 (20), hire_date DATE,

2> deptno NUMBER (3))

3> CLUSTER cluster_T1_T2 (deptno);

SQL>CREATE TABLE T2 (deptno NUMBER (3),

2> deptname VARCHAR2 (15))

3> CLUSTER cluster_T1_T2 (deptno);

Modyfikowanie Klastra

Do modyfikacji klastra służy polecenie ALTER CLUSTER.

W klastrze można zmodyfikować

Parametry zarządzania przestrzenią.
Parametry wykorzystania przestrzeni bloku danych (PCTFREE i PCTUSED).
Średni rozmiar klucza klastra (SIZE).
Ustawienia zapisów transakcji (INITRANS i MAXTRANS).

Składnia:

0x01 graphic

gdzie:

schemat jest schematem zawierającym klaster. Domyślnie schemat bieżącego użytkownika.

klaster to nazwa modyfikowanego klastra

SIZE określa nowy rozmiar.

ALLOCATE EXTENT jest rozmiarem (KB lub MB) dodawanego ekstentu.

SIZE domyślnie używane są parametry zarządzania przestrzenią klastra

DATAFILE jest jednym z plików danych należących do przestrzeni tabel klastra. Jeśli nie jest podany, wyboru dokona serwer Oracle.

INSTANCE jest używane w trybie równoległym do określenia numeru instancji, która ma używać klastra. Domyślną wartością jest ALL (wszystkie).

Opcje PCTUSED, PCTFREE, INITRANS, MAXTRANS i klauzula_przestrzeni zmieniają wartości parametrów bloku i ekstentu.

Nowe wartości dla parametrów PCTFREE, PCTUSED i SIZE stosują się do wszystkich bloków danych klastra (w tym już istniejących, jak i nowo przydzielanych). Jednak bloki utworzone wcześniej nie są reorganizowane natychmiast. Zmiany następują w miarę potrzeby. Parametry zarządzania przestrzenią INITIAL i MINEXTENTS nie mogą być zmieniane.

Zmiany INITRANS stosują się tylko do nowo dodawanych bloków danych. Zmiany MAXTRANS dotyczą wszystkich bloków.

Przykłady

Zmienić w klastrach CLU1, CLU2, CLU3 parametry wykorzystania przestrzeni i wykorzystania bloku.

SQL> ALTER CLUSTER clu1

2> STORAGE (NEXT 200K PCTINCREASE 33);

SQL>ALTER CLUSTER clu2

2> SIZE 512;

SQL> ALTER CLUSTER clu3

2> PCTFREE 40 PCTUSED 10;

Sklastrowane tabele mogą być modyfikowane poleceniem ALTER TABLE. Jednak polecenia modyfikacji parametrów wykorzystania przestrzeni bloków, zapisów transakcji i zarządzania przestrzenią są ignorowane. Trzeba je zmieniać na poziomie klastra.

Usuwanie Klastra

Kiedy klaster nie jest potrzebny, można go usunąćpoleceniem DROP CLUSTER.

Kiedy klaster jest usuwany:

Usuwane są także wszystkie jego tabele.
Usuwany jest indeks klastra.
Wszystkie ekstenty są zwalniane.

Składnia:

0x01 graphic

gdzie:

schemat to schemat zawierający usuwany klaster. Domyślnie schemat bieżącego użytkownika.

klaster to nazwa usuwanego klastra.

INCLUDING TABLES powoduje usunięcie tabel należących do klastra. Jeśli nie jest podane a, nie wszystkie tabele z klastra zostały usunięte, operacja się nie powiedzie i spowoduje błąd.

CASCADE CONSTRAINTS usuwa wszystkie związane referencyjne warunki integralności. Jeśli nie jest podane istnienie takich warunków spowoduje błąd.

Przykład

Usunięcie klastra CLU1 i wszystkich jego tabel.

SQL> DROP CLUSTER clu1

2> INCLUDING TABLES;

lub

SQL> DROP TABLE T1;

SQL> DROP TABLE T2;

SQL> DROP CLUSTER T1_T2;

Poszczególne tabele mogą być usuwane z klastra poleceniem DROP TABLE. Podczas usuwania pojedynczej tabeli:

Klaster, inne tabele oraz indeks pozostaje nie zmieniony.
Klaster powinien być utworzony ponownie w celu zmniejszenia fragmentacji.
Monitorujemy lub usuwamy indeks klastra jak inne indeksy.

Możemy usuwać indeks klastra aby zmienić parametry wykorzystania przestrzeni lub położenie indeksu klastra. Jednak musimy odtworzyć indeks klastra aby mieć dostęp do danych w tabelach klastra.

Zalety i Wady Klastrów Indeksowych

Klastry indeksowe umożliwiają szybszy dostęp do wielu rekordów o tej samej wartości klucza klastra.
Szybsze są operacje wybierania, aktualizacji i usuwania.
Pojedyncza operacja wstawiania jest nieco wolniejsza niż w przypadku tabeli samodzielnej.
Ładowanie danych do tabeli jest znacznie wolniejsze.
Dla każdego rekordu musi być odczytywany indeks klastra w celu odszukania bloku w którym powinien być wstawiony wiersz.
Nie jest możliwe ładowanie danych do tabel w klastrze bez utworzonego indeksu (stąd indeks klastra musi być aktualizowany przy wstawieniu każdej nowej wartości klucza).
Indeks klastra musi być częściej modyfikowany, więc generowanych jest więcej informacji wycofania i dziennika powtórzeń.
Bloki mogą nie mieścić się w SGA i wtedy muszą być odczytywane wielokrotnie.
Klastry indeksowe będą prawdopodobnie potrzebowały więcej przestrzeni niż zwykła tabela jeśli liczba i/lub rozmiar rekordów z tym samym kluczem klastra są bardzo zmienne.
Klastry indeksowe potencjalnie oszczędzają przestrzeń na indeksy (jeśli klaster indeksowy może zastąpić indeksowanie).
Klastry indeksowe potencjalnie mogą zaoszczędzić nieco przestrzeni jeśli klucz klastra jest stosunkowo duży.
Ładowanie ścieżką bezpośrednią nie jest możliwe.

Haszowanie

Haszowanie (hashing) to inny sposób przechowywania danych tabeli poprawiający efektywność wyszukiwania danych.

Typowe Zastosowania Haszowania

Stosowanie funkcji haszującej do kolumn tabeli.
Przechowywanie wierszy zgodnie z wynikami funkcji haszującej.
Zmniejszenie średniej liczby operacji

Haszowania używamy kiedy

Rozmiar tabel nie jest bardzo zmienny.
Pierwszorzędnym zadaniem jest optymalizacja zapytań.
Kryteriami zapytań jest zawsze równość dotycząca haszowanych kolumn.
Klucz haszowania jest dobrze rozłożony.

0x01 graphic

Uwaga: Aby zastosować haszowanie, tabela musi być sklastrowana. Przed utworzeniem tabeli używającej algorytmu haszowania musi być utworzony klaster haszujący.

0x01 graphic

Funkcja Haszująca

Funkcja haszująca (hash function) jest funkcją, która zastosowana na kluczu klastra zwraca wartość haszującą.

Funkcja haszująca:

Może być stosowana do jednej kolumny lub klucza złożonego.
Powinna powodować optymalne rozłożenie wierszy na wartości haszujące.
Próbuje zminimalizować liczbę niepożądanych kolizji.
Może być zdefiniowana przez użytkownika.

Kolizje mają miejsce kiedy dwie wartości klucza klastra dają w wyniku identyczną wartość haszującą. Użytkownik może wskazać kolumnę spełniającą takie kryteria, która ma być używana jako funkcja haszująca. Rozważania dotyczące opcji HASH IS (haszem jest) znajdują się w dalszej części tego rozdziału.

Tworzenie Klastra Haszującego

Do tworzenia klastra haszującego służy polecenie CREATE CLUSTER.

Składnia:

0x01 graphic

Uwaga: To nie jest pełna składnia polecenia.

gdzie:

schemat jest nazwą schematu tworzonego klastra. Domyślnie schemat bieżącego użytkownika.

klaster to nazwa tworzonego klastra.

SIZE określa rozmiar przestrzeni (w bajtach) na przechowanie wszystkich wierszy z tą samą wartością klucza haszującego. Domyślnie jeden blok.

HASHKEYS określa, że ma być utworzony klaster haszujący (domyślnie zostałby utworzony klaster indeksowy). Pozwala także na ustawienie liczby wartości haszujących. Jeśli podana liczba nie jest liczbą pierwszą, serwer Oracle wybiera następną większą liczbę pierwszą.

HASH IS specyfikuje kolumnę dostarczającą wartości haszujących. Zastępują one WYNIKI funkcji haszującej. Podana kolumna musi być jedyną kolumną klucza klastra i zawierać tylko liczby naturalne).

0x01 graphic

Uwaga: Inne opcje były omawiane wcześniej. Liczba wartości haszujących klucza haszującego jest ustalona w momencie jego tworzenia.

Ustawienie Liczby Wartości Haszujących

Wykorzystujemy parametr HASHKEYS polecenia CREATE CLUSTER.
Serwer Oracle zwiększa wartość HASHKEYS do najbliższej liczby pierwszej. Jeśli na przykład HASHKEYS podamy 100, funkcja haszująca będzie generować 101 wartości haszujących (od 0 do 100).
Minimalna wartość HASHKEYS to 2.

Wartość HASHKEYS podawana przy tworzeniu klastra haszującego wyznacza prawdopodobieństwo, że dwa klucze klastra będą miały tę samą wartość haszującą (wystąpi kolizja). Jeśli występuje wiele kolizji, musi być zwiększony rozmiar klucza haszującego i wtedy mniej kluczy mieści się w pojedynczym bloku danych. Minimalizacja liczby kolizji jest istotna, ponieważ na przechowywanie wierszy z kolidującymi wartościami klucza klastra mogą być niezbędne bloki nadmiarowe. Zakładając równomierny rozkład funkcji haszującej, jeśli HASHKEYS jest n, dane mają szanse spowodowania kolizji 1/n.

Liczbę kluczy haszujących przypisanych do pojedynczego bloku danych określamy za pomocą parametru SIZE. Parametr SIZE powinien być ustawiony na średni rozmiar przestrzeni potrzebnej na przechowanie wszystkich wierszy z danym kluczem haszującym.

Ustawienie Liczby Kluczy Haszujących na Blok Danych

Jeśli klaster haszujący zawiera pojedynczą tabelę i tylko jeden wiersz przypada na wartość klucza, ustawiamy parametr SIZE na średni rozmiar wiersza w tabeli. Jeśli więcej niż jeden wiersz przypada na wartość klucza, mnożymy tę liczbę przez liczbę wierszy na wartość klucza.
Jeśli klaster haszujący zawiera wiele tabel, SIZE powinien być ustawiony na średni rozmiar przestrzeni wymaganej na przechowanie wszystkich wierszy związanych z typową wartością haszującą.
Przeszacowanie wartości SIZE zwiększa rozmiar niewykorzystanej przestrzeni w klastrze.

Wartości parametrów HASHKEYS i SIZE mają wpływ na umieszczenie danych w blokach klastra haszującego.

Bloki Klastra Haszującego

Przy małym średnim rozmiarze klucza haszującego do pojedynczego bloku danych może być przypisanych wiele wartości klucza haszującego.
Przy dużym średnim rozmiarze klucza haszującego niewielka liczba wartości zmieści się w każdym bloku danych.
Częste kolizje mogą spowodować tworzenie bloków nadmiarowych.
Kiedy nowy wiersz jest zbyt duży i nie mieści się w oryginalnym bloku danych, tworzony jest blok nadmiarowy, co powoduje zwiększenie liczby operacji I/O przy wybieraniu tych danych.

Przykład

Tworzenie klastra haszującego EMP_H_CLU na kolumnie JOB w przestrzeni tabel TS_USERS. Zakładamy rozmiar bloku 2K i rozmiar przestrzeni danych w bloku 1800 bajtów. Zakładamy też, że SIZE został oszacowany na 500 bajtów, a istnieje 40 stanowisk (wartości haszujących).

SQL> CREATE CLUSTER emp_h_clu (JOB VARCHAR2 (9))

2> TABLESPACE ts_users

3> SIZE 500

4> HASHKEYS 40;

Cluster created

Serwer Oracle w każdym bloku będzie przechowywał po 3 klucze haszujące. Całkowita liczba kluczy haszujących będzie 41 (następna liczba pierwsza większa niż 40).

Serwer Oracle oblicza liczbę kluczy haszujących na blok przez podzielenie dostępnej przestrzeni przez wartość SIZE i zaokrąglenie wyniku w dół. Serwer Oracle od razu rezerwuje odpowiednią liczbę bloków na wszystkie klucze haszujące. W tym przykładzie są 3 klucze na blok, jest 41 kluczy, tak więc początkowo klaster będzie zawierał 14 bloków. Innymi słowy serwer Oracle uważa, że w klastrze haszującym istnieje 14 bloków danych, nawet jeśli tabela nie wymaga 14 bloków przestrzeni.

Parametr HASH IS

Przez parametr HASH IS wskazujemy kolumnę, której wartości mają być używane jako wartości haszujące.

Kolumna HASH IS

Musi być jedyną kolumną klucz klastra.
Klucz klastra musi składać się z pojedynczej kolumny typu NUMBER.
Klucz klastra musi zawierać tylko liczby naturalne.
Może zawierać funkcję haszującą definiowaną przez użytkownika (Oracle wersja 7.2 i późniejsze).
Jeśli te warunki nie są spełnione, używana jest wewnętrzna funkcja haszująca lub funkcja haszująca zdefiniowana przez użytkownika.

Przykłady

Utworzenie klastra haszującego EMP_RAISE w przestrzeni tabel TS_USERS. Jako funkcję haszującą wykorzystujemy kolumnę EMPLOYEE_NUMBER. Specyfikujemy 1000 wartości haszujących. Przeznaczamy 55 bajtów na przechowanie wierszy z tą samą wartością haszującą.

SQL>CREATE CLUSTER emp_raise (employee_number NUMBER (4))

2> TABLESPACE ts_users

3> HASHKEYS 1000

4> HASH IS employee_number

5> SIZE 55;

Cluster created.

Utworzenie tabeli COMPENSATION w klastrze EMP_RAISE.

SQL> CREATE TABLE compensation

2> (empno NUMBER (4), ename VARCHAR2 (10),

3> sal NUMBER (10, 2), comm NUMBER (9,0))

4> CLUSTER emp_raise (empno);

Table created.

0x01 graphic

Uwaga: W tym przykładzie parametr SIZE jest mniejszy niż w poprzednim. Możemy założyć, że EMPNO (numer pracownika) jest unikalny i że będzie prawdopodobnie tylko jeden wiersz przypadający na każdą wartość haszującą. Jest to prawdopodobne kiedy podana kolumna zawiera unikalne, równo rozłożone wartości, a wartość HASHKEYS podamy wystarczająco dużą, aby przewyższyła oczekiwaną liczbę wartości w kolumnie. Istnieje większe prawdopodobieństwo kolizji, kiedy jest więcej różnych wartości niż HASHKEYS lub jeśli w wartościach kolumny są przerwy (czyli wartości kolumny nie są równo rozłożone).

Funkcja Haszująca Definiowana przez Użytkownika

Przykład

Utworzenie klastra haszującego zawierającego informacje o pracownikach, gdzie kluczem klastra jest kod zbudowany na podstawie określonej przez użytkownika kombinacji danych o miejscu zamieszkania.

HASH IS MOD((home_area_code+ home_prefix+ home_suffix),101)

Modyfikacja Klastra Haszującego

Do modyfikacji klastra haszującego służy polecenie ALTER CLUSTER.

Składnia:

0x01 graphic

gdzie:

schemat jest nazwą schematu klastra.

klaster jest nazwą modyfikowanego klastra

STORAGE określa nowe wartości dla rozszerzania klastra (MINEXTENS i INITIAL nie mogą być zmienione).

ALLOCATE EXTENT określa SIZE, DATAFILE i INSTANCE używane przy nowym, jawnie rezerwowanym ekstencie.

Parametry PCTUSED, PCTFREE i INITRANS dotyczą tylko nowych bloków.

W klastrze haszującym modyfikujemy parametry zarządzania przestrzenią, wykorzystania przestrzeni bloków danych i ustawienia zapisów transakcji.

Przykład

Modyfikacja klastra haszującego EMP_RAISE w celu zmiany pewnych parametrów zarządzania przestrzenią.

SQL>ALTER CLUSTER emp_raise

2> STORAGE (NEXT 400K PCTINCREASE 0 MAXEXTENTS 5);

Cluster altered

Parametry SIZE, HASHKEYS i HASH IS nie mogą być zmieniane poleceniem ALTER CLUSTER.

W celu modyfikacji SIZE, HASHKEYS lub HASH IS konieczne jest ponowne utworzenie klastra i ponowne załadowanie danych.

Dodatkowe ekstenty klastra haszującego potrzebne są tylko na bloki nadmiarowe.

Usuwanie Klastra Haszującego

Do usuwania klastrów haszujących służy polecenie DROP CLUSTER.

Składnia:

0x01 graphic

gdzie:

schemat to schemat zawierający usuwany klaster.

klaster to nazwa usuwanego klastra

INCLUDING TABLES powoduje usuniecie tabel należących do klastra. Jeśli nie jest podane a nie wszystkie tabele z klastra zostały usunięte, operacja się nie powiedzie i spowoduje błąd.

CASCADE CONSTRAINTS usuwa wszystkie związane referencyjne warunki integralności.

Przykład

Usuwanie klastra haszującego EMP_RAISE razem z jego tabelami.

SQL> DROP CLUSTER emp_raise

2> INCLUDING TABLES;

Cluster dropped

Poszczególne tabele mogą być usuwane, bez usuwania klastra poleceniem DROP TABLE.
Kiedy klaster haszujący jest usuwany, zwalniane są wszystkie jego ekstenty.

Zalety i Wady Klastrów Haszujących

Klastry haszujące dostarczają bardzo szybkiej metody dostępu do pojedynczego rekordu (ponieważ nie jest potrzebny odczyt żadnego indeksu).
Klastry haszujące dostarczają bardzo szybkiej metody dostępu do wielu rekordów o tej samej wartości klucza klastra.
Szybsze są operacje wybierania, aktualizacji i wyszukiwania.
Pojedyncze wstawianie nie jest wolniejsze niż w przypadku zwykłej tabeli.
Ładowanie danych do tabeli jest nieco wolniejsze.
Bloki mogą nie mieścić się w SGA i będą musiały być odczytywane wielokrotnie.
Przestrzeń musi być zarezerwowana inicjalnie, co sprawia, że klastry haszujące mają zastosowanie tylko gdy liczba rekordów bardzo nie wzrasta.
Pełny przegląd tabeli wymaga przejrzenia wszystkich przydzielonych bloków nawet jeśli nie zawierają one wierszy.
Klastry haszujące są używane tylko do kryteriów poszukiwań z równością (kolumna = stała).
Istnieje potencjalne ryzyko przepełnienia jeśli funkcja haszująca nie rozkłada równomiernie danych.
Klastry haszujące wymagają zwykle więcej przestrzeni niż zwykła tabela jeśli liczba i/lub rozmiar rekordów z tym samym kluczem klastra jest bardzo zmienna.
Klastry haszujące potencjalnie oszczędzają miejsce na indeks (jeśli klaster haszujący może zastąpić indeks).
Niemożliwe jest ładowanie do tabeli ścieżką bezpośrednią.

Podsumowanie

Segmenty danych zawierają dane wstawiane do tabel.

Cechy Segmentu Danych

Typ Segmentu Danych

Funkcja

Klaster indeksowy

(index cluster)

Zawiera wiersze jednej lub wielu tabel pogrupowane fizycznie na podstawie wartości pewnej kolumny tabeli.

Klaster haszujacy

(hash cluster)

Zawiera wiersze pewnej tabeli pogrupowane fizycznie przez zastosowanie algorytmu haszowania.

Klastry definiują strukturę przechowywania. Każdy klaster zawiera jedną lub wiele definicji tabel.
Klastry haszujące mogą zawierać więcej niż jedną tabelę ale są to rzadkie przypadki.
Tabele w klastrze są wykorzystywane przez użytkowników w ten sam sposób co tabele samodzielne.

0x01 graphic

Zadania

Utwórz przestrzeń QUERY_DATA na pliku e:\kurs\database\db#\qradm#.ora o rozmiarze 100K i parametrach initial 5K, next 5K, pctincrease 0, inne parametry domyślne

Podłącz się jako DEMO i utwórz tabelę o nazwie S_EMP w przestrzeni tabel QUERY_DATA z następującymi kolumnami:

ID NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME VARCHAR2(25)

DEPTNO NUMBER(3)

Będąc podłączony jako DEMO utwórz klaster haszujący o nazwie EMP_HCLUSTER na 100 wartości kluczy opartych na kolumnie LAST_NAME tabeli S_EMP. Każdy klucz klastra ma mieć rozmiar 100 bajtów. Wykorzystaj przestrzeń tabel QUERY_DATA

Będąc podłączony jako DEMO w klastrze EMP_HCLUSTER utwórz tabelę H_EMP z takim kolumnami jak tabela S_EMP.

Będąc podłączony jako DEMO utwórz klaster indeksowy o nazwie DEPT_EMP_ICLUSTER. Kolumna klucza klastra powinna być typu NUMBER(3). Pozostaw wartości domyślne wszystkich parametrów oprócz następujacych:

TABLESPACE QUERY_DATA

SIZE 600

INITIAL EXTENT 6000

Będąc podłączony jako DEMO utwórz indeks klastra DEPT_EMP_ICLUSTER w przestrzeni tabel QUERY_DATA z domyślnymi pozostałymi parametrami.

Będąc podłączony jako DEMO skopiuj tabelę S_EMP do klastra DEPT_EMP_ICLUSTER używając klomny deptno tej tabeli jako kolumny klucza klastra. Kopię tabeli nazwij I_EMP.

Będąc podłaczony jako DEMO utwórz tabelę I_DEPT w klastrze DEPT_EMP_ICLUSTER. Tabela I_DEPT powinna mieć następujące kolumny:

ID NUMBER(3)

NAME VARCHAR(15)

Do klucza klastra uzyj odpowiedniej kolumny.

Będąc podłączony jako DEMO w perspektywie USER_SEGMENTS obejrzyj ile przestrzeni zajmuje każdy obiekt w przestrzeni tabel QUERY_DATA.

XI. Obsługa więzów integralności

Cele

Lekcja ta wyjaśnia, w jaki sposób utrzymywać spójność danych i deklaratywne więzy integralności.

Po przerobieniu tej lekcji powinieneś potrafić:

Wyjaśnić znaczenie deklaratywnych więzów integralności i spójności danych.
Opisać typy więzów integralności i ich wykorzystanie.
Definiować różne więzy.
Znać kwestie blokowania dotyczące więzów kluczy głównych i obcych.
Badać naruszenia więzów poprzez tabelę wyjątków (exception table).
Korzystać z perspektyw słownika danych do przeglądania więzów.

Przegląd

Charakterystykę danych w tabeli definiujemy za pomocą więzów integralności.

Więzy integralności

Not Null
Unique
Check
Klucz główny (Primary key)
Klucz obcy (Foreign key)

Więzy definiuje się przy:

Tworzeniu tabel.
Modyfikacji tabel.

Więzy mogą być:

Włączane.
Wyłączane.
Usuwane.

Naruszenia więzów mogą być zapisywane w tabeli wyjątków (exceptions table).

Słownik danych zawiera wiele pomocnych informacji o więzach.

Spójność danych i więzy integralności

Integralność danych gwarantuje, że dane w bazie spełniają predefiniowany zastaw więzów lub reguł biznesowych. Kiedy tylko to możliwe należy korzystać z deklaratywnych więzów integralności.

Deklaratywne więzy integralności

Dozwalają lub nie na wartości puste (null values).
Zapewniają w kolumnach wartości unikatowe.
Zapewniają wartości identyfikujące każdy wiersz tabeli.
Zapewniają, że kolumny odwołujące się/zależne zawierają poprawne wartości z tabel, do których się odwołują.

Zalety deklaratywne więzów integralności

Poprawiona wydajność.
Łatwość deklaracji.
Reguły zcentralizowane.
Łatwość modyfikacji.
Natychmiastowa informacja zwrotna dla użytkownika.
Elastyczność (włączanie/wyłącznie).
Pełna dokumentacja w słowniku danych.

Wady deklaratywnych więzów integralności

Jest dość trudno otrzymać przegląd użytkowników, przestrzeni tabel, baz danych.

W niektórych przypadkach do wymuszenia spójności danych korzysta się z wyzwalaczy.

Przykład

Złożone reguły biznesowe mogą zostać wymuszone za pomocą wyzwalaczy. Poniżej mamy kontrolę zmian pensji.

CREATE TRIGGER increase_chk

BEFORE UPDATE OF salary

ON s_emp FOR EACH ROW WHEN (NEW.salary <OLD.salary OR

NEW.salary>1.1* OLD.salary)

BEGIN

Raise_application_error(-20502,

`May not decrease salary. Increase must be<10 % `);

END;

Typy więzów

Za pomocą więzów integralności można wymusić kilka typów spójności danych.

Typy więzów integralności

Not null
Unique
Check
Klucz Główny (Primary key).
Klucz obcy (Foreign key).

Definiowanie Więzów

Więzy można deklarować w momencie tworzenia tabeli. Korzysta się wtedy z polecenia CREATE TABLE i dodaje w nim definicje więzów.

Składnia:

0x01 graphic

gdzie:

schemat jest właścicielem tabeli.

tabela jest nazwą tabeli.

kolumna określa nazwę kolumny w tabeli.

typ_kolumny jest typem danych kolumn

DEFAULT określa wartość jaka ma być wstawiona do kolumny jeśli zostanie ona pominięta w poleceniu INSERT.

column_constraint definiuje więz integralności jako część definicji kolumny.

table_constraint definiuje więz integralności jako część definicji tabeli.

Więzy mogą zostać zdefiniowane albo przy kolumnie - inline albo jako część definicji tabeli - out-of-line. Więzy przy kolumnach wprowadzają reguły dotyczące tylko tej kolumny, przy której są definiowane. Więz przy kolumnie jest specyfikowany jako fragment definicji kolumny.

Składnia:

Column_constraint::=

0x01 graphic

gdzie:

CONSTRAINT identyfikuje więz za pomocą nazwy constraint. Nazwa jest przechowywana w bazie.

NULL określa, że dla kolumny dopuszczalne są wartości null. Ustawienie domyślne.

NOT NULL oznacza, że w kolumnach nie może być wartości null.

UNIQUE definiuje kolumnę lub kombinację kolumn jako nie powtarzalne (unique).

PRIMARY KEY definiuje kolumnę lub kombinację kolumn jako klucz tabeli.

FOREIGN KEY definiuje kolumnę lub kombinację kolumn jako klucz obcy.

REFERENCES identyfikuje klucz główny lub unique, który jest wskazywany przez klucz obcy.

ON DELETE CASCADE specyfikuje, iż Serwer Oracle utrzymuje spójność referencyjną poprzez automatyczne usuwanie wierszy z zależnymi wartościami klucza obcego gdy usuwana jest wartość z klucza głównego lub unique.

CHECK definiuje warunek, który musi być spełniony dla każdego wiersza.

USING INDEX określa parametry indeksu, jakiego Serwer Oracle używa do wymuszenia klucza głównego lub klucza unique. Nazwa indeksu jest taka sama jak nazwa constraintu.

NOSORT oznacza, że wiersze są posortowane w porządku rosnącym i Oracle nie musi ich sortować przy tworzeniu indeksu.

DISABLE wyłącza więz integralności. Jeśli więz integralności jest wyłączony, Serwer Oracle nie wymusza go.

Więzy out-of-line są częścią definicji tabeli i mogą definiować reguły dla dowolnych kolumn tabeli. Więz taki specyfikowany jest po wyspecyfikowaniu wszystkich kolumn tabeli.

Składnia:

Table_constraint::=

0x01 graphic