Anteprima del Manuale

Oracle 11g
-
Guida Completa
Distribuito tramite il sito www.manualioracle.it
Copyright © 2012 Assi Loris
Qualsiasi abuso sarà perseguito e punito secondo i termini di legge.
Version: 5.0
Alcuni termini usati sono trademarks registrati dei rispettivi proprietari.
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Sommario
Introduzione al Manuale ........................................................................................................................................ 5
Cap 1 Overview dell’ Architettura Oracle ................................................................................................... 6
1.1 – Introduzione ad Oracle ..................................................................................................................................6
1.2 – SQL*Plus .................................................................................................... Error! Bookmark not defined.
1.3 – Introduzione all’SQL e al PL/SQL .............................................................. Error! Bookmark not defined.
1.4 – Cosa è una Istanza e Cosa è un Database .................................................... Error! Bookmark not defined.
1.5 – Strutture di memoria Oracle ........................................................................ Error! Bookmark not defined.
1.6 – I Principali Background Process di Oracle .................................................. Error! Bookmark not defined.
1.7 – Sessioni Oracle ............................................................................................ Error! Bookmark not defined.
Cap 2 Installare Oracle e Creare un Database ............................................................................................ 7
2.1 – Installare l’Oracle Software tramite OUI ......................................................................................................7
2.2 – Creare un Database...................................................................................... Error! Bookmark not defined.
2.3 – Configurare il Network Environment .......................................................... Error! Bookmark not defined.
Cap 3 Gestire Oracle ...................................................................................................................................... 8
3.1 – Database Administrator Authentication ........................................................................................................8
3.2 – Administrator Authentication Methods ....................................................... Error! Bookmark not defined.
3.3 – Ulteriore “superuser” Oracle ....................................................................... Error! Bookmark not defined.
3.4 – Start Up dell’Istanza Oracle ........................................................................ Error! Bookmark not defined.
3.5 – Shut Down dell’Istanza Oracle.................................................................... Error! Bookmark not defined.
3.6 – Data Dictionary ........................................................................................... Error! Bookmark not defined.
Cap 4 Strutture Fisiche del DB ..................................................................................................................... 9
4.1 – I Control Files ...............................................................................................................................................9
4.2 – I Redo Log Files .......................................................................................... Error! Bookmark not defined.
4.3 – Multiplexing dei Redo Log Files ................................................................. Error! Bookmark not defined.
4.4 – Archive Log Files e Archiving .................................................................... Error! Bookmark not defined.
4.5 – Initialization Parameter Files: PFILE e SPFILE ......................................... Error! Bookmark not defined.
4.6 – L’Automatic Diagnostic Repository (ADR) ............................................... Error! Bookmark not defined.
4.7 – Alert e Trace Files ....................................................................................... Error! Bookmark not defined.
Cap 5 Tablespace e Data File ........................................................................... Error! Bookmark not defined.
5.1 – Le Tablespace.............................................................................................. Error! Bookmark not defined.
5.2 – Viste di sistema relative alle tablespace ...................................................... Error! Bookmark not defined.
5.3 – Droppare Tablespaces e Files ...................................................................... Error! Bookmark not defined.
5.4 – Le default Tablespace Permanent e Temporary .......................................... Error! Bookmark not defined.
5.5 – SYSAUX Tablespace e Bigfile Tablespace (BFT) ..................................... Error! Bookmark not defined.
Cap 6 Segments e Storage Structures .............................................................. Error! Bookmark not defined.
6.1 – Data Blocks ................................................................................................. Error! Bookmark not defined.
6.2 – Multipli block sizes dentro lo stesso db ...................................................... Error! Bookmark not defined.
6.3 – Extents e Segmenti ...................................................................................... Error! Bookmark not defined.
6.4 – L’ Automatic Segment-Space Management (ASSM) ................................. Error! Bookmark not defined.
6.5 – Gli Oracle-Managed Files (OMF) ............................................................... Error! Bookmark not defined.
Cap 7 Gestione di Tabelle e Indici ................................................................... Error! Bookmark not defined.
7.1 – Gestione delle Tabelle ................................................................................. Error! Bookmark not defined.
7.2 – Overview sul Partizionamento .................................................................... Error! Bookmark not defined.
7.3 – Creazione e Gestione degli Indici ............................................................... Error! Bookmark not defined.
Cap 8 Altri Oggetti Oracle................................................................................ Error! Bookmark not defined.
8.1 – I Trigger ...................................................................................................... Error! Bookmark not defined.
8.2 – Cosa sono i Constraint................................................................................. Error! Bookmark not defined.
8.3 – Creare, modificare e droppare i Constraint ................................................. Error! Bookmark not defined.
8.4 – External Tables............................................................................................ Error! Bookmark not defined.
Cap 9 Gestire gli Utenti e la Sicurezza ............................................................ Error! Bookmark not defined.
9.1 – Gli Utenti..................................................................................................... Error! Bookmark not defined.
9.2 – I Profili ........................................................................................................ Error! Bookmark not defined.
9.3 – Object e System Privileges .......................................................................... Error! Bookmark not defined.
9.4 – Come revocare i Privilegi e Quali viste interrogare .................................. Error! Bookmark not defined.
9.5 – Gestire i Ruoli ............................................................................................. Error! Bookmark not defined.
9.6 – Auditing del DB .......................................................................................... Error! Bookmark not defined.
Cap 10 - Data Utilities ........................................................................................... Error! Bookmark not defined.
10.1 – Export e Import Data Pump....................................................................... Error! Bookmark not defined.
2
www.manualioracle.it
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
10.2 – Esempi di Expdp e Impdp ......................................................................... Error! Bookmark not defined.
10.3 – Comparazione Parametri exp/imp e expdp/impdp .................................... Error! Bookmark not defined.
Cap 11 - Automating Management ...................................................................... Error! Bookmark not defined.
11.1 – Cenni su AWR, ASH e ADDM ................................................................. Error! Bookmark not defined.
11.2 – Diagnosing Performance Statistics: Server-generated alerts ..................... Error! Bookmark not defined.
11.3 – ASMM ...................................................................................................... Error! Bookmark not defined.
11.4 – Automating Database Management .......................................................... Error! Bookmark not defined.
11.5 – Setup dell’Automatic Memory Management (AMM) ............................... Error! Bookmark not defined.
Cap 12 - Automating Tasks : Jobs and Scheduler .............................................. Error! Bookmark not defined.
12.1 – Introduzione all’Oracle Scheduler ............................................................. Error! Bookmark not defined.
12.2 – Creazione degli Scheduler Objects............................................................ Error! Bookmark not defined.
12.3 – Gestione dello Scheduler ........................................................................... Error! Bookmark not defined.
12.4 – Attributi e Job Class dello Scheduler ........................................................ Error! Bookmark not defined.
12.5 – Windows e Window Groups ..................................................................... Error! Bookmark not defined.
Cap 13 - Space Management................................................................................. Error! Bookmark not defined.
13.1 – Gestione degli Space e DBMS_SERVER_ALERT .................................. Error! Bookmark not defined.
13.2 – Segment Shrink ......................................................................................... Error! Bookmark not defined.
13.3 – The Segment Advisor ................................................................................ Error! Bookmark not defined.
Cap 14 - Introduzione al Backup e Recovery ...................................................... Error! Bookmark not defined.
14.1 – Introduzione .............................................................................................. Error! Bookmark not defined.
14.2 – Strutture e Processi necessari per il RECOVERY ..................................... Error! Bookmark not defined.
14.3 – Tipi di Failures .......................................................................................... Error! Bookmark not defined.
14.4 – Backup e Recovery Configuration ............................................................ Error! Bookmark not defined.
14.5 – Fast Recovery Area (Flash Recovery Area) .............................................. Error! Bookmark not defined.
Cap 15 - Introduzione ai PL/SQL Programs ................................................................................................. 10
15.1 – Le tabelle EMP e DEPT ............................................................................................................................10
15.2 – Il PL/SQL Program Construct e i suoi Componenti .................................. Error! Bookmark not defined.
15.3 – Variabili PL/SQL e non PL/SQL (bind variables) .................................... Error! Bookmark not defined.
15.4 – I Subprograms ........................................................................................... Error! Bookmark not defined.
15.5 – Come invocare una Procedura o Funzione ................................................ Error! Bookmark not defined.
Cap 16 - Creare le Procedure ............................................................................... Error! Bookmark not defined.
16.1 – Cosa è una stored procedure ..................................................................... Error! Bookmark not defined.
16.2 – Creare e droppare una procedura ............................................................... Error! Bookmark not defined.
16.3 – Formal and Actual Parameters .................................................................. Error! Bookmark not defined.
16.4 – Procedure con Parametri e i tipi di parameter modes ................................ Error! Bookmark not defined.
16.5 – Invocare una procedura con parametri ...................................................... Error! Bookmark not defined.
16.6 – Il subprogram nella declarative section di una Procedura ......................... Error! Bookmark not defined.
16.7 – Come vengono propagate le Exception ..................................................... Error! Bookmark not defined.
Cap 17 - Creare le Funzioni .................................................................................. Error! Bookmark not defined.
17.1 – Cosa è una stored function ........................................................................ Error! Bookmark not defined.
17.2 – Creare, Invocare e rimuovere una Funzione .............................................. Error! Bookmark not defined.
17.3 – Vantaggi e restrizioni delle chiamate dagli SQL statement ....................... Error! Bookmark not defined.
17.4 – Dove chiamare le Funzioni negli SQL statement ...................................... Error! Bookmark not defined.
17.5 – Differenze fra procedure e funzioni .......................................................... Error! Bookmark not defined.
Cap 18 - Gestire i Subprograms ........................................................................... Error! Bookmark not defined.
18.1 – System Privileges, Object Privileges e Grant Privileges ........................... Error! Bookmark not defined.
18.2 – Differenze fra Invokers Rights e Definers Rights ..................................... Error! Bookmark not defined.
18.3 – Le viste di dizionario per gestire gli stored objects ................................... Error! Bookmark not defined.
Cap 19 - I Package ................................................................................................. Error! Bookmark not defined.
19.1 – Creare e Droppare Packages...................................................................... Error! Bookmark not defined.
19.2 – Identificare un Package specification e un Package body ......................... Error! Bookmark not defined.
19.3 – Usare la DESCRIBE per descrivere i package e i suoi componenti .......... Error! Bookmark not defined.
19.4 – Disegnare un Package Construct come Public o Private ........................... Error! Bookmark not defined.
19.5 – Invocare un package construct .................................................................. Error! Bookmark not defined.
19.6 – Usare un bodiless package ........................................................................ Error! Bookmark not defined.
19.7 – I benefit nell’uso dei Package ................................................................... Error! Bookmark not defined.
19.8 – Package che usano l’overloading .............................................................. Error! Bookmark not defined.
19.9 – Usare la Forward Referencing ................................................................... Error! Bookmark not defined.
19.10 – Inizializzare variabili con una one-time-only procedure ......................... Error! Bookmark not defined.
19.11 – Usare le PL/SQL tables e i records nei Package ..................................... Error! Bookmark not defined.
www.manualioracle.it
3
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Cap 20 - Creare i db Trigger ................................................................................ Error! Bookmark not defined.
20.1 – Definizione di trigger e Tipi di trigger ...................................................... Error! Bookmark not defined.
20.2 – Progettare i Trigger e per cosa implementarli ........................................... Error! Bookmark not defined.
20.3 – Creare un DML trigger .............................................................................. Error! Bookmark not defined.
20.4 – I componenti del DML trigger .................................................................. Error! Bookmark not defined.
20.5 – I trigger row level, statement level ............................................................ Error! Bookmark not defined.
20.6 – I conditional predicated in un trigger ........................................................ Error! Bookmark not defined.
20.7 – Usare i qualifiers OLD e NEW in un DML Trigger .................................. Error! Bookmark not defined.
20.8 – Creare un INSTEAD OF trigger ............................................................... Error! Bookmark not defined.
20.9 – Differenze fra stored procedures e trigger ................................................. Error! Bookmark not defined.
20.10 – Cambiare un Trigger Status o Rimuovere un Trigger ............................. Error! Bookmark not defined.
20.11 – Eventi che fanno scattare il DB trigger ................................................... Error! Bookmark not defined.
20.12 – Creare un trigger per un DDL statement ................................................. Error! Bookmark not defined.
20.13 – Creare un trigger per un system event ..................................................... Error! Bookmark not defined.
20.14 – Il CALL statement dentro un trigger ....................................................... Error! Bookmark not defined.
20.15 – La mutating table ..................................................................................... Error! Bookmark not defined.
20.16 – Privilegi e Dictionary views associate ai trigger ..................................... Error! Bookmark not defined.
Cap 21 - Creare le Procedure ............................................................................... Error! Bookmark not defined.
21.1 – Native Dynamic SQL: l’Execute Immediate ............................................. Error! Bookmark not defined.
21.2 – Oracle supplied Packages .......................................................................... Error! Bookmark not defined.
22.3 – I SecureFile LOBs ..................................................................................... Error! Bookmark not defined.
22.4 – Usare APIs SQL e PL/SQL per accedere a SecureFile LOBs ................... Error! Bookmark not defined.
22.5 – Overview dell’Oracle Data Guard 11gr2 .................................................. Error! Bookmark not defined.
22.6 – Oracle Releases e Oracle Patches .............................................................. Error! Bookmark not defined.
4
www.manualioracle.it
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Introduzione al Manuale
Contenuto
In relazione al database Oracle 11.2, il presente manuale tratta:
- l’amministrazione e l’architettura del database
- la programmazione PL/SQL (dal Cap. 15)
Tanti argomenti qui descritti non sono trattati in maniera esaustiva ma sono un ottima base da cui partire.
Per ulteriori approfondimenti possono essere utili altri manuali ad hoc (relativi ad argomenti vasti come il Tuning, il
backup & recovery, le installazioni, ecc…).
Audience
Il presente manuale è rivolto a chiunque voglia conoscere l’architettura del database Oracle 11g o le basi del
linguaggio PL/SQL.
Particolarità
Il presente manuale è in italiano ma molti termini tecnici in esso contenuti sono in lingua inglese.
Abbiamo fatto tale scelta perché spesso a lavoro tali termini vengono detti in inglese e perché riteniamo più utile che
i lettori del manuale conoscano tali termini nella lingua usata nella documentazione ufficiale Oracle.
Molti argomenti sono schematizzati al fine di fornire una più semplice comprensione e memorizzazione.
Alcune abbreviazioni usate:
EM : Enterprise Manager
HWM : High Water Mark
OS o O.S. : Sistema Operativo
cashare : mettere in cache
init parameter : initialization parameter (contenuto nell’init file o nell’spfile)
recoverato : recovered
rollbackare : fare il rollback
startare : fare lo startup
tunare : fare il tuning
Principali Versioni
-
13.08.2012 :
14.08.2012 :
10.12.2013 :
25.01.2015 :
15.11.2016 :
08.03.2017 :
version 1.0
version 1.1 varie modifiche e rilascio al pubblico
version 2.0 varie modifiche
version 3.1 varie modifiche
version 4.0 varie modifiche
version 5.0 varie modifiche tra cui l’aggiunta dei capitoli relativi al PL/SQL
Disclaimer
Non si fornisce alcuna garanzia relativamente al fatto che il presente documento sia privo di errori. Non si assume
nessuna responsabilità sugli eventuali errori o danni derivanti dall’uso delle informazioni qui contenute.
www.manualioracle.it
5
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Cap 1 - Overview dell’ Architettura Oracle
1.1 – Introduzione ad Oracle
Ecco alcune definizioni introduttive :
DBMS (Database Management System)
Software progettato per creare e manipolare in modo efficiente un database (ovvero un insieme di dati strutturati)
solitamente da parte di più utenti.
Fra i DBMS più diffusi c’è l’Oracle database, prodotto dalla società americana Oracle Corporation.
RDBMS (Relational Database Management System)
Come si evince dal suo nome in inglese, è un sistema di gestione del database basato sul modello relazionale.
Il modello relazionale è un modello logico di rappresentazione dei dati.
Secondo tale modello, qualsiasi dato può essere rappresentato come relazione. E tale relazione può essere
rappresentata graficamente tramite una tabella.
Oracle Server
E’ costituito da 2 principali componenti:
-
Il database:
E’ una raccolta di dati correlati. Ha una struttura logica (tabelle, indici, ecc...) e una fisica (i file in cui sono
immagazzinati i dati)
-
L’istanza:
E’ costituita da una struttura di memoria e dai processi in background che gestiscono il db
……….
……….
……….
6
www.manualioracle.it
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Cap 2 - Installare Oracle e Creare un Database
Ecco i metodi più usati per installare il software Oracle 11g e per creare un database Oracle :
-
-
Installazione Software: la puoi fare in questi modi:
-
tramite il tool Oracle Universal Installer (OUI) in modalità interattiva (metodo più usato)
-
tramite il tool Oracle Universal Installer (OUI) in “silent mode”
Creazione del database:
-
ancora tramite il tool OUI
-
tramite il tool Database Configuration Assistant (DBCA) in modalità interattiva (metodo che
suggeriamo)
-
tramite il tool Database Configuration Assistant (DBCA) in “silent mode”
-
lanciando manualmente degli script ad hoc
2.1 – Installare l’Oracle Software tramite OUI
Usa Oracle Universal Installer (OUI) per installare il software Oracle 11g.
OUI è un tool che ha lo stesso look e la stessa funzionalità indipendentemente dalla piattaforma su cui viene
installato.
A seconda del sistema operativo lanci tale tool tramite 2 diversi file:
……….
……….
……….
www.manualioracle.it
7
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Cap 3 - Gestire Oracle
3.1 – Database Administrator Authentication
Come DBA, spesso può capitare di fare operazioni amministrative come, ad es., shutdown o startup del db.
Visto che solo il DBA può fare queste operazioni, dovrà usare uno utente “sicuro”.
I Privilegi Amministrativi
I privilegi che servono ad un amministratore per fare le principali operazioni sul db sono dati tramite 2 speciali
privilegi di sistema:
System Privilege
Operazioni Possibili
SYSDBA
-
STARTUP e SHUTDOWN
ALTER DATABASE: open, mount, backup o cambio del character set
CREATE DATABASE
CREATE SPFILE
ARCHIVELOG e RECOVERY
Include il privilegio di RESTRICTED SESSION
Praticamente con questo privilegio sei connesso come SYS
……….
……….
……….
8
www.manualioracle.it
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Cap 4 - Strutture Fisiche del DB
4.1 – I Control Files
Il control file è un file binario che viene creato automaticamente alla creazione del DB.
Viene cambiato automaticamente quando si aggiunge, cancella o rinomina un file del db. Su di esso NON
bisognerebbe fare alcun cambiamento a mano.
Quando si fa lo start up del DB, Oracle usa il control file per identificare (fra gli altri) i data file e i redo log file e li
apre.
Un control file può appartenere ad un solo DB e il suo contenuto include:
- DB name
- la timestamp della creazione del DB
- nomi, locations e status (online o offline) dei data files
- nomi e locations dei redo log files
- nomi delle tablespace
……….
……….
……….
www.manualioracle.it
9
Oracle 11g – Guida Completa: Architettura database e Programmazione PL/SQL
Cap 15 - Introduzione ai PL/SQL Programs
15.1 – Le tabelle EMP e DEPT
Negli esempi mostrati in questo manuale o che si trovano sul web riguardo i db, spesso si fa riferimento a queste 2
tabelle:
-
EMP : elenco degli impiegati. A volte viene anche chiamata EMPLOYEES.
-
DEPT: elenco dei dipartimenti. A volte viene anche chiamata DEPARTMENTS.
Tali tabelle potrebbero essere già presenti nel tuo db nel sample schema chiamato HR.
Esse sono legate da una foreign key per cui tutti gli impiegati elencati nella tabella EMP devono avere un
dipartimento elencato in DEPT:
create table dept (
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
);
create table emp (
empno number(4,0),
ename varchar2(10),
job
varchar2(9),
mgr
number(4,0),
hiredate date,
sal
number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno)
references dept (deptno)
);
A volte, tali tabelle hanno qualche campo in più o in meno rispetto a quanto descritto qui o hanno nomi leggermente
diversi: ad esempio, il loro campo DEPTNO può anche essere chiamato DEPTID.
Questo è un possibile modo per popolarle :
……….
……….
……….
10
www.manualioracle.it