Bieżący stan bazy danych Oracle oraz wszystkie informacje o wszystkich obiektach bazy danych można znaleźć w tzw. słowniku systemowym.

Słownik systemowy to zbiór perspektyw, które udostępniają użytkownikom wszystkie informacje na temat zawartości, budowy i bieżącego stanu bazy.

W słowniku systemowym znajdują się dwie grupy perspektyw. Perspektywy opisujące dynamiczne parametry instancji bazy danych oraz perspektywy opisujące statyczne obiekty w bazie.

Perspektywy dynamiczne

Nazwy tych perspektyw rozpoczynają się od v$...., w perspektywach tych można znaleźć wszystkie informacje na temat aktualnego stanu instancji bazy danych.

Przykładowe perspektywy dynamiczne:

v$parameter – zawiera informacje o parametrach inicjujących instancji,

v$session – zawiera informację o wszystkich aktualnie otwartych sesjach

v$lock – zawiera informację o wszystkich aktualnie założonych blokadach

Przykład

SELECT *
  FROM v$version;
                        

Przykład

SELECT username, program, module
  FROM v$session
 WHERE username IS NOT NULL
                        

Perspektywy statyczne

Perspektywy te zawierają informację o wszystkich obiektach zdefiniowanych w bazie danych.
Nazwy tych perspektyw zaczynają się od USER_..., ALL_... lub DBA_... i zawierają odpowiednio informacje o obiektach, których użytkownik jest właścicielem, do których ma dostęp i o wszystkich obiektach w bazie danych.

Przykładowe perspektywy statyczne:

user_tables – informacje o tabelach, których użytkownik jest właścicielem

all_tables – informacje o tabelach, których użytkownik jest właścicielem i do których ma jakiekolwiek prawa

dba_tables – informacje o wszystkich tabelach w bazie danych

Przykład

SELECT DISTINCT privilege, admin_option
  FROM dba_sys_privs
 WHERE grantee IN ('RESOURCE','CONNECT');
                        

Oprócz dwu powyższych grup istnieje jedna specjalna perspektywa, który zawiera listę wszystkich innych perspektyw w postaci nazwy perspektywy i krótkiego opisu jej zawartości. Ta specjalna perspektywa nazywa się DICTIONARY.

Perspektywy

Wynik każdego zapytania tworzy w sposób dynamiczny tabelę. Takie zapytanie można zapamiętać w bazie danych jako nowy obiekt. Obiekt taki nazywamy perspektywą.
Perspektywy są strukturami całkowicie dynamicznymi, tzn. zapytanie, które je definiuje w momencie definicji jest tylko sprawdzane pod względem poprawności składniowej i semantycznej, natomiast nie jest wykonywane. Zapytanie to jest wykonywane w momencie odwoływania się do perspektywy.
Perspektywy są obiektami bardzo często stosowanymi w aplikacjach bazodanowych opartych na bazie Oracle.

Wymagane przywileje

Aby utworzyć perspektywę potrzebny jest przywilej systemowy CREATE VIEW i przywilej SELECT na obiekcie, do którego definicja perspektywa się odwołuje.
W momencie odwoływania się do perspektywy wystarczą prawa obiektowe do perspektywy, natomiast nie są potrzebne prawa do obiektów, do których się odwołuje definicja tej perspektywy.

Tworzenie i usuwanie perspektyw

Składnia tworzenia perspektywy:

CREATE [OR REPLACE] [FORCE] VIEW nazwa [(kolumna{, kolumna})] 
AS
zapytanie
[WITH READ ONLY]
[WITH CHECK OPTION];
                        

Składnia polecenia usuwania perspektywy:

DROP VIEW nazwa;
                        

Użycie opcji FORCE przy tworzeniu perspektywy spowoduje, że perspektywa zostanie utworzony nawet pomimo niepoprawności zapytania, które go definiuje. Informacje o już zdefiniowanych perspektywach można odczytać w perspektywach słownika systemowego: user_views, all_views, dba_views.

Zastosowanie perspektyw

Zmiana nazw kolumn

CREATE VIEW rabaty (rabat, cena_min, cena_max)
    AS SELECT grc_rabat, grc_cena_od, grc_cena_do
         FROM grupy_cen;
