Oracle data dictionary: alla ricerca dell`informazione perduta

PROGRAMMING
Database
Il dizionario dati di Oracle è uno strumento
di eccezionale efficacia per sviluppatori
e DB. Vediamo quali informazioni
contiene e com’è possibile consultarlo
Oracle
data dictionary:
alla ricerca
dell’informazione perduta
[email protected] di Massimo Ruocchio
È laureato in matematica ed ha ottenuto la certificazione Oracle come Application Developer. Si occupa
di analisi, progettazione e sviluppo di applicazioni software in ambiente Oracle.
I
l data dictionary è una collezione di viste e
sinonimi di sistema che forniscono informazioni dettagliate sulla struttura, sulle attività
e sugli oggetti contenuti nel DB. In quest’articolo cercheremo di vedere come può essere utilizzato il dizionario, allo scopo di recuperare informazioni utilissime per uno sviluppatore ed indispensabili per un DBA. Nei paragrafi che seguono,
vedremo sia come ottenere informazioni dal data
dictionary mediante banali comandi SQL, che come
utilizzarlo per scrivere semplici utility in PL/SQL.
Le viste del dizionario sono divise fondamentalmente in quattro insiemi:
76
CP 108
Viste contenenti informazioni sugli oggetti di
proprietà dell’utente Oracle con cui si è connessi (denominate USER%);
Viste con informazioni su tutti gli oggetti accessibili per l’utente (ALL%);
Viste con informazioni sugli oggetti accessibili
dagli utenti DBA, dunque tutto ciò che esiste in
ogni utente Oracle (DBA%);
Le dynamic performances tables, vale a dire le
viste che contengono a runtime informazioni su
tutto quello che sta avvenendo nel DB (V$%).
Guarderemo con maggiore attenzione le viste
USER%, poiché nelle ALL% e DBA% possono
essere trovate le stesse informazioni per un numero
maggiore di oggetti.
Quest’articolo analizza il dizionario come era alla
versione 7.3.4 di Oracle. Le versioni 8, 8i e 9i
del Database prevedono molte nuove tipologie di
oggetto. Nelle ultime versioni, dunque, sono state
aggiunte molte altre viste al dizionario. Per ragioni
di spazio, si è deciso di non trattare in questa sede le
viste relative alle nuove tipologie di oggetto. Negli
articoli presenti in bibliografia è possibile trovare
informazioni sulle ultime versioni del dizionario.
Le viste principali
La prima select da eseguire è la seguente:
/* cosa c’è nel dizionario? */
Select * from dictionary;
La vista DICTIONARY fornisce l’elenco delle
viste presenti del dizionario con la relativa
descrizione. Le stesse informazioni possono essere
ottenute mediante il sinonimo DICT. La vista
DICT_COLUMNS fornisce, invece, le descrizioni
di tutte le colonne delle viste incluse nel dizionario. Facciamo un esempio. Vogliamo sapere quali
sono le tabelle di proprietà dell’utente SCOTT che
non si trovano sul tablespace ‘TABELLE_SCOTT’.
Prima di tutto si cerca nel dizionario quali sono le
viste che contengono informazioni sulle tabelle:
Select * from dict
Where table_name like ‘USER%TABLES%’;
TABLE_NAME
COMMENTS
USER_TABLES
User’s own relational tables
USER_TABLESPACES
Accessible tablespaces
Prendiamo in considerazione la USER_TABLES.
Effettuiamo dunque la select
select * from dict_columns
where table_name = ‘USER_TABLES’;
e vediamo che le colonne di interesse sono:
TABLE_NAME e TABLESPACE_NAME, quindi
con l’istruzione
select TABLE_NAME, TABLESPACE_NAME
from USER_TABLES
where TABLESPACE_NAME != ‘TABELLE_SCOTT’;
otterremo l’elenco delle tabelle desiderate.
Altra vista del dizionario molto utilizzata è la
CAT, oppure USER_CATALOG, che elenca le
tabelle, view, sinonimi e sequence di proprietà dell’utente. Per ogni record presente nella vista è indicato anche il tipo di oggetto.
Per conoscere tutti, ma proprio tutti, gli oggetti
di proprietà dell’utente si può consultare la vista
USER_OBJECTS. Questa vista rende informazioni
varie come nome ed ID dell’oggetto, chi e quando
l’ha creato o modificato e lo stato dell’oggetto.
Quando l’object è un programma (function, procedure, package...) è possibile determinarne la misura
in byte mediante la USER_OBJECT_SIZE, che fornisce non solo la lunghezza del sorgente ma anche
del codice dopo il parsing, dopo la compilazione e
la misura dei messaggi d’errore.
I dati nel DB
Veniamo agli oggetti che contengono i dati
del nostro DB; per avere informazioni sugli
oggetti principali esistono le seguenti viste:
USER_TABLES, USER_VIEWS, USER_CLUSTERS,
USER_SEQUENCES, USER_SNAPSHOTS, USER_
SYNONYMS.
Ognuna di queste viste fornisce tutte le informazioni sull’oggetto desiderato ed è corredata da
una serie di viste accessorie che forniscono informazioni sugli oggetti secondari, quelli che esistono solo come parte di un oggetto principale.
Ad esempio, relativamente alle tabelle, si possono avere informazioni sui seguenti elementi
accessori:
Colonne della tabella
Commenti sulla tabella
Commenti sulle colonne
Constraint presenti sulla tabella
Colonne del constraint
Indici presenti sulla tabella
Composizione degli indici
USER_TAB_COLUMNS
USER_TAB_COMMENTS
USER_COL_COMMENTS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_INDEXES
USER_IND_COLUMNS
Vediamo un esempio di utilizzo delle informazioni del dizionario per risolvere un semplice problema: abbiamo bisogno di eliminare tutti gli indici
di una tabella e ricostruirli perfettamente identici.
Il Listato 1 mostra un semplice script PL/SQL che
legge gli indici presenti sulla tabella ed inserisce
in una tabella d’appoggio l’istruzione DDL per la
creazione dell’indice letto.
A fine inserimento viene creato, mediante
spool, un file “indici.sql” da lanciare per creare
gli indici. Lo script presentato utilizza le viste
USER_INDEXES e USER_IND_COLUMNS per
recuperare le informazioni sugli indici e sulle
colonne che li compongono.
Veniamo ai constraint. Mediante la vista
USER_CONSTRAINTS è possibile ricavare il
nome e l’owner della tabella su cui è stato creato il
constraint, il nome del constraint, il tipo (‘P’ per le
primary key, ‘R’ per le foreign key, ‘U’ per le unique
key, ‘C’ per i check constraint, ‘V’ per le check option
delle view modificabili), la regola da applicare (per
i check constraint), l’owner ed il nome del constraint
di riferimento (per le foreign key), lo stato del constraint. Se, ad esempio, volessimo conoscere il nome
di tutte le tabelle che hanno una foreign key che
punta alla tabella ‘COMUNI’ potremmo eseguire
il seguente SQL:
select a.table_name, a.constraint_name,
b.constraint_name references
from user_constraints a, user_constraints b
where a.constraint_type = ‘R’
and b.table_name = ‘COMUNI’
and a.r_constraint_name = b.constraint_name;
TABLE_NAME
CONSTRAINT_NAME
REFERENCES
CLIENTI
FORNITORI
CLI_COM_FK
FOR_COM_FK
COM_PK
Per conoscere le colonne che compongono un
dato constraint, è possibile interrogare la vista
USER_CONS_COLUMNS.
Il codice nel DB
Per quanto riguarda i database trigger sono
disponibili due viste: USER_TRIGGERS e
USER_TRIGGER_COLS. La USER_TRIGGERS
lista informazioni su tutti i trigger, includendo le
clausole WHEN e REFERENCING, l’evento che
scatena il trigger (INSERT UPDATE o DELETE,
BEFORE o AFTER), lo stato, la descrizione, l’owner, il nome ed il corpo del trigger.
Il corpo è conservato in un campo di tipo LONG,
dunque per visualizzarlo da SQL*Plus è necessaria
una SET LONG N, dove N è un numero maggiore
o uguale della lunghezza in byte del testo del trigger
body. La vista USER_TRIGGER_COLS elenca le
colonne utilizzate da ogni trigger. Per ogni colonna
indica anche, nel campo COLUMN_USAGE,
come questa viene utilizzata (IN lettura, OUT
scrittura, IN OUT lettura e scrittura) e se ad
esser utilizzato è il valore della colonna prima
o dopo l’esecuzione del comando DML che ha
77
CP 108
PROGRAMMING
Database
scatenato il trigger (OLD o NEW). La vista
USER_UPDATABLE_COLUMNS indica le
colonne delle view che possono essere
modificate mediante la view stessa, l’eventuale presenza della check option può essere
appurata mediante la consultazione della
USER_CONSTRAINTS (CONSTRAINT_
TYPE = ’V’).
La vista USER_SOURCE contiene il codice
di tutte le procedure, funzioni e package. La
vista presenta un record per ogni linea di
codice, può essere letta per NAME, il nome
del programma, TYPE, il tipo del programma
o LINE, il numero di linea. Il codice è contenuto nel campo TEXT.
Da notare che per i package è presente il
codice complessivo della specification e quello
del body, non suddiviso tra le singole procedure e funzioni che li compongono. Gli
errori di compilazione possono essere letti
dalla USER_ERRORS, oltre che col classico
SHO ERR. La vista USER_ DEPENDENCIES
elenca le dipendenze funzionali presenti tra
programmi ed oggetti del DB.
Per ogni oggetto, individuato mediante
NAME e TYPE, è possibile vedere nome,
tipo, owner e Dblink degli oggetti referenziati.
La consultazione delle dipendenze funzionali
può essere effettuata anche mediante le viste
DEPTREE e IDEPTREE, queste forniscono
le dipendenze in forma grafica, con una struttura grafica.
La IDEPTREE differisce dalla DEPTREE
per la rappresentazione grafica dei vari livelli.
DEPTREE ed IDEPTREE non sono presenti
per default nell’installazione di Oracle, ma
devono essere create mediante gli appositi
script forniti con i CD d’installazione.
Quanto spazio mi resta?
Per il monitoraggio degli spazi, esistono cinque
viste disponibili a tutti gli utenti.
USER_TABLESPACES,
USER_TS_
QUOTAS e USER_FREE_SPACE consentono di verificare quali sono i tablespace disponibili per l’utente, quali sono le quote (in byte
e data block) assegnate all’utente e quali sono
le porzioni di spazio libero disponibili per ogni
database.
Vediamo un esempio di utilizzo della
USER_FREE_SPACE: per conoscere lo spazio
disponibile sul tablespace TS_TABELLE si effettua la semplice select:
78
CP 108
select sum(bytes)
from user_free_space
where tablespace_name = ‘TS_TABELLE’;
LISTATO 1 Un semplice script PL/SQL per la creazione di indici
set
set
set
set
echo off
serverout on
verify off
line 1000
create table INDICI_DA_CREARE
(nome varchar2(30), stringa varchar2(2000));
Accept Tabella prompt “Nome della tabella>”
declare
Str_colonne varchar2(30000);
Str_indice varchar2(30000);
cursor indici is
select i.index_name, i.tablespace_name,
i.initial_extent/1024 initial_extent,
i.next_extent/1024 next_extent,
i.min_extents,i.max_extents,
i.pct_increase,
i.ini_trans, i.max_trans,
decode(i.uniqueness,’UNIQUE’,’UNIQUE’,null)
uniqueness,
i.pct_free, i.freelists, i.freelist_groups
from user_indexes i
where table_name = upper(‘&Tabella’);
cursor colonne(indice varchar2) is
select column_name
from user_ind_columns
where index_name = indice
order by column_position;
Begin
For rec in indici loop
Str_colonne := null;
For r in Colonne(rec.index_name) loop
Str_colonne := Str_colonne||’, ‘
||r.column_name;
End loop;
Str_colonne := ltrim(Str_colonne,’, ‘);
Str_indice := ‘Create ‘||rec.uniqueness||
‘ index ‘||rec.index_name
||’ on ‘||upper(‘&Tabella’)
||’ (‘||Str_colonne||’)’||
‘ Tablespace ‘||
rec.tablespace_name||
‘ Initrans ‘||rec.ini_trans||
‘ Maxtrans ‘||rec.max_trans||
‘ Pctfree ‘||rec.pct_free||
‘ Storage (Initial ‘||
rec.initial_extent||’K’||
‘ Next ‘||rec.next_extent||’K’||
‘ Minextents ‘||rec.min_extents||
‘ Maxextents ‘||rec.max_extents||
‘ Pctincrease ‘||rec.pct_increase||
‘ Freelists ‘||rec.freelists||
‘ Freelist groups ‘||
rec.freelist_groups||’);’;
Insert into INDICI_DA_CREARE
values (rec.index_name, Str_indice);
end loop;
End;
/
set head off
spool indici.sql
Select Stringa
From Indici_da_creare;
spool off;
set head on
set echo on
Con la precedente istruzione si vede lo spazio
complessivo disponibile nel tablespace. Ogni volta
che viene allocato un extent è necessario che lo
spazio disponibile sia ininterrotto sul datafile. Per
calcolare quanti extent da N byte è possibile allocare nel tablespace TB_TABELLE possiamo eseguire la seguente istruzione SQL:
select sum(trunc(bytes/N))
from user_free_space
where tablespace_name = ‘TS_TABELLE’;
Per evitare problemi è possibile utilizzare l’istruzione ALTER TABLESPACE TS_TABELLE COALESCE per accorpare gli eventuali spazi liberi contigui, minimizzare la dimensione del next_extent e la
pct_increase oppure esportare ed importare l’intero
DB, in modo da inserire tutti i dati nell’initial_extent
ed eliminare la frammentazione di spazi.
Le viste USER_SEGMENTS e USER_
EXTENTS forniscono informazioni sui contenitori
di dati (TABELLE, INDICI, CLUSTER, ROLLBACK, TEMPORARY ecc.) e sugli extent che li
compongono.
Avevamo già visto in precedenza, anche con
l’esempio del Listato 1, che sulla USER_INDEXES
è possibile trovare informazioni su come è stato
dimensionato un indice in creazione. La
USER_SEGMENTS, per lo stesso indice, ci fornisce lo stato attuale del dimensionamento dell’indice. Vediamo un esempio:
select TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS
from user_extents
where segment_name = ‘I_COMUNI’;
TABLESPACE_NAME EXTENT_ID
INDEXES
INDEXES
INDEXES
INDEXES
INDEXES
INDEXES
INDEXES
INDEXES
INDEXES
INDEXES
0
1
2
3
4
5
6
7
8
9
BYTES
BLOCKS
10240
10240
20480
30720
40960
61440
92160
133120
194560
286720
5
5
10
15
20
30
45
65
95
140
Evidentemente in partenza l’indice era definito
con un next di 10240, che poi è aumentato a causa
della pct_increase (il numero dei block allocati è
sempre approssimato al multiplo di 5 più vicino).
Il prossimo extent sarà di 430080 byte, come
si vede sia sulla USER_SEGMENTS sia sulla
USER_INDEXES. Solo per gli utenti DBA sono
disponibili le viste DBA_DATA_FILES, DBA_
FREE_SPACE_COALESCED e DBA_ROLLBACK_
SEGS. La prima dà informazioni sui file che compongono il database, la seconda fornisce il resoconto della operazione di COALESCE di cui si è
detto sopra, l’ultima dà informazioni sui rollback
segment del DB.
Utenti e privilegi
select segment_name, tablespace_name,
bytes, blocks, extents,
initial_extent initial,
next_extent next, pct_increase
from user_segments
where segment_name = ‘I_COMUNI’;
SEGMENT_NAME TABLESPACE_NAME BYTES
I_COMUNI
INDEXES
INITIAL NEXT
10240
BLOCKS EXTENTS
880640 430
10
PCT_INCREASE
430080
50
select index_name, tablespace_name, initial_extent,
next_extent, pct_increase
from user_indexes
where index_name = ‘I_COMUNI’;
INDEX_NAME TABLESPACE_NAME INITIAL_EXTENT
I_COMUNI
INDEXES
10240
NEXT_EXTENT PCT_INCREASE
430080
50
Ed ecco la storia degli extent allocati per l’indice:
Nel Dizionario è possibile ottenere molte informazioni anche sugli utenti e sui privilegi che gli sono
concessi. La vista USER_USERS fornisce alcune
informazioni sull’utente, come il tablespace di default
e temporaneo. Mediante la vista USER_DB_LINKS
è possibile conoscere i collegamenti esistenti con
altri DB, grazie ai quali è possibile utilizzare oggetti
remoti come se risiedessero sul nostro ambiente.
Per consultare i privilegi concessi agli utenti
sulle tabelle del DB sono disponibili le viste
USER_TAB_PRIVS, USER_TAB_PRIVS_MADE
e USER_TAB_PRIVS_RECD. La prima mostra,
per ogni grant concessa, il nome della tabella, dell’owner, dell’utente che ha concesso la grant e di
quello che l’ha ricevuta. Sulla seconda vista si
vedono solo le grant su oggetti di proprietà dell’utente connesso; sulla terza solo le grant concesse
all’utente connesso.
Stessa logica seguono, per le colonne, le viste
USER_COL_PRIVS, USER_COL_PRIVS_MADE
e USER_COL_PRIVS_RECD.
Le viste USER_ROLE_PRIVS e USER_SYS_
PRIVS contengono informazioni, rispettivamente,
sui role e sui privilegi di sistema concessi all’utente
connesso. Se, ad esempio, si esegue la seguente
istruzione SQL:
79
CP 108
PROGRAMMING
Database
select * from user_role_privs;
USERNAME
GRANTED_ROLE
ADM DEF OS_
NOME_UTENTE
NOME_UTENTE
NOME_UTENTE
CONNECT
DBA
RESOURCE
NO
NO
NO
YES NO
YES NO
YES NO
select o.object_name name, o.object_type type,
s.sid, s.machine, s.terminal
from user_objects o, v$session s,
v$locked_object l
where s.sid = l.session_id
and l.object_id = o.object_id
NAME
TYPE
CLIENTI TABLE
Si vede che all’utente è stato attribuito il role
di DBA, oltre a quelli banali CONNECT e
RESOURCE. Per verificare se l’utente ha dei limiti
nell’utilizzo di alcune risorse si può interrogare la
vista USER_RESOURCE_LIMITS.
FIGURA 1 L’Object browser di PL/SQL Developer, uno dei più diffusi tool
per la consultazione del dizionario dati
SID MACHINE TERMINAL
17 XXX528
Windows NT
Il client XXX528 (nome di rete Windows), con
sistema operativo Windows NT, tiene ferma la
tabella CLIENTI con la sessione 17. È anche possibile conoscere l’ultimo comando SQL eseguito
da ogni sessione mediante l’accesso alla vista
V$SQLTEXT_WITH_NEWLINES dove il campo
ADDRESS è valorizzato come il campo SQL_
ADDRESS della V$SESSION per la sessione desiderata. Il capitolo 3 dell’articolo [1] in bibliografia è
una guida molto dettagliata all’utilizzo delle V$.
Conclusioni
Altre due viste sui privilegi sono disponibili solo ai
DBA. Sono la DBA_PROFILES e la DBA_ROLES.
Come si intuisce facilmente queste viste forniscono
informazioni sui profili e sui role che il DBA può
attribuire agli utenti.
C’è altro ancora...
80
CP 108
Nel dizionario sono presenti molte altre viste, ad
esempio per il controllo delle attività degli utenti
(AUDITING). In generale è piuttosto semplice trovare l’informazione che serve mediante il dizionario, aiutandosi con la vista DICT e con gli articoli
citati in bibliografia. Concludiamo con un veloce
accenno alle dynamic performance views, meglio
note come V$. Il nome di queste viste è dovuto
al loro continuo aggiornamento durante l’attività
del database ed al fatto che contengono soprattutto
informazioni sulle performance del sistema. Qualche esempio. Per sapere quali sono, al momento, gli
oggetti sottoposti a lock e le sessioni che li stanno
utilizzando si può fare come segue:
Lo scopo di questo articolo è mettere in evidenza
le potenzialità dell’Oracle data dictionary e mostrare
qualche esempio di utilizzo, in modo che il lettore
sappia come trovare autonomamente le informazioni
di cui ha bisogno. Gli sviluppatori tendono spesso
a sottovalutare il dizionario, anche per la convinzione che esso possa essere utile solo ai DBA nella
loro attività di amministrazione. Dopo ciò che si è
visto, dovrebbe risultare invece evidente che la conoscenza, al giusto livello di approfondimento, del data
dictionary permette sovente di migliorare sia la qualità
sia i tempi di produzione delle applicazioni sviluppate
su database Oracle. Esistono molti tool che consentono di visualizzare, anche in un’accattivante veste
grafica, le informazioni contenute nel dizionario. Tra
questi sono molto diffusi Toad (una versione demo è
presente nel CD allegato) e PL/SQL Developer, in
Figura 1 è possibile vedere l’object browser di PL/SQL
Developer. È sicuramente utile conoscere questi tool,
ma è comunque importante sapere cosa c’è dietro, per
meglio comprenderne il funzionamento.
BIBLIOGRAFIA
[1] Oracle, “Oracle8i Reference Release 8.1.5
A67790-01”, Capitoli 2 e 3, Oracle Corp.
[2] Oracle, “Oracle8i Concepts Release 8.1.5
A67781-01”, Capitolo 2, Oracle Corp.
[3] Oracle, “Generic server readme for Oracle 9i”,
Oracle Corp.
RIFERIMENTI
[4] http://technet.oracle.com