DBMS ORACLE 9i

DBMS ORACLE 9i
I componenti del Database
• Server Oracle :
– Strutture di memoria
– Processi
– File fisici
Directory principali:
– c:\oracle\admin\<nome database>\pfile
– c:\oracle\ora92\database
– c:\oracle\oradata\<nome database>
Strutture di memoria
•
SGA
–
–
–
–
–
•
Redo Log Buffer Cache
Database Buffer Cache
Shared Pool
Large Pool
Java Pool
PGA
•
SGA
Caratteristiche:
– Dimensionamento componenti dinamico in
init.ora
– SGA_MAX_SIZE: estensione massima
Componenti:
–
–
–
–
DB BC: dati elaborati dal sistema
RL BC: modifiche sul database
Large Pool: dati per backup o restore
Java Pool: dati per esecuzione codice java
– Shared Pool Area:
•
•
Data Dictionary: informazioni su utenti, tabelle,
file, permessi; viene usato per i vari controlli.
Library cache:
– Shared Sql Area: query lanciate sul DB e codice
risultante;
– Pl/SQL: codice procedure e funzioni PL/SQL;
– Locks: informazioni sui lock per impedire conflitti.
•
PGA:
Caratteristiche:
– informazioni sui singoli processi (variabili,
ecc.);
– non è condivisa.
Processi
1. DBWn (scrittore database)
Attivazione:
–
–
2.
Esaurimento buffers liberi
Checkpoint
LGWR (scrittore file log)
Attivazione:
–
–
–
–
Commit
1/3 Redo log buffer cache piena
DBWn in scrittura
Timeout ogni 3 secondi
3. Checkpoint
– trasferimento dati sui dischi
– update intestazioni CF e DF
Nota: frequenti checkpoint minor tempo recovery
4. SMON (system monitor)
– recovery istanza dopo crash in base agli RLF
– pulizia segmenti temporanei
5. PMON (process monitor)
– pulizia risorse processi per anomalie
– rilascio locks
6. ARCn (archiver)
– copia RLF prima di riscrittura
– modalità ARCHIVELOG
Processo utente:
Programma applicativo (SQL*Plus, …) che
gira sul server o sul client.
Processo server:
prende le richieste dal client, esegue i comandi
e restituisce i dati all’utente.
File fisici
• CF (CONTROL FILES): informazioni su
DB (nome, dimensione e posizione dei file,
…)
• RLF (REDO LOG FILES): comandi
modifica DB, quindi ~backup DB
• DF (DATA FILES): dati DB
Control File:
–
–
–
–
–
–
nome db
DF: nome, path, stato (online/offline)
RLF: nome, path
informazioni stato archive
nome tablespaces
informazioni Checkpoint più recente
Nota: Unico Control File Mirror
Aggiornamento:
– Processo server per aggiunta Files
– CheckPoint
– ARCn
Redo Log Files:
– comandi di modifica database
Aggiornamento:
– LGWn
Nota: Garanzia coerenza dati in seguito a crash,
possibilità di ricreazione del database
Crash & Recovery:
Situazioni possibili in caso di crash:
•
•
•
•
transazioni in corso, già applicate ai dati.
transazioni in corso, NON ancora eseguite.
transazioni completate, già applicate ai dati.
transazioni completate, NON ancora eseguite.
Recovery:
riportare il DataBase nell’ultimo stato coerente possibile • ripristino transazioni committate al momento del crash
• annullamento transazioni NON committate al momento del
crash
Archivelog:
Modalità archiviazione degli RLF
Attivazione (in qualunque momento) attivazione ARCn
Noarchivelog (default):
Sovrascrittura circolare degli RLF
Data Files:
– dati del DB
Struttura:
segment
extent
blocco
Tablespace:
contenitore logico costituito da uno o più DF memorizzati
anche su dischi diversi
quota tablespace1
quota tablespace2
Utente
Schema
quota tablespaceN
Tablespace
Vantaggi:
• riduzione contese di accesso a disco (DF su dischi diversi).
• gestione quote per singolo utente
• gestione efficiente applicazioni che usano lo stesso database
• gestione operazioni di Back Up e Recovery per singolo tablespace
Tablespace
Creazione:
– allocazione di un unico DF
– modalità:
• dictionary-managed: informazioni spaziali memorizzate nel DD
modifiche accesso al DD
• locally-managed (default): informazioni spaziali memorizzate
nell’header di ogni DF mediante matrice di bit (ogni bit corrisponde a un
blocco del DF e indica se è libero o meno)
nessun aggiornamento sul DD prestazioni superiori
Data Dictionary:
insieme di tabelle e viste contenenti informazioni
su utenti, tabelle, privilegi, …
Viste di Sistema:
query memorizzate nel DB, utili per ricavare
informazioni sul DB poiché le tabelle interne
(accessibili dagli amministratori) memorizzano i
dati in formati di difficile interpretazione.
Viste principali:
• USER_
Contengono informazioni sugli oggetti di proprietà dell’utente che
lancia la query sulla vista.
SQL> select * from user_tables;
• ALL_
Contengono informazioni sugli oggetti sui quali l’utente che lancia la
query ha accesso (o privilegi).
SQL> select * from all_tables;
• DBA_
Contengono informazioni sugli oggetti di tutti gli utenti. Solo gli utenti
privilegiati possono eseguire delle query sulle viste DBA_.
Inoltre alcune di esse contengono informazioni sulle strutture del
DB, come l’elenco dei DF.
Esempio:
- elenco di tutte le viste del DD:
SQL> select * from DICTIONARY;
- informazioni sul contenuto delle colonne:
SQL> select * from DICT_COLUMNS;
Creazione del DB:
– DBCA
– comandi SQL
Directory principali:
C:\oracle\admin\ORCL\bdump
C:\oracle\admin\ORCL\cdump
C:\oracle\admin\ORCL\create
C:\oracle\admin\ORCL\pfile
C:\oracle\admin\ORCL\udump
C:\oracle\database
C:\oracle\oradata\ORCL
Creazione DB
Variabili d’ambiente:
•
ORACLE_BASE indica la directory base dell’installazione. Es: c:\oracle
•
ORACLE_HOME indica la directory contenente il software, ed è relativa alla
oracle_base. Es: ORACLE_HOME=%ORACLE_BASE%\ora92 risolto in
ORACLE_HOME=c:\oracle\ora92
•
ORACLE_SID indica il nome dell’istanza.
•
DBNAME indica il nome del database
Creazione DB
Utenti di default:
• SYS (default password: CHANGE_ON_INSTALL)
•
– proprietario di tutte le tabelle di sistema, compreso DD
SYSTEM (default password: MANAGER)
– amministratore meno “potente” di SYS
Ruoli:
• SYSOPER
–
–
–
–
–
–
STARTUP e SHUTDOWN
ALTER DATABASE [ OPEN / MOUNT ]
ALTER DATABASE ARCHIVELOG
RECOVER DATABASE
CREATE SPFILE
ecc..
• SYSDBA
– tutti i privilegi inclusi in SYSOPER
– CREATE DATABASE
– ecc…
Autenticazione:
-
tramite SO (credenziali di accesso al SO)
tramite username / password
Stati DB:
1.
2.
3.
4.
CLOSED
NOMOUNT (creazione di un DB)
MOUNT (recovery, passaggio ad ARCHIVELOG, …)
OPEN (accesso consentito a utenti normali)
Avvio:
- STARTUP: da Closed a Open
- STARTUP NOMOUNT: da Closed a NoMount
- STARTUP MOUNT: da Closed a Mount
Passaggio di stato:
SQL> alter database mount;
(passaggio umount mount)
SQL> alter database open;
(passaggio mount open)
Arresto:
- SHUTDOWN (impedimento di nuove connessioni e
attesa disconnessione di tutti gli utenti)
- SHUTDOWN IMMEDIATE (disconnessione
immediata di tutti gli utenti, e chiusura del Database)
- SHUTDOWN TRANSACTIONAL (impedimento
nuove connessioni e attesa commit o rollback)
- SHUTDOWN ABORT (in caso di mal funzionamento
degli altri comandi di shutdown; implica recovery al
prossimo avvio)
DATABASE
TableSpace
System
B
B
B
TableSpace
Users
TableSpace
…….
B
ext table 1
ext table 1
ext table N
DataFileN
ext table N
DataFile2
ext table 2
DataFile1
ext table 2
Riepilogo comandi SQL:
-
create table <nomeTabella> (
<colonna1> <tipo di dato> [primary key | unique | not null]
......
<colonnaN> <tipo di dato> [primary key | unique | not null]
[vincoli]
);
vincoli: primary key (<colonna i>,… , <colonna j>);
foreign key (<colonna i>) references <tabella j> (<colonna k>);
tipo di dato:
char(size) stringa di caratteri di dimensione fissa,
varchar2(size) stringa di caratteri di dimensione variabile,
number,
date,
…
Riepilogo comandi SQL:
-
insert into <tabella> [<colonna i,…,colonna j>]
values(<valore i,…,valore j>);
-
update <tabella> set
<colonna i> = <espressione i>,… , <colonna j> = <espressione j>
[ where <condizione> ]
-
delete from <tabella> [where <condizione>]
-
select [distinct] [<alias ak>.]<colonna i>,… ,[<alias al>.]<colonna j>
from <tabella 1>[alias a1>],… , <tabella n>[alias an>]
[where <condizione>]
SQL*PLUS
• Ogni comando deve terminare con ‘;’ oppure
con ‘/’;
• Per uscire: ‘exit’ oppure ‘quit’;
• SQL buffer:
– l: elenca le linee memorizzate nel buffer; la corrente è
identificata da ‘*’;
– l<numero>: imposta la linea corrente alla linea
<numero>;
– c/<vecchia stringa>/<nuova stringa>: rimpiazza la
prima occorrenza della vecchia stringa con la nuova
nel buffer;
– r: esegue il contenuto corrente del buffer.
• Comandi vari:
– desc <nome tabella>: elenca la struttura di
una tabella;
– spool <nome file>: memorizza su un file il
contenuto dello schermo dal momento della
chiamata; percorso del file:C:\oracle\ora92\bin
– spool off;
– @<nome file>: richiama i comandi salvati nel
file <nome file>.sql;
– copy:
Es: copy from scott/tiger create prova using select
* from prova1;
COMANDO DESCRIZIONE
@path (“at”) Esegue lo specifico file di istruzioni indicato nel path
/ (“slash”) Esegue il comando SQL o il blocco PL/SQL precedentemente stilato
ACC[EPT] Legge una riga di input e la memorizza in una variabile
A[PPEND] Aggiunge uno specifico testo alla fine della riga corrente nel buffer
ATTRIBUTE Specifica la caratteristica per un dato attributo della colonna di
Tipo Oggetto e lista la corrente caratteristica per un singolo attributo o per tutti
gli attributi
BRE[AK] Specifica come e dove verranno formattati i cambiamenti in un report
BTI[TLE] Posiziona e formatta uno specifico titolo in fondo ad ogni pagina di
report o lista la corrente definizione di BTITLE.
C[HANGE] Cambia il testo nella corrente linea del buffer
CL[EAR] Resetta o cancella il corrente valore
COL[UMN] Visualizza le caratteristiche di una data colonna
COMP[UTE] Calcola e stampa il totale delle linee, usando diversi standard di
calcolo, su un raggruppamento di righe selezionate
CONN[ECT] Connette un dato utente al database
COPY Copia i dati da una query in una tabella di database locale o remoto
DE[FINE] Definisce una variabile o lista il valore e il tipo di dato di una variabile
DEL Cancella una o più righe nel buffer
DESC[RIBE] Lista le specifiche di una tavola, vista o sinonimo o le specifiche di
una procedura o funzione
•
•
•
•
•
•
•
Il comando column <nome colonna> <opzione 1> <opzione 2> … viene
usato per formattare le colonne del risultato di una query. Le opzioni più
frequentemente usate sono:
format A<n>
Per dati alfanumerici, questa opzione imposta la lunghezza del <nome
colonna> a <n>. Per colonne che hanno tipi di dati number, il comando
format può essere utilizzato per specificare il formato prima e dopo il punto
decimale. Per esempio, format 99,999.99 specifica che se il valore ha più
di tre cifre prima del punto decimale,le cifre verranno separate da una
virgola, e solo due cifre vengono visualizzate dopo il punto decimale.
heading <testo> rinomina <nome colonna> e gli dà una nuova
intestazione.
null <testo> viene usato per specificare l’output dei valori null (tipicamente,
i valori null non sono visualizzati).
column <nome colonna> clear cancella la definizione del formato per
<nome colonna>.
Il comando set linesize <numero> può essere usato per impostare la
massima lunghezza di una singola linea che può essere visualizzata a
schermo.
Set pagesize <numero> imposta il numero totale di linee che SQL*Plus
visualizza prima di visualizzare nuovamente i nomi di colonna e le
intestazioni, rispettivamente, delle righe selezionate.