desc rabaty;
SELECT * 
  FROM rabaty;
                        

Ukrywanie części kolumn – wykonanie projekcji na tabeli

CREATE VIEW ceny
    AS SELECT tow_nazwa, tow_cena
         FROM towary;
                        

Ukrywanie wierszy – dokonanie selekcji na tabeli

CREATE VIEW klient_indywidualny
    AS SELECT * 
         FROM klienci
        WHERE klt_typ = 'IND';
                        

Delegowanie przywilejów

CREATE VIEW zamowienia_ind
    AS SELECT Z.* 
         FROM zamowienia Z, klienci K
        WHERE Z.zam_klt_id = K.klt_id
          AND K.klt_typ = 'IND';
REVOKE ALL ON zamowienia FROM kowalski;
GRANT SELECT ON zamowienia_ind TO kowalski;
                        

Agregacja obliczeń

CREATE VIEW podsumowanie
    AS SELECT T.tow_nazwa nazwa,
              sum(P.pzm_ilosc) sprzedane
         FROM towary T, pozycje_zamowien P
        WHERE T.tow_id = P.pzm_tow_id
        GROUP BY T.tow_nazwa;
                        

Upraszczanie zapytań do wielu tabel

CREATE VIEW zamowienia_klientów
    AS SELECT decode(K.klt_typ,
                     'IND', K.klt_nazwisko,
                     K.klt_nazwa) klienci,
              Z.zam_opis zamowienia
         FROM klienci K, zamowienia Z
        WHERE K.klt_id = Z.zam_klt_id;
                        

Operacje DML na perspektywach

W zapytaniach perspektywy traktowane są dokładnie tak jak tabele. Natomiast w przypadku operacji DML możliwe jest ich wykonywanie na perspektywach, ale pod warunkiem, że perspektywy te spełniają pewne założenia, zróżnicowane w stosunku do rodzaju operacji.

DELETE

Aby można było wykonać operację DELETE na perspektywie, perspektywa nie może być oparta na zapytaniu zawierającym:

• Złączenie
• Funkcje grupowe lub klauzulę GROUP BY
• Klauzulę DISTINCT
• Skorelowane podzapytania

UPDATE

Aby można było wykonać operację UPDATE na perspektywie, perspektywa nie może być oparta na zapytaniu zawierającym:

• Wszystko co dotyczy operacji DELETE
• Wyrażenie w kolumnie

INSERT

Aby można było wykonać operację INSERT na widoku, perspektywie, perspektywa nie może być oparta na zapytaniu zawierającym:

• Wszystko, co dotyczy operacji UPDATE
• Tabela, na której oparta jest perspektywa zawiera kolumnę NOT NULL bez wartości domyślnej, i kolumna ta nie jest odwzorowana w perspektywie.

Powyższe obostrzenia można pominąć definiując odpowiednie wyzwalacze typu instead-off.

Jeżeli perspektywa zostanie utworzona z klauzulą WITH READ ONLY, to nie będzie można wykonać na niej żadnych operacji DML.

Jeżeli perspektywa zostanie utworzony z klauzulą WITH CHECK OPTION, to nie będzie można wstawić do niej żadnego wiersza, który nie byłby później widoczny w tej perspektywie.

Sekwencje

Sekwencja jest obiektem bazy danych Oracle, służącym do generowania unikatowych numerów służących najczęściej do zasilania kolumn z kluczem głównym w tabelach.

Można ją sobie wyobrazić jako ciąg liczb o określonych właściwościach z wskaźnikiem, który wskazuje liczbę na pewnej pozycji. Pobranie liczby z sekwencji powoduje, że zwrócona zostaje liczba wskazywana przez ten wskaźnik, natomiast wskaźnik automatycznie zaczyna wskazywać kolejną liczbę. Dzięki temu mechanizmowi zagwarantowane jest, że za każdym odwołaniem do sekwencji zwrócony zostanie inny numer.

Tworzenie, modyfikacja i usuwanie

Tworzenie sekwencji

CREATE SEQUENCE nazwa_sekwencji
  [INCREMENT BY liczba]
    [START WITH liczba]
      [MINVALUE liczba]
      [MAXVALUE liczba]
         [CYCLE];
                        

