Relazione Sistemi informativi LM ORACLE DATABASE

UNIVERSITÀ DEGLI STUDI DI BOLOGNA
Facoltà di Ingegneria Informatica
Corso di laurea in Ingegneria Informatica Magistrale
Relazione Sistemi informativi LM
ORACLE DATABASE
Scritta da: Ezio Tomassetti
Anno accademico 2011-2012
Indice
1 Oracle Database - Introduzione
1.1 Struttura del Database . . . . . . . .
1.2 Licenze . . . . . . . . . . . . . . . . .
1.3 Piattaforme supportate . . . . . . . .
1.4 Limiti . . . . . . . . . . . . . . . . .
1.4.1 Logical database limit . . . .
1.4.2 Dimensione massima database
1.5 Client Server Architecture . . . . . .
2 Oracle Database - Data Type
2.1 Custom datatype . . . . . .
2.2 Large Object Datatype . . .
2.2.1 Cosa sono . . . . . .
2.2.2 Tabella Large Object
2.2.3 LOB Locator . . . .
.
.
.
.
.
.
.
. . . . . .
. . . . . .
. . . . . .
Datatypes
. . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
3 Oracle Database - Dati spaziali e Geometrici
3.1 Geometry types . . . . . . . . . . . . . . . . .
3.2 Indicizzazione di Dati spaziali . . . . . . . . .
3.2.1 R-Tree Indexing . . . . . . . . . . . . .
3.2.2 MBR Enclosing a Geometry . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
1
3
3
4
4
6
7
.
.
.
.
.
9
15
16
16
16
17
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
19
20
22
23
23
4 Oracle Database - Trigger e Stored Procedure
4.1 Trigger Types . . . . . . . . . . . . . . . . . . .
4.2 Linea guida per la progettazione dei Triggers . .
4.3 Privilegi . . . . . . . . . . . . . . . . . . . . . .
4.4 Creazione Trigger . . . . . . . . . . . . . . . . .
4.5 Stored Procedure . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
25
25
26
27
27
28
5 Oracle Database - Sequenze e cursori
31
5.1 Create Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . 31
i
ii
INDICE
5.2
5.3
5.1.1 Parametri . . . . . . . . . . . . . . . . . . . . . . . . . 31
Privilegi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
6 Oracle Database - Viste
6.1 Cancellazione di una vista . . .
6.2 Modifica della definizione di una
6.3 Interrogazione di una vista . . .
6.4 Operazioni DML con le viste . .
. . .
vista
. . .
. . .
.
.
.
.
7 Sicurezza
7.1 Creazione utente . . . . . . . . . . . .
7.2 Autenticazione da database . . . . . .
7.2.1 Tablespace di Default . . . . . .
7.2.2 Temporary Tablespace . . . . .
7.2.3 Le quote in Oracle . . . . . . .
7.2.4 Scadenza delle password utente
7.2.5 Bloccare l’account . . . . . . .
7.3 I permessi . . . . . . . . . . . . . . . .
7.3.1 I permessi sugli oggetti . . . . .
7.3.2 I permessi di sistema . . . . . .
7.4 Sistemi di autenticazione in rete . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
37
37
38
38
38
.
.
.
.
.
.
.
.
.
.
.
41
42
42
42
43
43
44
44
45
45
47
47
8 Oracle Database - Alter Table
49
8.1 Modificare una colonna . . . . . . . . . . . . . . . . . . . . . . 49
8.2 Cancellazione di una colonna . . . . . . . . . . . . . . . . . . . 50
9 Oracle Database - Funzionalità
9.1 Cluster . . . . . . . . . . . . .
9.1.1 Tipi di Cluster . . . .
9.2 Hot Backup . . . . . . . . . .
9.3 Replicazione . . . . . . . . . .
9.4 Funzioni Explain/Analyze . .
9.4.1 ANALYZE . . . . . .
9.4.2 EXPLAIN PLAN . . .
9.5 Locking . . . . . . . . . . . .
9.6 Supporto ODBC/JDBC . . .
9.7 Benchmark . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
51
51
51
53
54
55
55
57
58
59
59
Capitolo 1
Oracle Database - Introduzione
Oracle è uno tra i più famosi database management system (DBMS), cioè
sistema di gestione di basi di dati, ed è stato scritto in linguaggio C. Oracle
fa parte dei cosiddetti RDBMS (Relational DataBase Management System)
ovvero di sistemi di database basati sul Modello relazionale che si è affermato
come lo standard dei database dell’ultimo decennio. La società informatica
che lo produce, la Oracle Corporation, è una delle più grandi del mondo. È
stata fondata nel 1977 ed ha la sua sede centrale in California. Il fondatore
nonché Chief Executive Officer ed importante azionista è Lawrence J. Ellison.
La prima versione di Oracle risale al 1977, da allora sono state introdotte
numerose modifiche e miglioramenti per seguire gli sviluppi tecnologici, fino
ad arrivare alla versione 11g R2.
1.1
Struttura del Database
Una base di dati Oracle comprende istanze e dati memorizzati. Un’istanza
è costituita da un insieme (set) di processi di sistema e strutture di memoria
che interagiscono con i dati memorizzati. Tra questi processi i seguenti sono
necessari per il funzionamento dell’istanza:
• PMON (monitor dei processi)
• SMON (monitor di sistema)
• DBWR (scrive nei datafile)
• LGWR (scrive nei logfile)
• CKPT (scrive i checkpoint controllandone la consistenza)
1
2
1. Oracle Database - Introduzione
• ARCH (archiviatore dei log delle transazioni per il DB in modalità
archive log mode)
Un compito importante è svolto dalla System Global Area (SGA), una regione di memoria condivisa che contiene dati ed informazioni per il controllo
di un’istanza Oracle. La SGA si occupa della cache, i dati bufferizzati, i
comandi SQL e informazioni sull’utente. Le strutture fisiche fondamentali
per un’istanza sono:
• control files: qui sono memorizzate informazioni essenziali al corretto
funzionamento del database. Tra queste il DBID identificativo dell’istanza, il valore di CKPT per la sincronizzazione dei datafile e dati
relativi ad alcune viste V$ da interrogare quando il DB stesso non è in
stato di Open. È necessario averne almeno uno associato all’istanza;
per maggior sicurezza possono esserne creati più di uno, il database
stesso si occuperà della loro sincronizzazione, in modo da poter avviare
il DB anche in stato di mount ed avviare un recovery.
• l’archivio delle transazioni (online redo logs): i redologs sono necessari
per il funzionamento del Db stesso, il numero minimo di redo logs e è
2.
• i rollback/undo segments
• il tablespace system
• un tablespace di tipo temporaneo
Oracle memorizza i dati sia logicamente, sotto forma di tablespace, sia
fisicamente, sotto forma di file (datafile). Un tablespace, formato da uno o
più datafile, contiene vari tipi disegment; ogni segment a sua volta si suddivide in uno o più extent. Ogni extent comprende gruppi contigui di blocchi
di dati (data block), quest’ultimi sono la più piccola informazione memorizzabile da Oracle. A livello fisico, i file comprendono almeno due o più
extent. Fino alla versione 8i la dimensione del blocco di dati era stabilita
alla creazione del database e non poteva più essere modificata; dalla versione
9i in poi i blocchi di dati possono essere di dimensione variabile, sebbene
ogni tablespace debba necessariamente essere costituita da datafile con la
stessa dimensione di blocco dati. Oracle tiene traccia dei dati memorizzati
tramite l’aiuto di informazioni presenti nelle tabelle di sistema. Esse contengono il dizionario dei dati e se presenti indici e cluster. Un dizionario dati
consiste di una collezione di tabelle che contengono informazioni riguardo
tutti gli oggetti del database. Tra le varie potenzialità possiamo memorizzare ed eseguire stored procedure e funzioni. Grazie al PL/SQL, un’estensione
1.2 Licenze
procedurale del linguaggio SQL, sviluppato da Oracle, e a Java possiamo
scrivere funzioni, procedure, trigger e package. Oracle è un RDBMS che se
configurato e gestito in maniera appropriata, garantisce una sicurezza dei
dati molto elevata. È possibile attivare a questo proposito la modalità detta
ARCHIVING (o ARCHIVELOG MODE). Essa consiste nel registrare tutte
le transazioni che avvengono nel DB anche in file di sistema operativo che
dovranno essere utilizzati in caso di DB RECOVERY dovuta a crash totale
o parziale del sistema. In questa modalità è possibile sfruttare l’HOT BACKUP ossia il salvataggio dei dati a sistema acceso senza effettuare fermi. Le
modalità per il backup a caldo (hot backup) sono diverse. Quella standard
oracle è denominata RMAN ossia Recovery Manager. Nulla vieta comunque
all’amministratore del DB di gestire il backup/restore delle istanze oracle in
maniera manuale o automatica tramite scripting.
1.2
Licenze
• ’Enterprise Edition’ (EE) è l’ideale per le industrie e grandi aziende.
• ’Standard Edition’ (SE) è l’ideale per le medie aziende.
• ’Standard Edition One’ progettato per le Medie Imprese e dipartimenti
aziendali (funziona su server a due processori).
• ’Express Edition’ (’Oracle Database XE’) è totalmente gratuito, orientato a studenti e programmatori. Ha alcune limitazioni: può salvare al
massimo quattro gigabyte per ogni utente su disco, ed usa al massimo
un gigabyte di memoria principale, una sola cpu, un host.
1.3
Piattaforme supportate
Prima del rilascio di Oracle9i, Oracle Corporation ha adattato il motore
del proprio database ad una vasta gamma di piattaforme. Recentemente il
supporto è stato consolidato ad un piccolo numero di piattaforme / sistemi
operativi:
• Apple Mac OS X Server: PowerPC
• HP HP-UX: PA-RISC, Itanium
• HP Tru64 UNIX: Alpha
• HP OpenVMS: Alpha, Itanium
3
4
1. Oracle Database - Introduzione
• IBM AIX5L: IBM POWER
• IBM z/OS: zSeries
• Linux: x86, x86-64, PowerPC, zSeries, Itanium
• Microsoft Windows: x86, x86-64, Itanium
• Sun Solaris: SPARC, x86, x86-64
1.4
Limiti
1.4.1
Logical database limit
Vediamo qualche limite di Oracle.
Item
CREATE
TERIALIZED
VIEW definition
GROUP BY clause
Indexes
Columns
Constraints
MA-
Type
Maximum
size
Limit
64K Bytes
Maximum
length
The GROUP BY expression
and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within
a single database block.
Unlimited
Maximum
per table
total size
of indexed
column
Per table
Per index
(or clustered index)
Per
bitmapped
index
Maximum
per column
75% of the database block size
minus some overhead
1000 columns maximum
32 columns maximum
30 columns maximum
Unlimited
1.4 Limiti
Subqueries
Partitions
Rows
Stored Packages
Trigger Cascade Limit
Users and Roles
Tables
5
Maximum
levels
of
subqueries
in a SQL
statement
Maximum
length of
linear partitioning
key
Maximum
number of
columns in
partition
key
Maximum
number of
partitions
allowed
per table
or index
Maximum
number
per table
Maximum
size
Maximum
value
Maximum
Maximum
per
clustered
table
Unlimited in the FROM clause of
the top-level query
255 subqueries in the WHERE clause
4 KB - overhead
16 columns
1024K - 1
Unlimited
PL/SQL and Developer/2000
may have limits on the size
of stored procedures they can
call. The limits typically range
from 2000 to 3000 lines of code.
See Also: Your PL/SQL or
Developer/2000 documentation
for details
Operating system-dependent, typically 32
2,147,483,638
32 tables
6
1. Oracle Database - Introduzione
Maximum
per
database
1.4.2
Unlimited
Dimensione massima database
Un database Oracle può essere diviso in tablespaces. Tablespaces possono
essere di 2 tipi:
1. Smallfile Tablespace(STF): contiene 1022 datafile ciascuno dei quali
contiene
222
blocchi.
2. Bigfile tablespace (BFT): contiene solo un datafile contenente
232
blocchi.
La massima dimensione del datafile la calcoliamo:
maximum datafile size=db blcok size*maximum number of blocks.
In database db block size can have 2K, 4K,8K,16K,32K.
In un database ci possono essere al massimo 65533 data files.
Quindi,
maximum database size=maximum datafile size*maximum datafile can be
in a database.
Consideriamo 32K in un STF,
maximum datafile size=power(2,22)*32/1024/1024 G=128G.
Quindi,
maximum database size= 128*65533 G=8388224 G;
Considerando un BFT:
maximum datafile size=power(2,32)*32/1024/1024 G=131072 G.
quindi,
maximum database size=131072*65533 G=8589541376 G.
1.5 Client Server Architecture
1.5
Client Server Architecture
Un database server è il software di gestione di un database Oracle, e un
client è un’applicazione che richiede informazioni ad un server. Ogni computer in una rete è un nodo che può ospitare uno o più database. Ogni nodo
di un sistema di database distribuito può agire come un client, un server, o
entrambi, a seconda della situazione
L’architettura di Oracle si basa sul classico paradigma di comunicazione client-server, in cui sono presenti uno o più processi client che richiedono
un servizio, e, un processo server che eroga tale servizio. Nel caso di un
Database Management System, il servizio offerto è quello di accesso ai dati
presenti all’interno della base di dati, mentre il linguaggio di formulazione
delle richieste da parte dei client è l’SQL (Structured Query Language).
Lato client sono quindi disponibili per gli utilizzatori finali del database le
funzionalità di controllo, formulazione richieste via SQL, immissione ed elaborazione dati, e, visualizzazione dei risultati; di contro, lato server, troviamo
la logica della base di dati e il data processing vero e proprio. Scendendo
ad un maggiore livello di dettaglio, l’architettura del DBMS ORACLE lato
server si compone di una o più istanze oracle, di uno o più processi listener e
di uno o più oracle database ognuno legato ad una data istanza oracle. Una
istanza oracle è costituita da un insieme di processi (di background), e, di
strutture dati allocate in memoria, che creano il meccanismo di accesso ai
file di un database Oracle. Per poter accedere ai dati contenuti nel database,
l’istanza deve essere attiva. Ogni istanza attiva risiede in una specifica area
di memoria denominata SGA (System Global Area), in grado di contenere
le informazioni condivise, e le tutte informazioni necessarie all’oracle server.
Nella SGA vengono utilizzate delle particolari strutture dati, di seguito si
riporta una breve descrizione delle quelle fondamentali.
7
8
1. Oracle Database - Introduzione
• The shared pool. Essa è usata per memorizzare gli statement SQL recentemente utilizzati, questo consente ad un eventuale processo oracle,
di reperire informazioni in maniera più veloce.
• The database buffer. Essa è usata per memorizzare dati recentemente
trattati. Tali dati sono letti e/o scritti nei data file (trattati successivamente).
• The redo log. Essa è utilizzata per tenere traccia degli eventi dei
processi in background.
• Data and Library caches. Sono aree di cache utilizzate per contenere
i dati in transito da e verso il database (data) e le istruzioni SQL e
PL/SQL in esecuzione (library).
L’architettura oracle si compone di uno o più processi utente, che rappresentano gli utilizzatori finali del DB (utilizzatori umani o entità software), e
di un processo server, il cui compito è quello di accettare richieste da parte
dei processi utente, e smistare poi tali richieste all’istanza oracle e cosı̀ via.
Questa tipica architettura client-server che caratterizza i DBMS ORACLE,
offre la possibilità effettuare connessioni dirette a un database remoto (ma
anche locale), ciò viene realizzato mediante Net8 (successore SQL*Net) che
è il prodotto per il networking tra un processo client e un processo database
server.
Capitolo 2
Oracle Database - Data Type
Vediamo una tabella che riassume i data type di Oracle 11g.
Datatype
Description
Max
Size:Oracle
11g
VARCHAR2(size) A differenza del precedente, questo ti- 4000 bytes mipo alfanumerico è a lunghezza varia- nimum is 1
bilee può memorizzare fino a 4000 caratteri.
Il vantaggio che introduce è essenzialmente uno, il risparmio di spazio. Infatti VARCHAR2 non inserisce gli spazi mancanti se la lunghezza del dato immesso è inferiore a quella specificata.
Non esiste, inoltre, un valore di default
per questo tipo di dati, pertanto Oracle
solleverà un errore se non sarà specificato il valore di n.
Se definiamo il campo Cognome come
Cognome VARCHAR2(30)
NVARCHAR2
(size)
Variable length national character set 4000 bytes ministring having maximum length size by- mum is 1
tes.You must specify size
Now deprecated (provided for back- ward compatibility only)VARCHAR is
a synonym for VARCHAR2 but this
usage may change in future versions.
VARCHAR
9
10
2. Oracle Database - Data Type
CHAR(size)
Rappresenta sequenze alfanumeriche
(lettere e numeri), ed è a lunghezza fissa, specificata mediante il valore racchiuso fra le parentesi (n). Questo valore è compreso tra 1 e 2000 caratteri.
Lunghezza fissa significa che inserendo un numero di caratteri inferiore a n,
i caratteri non utilizzati vengono sostituiti da altrettanti spazi. Supponiamo
di definire una colonna del tipo:
Cognome CHAR (10);
e di inserirvi un’informazione di cinque
lettere, ad esempio ’Rossi’. Oracle inserirà la stringa ’Rossi’ nella colonna specificata (Cognome) e aggiungerà altri
cinque spazi alla fine della parola per
raggiungere la lunghezza definita (10).
Se non specifichiamo alcun valore n allora Oracle, per default, considera la
lunghezza massima di un solo carattere. Pertanto le dichiarazioni:
Cognome CHAR (1);Cognome
CHAR;
sono equivalenti. Se eccediamo la lunghezza specificata, Oracle solleva un errore.
2000
bytesDefault
and
minimum size is
1 byte.
NCHAR(size)
Fixed length national character set data of length size bytes. This should
be used for fixed length data. Such as
codes A100, B102...
2000
bytesDefault
and
minimum size is
1 byte.
11
NUMBER(p,s)
Consente la memorizzazione di valori
numerici interi, positivi e negativi, dei
numeri a virgola fissa e di quelli a virgola mobile. I parametri p e s, anch’essi numerici, indicano rispettivamente la
precisione e la scala. La precisione può
avere un valore da 1 a 38, mentre la
scala un valore da -84 a 127. Se omettiamo questi valori, Oracle considera il
massimo valore per entrambi. Per conoscere i limiti di memorizzazione per
questo datatype ricorriamo alle seguenti regole:
la scala indica le cifre dopo la virgola,
ovvero quelle decimali;
il numero di cifre intere (prima della
virgola) è dato dalla sottrazione di p-s.
Queste regole però non valgono sempre,
ad esempio quando il valore della scala
è negativo oppure maggiore del valore
della precisione. In casi come questi è
opportuno consultare la documentazione di Oracle.
Facciamo un esempio. Creando una tabella definiamo un campo come NUMBER (6, 2). I numeri memorizzabili in
questo campo avranno al massimo due
cifre decimali e quattro cifre intere (62). Pertanto l’intervallo di valori sarà
da -9.999,99 a 9.999,99.
Altro esempio. Definiamo un campo
come NUMBER (5, 3). I numeri avranno al massimo tre cifre decimali e due
cifre intere (5-3). Pertanto l’intervallo
andrà da -99,999 a 99,999.
Un numero con cifre decimali superiori
al valore della scala sarebbe automaticamente arrotondato secondo il valore
della scala. Se invece si eccede il valore
di precisione Oracle solleva un errore.
Ad esempio, se definiamo il campo
Salario NUMBER (3, 1);
ed vi inseriamo 124,7 l’operazione non
sarebbe eseguita e Oracle segnalerebbe
l’errore (3-1 = 2 cifre intere al massimo).
The precision p
can range from 1
to 38.The scale
s can range from
-84 to 127.
12
2. Oracle Database - Data Type
BINARY INTEGER
signed integers (older slower version of
PLS INTEGER)
LONG
Character data of variable length 2 Gigabytes (A bigger version the VARCHAR2 but now depredatatype)
cated (provided
for
backward
compatibility
only).
13
DATE
È usato per memorizzare date e orari.
Un campo di questo tipo può contenere
le seguenti informazioni:
secolo
anno
mese
giorno
ora
minuti
secondi
Oracle fornisce svariate e utilissime funzioni per operare e trasformare questo
tipo di dati. Il default di memorizzazione è GG-MES-AA ovvero due cifre per il giorno, tre lettere per il mese
e due cifre per l’anno. La data 31-dic75 ne è un esempio.
Se inseriamo una data senza specificare
l’orario, questo viene aggiunto da Oracle con valore 00:00:00.
Quando lavoriamo con il tipo DATE possiamo fare riferimento alla funzioneSYSDATE che riporta la data e
l’ora attuale del sistema, quindi del
server Oracle.
Infine è possibile specificare date che
vanno dal 01 gennaio 4712 A.C. al 31
dicembre 9999 D.C.
Ora che conosciamo i datatype più utilizzati in Oracle, le strutture delle tabelle ci risulteranno più chiare.
TIMESTAMP
Questo tipo di dato è un’estensione del
(fractiotipo DATE che permette di specificanal seconds precision)
re la precisione della frazione di secondo memorizzata. Il valore di default
(per la precision è 6, ma può valere un
qualunque intero compreso tra 0 e 9.
from
January
1,
4712 BC
to
December
31, 9999 AD.
Accepted
values of fractional seconds precision
are 0 to 9.
(default = 6)
14
2. Oracle Database - Data Type
TIMESTAMP
Questo tipo di dato è un’estensione del
(fractioTIMESTAMP che permette di memonal seconds precision)
rizzare la differenza di time zone, cioè la
WITH
differenza tra l’ora locale e PersonNa{LOCAL}
meProductIDla UTCla UTC (UniverTIMEZONE
sal Time Coordinate o GMT Greenwich
Mean Time), differenza che è espressa
in ore e minuti. In questo modo risulta più semplice confrontare due valori temporali registrati sul medesimo
database da aree geografiche diverse.
INTERVAL
Rappresenta un periodo di tempo coYEAR
me anni e mesi, e viene utilizzato per
(year precision) memorizzare la differenza tra due date,
TO MONTH
della quale interessa solo la porzione di
anno e mese. Precision fa riferimento
agli anni, e se non specificato assume il
valore di default che è 2.
INTERVAL
Usato per rappresentare periodo di
DAY
tempo come giorno, ora, minuti e se(day precision)
condi. La precisione fa riferimento al
TO SECOND giorno, default è 6, e può variare tra 0
(fractioe 9. Più alta è la precisione, maggiore
nal seconds precision)
può essere la differenza tra due date. In
genere viene usato per memorizzare la
differenza tra due date con precisione
del secondo.
RAW(size)
E’ un tipo di dato a dimensione variabile, usato per memorizzare dati nonstrutturati, senza avere conversione in
caratteri. Esiste solo per compatibilità,
e può contenere fino a 2000 bytes. Oracle suggerisce di usare il tipo BLOB o
BFILE.
Accepted
values of fractional seconds precision
are 0 to 9.
(default = 6)
Accepted values
are 0 to 9. (default = 2)
day precision may
be 0 to 9. (default = 2)
fractional seconds precision
may be 0 to 9.
(default = 6)
Maximum size
is 2000 bytes
2.1 Custom datatype
LONG RAW
Ha le stesse caratteristiche del tipo
RAW, ma può registrare fino a 2Gb.
Oracle suggerisce di usare il tipo BLOB
in alternativa.
CLOB
Character Large Object
NCLOB
National Character Large Object
BLOB
Binary Large Object
BFILE
pointer to binary file on disk
XMLType
XML data
2.1
15
2 Gigabytes but now deprecated (provided
for
backward
compatibility
only)
8 TB to 128 TB
(4 Gigabytes 1) * (database
block size)
8 TB to 128 TB
(4 Gigabytes 1) * (database
block size)
8 TB to 128 TB
(4 Gigabytes 1) * (database
block size)
8 TB to 128 TB
(4 Gigabytes 1) * (database
block size)
4 Gigabytes
Custom datatype
CREATE OR REPLACE TYPE <type_name>
AUTHID <CURRENT USER | DEFINER>
AS OBJECT (
<attribute> <attribute data_type>,
<inheritance clause>,
<subprogram spec>)
<FINAL | NOT FINAL> <INSTANTIABLE | NOT INSTANTIABLE>;
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code
CHAR(3),
p_number CHAR(8));
16
2. Oracle Database - Data Type
INSERT INTO phone
(per_id, per_phone)
VALUES
(2, phone_t(’212’, ’123-4567’));
2.2
2.2.1
Large Object Datatype
Cosa sono
Large Objects sono un set di datatype disegnati per contenere grandi
quantità di dati. Possono contenere dati per un range che va da 8 terabytes
a 128 terabytes, a secondo della configurazione del database. Large Objects
sono definiti da datatype: BLOB, CLOB, NCLOB, and BFILE. I tipi di dati
BLOB e CLOB sono creati mediante l’uso di CREATE TABLE o ALTER
TABLE o con comandi CREATE o ALTER TYPE. In realtà, essi sono creati
in modo identico ad altri tipi di dati non-sized, come DATA e LONG con
l’eccezione della clausola di archiviazione LOB. La clausola di archiviazione
LOB non è necessaria se la dimensione massima del BLOB non supera i 4000
byte. Fino a 4000 byte può essere memorizzato in linea con gli altri dati
nella tablespace. Se la lunghezza del BLOB supera i 4000 byte deve essere
memorizzato in un sistema di storage in default (lo stesso di default per le
tabella) o in una zona LOB definita esplicitamente.
Abbiamo già accennato ai large object nella tabella dei datatype, approfondiamo, tornando dalla tabella.
2.2.2
Tabella Large Object Datatypes
SQL Datatype
BLOB
CLOB
Description
Binary Large Object
Memorizzano tipi di dati in formato binario.
Tipicamente sono usati per dati multimediali
come immagini, audio, video.
Character Large Object
Memorizza dati string nel database nel formato character set. Usati per grandi stringhe o documenti che usano esclusivamente il
formato character set.
2.2 Large Object Datatype
NCLOB
National Character Set Large Object
Dati stringa memorizzati nel formato National Character Set. Usati per stringhe gradi
o documenti in National Character Set.
External Binary File
Un file binario archiviato al di fuori della
base di dati nel sistema host di file del sistema operativo, ma accessibile da tabelle. BFILEs può essere acceduta tramite l’applicazione su una base di sola lettura. BFILE si usa per memorizzare i dati statici, come ad esempio dati di immagine, che non
ha bisogno di essere manipolato dalle applicazioni.Qualsiasi tipo di dati, cioè, nessun file del sistema operativo, possono essere memorizzati in un BFILE. Ad esempio,
è possibile memorizzare i character data in
un BFILE e poi caricare i dati BFILE in
un CLOB specificando il set di caratteri al
momento del caricamento
BFILE
2.2.3
17
LOB Locator
Un’istanza LOB è un localizzatore e un valore. Il LOB locator è un
riferimento al punto in cui il valore LOB è fisicamente memorizzati. Quando
si utilizza un LOB in un’operazione passato come parametro, in realtà si sta
passando un LOB locator. La maggior parte delle volte, è possibile lavorare
con un’istanza di LOB nella propria applicazione senza preoccuparsi della
semantica di localizzatori LOB. Non c’è bisogno di localizzatori LOB da
dereferenziare.
18
2. Oracle Database - Data Type
Capitolo 3
Oracle Database - Dati spaziali
e Geometrici
Oracle Spatial è progettato per rendere la gestione dei dati spaziali più
semplice e più naturale per gli utenti di applicazioni location-enabled e sistemi di informazione geografica (GIS). Una volta che i dati spaziali vengono
memorizzati in un database Oracle, possono essere facilmente manipolati,
recuperati e sono relativi a tutti gli altri dati memorizzati nel database.
Un esempio comune di dati spaziali può essere visto in una mappa stradale. Una road map è un oggetto bidimensionale che contiene punti, linee e
poligoni che può rappresentare le città, strade e confini politici come stati o
province. Una road map è una visualizzazione di informazioni geografiche.
La posizione delle città, strade e confini politici che esistono sulla superficie
della Terra sono proiettate su uno schermo bidimensionale o un pezzo di carta, conservando le relative posizioni e distanze relative degli oggetti.
I dati che indicano la posizione della Terra (come la longitudine e latitudine)
di questi oggetti sono resi dai dati spaziali. Quando la mappa è resa, i dati
spaziali vengono utilizzati per proiettare la posizione degli oggetti in due dimensioni. Un GIS è spesso utilizzato per memorizzare, recuperare, e rendere
questi dati spaziali relativi alla Terra.
Tipi di dati spaziali (diversi dai dati GIS) che possono essere memorizzati
utilizzando dati spaziali sono dati da sistemi computer-aided design (CAD) e
computer-aided manufacturing (CAM). Invece di operare su oggetti su scala
geografica, lavorano su scala minore, come ad esempio per un motore automobilistico o circuiti stampati.
Le differenze tra questi sistemi sono la dimensione e la precisione dei dati,
non la complessità dei dati. I sistemi potrebbero coinvolgere tutti lo stesso
numero di punti dati. Su una scala geografica, la posizione di un ponte può
variare da pochi decimi di centimetro, senza causare alcun problema evidente
19
20
3. Oracle Database - Dati spaziali e Geometrici
per i costruttori stradali, mentre se il diametro dei pistoni di un motore è
diverso da pochi decimi di centimetro, il motore non funziona correttamente.
3.1
Geometry types
I punti bidimensionali sono elementi composti da due coordinate, X e
Y, spesso corrispondenti a longitudine e latitudine. Le linee di stringhe sono
composte da una o più coppie di punti che definiscono i segmenti di linea. I
poligoni sono composti da linee di stringhe collegate che formano un anello chiuso, e l’area del poligono è implicita. Per esempio, un punto potrebbe rappresentare una posizione di un edificio, una stringa di linee potrebbe
rappresentare una strada o un percorso, e un poligono potrebbe rappresentare uno stato, città. Spatial può anche supportare la memorizzazione e
l’indicizzazione di geometric types 3D o 4D.
Vediamo come vengono definiti i tipi geometrici
Value
dl 00
dl 01
dl 02
dl 03
dl 04
dl 05
dl 06
Geometry Type
UNKNOWN GEOMETRY
POINT
LINE or CURVE
Description
Spatial ignores this geometry.
Geometry contains one point.
Geometry contains one line string
that can contain straight or circular arc segments, or both. (LINE
and CURVE are synonymous in
this context.)
POLYGON
Geometry contains one polygon
with or without holes.Foot 1
COLLECTION
Geometry is a heterogeneous collection of elements. COLLECTION is a superset that includes
all other types.
MULTIPOINT
Geometry has one or more points.
(MULTIPOINT is a superset of
POINT.)
MULTILINE or MULTI- Geometry has one or more liCURVE
ne strings. (MULTILINE and
MULTICURVE are synonymous
in this context, and each is
a superset of both LINE and
CURVE.)
3.1 Geometry types
dl 07
21
MULTIPOLYGON
Geometry can have multiple, disjoint polygons (more than one
exterior boundary).
(MULTIPOLYGON is a superset of
POLYGON.)
Figura 3.1: Tipi geometrici
22
3. Oracle Database - Dati spaziali e Geometrici
Vediamo un esempio:
INSERT INTO cola markets VALUES(
1,
’cola a’,
SDO GEOMETRY(
2003, – two-dimensional polygon
NULL,
NULL,
SDO ELEM INFO ARRAY(1,1003,3),
–
one rectangle (1003 = exterior)
SDO ORDINATE ARRAY(1,1, 5,7) – only
2 points needed to
– define rectangle (lower left and upper
right) with
– Cartesian-coordinate data
)
);
3.2
Indicizzazione di Dati spaziali
L’introduzione di funzionalità di indicizzazione spaziale nel motore di database Oracle è una caratteristica chiave del prodotto spaziale. Un indice
spaziale, come qualsiasi altro indice, fornisce un meccanismo per limitare le
ricerche, ma in questo caso il meccanismo si basa su criteri spaziali come
intersezione e contenimento. Un indice spaziale è necessario per:
3.2 Indicizzazione di Dati spaziali
• Trovare gli oggetti all’interno di uno spazio dati indicizzato che interagiscono con un punto o con una zona di interesse (windows query)
• Trovare le coppie di oggetti all’interno di due spazi dati indicizzati che
interagiscono spazialmente l’uno con l’altro (spatial join)
Un indice spaziale viene considerato un indice logico. Le voci dell’indice
possono essere ordinate in un dominio linearmente ordinato, e le coordinate
per una geometria possono essere coppie di numeri interi, in virgola mobile.
3.2.1
R-Tree Indexing
Un indice R-tree spaziale è in grado di indicizzare i dati spaziali fino
a quattro dimensioni. Un indice R-tree approssima ciascuna geometria da
un singolo rettangolo che racchiude minimamente la geometria (chiamato il
rettangolo minimo di delimitazione, o MBR).
3.2.2
MBR Enclosing a Geometry
Vediamo un esempio:
In figura:
• Da 1 a 9 sono geometrie
• a, b, c, e d sono i nodi foglia dell’indice R-tree, e contiene il numero
minimo di rettangoli delle geomtrie, insieme con i puntatori alle geometrie. Per esempio, a contiene l’MBR della geometria 1 e 2, b contiene
l’MBR della geometria 3 e 4.
• A contiene l’MBR di a e b, e B contiene l’MBR di c e d.
• La root contiene l’MBR di A e B.
23
24
3. Oracle Database - Dati spaziali e Geometrici
Capitolo 4
Oracle Database - Trigger e
Stored Procedure
Un trigger è una unità di programma memorizzata nel database e viene
eseguito in risposta a un evento specifico. L’evento specifico è associato ad
una tabella, una vista, uno schema, o al database, ed è uno dei seguenti:
• A database manipulation (DML) statement (DELETE, INSERT, or
UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
4.1
Trigger Types
Un trigger DML viene eseguito da una dichiarazione DML, un trigger
DDL viene eseguito da una dichiarazione DDL, un trigger DELETE è eseguito da una dichiarazione DELETE, e cosı̀ via. Un trigger INSTEAD OF
è un trigger DML che è definito in una vista (non un tabella). Un sistema
trigger è definito in uno schema o nel database. Un trigger definito in uno
schema viene generato per ogni evento associato al proprietario dello schema
(l’utente corrente). Un trigger definito in un database viene generato per
ogni evento associato a tutti gli utenti. Un trigger semplice può essere eseguito esattamente in uno dei punti seguenti:
• Prima della dichiarazione attivazione
25
26
4. Oracle Database - Trigger e Stored Procedure
• Dopo la dichiarazione attivazione
• Prima di ogni riga che interessa la dichiarazione di attivazione
• Dopo ogni riga che interessa la dichiarazione di attivazione
Un trigger composto può eseguire a più di un punto di temporizzazione. Il
trigger composto può rendere più facile programmare con un approccio in
cui si desidera pre-implementare le azioni.
4.2
Linea guida per la progettazione dei Triggers
Per i trigger si può seguire una linea di progettazione:
• Utilizzare trigger per garantire che quando una specifica operazione
viene eseguita, le cause connesse siano eseguite.
• Non definire trigger di funzionalità di database duplicate. Ad esempio,
non si definisce un trigger se dati errati si possono controllare lo stesso
attraverso vincoli.
Sebbene sia possibile utilizzare trigger e vincoli di integrità per definire e
applicare qualsiasi tipo di regola di integrità, Oracle consiglia vivamente
di utilizzare i trigger per vincolare inserimento dei dati solo nelle seguenti
situazioni:
• Per applicare l’integrità referenziale quando le tabelle figlio e padre
sono su diversi nodi di un database distribuito
• Per far rispettare le regole di business complesse non definibili con
vincoli di integrità
• Quando una regola di integrità referenziale è necessaria e non può essere
eseguita utilizzando i vincoli di integrità seguenti:
– NOT NULL, UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
– DELETE CASCADE
4.3 Privilegi
– DELETE SET NULL
• Limitare la dimensione di trigger. Se la logica per il trigger richiede
molto di più di 60 linee di codice PL / SQL, mettere la maggior parte
del codice in un sottoprogramma memorizzato e richiamare il sottoprogramma dal trigger. Le dimensioni del trigger non può superare i 32
KB.
• Non creare i trigger ricorsivi.
• Se si utilizza un trigger LOGON per controllare accessi da parte degli
utenti, bisogna fare attenzione alle eccezioni. Altrimenti, se un’eccezione non gestita potrebbe bloccare tutte le connessioni al database.
• Se si utilizza un trigger LOGON solo fine di eseguire un pacchetto,
mettere la parte di gestione delle eccezioni nel pacchetto invece che nel
trigger.
4.3
Privilegi
Per creare trigger:
• Si devono avere i privilegi di sistema per CREATE TRIGGER
• Una condizione delle seguenti deve tornare true:
– È proprietario della tabella specificata nell’istruzione
– Ho i privilegi di ALTER per la tabella usata dal trigger
– Si hanno ALTER ANY TABLE system privilege
Per creare un trigger in un altro schema, o fare riferimento a una tabella in
un altro schema da un trigger:
• È necessario avere il privilegio CREATE ANY TRIGGER
• È necessario avere il privilegio EXECUTE sul sottoprogramma di riferimento
4.4
Creazione Trigger
Vediamo la sintassi:
27
28
4. Oracle Database - Trigger e Stored Procedure
CREATE TRIGGER nome_trigger
modo evento [OR evento]
ON tabella
[REFERENCING referenza]
[FOR EACH ROW]
[WHEN (predicato SQL)]
blocco PL/SQL;
Vediamo un esempio:
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (NEW.EMPNO > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.SAL - :OLD.SAL;
dbms_output.put(’Old salary: ’ || :OLD.sal);
dbms_output.put(’ New salary: ’ || :NEW.sal);
dbms_output.put_line(’ Difference ’ || sal_diff);
END;
Dato che il trigger utilizza la parola chiave BEFORE, può accedere ai
nuovi valori, prima di andare oltre, e può cambiare i valori se c’è un errore mediante l’assegnazione di: NEW.column name. È possibile utilizzare la
parola chiave AFTER per dare la possibilità al trigger di interrogare o modificare la tabella stessa, in quanto può accedere solo dopo i primi cambiamenti
e quando la tabella è in uno stato consistente. Il trigger utilizza la clausola
FOR EACH ROW; potrebbe essere eseguita più volte, per l’aggiornamento
o l’eliminazione di più righe.
4.5
Stored Procedure
Il linguaggio usato per codificare stored procedure è un estensione procedurale database-specific di SQL.
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
4.5 Stored Procedure
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
29
30
4. Oracle Database - Trigger e Stored Procedure
Capitolo 5
Oracle Database - Sequenze e
cursori
Per soddisfare vincoli come PRIMARY KEY o UNIQUE dobbiamo in
qualche modo conoscere l’ultimo valore inserito (o addirittura tutti) nella
relativa colonna, ed evitare la ripetizione di un valore già inserito. Per evitare
di ricorrere a query molto complesse per ricavare il nuovo valore possiamo
servirci delle sequenze. Le sequenze di Oracle sono dei generatori automatici
di numeri che permettono di assegnare, in automatico, i valori nelle colonne
numeriche PRIMARY KEY.
5.1
Create Sequence
Il comando per creare una sequenza è CREATE SEQUENCE.
Sintassi di CREATE SEQUENCE
CREATE SEQUENCE <schema>.<nome sequenza>
START WITH <n>
INCREMENT BY <n>
MINVALUE <n> MAXVALUE <n>
[CYCLE | NOCYCLE];
5.1.1
Parametri
nome
Identifica univocamente la sequenza all’interno di uno schema. Non è possibile avere due sequenze con lo stesso nome nello stesso schema. Diversamente,
due schemi all’interno di uno stesso database possono creare sequenze con gli
31
32
5. Oracle Database - Sequenze e cursori
stessi nomi. Il nome della sequenza sarà memorizzato nel dizionario dei dati
di Oracle e vi faremo riferimento ogni volta che useremo la sequenza nei comandi SQL e PL/SQL. Lo schema indica, ovviamente, l’utente proprietario
della sequenza. È possibile creare una sequenza in qualunque schema solo se
si possiede il privilegio CREATE ANY SEQUENCE.
START WITH
Consente di definire il valore di partenza ovvero il numero più piccolo generato da sequenza crescente o il numero più grande per una sequenza decrescente.
Il valore di default è 1.
INCREMENT BY
Questa clausola definisce l’intervallo tra i valori nella sequenza e può assumere qualsiasi valore. Se, ad esempio, assegniamo un incremento di 1 la
sequenza aumenterà di volta in volta di una unità. Se invece scegliamo un
valore negativo, ad esempio -2, al valore corrente verrà sottratto 2 ad ogni
passo.
MINVALUE
Rappresenta il più piccolo numero della sequenza ne costituisce cioè il limite
inferiore. Il valore di default è NOMINVALUE che equivale a 1 per una sequenza crescente e a -1026 per una sequenza decrescente.
MAXVALUE
Definisce il limite superiore della sequenza. Il valore di default è NOMAXVALUE che equivale a 1027 per una sequenza crescente e a -1 per una sequenza
decrescente.
CYCLE E NOCYCLE
Queste clausola sono opzionali: CYCLE configura la sequenza per la replica
dei numeri dopo che è stato raggiunto un limite (superiore o inferiore) mentre
la clausola NOCYCLE impedisce la replica dei numeri al raggiungimento del
limite. Per default il comportamento di una sequenza è NOCYCLE. Se usassimo NOCYCLE in una sequenza incrementale e cercassimo di far generare
un numero superiore a MAXVALUE Oracle solleverebbe un errore. Nota: Il
valore per la clausola START WITH deve essere sempre maggiore o uguale
a MINVALUE.
5.2
Privilegi
Per poter creare una sequenza nel proprio schema necessitiamo del privilegio CREATE SEQUENCE, che ci permette inoltre di modificare una
sequenza già esistente e di eliminarla. Se l’amministratore di sistema (SYSTEM) ha concesso il privilegio di sistema CONNECT all’utente che desidera
creare la sequenza, questi non necessiterà anche del privilegio CREATE SE-
5.3 Cursor
33
QUENCE in quanto è già idoneo a creare risorse (tabelle, indici, sequenze,
ecc.) all’interno del proprio schema.
Esempio
Per esempio creiamo una nuova tabella, PadreSeq, ed una sequenza per generare i valori della colonna idpadre.
CREATE TABLE PadreSeq
(
IDPadre NUMBER (6) CONSTRAINT pk_PadreSeq_ID PRIMARY KEY,
Nominativo VARCHAR2 (20) CONSTRAINT nn_PadreSeq_Nom NOT NULL,
DataNascita DATE
);
CREATE SEQUENCE myseq
START WITH 1 INCREMENT BY 1
MINVALUE 1 NOMAXVALUE NOCYCLE;
5.3
Cursor
La dichiarazione di un cursore specifica un insieme di tuple (come risultato
di una query) in modo che le tuple possano essere processate individualmente,
una alla volta, usando l’istruzione fetch. La dichiarazione di un cursore ha
la sintassi:
cursor <nome cursore> [(<lista parametri>)] is <istruzione select>;
Il nome di un cursore è un identificatore non dichiarato, diverso da qualunque nome di variabile PL/SQL. Un parametro ha la forma ¡nome parametro¿
¡tipo parametro¿. I possibili parametri sono char, varchar2, number, date e
boolean e tutti i corrispondenti sotto-tipi come integer. I parametri sono
utilizzati per assegnare valori alle variabili che sono date in un’istruzione
select.
Esempio: Vogliamo recuperare i seguenti valori degli attributi dalla tabella EMP in maniera orientata alle tuple: il titolo di lavoro e il nome di
quegli impiegati che sono stati assunti dopo una certa data, e che hanno un
manager che lavora in un determinato dipartimento.
cursor employee_cur (start_date date, dno number) is
select JOB, ENAME from EMP E where HIREDATE > start_date
and exists (select * from EMP
where E.MGR = EMPNO and DEPTNO = dno);
34
5. Oracle Database - Sequenze e cursori
Prima che un cursore possa essere usato, deve essere aperto utilizzando
l’istruzione open.
open <nome cursore> [(<lista di parametri>)];
L’istruzione select associata viene quindi processata e il cursore punta
alla prima tupla selezionata. Le tuple selezionate possono essere processate
una alla volta utilizzando il comando fetch
fetch <nome cursore> [(<lista di variabili>)];
Il comando fetch assegna i valori degli attributi selezionati dalla tupla
corrente alla lista di variabili. Dopo un comando fetch, il cursore avanza
alla successiva tupla nell’insieme del risultato dell’istruzione select ottenuto
con il comando open. Da notare che le variabili nella lista devono avere lo
stesso tipo di dati dei valori delle tuple selezionate. Dopo che tutte le tuple
sono state processate, si utilizza il comando close per chiudere e disabilitare
il cursore.
close ¡nome cursore¿;
L’esempio riportato sotto illustra come un cursore viene utilizzato insieme
a un loop continuo:
declare
cursor emp_cur is select * from EMP;
emp_rec EMP%ROWTYPE;
emp_sal EMP.SAL%TYPE;
begin
open emp_cur;
loop
fetch emp_cur into emp_rec;
exit when emp_cur%NOTFOUND;
emp_sal := emp_rec.sal;
<sequenza di istruzioni>
end loop;
close emp_cur;
end;
Ogni loop può essere completato incondizionatamente usando la clausola
exit:
exit [<etichetta blocco>] [when <condizione>]
5.3 Cursor
Usando exit senza un’etichetta di blocco si provoca il completamento del
loop che contiene l’istruzione exit. Una condizione può essere un semplice
paragone di valori. In molti casi, comunque, la condizione fa riferimento a
un cursore.
35
36
5. Oracle Database - Sequenze e cursori
Capitolo 6
Oracle Database - Viste
Una vista, (o view), è un oggetto logico che consente di avere una rappresentazione dei dati personalizzata dagli utenti. Per ottenere informazioni
capita di costruire query complesse (con join, alias, nidificazioni, etc.) da dare magari ad un altro componente del team di sviluppo, che avrebbe bisogno
di qualcosa di molto più semplice e meno lungo. La vista può essere intesa
come l’alias di una query, ovvero un modo veloce di eseguire una certa query.
Se la query definita ogni volta e non viene memorizzata in nessuna struttura
fisica o logica, la vista invece è un oggetto creato dall’utente e memorizzato
nel dizionario dei dati. Ciò che resta memorizzato è solo la definizione della
query e non i dati che continuano a essere memorizzati nelle rispettive tabelle.
Le tabelle interrogate da una vista sono chiamate base tables, cioè tabelle di
base Lo statement che consente la creazione di una vista è CREATE VIEW.
Vediamo una vista semplice e una in sola lettura
CREATE VIEW my_view AS
SELECT nominativo, data_assunz, paese_residenza
FROM impiegati;
CREATE VIEW readonly_view AS
SELECT * FROM padre
WITH READ ONLY;
6.1
Cancellazione di una vista
Per eliminare una vista usiamo lo statement DROP VIEW.
Sintassi di DROP VIEW
37
38
6. Oracle Database - Viste
DROP VIEW [ schema. ] view
[ CASCADE CONSTRAINTS ] ;
Quando viene eseguita un’operazione di questo genere Oracle automaticamente, oltre a cancellare la definizione della vista dal dizionario dei dati,
procede con l’eliminazione degli eventuali privilegi sulla vista concessi ad
altri utenti.
6.2
Modifica della definizione di una vista
Per modificare una vista possiamo cancellarla e ricrearla aggiungendo la
modifica oppure possiamo sovrascriverla. La prima soluzione ha però come
conseguenza la cancellazione di tutti i privilegi eventualmente concessi ad altri utenti per l’utilizzo della vista, mentre con la seconda soluzione i privilegi
sarebbero mantenuti. Per sostituire la definizione di una vista usiamo quindi
la clausola OR REPLACE del comando CREATE VIEW. Se la vista esiste
nello schema corrente sarà modificata, se invece non esistesse Oracle ne creerebbe una nuova. Ad esempio immaginiamo voler modificare la definizione
della vista my view, per includere la colonna indirizzo.
CREATE OR REPLACE VIEW my_view AS
SELECT nominativo, data_assunz, paese_residenza, indirizzo
FROM impiegati;
6.3
Interrogazione di una vista
Una vista viene interrogata con lo statement SELECT, cosı̀ come accade
per le tabelle. Ad esempio interroghiamo le viste create precedentemente.
SELECT * FROM my_view;
6.4
Operazioni DML con le viste
Le viste consentono l’esecuzione delle classiche operazioni di DML, ma
con alcune limitazioni. Queste operazioni sono impossibili se ad esempio la
definizione della vista include:
• la clausola DISTINCT;
• la clausola GROUP BY;
6.4 Operazioni DML con le viste
• query nidificate.
Quelle appena elencate sono solamente alcune fra le limitazioni possibili.
Bisogna tener presente che le operazioni che si eseguono sulle viste vengono
in realtà effettuate sulle tabelle di base.
39
40
6. Oracle Database - Viste
Capitolo 7
Sicurezza
La sicurezza nei database permette di consentire o meno le azioni dell’utente sul database e agli oggetti al suo interno. Oracle utilizza schemi e
domini di sicurezza per controllare l’accesso ai dati e limitare l’uso di risorse.
Oracle fornisce una gamma completa di controlli su accessi discrezionali. Il
controllo di accesso discrezionale regola l’accesso degli utenti a tutti gli oggetti denominati attraverso i privilegi. Un privilegio è un permesso di accedere
a un oggetto con nome in un modo prescritto, ad esempio, il permesso di
interrogare una tabella.
Oracle Server è un database relazionale multi-utenza, cioè consente la connessione simultanea di più utenti ad un medesimo DB. È buona norma e compito
del DBA creare e amministrare gli utenti di ciascun database, decidere quali
azioni possono compiere sulle tabelle concedendo loro gli appositi privilegi,
questo perché non tutti abbiano la possibilità modificare le tabelle presenti
in qualsiasi schema o di poter effettuare qualsiasi modifica sul database.
Esistono vari metodi per definire il controllo degli accessi in Oracle:
• Roles: forniscono un mezzo di assegnamento dei permessi organizzata
per utenti. Quindi non è indispensabile dare ad ogni utente i permessi
• Views: meccanismo per controllare l’accesso ai dati. Possono limitare
l’accesso a colonne o righe specificate.
• Virtual Private Databases: pone politiche di sicurezza a righe e a
colonne.
• Label security: permette di assegnare etichette di sicurezza su righe di
dati e controllarne l’accesso basandosi su queste etichette
41
42
7. Sicurezza
7.1
Creazione utente
CREATE USER
Questo comando consente di creare un utente all’interno di un database.
Solitamente è l’utente SYSTEM a svolgere questo importante compito ma,
in realtà, anche altri utenti possono creare account utente, purché in possesso
dei giusti privilegi.
Oracle permette la gestione di tre differenti metodi di autenticazione utenti:
• autenticazione da database
• autenticazione esterna -¿ Accesso senza password (credenziali di sistema)
• autenticazione globale -¿ Server centralizzato.
Analizzeremo solamente l’autenticazione del primo tipo.
7.2
Autenticazione da database
Fondamentalmente consiste nella ricerca, da parte di Oracle, dell’utente
e password forniti durante la fase di login all’interno di un’apposita tabella
del dizionario dei dati e ne verifica la corrispondenza dei dati.
Sintassi semplificata di CREATE USER
CREATE USER <nome utente> IDENTIFIED BY <password>;
In realtà lo statement CREATE USER ha al seguito numerosi attributi
opzionali che possiamo anche impostare per meglio definire le caratteristiche
dell’utente da creare. Gli stessi attributi possono anche essere definiti o modificati successivamente mediante lo statement ALTER USER.
7.2.1
Tablespace di Default
Ciascun utente, se in possesso dell’apposito permesso, può creare oggetti
all’interno del proprio database, più precisamente all’interno di un tablespace. Questo viene indicato come tablespace di default. Durante la creazione
dell’utente, o in fase di modifica (ALTER USER), possiamo anche specificare il default tablespace usando la clausola DEFAULT TABLESPACE.
7.2 Autenticazione da database
43
Se la clausola viene omessa, Oracle imposta il tablespace SYSTEM come
tablespace di default. Questo deve assolutamente essere evitato perché il
tablespace SYSTEM è di proprietà dell’utente amministrativo SYS e in questo è memorizzato il dizionario dei dati di Oracle. Nessun altro oggetto
diverso dal dizionario dei dati dovrebbe essere memorizzato nel tablespace
SYSTEM. Proviamo a creare un nuovo utente e ad assegnargli un tablespace di default differente da SYSTEM. Colleghiamoci al database ARCHIVIO
mediante SQL* Plus come utente amministrativo SYSTEM.
Creazione di un nuovo utente e assegnazione del tablespace di default (users)
CREATE USER rossi IDENTIFIED BY luigi DEFAULT TABLESPACE users;
7.2.2
Temporary Tablespace
Quello temporaneo è invece il tablespace in cui Oracle posiziona i segmenti temporanei prodotti durante le grandi operazioni di ordinamento (ORDER
BY). Quando viene prodotto un ordinamento dei dati solitamente Oracle cerca di effettuarlo in un’apposita area di memoria dell’istanza al fine di rendere
l’operazione molto più veloce. Se quest’area di memoria è troppo piccola per
contenere l’operazione di ordinamento in corso allora Oracle suddivide l’ordinamento in piccole sezioni e le alloca nel tablespace temporaneo. Anche gli
statement SELECT DISTINCT sottopongono i dati delle tabelle interessate
ad un ordinamento, se non è possibile in memoria, sicuramente nel tablespace temporaneo. Oracle crea sempre un tablespace temporaneo, denominato
temp, durante la creazione di un database. Come accade per il tablespace di
default anche per quello temporaneo, se non specificato durante la creazione
di un utente, viene assegnato automaticamente il tablespace SYSTEM.
Creazione di un nuovo utente e assegnazione del tablespace di default (users) e te
CREATE USER sampras IDENTIFIED BY pete
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
7.2.3
Le quote in Oracle
Se ciascun utente avesse a disposizione uno spazio di memorizzazione illimitato all’interno del database, questo aumenterebbe a dismisura. Questo
è uno dei motivi per i quali viene stabilita una quota, ovvero uno spazio con
dimensione prestabilita, per ciascun utente o almeno per quelli la cui dimensione degli oggetti posseduti rischia di aumentare in maniera incontrollata.
Le quote possono essere espresse in byte, in kilobyte, in megabyte oppure
in maniera illimitata mediante la clausolaUNLIMITED. Per dichiarare una
44
7. Sicurezza
quota tramite i comandi CREATE USER e ALTER USER usiamo la clausola
QUOTA. Vediamo qualche esempio.
Creazione di un utente con quota di 24MB nel tablespace "users"
CREATE USER pierf IDENTIFIED BY pinco
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 24M ON users;
La dimensione della quota è espressa nel formato numero + suffisso, dove
il suffisso può essere K per indicare lo spazio in kilobyte ed M per indicare lo
spazio in megabyte. Quando non specifichiamo alcun suffisso allora la quota
è intesa in byte. Nel prossimo esempio assegnamo ad un utente già creato,
una quota illimitata all’interno del suo default tablespace. L’utente in questo
caso non avrà limitazioni di spazio.
Assegnazione posticipata di una quota per un utente
CREATE USER noise IDENTIFIED BY home
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
ALTER USER noise QUOTA UNLIMITED ON users;
7.2.4
Scadenza delle password utente
L’amministratore di database può decidere di far scadere la validità di una
password utente e di costringere lo stesso utente a modificarla al successivo
accesso al DB. Per far scadere una password si usa la clausola PASSWORD
EXPIRE nel comando CREATE USER oppure ALTER USER.
Creazione di un utente con scadenza della password
CREATE USER angel IDENTIFIED BY cresch34
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PASSWORD EXPIRE;
Concedere all’utente il permesso di collegarsi al database
GRANT CREATE SESSION TO angel;
7.2.5
Bloccare l’account
Se l’amministratore di database vuole evitare che un utente si colleghi
allora può decidere di bloccare il suo account. A tale scopo si usa la clausola
ACCOUNT LOCK nel comando CREATE USER oppure ALTER USER.
Blocchiamo l’account angel, creato nell’esempio precedente.
7.3 I permessi
SQL> ALTER USER angel ACCOUNT LOCK;
7.3
I permessi
Il permesso, come noto, rappresenta la capacità di compiere un’azione. In
Oracle i permessi sono chiamati privilegi e consentono di accedere ad oggetti
logici, come le tabelle, posseduti da altri utenti del database, di creare oggetti o di eseguire particolari attività amministrative. Queste sono solamente
alcune delle possibili azioni che in realtà sono davvero numerose. In questa
lezione tratteremo due categorie di privilegi Oracle:
• Privilegi sugli oggetti - intesi come l’insieme di operazioni che è possibile
compiere su un particolare schema. Ricordiamo che lo schema è, in
realtà, un’utente che possiede oggetti;
• Privilegi di sistema - consentono di eseguire operazioni di sistema, come
la creazione di tabelle, di utenti o di avere la possibilità di connettersi
ad un database.
7.3.1
I permessi sugli oggetti
Vediamo alcuni dei permessi: ALTER, SELECT, DELETE, INSERT e
UPDATE. L’oggetto su cui agiscono è la tabella ma questi possono anche
operare su funzioni, procedure, sequenze, viste, ecc. L’utente amministrativo SYSTEM è l’account abilitato alla concessione dei privilegi sugli oggetti
ad un altro utente ma, in realtà, la concessione può essere anche data dal
proprietario dell’oggetto. Negli esempi che seguiranno le concessioni e le revoche dei privilegi sono state eseguite usufruendo dell’utente SYSTEM. Per
concedere ad un utente un privilegio sugli oggetti utilizziamo il comando
GRANT.
Sintassi di GRANT
GRANT <privilegio> ON <schema>.<oggetto> TO <utente>;
Per revocare, invece, un privilegio sugli oggetti già concesso ad un utente
utilizziamo il comando REVOKE.
Sintassi di REVOKE
REVOKE <privilegio> ON <schema>.<oggetto> FROM <utente>;
45
46
7. Sicurezza
Il privilegio ALTER
Concesso su un oggetto tabella, permette all’utente a cui è affidato di
modificare la struttura di questa tabella, anche se di proprietà di un altro
utente. In particolare permette di:
• aggiungere nuove colonne alla tabella;
• cancellare colonne dalla tabella;
• modificare il nome della tabella;
• modificare il datatype di colonna.
Concediamo all’utente sampras il privilegio di alterare la tabella impiegati,
di proprietà dell’utente myself.
GRANT ALTER ON myself.impiegati TO sampras;
Il privilegio SELECT
Consente di eseguire query, interrogazioni, su tabelle o viste (oggetti logici su cui è concesso) che non sono di proprietà dell’utente beneficiario del
privilegio. Può persino essere concesso a livello di colonna, al fine di eseguire
query solo su alcune colonne e non su tutte quelle che compongono la tabella. Concediamo all’utente pierf il privilegio di eseguire una SELECT sulla
tabella contatti, di proprietà dell’utente myself.
Esempio di output su console
GRANT SELECT ON myself.contatti TO pierf;
Il privilegio DELETE
Permette di rimuovere righe da una tabella o da una vista, di proprietà di
un altro utente. Per poter effettivamente rimuovere le righe è necessario che
all’utente beneficiario del privilegio DELETE sia concesso anche il privilegioSELECT sulla stessa tabella. Concediamo all’utente noise il privilegio di
cancellare righe dalla tabella copia contatti, di proprietà dell’utente myself.
GRANT DELETE ON myself.copia_contatti TO noise;
7.4 Sistemi di autenticazione in rete
47
Il privilegio INSERT
Permette di inserire nuove righe in una tabella posseduta da un altro
utente. Questo privilegio può essere concesso anche a livello di colonna per
consentire, all’utente beneficiario, di inserire dati solo nelle colonne specificate
nella GRANT e non su tutte quelle che compongono la tabella. Concediamo
all’utente sampras il privilegio di inserire nuove righe nella tabella contatti,
di proprietà dell’utente myself.
GRANT INSERT ON myself.contatti TO sampras;
Il privilegio UPDATE
Permette di modificare i dati contenuti nella tabella specificata, di proprietà di un altro utente. Può essere concesso sia sull’intera tabella che sulla
singola colonna, al fine di limitare la modifica dei dati solo su alcune colonne
e non su tutte. Concediamo all’utente noise il privilegio di aggiornare esclusivamente le colonne ID Impiegato e Nominativo della tabella impiegati, di
proprietà dell’utente myself.
GRANT UPDATE (ID_Impiegato, Nominativo) ON myself.impiegati TO noise
7.3.2
I permessi di sistema
Analizzeremo essenzialmente due privilegi di sistema:CREATE SESSION
e CONNECT.
CON:
GRANT CREATE SESSION TO sampras;
Si garantisce il login al database ma non la creazione di oggetti.
GRANT CONNECT TO sampras;
garantisce la creazione di oggetti
7.4
Sistemi di autenticazione in rete
Public Key Infrastructure-Based Authentication I sistemi di autenticazione basati su sistemi di crittografia a chiave pubblica emettono certificati
digitali per i client, che li utilizzano per accedere al server centrale senza coinvolgere il server di autenticazione. Oracle fornisce un infrastruttura a chiave
publica PKI per l’utilizzo di queste chiavi con certificato. I componenti sono:
• Secure Sockets Layer (SSL): permettono l’autenticazione e la gestione
sicura della chiave di sessione
48
7. Sicurezza
• Oracle Call Interface OCI e PL/SQL: per firmare le funzione specificate
dall’utente, utilizzando un certificato
• Un trusted certificate: una parte dell’identità, comunque attendibile,
per identificare che chi ha fatto richiesta sia davvero chi dice di essere
• Oracle wallet: strutture dati che contengono una chiave privata dell’utente e certificato e una serie di certificati principali dell’utente
• Oracle wallet manager: applicazione java utilizzata per gestire e modificare le informazioni di protezione nel oracle wallet.
– Gestisce i certificati X.509v3 sui client e server Oracle
– Genera la coppia di chiave pubblica-privata e crea la richiesta di
certificato
– Installa un certificato per l’identità
– Consente di configurare i certificati
– Apre il wallet per consentire l’accesso a servizi basati su PKI
– Crea un portafoglio che può essere letto con Oracle Enterprise
Login Assistant
• Oracle Enterprise Security Manager: consente la gestione centralizzata
dei privileggi e aumentare il livello di sicurezza.
• Oracle Enterprise Access Assistant: tool basato in java per aprire e
chiudere il portafoglio degli utenti al fine di abilitare o disabilitare la
sicurezza SSL-based per un applicazione.
Capitolo 8
Oracle Database - Alter Table
A volte può essere utile modificare una tabella già esistente. Aggiungere una colonna Può accadere che, dopo aver creato una tabella, abbiamo
la necessità di aggiungervi una o più colonne. Se la tabella in questione è
vuota, l’utente proprietario potrebbe decidere di eliminarla definitivamente
e ricrearla daccapo con l’aggiunta della colonna desiderata. Questa potrebbe
essere una soluzione valida ma inaccettabile per un amministratore di database, soprattutto quando all’interno della tabella ci sono dati importanti che
non vogliamo assolutamente perdere. Allora come procediamo? Ricorriamo
al comando ALTER TABLE e alla sua clausola ADD.
Sintassi di ALTER TABLE con ADD
ALTER TABLE <schema>.<tabella> ADD <colonna> <datatype>;
Possiamo anche modificare la struttura di una tabella appartenente ad un
altro utente: è innanzitutto obbligatorio possedere il corretto privilegio (privilegio ALTER), quindi dobbiamo specificare lo schema di appartenenza della
tabella.
ALTER TABLE impiegati ADD Indirizzo VARCHAR2(30);
8.1
Modificare una colonna
Può capitare anche di dover modificare le impostazioni di colonna per una
tabella che abbiamo creato in precedenza. Può accadere, ad esempio, di aver
impostato per una colonna una capacità di memorizzazione troppo piccola
e di doverla aumentare al fine di memorizzare i dati voluti. A tale scopo
usiamo il comando ALTER TABLE accompagnato dalla clausola MODIFY.
Sintassi di ALTER TABLE con MODIFY
ALTER TABLE <schema>.>tabella> MODIFY <colonna> <nuovi attributi>;
49
50
8. Oracle Database - Alter Table
Nota: Possiamo tranquillamente aumentare senza incorrere in errori Oracle la dimensione di un datatype VARCHAR2 o la precisione di un datatype
NUMBER. Diversamente, se dovessimo diminuire queste dimensioni è necessario che i dati già presenti in tabella entrino completamente nella nuova
dimensione di colonna scelta.
8.2
Cancellazione di una colonna
Quando una colonna non è più necessaria possiamo liberare lo spazio di
memoria da essa occupato con il comando ALTER TABLE seguito dalla
clausola DROP COLUMN.
ALTER TABLE impiegati DROP COLUMN nuova_colonna;
Un’alternativa alla cancellazione di una colonna è renderla temporaneamente inutilizzabile e di cancellarla in un secondo momento usando la clausola
SET UNUSED COLUMN.
Sintassi di ALTER TABLE per disabilitare una colonna
ALTER TABLE <schema>.<tabella> SET UNUSED COLUMN <colonna>;
Capitolo 9
Oracle Database - Funzionalità
9.1
Cluster
L’utililzzo dei cluster permette in determinate condizioni di ridurre l’I/O
su disco e quindi di accelerare l’accesso ai dati quando si fa il join tra le
tabelle interessate.
9.1.1
Tipi di Cluster
In oracle vi sono tre tipologie di cluster:
• Index Cluster, il default. Viene usato un indice per gestire i dati del
cluster
• Hash cluster. Viene usata una funzione hash sui campi comuni del
cluster per accedere ai dati del cluster.
• Sorted Hash Cluster. Viene usata una funzione hash sui campi comuni
del cluster per accedere ai dati del cluster ed ogni lista corrispondente
ad una chiave hash contiene i record ordinati.
A parte la creazione e la manutenzione strettamente di competenza del
DBA l’utilizzo del cluster dal punto di vista applicativo è trasparente: le
tabelle che lo compongono si utilizzano come tabelle normali. Ovviamente
cambiano le prestazioni a seconda del tipo di uso. Ad esempio un full table
scan su una tabella componente un cluster è generalmente più lento. Viceversa l’accesso in join alle tabelle del cluster è più veloce. Vi è un piccolo
risparmio nello spazio occupato in quanto i valori delle colonne condivise non
sono duplicati.
51
52
9. Oracle Database - Funzionalità
Esempio di creazione di un index cluster
Prima si crea il cluster:
CREATE CLUSTER TEST_CLUSTER (commoncolumn number(10))
SIZE 1024 TABLESPACE users;
La clausola size indica la dimensione in byte da riservare in ogni blocco
per le righe corrispondenti ad ogni chiave (o hash value nel caso di hash
cluster). In questo caso ad esempio verranno riservati 1024 byte per ogni
chiave, il che significa che con blocchi da 8 Kb non vi saranno più di 8 chiavi
per blocco. Se non viene specificata questa clausola oracle riserva un blocco
per ogni chiave. Ora si possono creare le tabelle del cluster:
CREATE TABLE ORDINI_TESTATE (
ORD_ID NUMBER(10),
DESCRIZIONE VARCHAR2(100))
CLUSTER TEST_CLUSTER(ORD_ID);
A questo punto per poter inserire dati in questa tabella (cluster) occorre
prima creare un indice, altrimenti:
SQL > insert into ordini_righe values (1,’a’);
insert into ordini_righe values (1,’a’)
ERRORE alla riga 1:
ORA-02032: imposs. usare tabelle in cluster prima
che l’indice del cluster sia creato
quindi:
SQL > create index test_cluster_index
on cluster test_cluster;
Indice creato.
Infatti:
SQL > insert into ordini_righe values (1,’a’);
Creata 1 riga.
9.2 Hot Backup
9.2
53
Hot Backup
Backup a caldo (o Hot backup) backup effettuato mentre il programma
che usa il file da copiare è attivo. I dati possono quindi risultare modificati
mentre il backup è in corso.
Oracle Recovery Manager (comunemente chiamato con il nome dell’eseguibile
RMAN) è il programma di gestione del backup dei database oracle fornito da
Oracle stesso. Mantiene un catalogo dei backup effettuati. Questo catalogo si
trova sempre sul controlfile, ma in questo caso le informazioni sono mantenute
per un periodo limitato dal parametro CONTROL FILE RECORD KEEP TIME,
per evitare la crescita eccessiva del control file. Per default il parametro è
settato a 7 il che significa che le informazioni sui backup effettuati vengono
mantenute nei controlfile per sette giorni, dopo di che lo spazio viene riusato
in modo circolare per scrivere nuove informazioni. In alternativa è possibile
usare il cosiddetto Recovery Catalog che in pratica è un catalogo memorizzato su un’altro database. I vantaggi dell’uso di un Recovery catalog sono
almeno tre:
La perdita dei control file non comporta la perdita delle informazioni sui
backup
• si possono mantenere facilmente le informazioni sui backup per periodi
lunghi
• si possono utilizzare script memorizzati nel recovery catalog
• Lo svantaggio è dato da un piccolo sovraccarico di gestione.
Fare un backup completo di un database a caldo può essere fatto cosı̀:
RMAN>BACKUP DATABASE FORMAT ’/usr/backup/full_%U’
PLUS ARCHIVELOG ALL FORMAT ’/usr/backup/arch_%U’
DELETE INPUT;
L’informazione su questo backup verrà salvata nel catalog (sia controlfile che recovery catalog se configurato), in caso di necessità il ripristino è
altrettanto facile:
RMAN>RESTORE DATABASE;
RMAN>RECOVER DATABASE;
RMAN>ALTER DATABASE OPEN;
54
9. Oracle Database - Funzionalità
9.3
Replicazione
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names
=> ’scott’,
source_database
=> ’prod’,
destination_database
=> ’test’,
capture_name
=> ’capture_scott’,
capture_queue_table
=> ’rep_capture_queue_table’,
capture_queue_name
=> ’rep_capture_queue’,
capture_queue_user
=> null,
apply_name
=> ’apply_scott’,
apply_queue_table
=> ’rep_dest_queue_table’,
apply_queue_name
=> ’rep_dest_queue’,
apply_queue_user
=> null,
propagation_name
=> ’prop_scott’,
log_file
=> ’exp_scott.log’,
bi_directional
=> false,
include_ddl
=> true,
instantiation
=> dbms_streams_adm.instantiation_schema,
perform_actions
=> false,
script_name
=> ’schema_replication.sql’,
script_directory_object
=> ’script_dir’
);
END;
schema names: il nome dello schema (o degli schemi, separati da virgole)
da propagare (in questo caso scott)
* source database: il database sorgente
* destination database: il database di destinazione
* capture name: il nome del processo di cattura configurato per acquisire le
modifiche effettuate su PROD
* capture queue table: il nome della tabella di accodamento per ogni coda
utilizzata da un processo di cattura
* capture queue name: il nome di ogni coda utilizzata da un processo di
cattura
* capture queue user: va impostato a null per indicare che la procedura non
può dare il grant di alcun privilegio
* propagation name: il nome di ciascuna propagazione configurata per propagare le modifiche
* log file: il nome del file di log generato da expdp
* bi directional: se impostato a ’false’ indica che la propagazione dele infor-
9.4 Funzioni Explain/Analyze
55
mazioni è monodirezionale dal db di produzione a quello di test
* include dll: impostato a ’true’ indica che sia le istruzioni DDL sia quelle
DML vanno replicate dal db sorgente a quello di destinazione
* instantiation: se impostato a DBMS STREAMS ADM.INSTANTIATION SCHEMA
indica che l’istanziazione dello schema di destinazione verrà effettuata utilizzando le utilities expdp/impdp
* perform action: se impostato a ’true’ consente l’esecuzione dello script
PL/SQL, se impostato a ’false’ ne memorizza il risultato in un ulteriore script
che verrà posizionato nella directory indicata dal parametro ’script directory object’
* script name: impostato con la stringa ’schema replication.sql’. Questo
script verrà creato nella directory Oracle ’script dir’ (/home/oracle/script dir)
e conterrà tutti i passaggi per istanziare lo schema di replica
* script directory object: la directory Oracle in cui memorizzare lo script
’schema replication.sql’
9.4
9.4.1
Funzioni Explain/Analyze
ANALYZE
Gli indici sono oggetti della basedati di ORACLE che forniscono un metodo veloce e efficiente di richiamo dei dati dalle tabelle. Gli indirizzi fisici
delle righe richieste possono essere richiamati molto più efficientemente dagli
indici anzichè leggere l’ intera tabella provocando miglioramenti significativi
alle prestazioni di SQL. La struttura di default degli indici è il B-Tree (Balanced tree). Un indice B-tree ha una struttura gerarchica di tipo albero con
nodi e fogli. Ogni nodo di tale albero rappresenta una chiave di ricerca, che
contiene a sua volta una serie di intervalli. L’indice lo si può immaginare
come un oggetto che divide la tabella in blocchi, ognuno dei quali contiene
una lista dei valori chiave e indirizzi fisici (ROWIDs) delle righe nella basedati. Quando viene cancellata una riga da una tabella, Oracle non riscrive
il corrispondente spazio occupato dall’indice a meno che l’indice non venga
ricreato. Se ciò accade, gli indici diventano frammentati, specialmente nelle tabelle molto dinamiche in cui vengono eseguiti frequentemente comandi
DML.
La vista di USER INDEXES contiene le informazioni statistiche che vengono
inserite ogni volta che il comando ANALYZE INDEX viene eseguito:
ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;
56
9. Oracle Database - Funzionalità
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
La vista USER INDEXES viene usata dall’ottimizzatore Oracle, tramite il
metodo CBO(Cost Based Optimizer), per fornire le informazioni necessarie a
determinare il percorso ottimale di esecuzione del comando SQL d’interesse.
Quindi purtroppo non conserva le statistiche sulla condizione interna degli indici di ORACLE. Questo può avvenire solo utilizzando il comando ANALYZE
INDEX seguito dalla clausola VALIDATE STRUCTURE che non cambia le
statistiche nella vista USER INDEXES; quindi potrebbe essere eseguita con
sicurezza senza interessare i CBO.
ANALYZE INDEX popola la vista SYS.INDEX STATS che può essere
associata a un sinonimo pubblico INDEX STATS. Questa vista fornisce informazioni su di un solo indice per volta, difatti non può contenere più di
una riga. Si osservino i dati della INDEX STATS:
select name,
height,
lf_rows,
del_lf_rows
from INDEX_STATS;
Il risultato della query è il seguente:
NAME
HEIGHT LF_ROWS
DEL_LF_ROWS
-------------- -------- ----------- ------------------NOME_INDICE
2
4784
1980
Il campo HEIGHT si riferisce al numero massimo dei livelli incontrati all’interno dell’ indice.
Il campo LF ROWS si riferisce al numero di righe appartenenti a quell’indice.
Il campo DEL LF ROWS si riferisce al numero di righe che sono state cancellate.
La prima regola è che un indice con un HEIGHT maggiore di 3 ha buone
possibilità di essere ricreato. Ovviamente esistono delle eccezioni ma, generalmente, tabelle con HEIGHT di 4 o più possono causare letture non
9.4 Funzioni Explain/Analyze
57
necessarie dell’indice e quindi per ottenere benefici sarebbe opportuna la ricreazione dell’indice stesso.
La seconda regola è che il campo DEL LF ROWS dovrebbe essere sempre
inferiore al 20% del campo LF ROWS poiché significherebbe che la tabella è
stata soggetta a numerose cancellazioni, quindi anche in questo caso sarebbe
necessaria la ricostruzione dell’indice.
Nell’esempio riportato DEL LF ROWS è uguale a 1980, LF ROWS è
uguale a 4784, il rapporto è: (1980*100) / 4784 = 41.38L’indice in questo
caso sarebbe assolutamente da ricostruire!!
9.4.2
EXPLAIN PLAN
E’ il comando utilizzato da ORACLE per visualizzare il piano di esecuzione prescelto dall’ottimizzatore. La visualizzazione in formato testuale è
organizzata in una struttura ad albero che specifica:
• L’operazione eseguita
• L’oggetto su cui agisce l’operazione
• Il numero di righe coinvolte
• Il costo dell’operazione (non è espresso in unità di misura, è un numero
puro calcolato dall’ottimizzatore di Oracle e utilizzabile per confronti)
Le informazioni relative al piano vengono memorizzate nella PLAN TABLE
che fa parte dello schema.
ESEMPIO:
EXPLAIN PLAN FOR
select * from LINEITEM, PART
where p_NAME >’qqq’ and p_partkey=l_partkey
Plan Table
OPERAZIONE
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL
TABLE ACCESS BY INDEX RO
INDEX UNIQUE SCAN
NOME
LINEITEM
PART
SYS C0095
ROWS
27K
27K
41
66K
66K
BYTES
8M
8M
8M
7M
COST
83
83
1
2
1
58
9. Oracle Database - Funzionalità
IL NOME E’ L’OGGETTO SU CUI HA EFFETTO L’OPERAZIONE
ROWS IL NUMERO DI RIGHE LETTE
BYTES IL NUMERO DI BYTE LETTI
COST IL COSTO DELL’OPERAZIONE
9.5
Locking
Il lock può avere diverse granularità ovvero diversi gradi di precisione,
può essere su un singolo record, su una intera tabella o su un database e può
essere con diversi gradi di visibilità (lettura, scrittura, lettura e scrittura).
Oracle usa automaticamente solo lock Row Level
Lock table Inibisce l’accesso alla tabella ad altri utenti.
Sintassi:
LOCK TABLE tabella IN lockmode MODE [NOWAIT]
Parametri:
tabella: il nome della tabella.
lockmode: puo’ essere uno dei seguenti
ROW SHARE: permette l’accesso a piu’ entita’ sulla tabella, ma inibisce gli
utenti dal fare a loro volta un lock per averne l’accesso esclusivo.
ROW EXCLUSIVE: come il ROW SHARE, ma impedisce anche i lock in
modo SHARE; e’ automaticamente ottenuto quando si inserisce, modifica o
eliminano dei dati.
SHARE UPDATE: sinonimo di ROW SHARE.
SHARE: permette le interrogazioni contemporanee ma non le modifiche sulla
tabella.
EXCLUSIVE: permette solo le query sulla tabella e inibisce qualunque altra
attivita’ sulla tabella.
SHARE ROW EXCLUSIVE: permette le interrogazioni ma proibisce agli
utenti le modifiche e il lock in modo SHARE.
NOWAIT: se specificato, Oracle restituisce immediatamente il controllo all’utente nel caso in cui la tabella abbia un lock di un altro utente; se omesso,
Oracle aspetta finche’ la tabella non e’ di nuovo disponibile e, poi, restituisce
il controllo all’utente.
Esempio: LOCK TABLE macchine IN EXCLUSIVE MODE NOWAIT;
9.6 Supporto ODBC/JDBC
Impedisce operazioni che non siano una query agli altri utenti sulla tabella
MACCHINE e controlla che la tabella non sia gia’ sotto lock.
9.6
Supporto ODBC/JDBC
Microsoft driver ODBC per Oracle consente di collegare l’applicazione
compatibile ODBC a un database Oracle. Permette l’accesso a pacchetti PL
/ SQL , e l’accesso di Oracle all’interno di Internet Information Services(IIS).
Oracle RDBMS multiutente è un database relazionale che viene eseguito con
diverse workstation e sistemi operativi.
Collegarsi all’RDBMS Oracle da un’applicazione Java e’ semplice: basta
utilizzare i corretti driver JDBC.
9.7
Benchmark
EMP7
Sistema:Windows 7
Processore: Centrino duo t2350
Numero analizzato:
105.413.504
Elapsed: 00:00:15.179
TCP-B
11 SECONDI
59
60
9. Oracle Database - Funzionalità