Przykład

CREATE SEQUENCE seq_towary
   INCREMENT BY 10
     START WITH 1000
       MAXVALUE 10000;
                        

Zmiana sekwencji

ALTER SEQUENCE nazwa_sekwencji
 [INCREMENT BY nowa_liczba]
     [MINVALUE nowa_liczba]
     [MAXVALUE nowa_liczba];
                        

Usuwanie sekwencji

DROP SEQUENCE nazwa_sekwencji;
                        

Informacje o już zdefiniowanych sekwencjach można odczytać w widokach słownika systemowego: user_sequences, all_sequences, dba_sequences.

Korzystanie z sekwencji

Aby pobrać kolejny numer z sekwencji należy odwołać się do sekwencji w następujący sposób:

nazwa_sekwencji.nextval
                        

Można również pobrać bieżący numer z sekwencji odwołując się w następujący sposób.

nazwa_sekwencji.currval
                        

Do sekwencji można się odwoływać w zapytaniach oraz w poleceniu INSERT.

Przykład

SELECT seq_towary.nextval
  FROM dual;
                        

Przykład

SELECT seq_towary.nextval Lp, zam_opis
  FROM zamowienia;
                        

Przykład

INSERT INTO towary (tow_id,
                   tow_nazwa,
                   tow_jednostka,
                   tow_cena,
                   tow_podatek)
     VALUES (seq_towary.nextval, 'Zegar', 'szt', '10', '7');
                        

Sekwencja jest obiektem niezależnym od tabeli, i to użytkownik decyduje, że numer pobrany z sekwencji ma trafić do jakiejś konkretnej tabeli.

Sekwencja nie podlega transakcjom, tzn. że nawet po wycofaniu transakcji w której były pobierane wartości z sekwencji, sekwencja ta „nie cofa się”.

Indeksy

Indeksy pozwalają zdecydowanie przyspieszyć wyszukiwanie wierszy w tabelach.

W przypadku braku indeksu znalezienie wiersza spełniającego pewien warunek wymagałoby przejrzenia sekwencyjnie całej tabeli. W przypadku dużych i bardzo dużych tabel, takie wyszukiwanie mogłoby trwać nawet kilka godzin.

Indeks jest najczęściej zorganizowany w postaci drzewa, w którym bardzo szybko można znaleźć adres fizycznego położenia wierszy spełniających pewne warunki, a dzięki temu, niezależnie jak duża jest tabela, wyszukiwanie trwa zawsze bardzo krótko.

Tworzenie indeksu

CREATE INDEX nazwa_indeksu
    ON nazwa_tabeli (kolumna{, kolumna});
                        

Tworzenie indeksu

CREATE INDEX nazwa_indeksu
    ON nazwa_tabeli (kolumna{, kolumna});
                        

Usuwanie indeksu

DROP INDEX nazwa_indeksu;
                        

Przykład

CREATE INDEX cena_idx
    ON towary(tow_cena);
                        

W bazach danych Oracle istnieje wiele sposobów indeksowania danych. Odpowiedni dobór indeksów jest kluczową kwestią, jeżeli chodzi o wydajność aplikacji osadzonych na bazie danych Oracle, jednak ten bardzo obszerny i trudny temat nie należy do tego kursu.

Informacje o już zdefiniowanych indeksach można odczytać w widokach słownika systemowego: user_indexes, all_indexes, dba_indexes.

Komentarze

W bazie danych Oracle można umieszczać komentarze do tabel i kolumn.
Nie należy mylić tych komentarzy z komentarzami w kodzie skryptu tworzącego obiekty, ponieważ taki komentarz zostaje po prostu pominięty i odrzucony podczas wykonywania się poleceń.

Komentarze dodaje się następującym poleceniem:

COMMENT ON TABLE | COLUMN nazwa_tabeli[.nazwa_kolumny]
     IS 'komentarz';
                        

Przykład

CREATE INDEX cena_idx
    ON towary(tow_cena);
                        

Aby zmodyfikować komentarz należy tym samym poleceniem nadpisać starą wersję.

Przykład

COMMENT ON COLUMN towary.tow_cena
     IS 'Cena netto';
                        

Aby usunąć komentarz, należy go zmodyfikować powyższym poleceniem na napis pusty: ''.

Komentarze do poszczególnych obiektów można znaleźć w słowniku systemowym w widokach user_tab_comments, user_col_comments, all_tab_comments, all_col_comments, dba_tab_comments, dba_col_comments.

Synonimy

Synonimy są to zastępcze nazwy dla innych obiektów bazodanowych. Wprowadza się je zazwyczaj dla uproszczenia odwołań do obiektów, chociaż czasem można wykorzystać je do bardziej „ambitnych” celów, np. uniezależnienie się w aplikacji od nazwy schematu, na którym znajdują się przetwarzane dane.
W przeciwieństwie do widoków, do synonimów nie można określić praw. Aby móc odwoływać się do obiektu poprzez synonim, należy mieć prawa do obiektu, na którym jest synonim oparty.

Tworzenie synonimów

CREATE [PUBLIC] SYNONYM nazwa_syninimu
   FOR [wlasciciel.]nazwa_obiektu;
                        

Usuwanie synonimu

DROP [PUBLIC] SYNONYM nazwa_synonimu.
                        

Przykład

CREATE SYNONYM towary
   FOR student.towary;
                        

Jeżeli synonim zostanie utworzony jako publiczny, to będzie on automatycznie widoczny przez wszystkich użytkowników. Nie oznacza to jednak, że wszyscy użytkownicy będą mogli się odwoływać do obiektu, do którego odnosi się synonim.

Przykładem publicznego synonimu jest dual. W rzeczywistości jest to obiekt użytkownika sys, ale dzięki synonimowi publicznemu każdy użytkownik może się odwoływać do tego obiektu bez poprzedzania go nazwą właściciela.

Jeżeli zdarzy się, że użytkownik posiada własną tabelę oraz synonim do tabeli innego użytkownika o tej samej nazwie, to odwołania do tej nazwy będą kierowane do prywatnej tabeli tego użytkownika.

Informację o istniejących synonimach można odczytać ze słowników systemowych user_synonyms, all_synonyms, dba_synonyms.

Linki bazodanowe

Linki bazodanowe (Database links) są to obiekty, które definiują odwołania do innej bazy danych, która może znajdować się nawet w zupełnie innym systemie połączonym poprzez sieć WAN.

Linki bazodanowe tworzymy poleceniem:

CREATE DATABASE LINK nazwa_linku
  CONNECT TO użytkownik IDENTIFIED BY hasło
  USING alias_bazy;
                        

Użytkownik, hasło i alias_bazy służą do autentyfikacji w zdalnej bazie danych podczas wykonywania się instrukcji korzystającej z linku bazodanowego. W sesji, w której skorzystamy z linku bazodanowego będziemy mieli taki kontekst i takie prawa w zdalnej bazie, jakie są nadane użytkownikowi, do którego odwołuje się link bazodanowy.

Przykład

CREATE DATABASE LINK db1_student
  CONNECT TO student IDENTIFIED BY student
  USING db1;
                        

Aby odwołać się do obiektu znajdującego się w odległej bazie danych, musimy nazwę obiektu postfiksować nazwą linku bazodanowego. Do linku bazodanowego, a więc do zdalnych obiektów możemy się odwoływać we wszystkich komendach DML. Wykonywanie operacji DDL na zdalnej bazie danych jest niedopuszczalne.

Przykład

CREATE TABLE nowe_towary
  AS SELECT * FROM towary@db1_student;
                        

W jednym poleceniu SQL można odwoływać się zarówno do obiektów lokalnych jak i do obiektów zdalnych. W takim wypadku można powiedzieć, że to serwer bazodanowy, do którego jesteśmy zalogowani, nawiązuje w naszym imieniu sesję ze zdalnym serwerem logując się na użytkownika wskazanego w definicji linku, po to aby zdobyć informacje potrzebne do wykonania polecenia SQL.

Przykład

SELECT K.klt_imie, K.klt_nazwisko, Z.zam_opis
  FROM klienci K, zamowienia@db1_student Z
 WHERE K.klt_id = Z.zam_klt_id;