Oracle

Page last edited 600 giorni 13 ore fa
Da wiki.betaingegneria.it.

Oracle[modifica]

top database

Comandi utili[modifica]

Sequenze[modifica]

  create sequence SCHEMA.seq_nomesequenza
  start with 86
  maxvalue 999999
  minvalue 1
  nocycle
  cache 20
  noorder;

Problema: la sequenza qui sopra arriva al massimo (1 milione), viene generato un errore:[modifica]

  ORA-08004: the sequence SEQ_IDSMS.NEXTVAL exceeds MAXVALUE and cannot be instantiated
Soluzione:[modifica]
  alter sequence SCHEMA.seq_nomesequenza maxvalue 9999999999;

porto così il limite a 10 miliardi (la capacità massima di un campo number(10))

  select SCHEMA.seq_nomesequenza.nextval  from dual;
  
     NEXTVAL
  ----------
     1000000

Aggiunta di constraint[modifica]

  alter table ricevute.capitoli add constraint pk_capitoli 
  primary key (id);

Trigger[modifica]

  create or replace trigger schema.idchiave_bef_ins before insert on tabella for each row
  begin
     select seq_nomesequenza.nextval into :new.id from dual;
  end;

Elenco delle stored procedures[modifica]

  select * from all_procedures
  where owner='NOME_SCHEMA'
  AND OBJECT_TYPE='PROCEDURE'

Informazioni sul tablespace di uno schema[modifica]

Con questa query vedi i tablespace su cui puoi scrivere con l'utente con cui sei collegato:

  select * from user_ts_quotas;

Charset utilizzzato dal database[modifica]

  select * from v$nls_parameters
  where parameter='NLS_CHARACTERSET'

Connection pooling spiegato da Tim Burleson[modifica]

Errore nella creazione della miniatura: Impossibile salvare la miniatura nella destinazione

Tratto da AskTom.com

Mi sono imbattuto in questo link cercando "cos'è il connection pooling Oracle?" su Google.

Questa la risposta di Tom Burleson

Un pool ("squadra") di connessioni è generalmente la pratica di uno strato software intermedio (application server) di ottenere N connessioni a un database (ad esempio 20 connessioni).

Queste connessioni sono memorizzate in un "pool" nel livello intermedio, un "array", se volete. Ogni connessione è impostata inizialmente su "non in uso".

Quando un utente invia una pagina web al server applicativo (Tomcat, PHP, ...), viene eseguito un pezzo di codice, il codice dice: "ho bisogno di arrivare al database", invece di collegarsi proprio lì e in quel momento (cosa che richiede tempo), si va solo a questo array e si dice "mi dai una connessione per favore?".

Il software di gestione del pool di connessioni contrassegna la connessione come "in uso" e ve la dà.

Quindi voi generate la pagina, la formattate in qualche modo in html - e poi ritornate la connessione al pool dove qualcun altro può riutilizzarla.

In questo modo, con 20 connessioni al database, è possibile evitare il sovraccarico di collegare / scollegare e (ancora più importante forse) si può servire una comunità di centinaia di utenti simultaneamente utilizzando solo 20 connessioni! (al contrario del caso client-server nel quale centinaia di utenti richiederebbero simultaneamente centinaia di connessioni!)

Sul mio sito, io uso mod_plsql; mod_plsql riusa queste connessioni - al massimo, ho tra 10 e 15 connessioni là fuori (il pool di connessioni di suo si spegne dopo un periodo di inattività e assegna nuove connessioni in base alle esigenze).

Questo post è stato scritto nel 2004.

Connection Pooling spiegato da Oracle[modifica]

Il Connection pooling - o array di connessioni - nelle estensioni API di JDBC 2.0 è uno strato software che consente il riutilizzo delle connessioni a database.

Questo strato consente il riuso di connessioni fisiche e la riduzione del sovraccarico per le vostre applicazioni. La funzionalità di connection pooling minimizza le costose operazioni di creazione e chiusura delle sessioni.

Questi sono i concetti centrali:

  • sorgenti dati in connection pooling: come una connessione normale solo che i metodi ritornano istanze di array di connessioni anziché l'isnaza do una sola connessione.
  • array di connessioni: si tratta di un'unica connessione fisica al database che rimane aperta durante l'uso di una serie di istanze di connessioni logiche

Una istanza di connessione logica è una semplice istanza di connessione (come una istanza della classe Connection o della classe OracleConnection) ritornata da una istanza di array di connessioni. Ogni connessione logica agisce come gestore temporaneo di una connessione fisica rappresentata dall'istanza di array di connessioni.

Errore nella creazione della miniatura: Impossibile salvare la miniatura nella destinazione

Uno sguardo un po' più da vicino [1][modifica]

La seguente sequenza descrive cosa succede quando un client JDBC fa richiesta di una connessione ad un oggetto DataSource che implementa il connection Pooling:

  • il client invoca il metodo DataSource.getConnection()
  • il server applicativo che fornisce l'implementazione della classe DataSource controlla nel suo array di connessioni per vedere se c'è un oggetto PooledConnection - una connessione fisica al database - disponibile. Determinare la disponibilità di un dato oggetto PooledConnection può comportare il controllo delle credenziali di autorizzazione, o il tipo di autorizzazione, come anche l'impiego di altri criteri specifici dell'applicazione. Il metodo di lookup e gli altri metodi associati alla gestione del connection pool sono specifici dell'application server.
  • se non esistono oggetti PooledConnection disponibili, il server applicativo invoca il metodo ConnectionPoolDataSource.getPooledConnection per farsi dare una nuova connessione fisica. IL driver JDBC che implementa la classe ConnectionPoolDataSource.getPooledConnection crea un nuovo oggetto PooledConnection e lo ritorna al server applicativo.
  • Indipendentemente dal fatto che l'oggetto PooledConnection sia stato preso dal pool o creato al momento, il server applicativo esegue un po' di contabilità interna per notificare che la connessione fisica è ora in uso.
  • Il server applicativo invoca il metodo PooledConnection.getConnection() per farsi ritornare un oggetto Connection logico. Questo oggetto Connection logico è al fin della fiera un "gestore" di un oggetto PooledConnection fisico, ed è questo gestore che è ritornato dal metodo DataSource.getConnection quando il connection pooling è un funzione.
  • Il server applicativo registra se stesso come un ConnectionEventListener chiamando il metodo PooledConnection.addConnectionEventListener(). Questo viene fatto cosicché il server applicativo verrà notificato quando la connessione fisica è disponibile al riuso.
  • l'oggetto Connection logico è ritornato al client JDBC che usa la stessa stessa API connection come nel caso del semplice DataSource. Notate che la connessione fisica sottostante non può essere riutilizzata finché il client non invoca il metodo Connection.close(). Il connection pooling può anche essere implementato in una architettura two-tier dove non c'è un application server. In questo caso il driver JDBC fornisce entrambe le implementazioni della DataSource che è visibile al client, e della sottostante ConnectionPoolDataSource.

Oracle 11g XE 64 bit[modifica]

Utilissimi questi due howto

e in più occorreva fare questo

  sudo apt-get install libaio1

altrimenti dava questo errore

  Starting Oracle Net Listener.
  Starting Oracle Database 11g Express Edition instance.
  Failed to start Oracle Net Listener using /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr and Oracle Express Database using /u01/app/oracle/product/11.2.0/xe/bin/sqlplus.

Variabili d'ambiente

  ORACLE_HOME_LISTENER=/u01/app/oracle/product/11.2.0/xe
  ORACLE_SID=XE
  ORACLE_BASE=/u01/app/oracle
  ORACLE_TERM=xterm
  ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

Installazione Oracle 11g XE 64 bit su Ubuntu 14.04[modifica]

Mi sono basato su questo ottimo howto. La versione di Oracle 11 64 bit è disponibile solo per distribuzioni tipo RedHat con il file rpm; occorre quindi scaricarlo e convertirlo in .deb

Installazione di alien[modifica]

alien serve per convertire .rpm -> .deb

  sudo apt-get install alien libaio1 unixodbc vim

Download di Oracle 11g XE 64 bit[modifica]

  wget http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html?ssSourceSiteId=ocomen

Qui occorre avere un account su www.oracle.com (è gratuito) e occorre selezionare l'opzione obbligatoria Accept License Agreement.

Trasformazione in .deb[modifica]

Meglio aprire una sessione come root per fare prima:

  $ sudo -s
  #

Innanzitutto ocorre unzippare il pacchetto rpm:

  # unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip 
  Archive:  oracle-xe-11.2.0-1.0.x86_64.rpm.zip
  creating: Disk1/
  creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql  
  creating: Disk1/response/
  inflating: Disk1/response/xe.rsp   
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm
  
  # ll
  totale 1315108
  drwxr-xr-x 3 marcob marcob      4096 gen 12 10:36 ./
  drwxr-xr-x 7 marcob marcob      4096 gen  9 14:50 ../
  drwxrwxr-x 4 marcob marcob      4096 ago 28  2011 Disk1/

Mi posiziono nella direcotry che contiene l'RPM:

  # cd Disk1

A questo punto si lancia lo script di trasformazione (se non si è root si deve premmettere sudo al comando, ricordate)

  # alien --scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm

che impegna parecchio le CPU:

Errore nella creazione della miniatura: Impossibile salvare la miniatura nella destinazione

Dopo circa 10 minuti mi compare il messaggio di conclusione

  oracle-xe_11.2.0-2_amd64.deb generated

Alla buon'ora. Ora, prima di lanciare l'installazione del .deb, occorre fare un paio di

Attività propedeutiche[modifica]

  # cd /sbin/
  # touch chkconfig
  # nano chkconfig

Copia e incolla questo file all'interno dell'editor:

   #!/bin/bash
   # Oracle 11gR2 XE installer chkconfig hack for Ubuntu
   file=/etc/init.d/oracle-xe
   if  grep INIT` ; then
   echo >> $file
   echo '### BEGIN INIT INFO' >> $file
   echo '# Provides: OracleXE' >> $file
   echo '# Required-Start: $remote_fs $syslog' >> $file
   echo '# Required-Stop: $remote_fs $syslog' >> $file
   echo '# Default-Start: 2 3 4 5' >> $file
   echo '# Default-Stop: 0 1 6' >> $file
   echo '# Short-Description: Oracle 11g Express Edition' >> $file
   echo '### END INIT INFO' >> $file
   fi
   update-rc.d oracle-xe defaults 80 01

Modificare i diritti

   # chmod 755 /sbin/chkconfig

Aggiungere i parametri per il kernel

   # cd /etc/sysctl.d/
   # touch /etc/sysctl.d/60-oracle.conf 
   # nano 60-oracle.conf 

Copiare questo testo all'interno del file

   # Oracle 11g XE kernel parameters  
   fs.file-max=6815744  
   net.ipv4.ip_local_port_range=9000 65000  
   kernel.sem=250 32000 100 128 
   kernel.shmmax=536870912 

Il significato di questi parametri è l'allocazione massima di memoria che il kernel consentirà a Oracle: kernel.shmmax = massimo valore possibile, ad esempio quantità di RAM fisica ( in bytes es. 512MB RAM = 512*1024*1024 = 536870912 bytes)

Carichiamo i parametri del kernel:

   # service procps start
   procps stop/waiting
   #

Verifico che il kernel abbia preso la modifica

   # sysctl -q fs.file-max
   fs.file-max = 6815744

Ok. Ora dobbiamo riservare dello spazio di Swap per Oracle, che richiede come minimo 2 GB. Quanto swap ho lasciato quando ho installato Ubuntu 14.04?

   # free -m 
                total       used       free     shared    buffers     cached
   Mem:          3754       2540       1213         52         22        678
   -/+ buffers/cache:       1840       1914
   Swap:         3892         55       3837

Lo spazio è sufficiente. Se non lo fosse, nell'articolo si trovano indicazioni per aumentare lo spazio di swap.

Ultimi comandi:

   # ln -s /usr/bin/awk /bin/awk
   # mkdir /var/lock/subsys
   # touch /var/lock/subsys/listener

Intallazione del .deb[modifica]

Ora supponiamo di aver sistemato il .deb sotto /opt/oracle

   root@jsbach:/opt/oracle# ll
   totale 269412
   drwxr-xr-x 2 root root      4096 gen 12 11:18 ./
   drwxr-xr-x 8 root root      4096 gen  9 09:38 ../
   -rw-r--r-- 1 root root 275864468 gen 12 11:18 oracle-xe_11.2.0-2_amd64.deb

a questo punto si lancia l'installazione; l'output è piuttosto contenuto, per cui lo riporto per intero:

   # dpkg --install oracle-xe_11.2.0-2_amd64.deb 
   Selezionato il pacchetto oracle-xe non precedentemente selezionato.
   (Lettura del database... 215708 file e directory attualmente installati.)
   Preparativi per estrarre oracle-xe_11.2.0-2_amd64.deb...
   Estrazione di oracle-xe (11.2.0-2)...
   Configurazione di oracle-xe (11.2.0-2)...
   Executing post-install steps...
    Adding system startup for /etc/init.d/oracle-xe ...
      /etc/rc0.d/K01oracle-xe -> ../init.d/oracle-xe
      /etc/rc1.d/K01oracle-xe -> ../init.d/oracle-xe
      /etc/rc6.d/K01oracle-xe -> ../init.d/oracle-xe
      /etc/rc2.d/S80oracle-xe -> ../init.d/oracle-xe
      /etc/rc3.d/S80oracle-xe -> ../init.d/oracle-xe
      /etc/rc4.d/S80oracle-xe -> ../init.d/oracle-xe
      /etc/rc5.d/S80oracle-xe -> ../init.d/oracle-xe
   You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
   
   Elaborazione dei trigger per ureadahead (0.100.0-16)...
   ureadahead will be reprofiled on next reboot
   Elaborazione dei trigger per mime-support (3.54ubuntu1.1)...
   Elaborazione dei trigger per gnome-menus (3.10.1-0ubuntu2)...
   Elaborazione dei trigger per desktop-file-utils (0.22-1ubuntu1)...
   Elaborazione dei trigger per bamfdaemon (0.5.1+14.04.20140409-0ubuntu1)...
   Rebuilding /usr/share/applications/bamf-2.index...
   Elaborazione dei trigger per libc-bin (2.19-0ubuntu6.4)...

Configurazione[modifica]

   # /etc/init.d/oracle-xe configure
   
   Oracle Database 11g Express Edition Configuration
   -------------------------------------------------
   This will configure on-boot properties of Oracle Database 11g Express 
   Edition.  The following questions will determine whether the database should 
   be starting upon system boot, the ports it will use, and the passwords that 
   will be used for database accounts.  Press <Enter> to accept the defaults. 
   Ctrl-C will abort.
   
   Specify the HTTP port that will be used for Oracle Application Express [8080]:
       Specify a port that will be used for the database listener [1521]:
   
   Specify a password to be used for database accounts.  Note that the same
   password will be used for SYS and SYSTEM.  Oracle recommends the use of 
   different passwords for each database account.  This can be done after 
   initial configuration:password (non si vede)
   Confirm the password:password (non si vede)
   
   Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n
   
   Starting Oracle Net Listener...Done
   Configuring database...
   Database Configuration failed.  Look into /u01/app/oracle/product/11.2.0/xe/config/log for details

Iniziamo con la catena di reinstallazioni seguendo le indicazioni del sito.

Trubleshooting[modifica]

Per tornare indietro, lanciare questi comandi

   # dpkg --purge oracle-xe
   # rm -r /u01
   # update-rc.d -f oracle-xe remove
     Removing any system startup links for /etc/init.d/oracle-xe ...
     /etc/rc0.d/K01oracle-xe
     /etc/rc1.d/K01oracle-xe
     /etc/rc2.d/S80oracle-xe
     /etc/rc3.d/S80oracle-xe
     /etc/rc4.d/S80oracle-xe
     /etc/rc5.d/S80oracle-xe
     /etc/rc6.d/K01oracle-xe

Ora occorre riservare a Oracle più memoria:

   # rm -rf /dev/shm
   # mkdir /dev/shm 
   # mount -t tmpfs shmfs -o size=3788m /dev/shm
   #

3788 = 3.7 * 1024 (3.7 la vedo da Rotella (alto dx) > Impostazioni di sistema > Dettagli)

Ora bisognerebbe ripetere questi comandi ogni volta prima di far partire Oracle: li rendiamo permanenti inserendoli negli script di boot:

   # touch /etc/rc2.d/S01shm_load
   # nano /etc/rc2.d/S01shm_load

Copia a incolla questo nel file:

   #!/bin/sh
   case "$1" in
   start) mkdir /var/lock/subsys 2>/dev/null
          touch /var/lock/subsys/listener
          rm /dev/shm 2>/dev/null
          mkdir /dev/shm 2>/dev/null
          mount -t tmpfs shmfs -o size=2048m /dev/shm ;;
   *) echo error
      exit 1 ;;
   esac

Cambia i permessi

   chmod 755 /etc/rc2.d/S01shm_load

Ripeto l'installazione del deb e la configurazione. Si arresta qui

   Port 1521 appears to be in use by another application. Specify a different port.

Bisogna liberare la porta 1521. Chi sta usando la porta 1521?

   # fuser 1521/tcp
   1521/tcp:            17981

Chi è 17981?

   # ps aux | grep "17981"
   oracle   17981  0.0  0.3 224520 13184 ?        Ssl  11:30   0:00 /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr LISTENER -inherit

È il listener (e chi, se no)?

Ammazzo il listener, allora (prima ho anche eliminato la directory /u01, però il processo è ancora in piedi).

   # kill -9 17981

Ora riprovo a configurare:

   # /etc/init.d/oracle-xe configure

Qui, a proposito di questa opzione:

   Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n

io metto no, Oracle è greedy e non voglio che inchiodi tutto il pc alla partenza: lo faccio partire quando voglio io.

   Starting Oracle Net Listener...Done
   Configuring database...Done
   Starting Oracle Database 11g Express Edition instance...Done
   Installation completed successfully.

Ok!!!!

Adesso ultime cose:

Variabili di ambiente per Oracle[modifica]

Apro il file ./bashrc e alla fine aggiungo

   # Oracle environmental variables
   export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
   export ORACLE_SID=XE
   export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
   export ORACLE_BASE=/u01/app/oracle
   export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
   export PATH=$ORACLE_HOME/bin:$PATH

Lo leggo per caricare le variabili

   . ./.profile

Avvio del server Oracle: appena installato non serve, è già partito. Ma il comando per marcia arresto è

   $ sudo service oracle-xe start
   $ sudo service oracle-xe stop
   $ sqlplus sys as sysdba
   
   SQL*Plus: Release 11.2.0.2.0 Production on Lun Gen 12 12:14:28 2015
   
   Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
   Enter password: la password che abbiamo dato durante la configurazione
   
   Connected to:
   Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
   
   SQL> create user marcob identified by marcob;
   
   SQL> grant connect,resource to marcob;            
   
   Grant succeeded.
   
   
   SQL> select sysdate from dual;
   
   SYSDATE
   ------------------
   12-GEN-15
   

Direi che si può stappare un bottiglia di Prosecco.

Info utili per questa installazione di XE[modifica]

Posizione del file tnsnames.ora per il tnsping:

   /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora

Errori[modifica]

In cui mi sono imbattuto.

Errore durante il salvataggio delle modifiche nella tabella[modifica]

Accade fino alla versione 11 in cui esistono i campi di tipo LONG. Il dato da salvare è un file di testo di dimensione > 4000 byte. Tento di sostituire il valore esistente (not null) con un valore diverso

  update tabella set campo = nuovo_valore;

e l'errore che accade è:

  Errore durante il salvataggio delle modifiche nella tabella "SCHEMA"."TABELLA"
  Riga 2: Il commit dei dati è stato eseguito in un'altra o nella stessa sessione. Impossibile aggiornare la riga.

Soluzione[modifica]

eseguire in sequenza

  update tabella set campo = null;
  update tabella set campo = nuovo_valore;

Oggetti di Oracle[modifica]

Domande

  • Quali i ruoli di un utente?
  select * from sys.USER_ROLE_PRIVS
  where USERNAME=<nome utente>

si può lanciare anche dallo user stesso

  • quali sono le tabelle di un utente?
  SELECT *
  FROM sys.user_tables
  • Quali gli oggetti e i i privilegi in un ruolo?

Come funzionano le transazioni[modifica]

Fonte

Definizioni[modifica]

Prima di approfondire il tema delle transazioni, è necessario conoscere i termini definiti in questa sezione.

I compiti (jobs) o le attività (tasks) che Oracle gestisce, sono chiamati sessioni. Una sessione utente viene avviata quando si esegue un programma applicativo o uno strumento come Oracle Forms e ci si connette a Oracle. Oracle permette alle sessioni utente di lavorare "simultaneamente" e condividere le risorse del server. Per fare questo, Oracle deve controllare la concorrenza (concurrency), l'accesso agli stessi dati da parte di molti utenti. Senza adeguati controlli di concorrenza, ci potrebbe essere una perdita di integrità dei dati. Cioè, le modifiche ai dati o alle strutture potrebbero essere fatte nell'ordine errato.

Oracle utilizza i blocchi (locks) per controllare l'accesso concorrente ai dati. Un blocco ti dà la proprietà temporanea di una risorsa di database, ad esempio una tabella o riga di dati. Pertanto, i dati non possono essere modificati da altri utenti fino a che non hai finito. Non dovreste mai bloccare in modo esplicito una risorsa, perché i meccanismi di default di blocco proteggono i dati e le strutture di Oracle. Tuttavia, è possibile richiedere i blocchi di dati sulle tabelle o righe quando è a vostro vantaggio operare sopra (override) al blocco predefinito. È possibile scegliere tra diverse modalità di bloccaggio quali la condivisione di righe ed il modo esclusivo.

Un deadlock può verificarsi quando due o più utenti tentano di accedere allo stesso oggetto di database. Ad esempio, due utenti che aggiornano la stessa tabella potrebbero aspettare che ciascuno cerchi di aggiornare una riga al momento bloccata dall'altro utente. Poiché ogni utente è in attesa di risorse detenute da un altro utente, non si può continuare a meno che Oracle stesso non rompa la situazione di stallo. Oracle segnala un errore alle transazione partecipanti che avevano completato la minor quantità di lavoro, e il codice di errore Oracle "situazione di stallo rilevato in attesa di risorsa" viene restituito in SQLCODE e in SQLCA.

Quando una tabella viene interrogata da un utente e aggiornata da un altro, allo stesso tempo, Oracle genera una vista coerente in lettura (read-consistent) dei dati della tabella per la query. Cioè, una volta che una query inizia e procede, i dati letti dalla query non cambiano. Come attività di aggiornamento continua, Oracle prende istantanee (snapshots) di dati e registra le variazioni della tabella in un segmento di rollback. Oracle utilizza le informazioni nel segmento di rollback per costruire i risultati delle query di lettura coerenti e di annullare le modifiche, se necessario.

Come le transazioni proteggono il vostro database[modifica]

Oracle è transaction-oriented; cioè, utilizza le transazioni per assicurare l'integrità dei dati.

Cos'è una transazione[modifica]

Una transazione è una serie di una o più istruzioni SQL logicamente correlate, definite dall'utente per eseguire una certa operazione. Oracle tratta la serie di istruzioni SQL come unità in modo che tutti i cambiamenti apportati dalle dichiarazioni siano

  • resi permanenti o confermati (commit) oppure
  • annullati (rollback),

allo stesso istante. Se il programma va in errore nel mezzo di una transazione, il database viene ripristinato automaticamente allo stato precedente la transazione.

Le prossime sezioni illustrano come definire e governare le transazioni. In particolare, si impara a

  • iniziare e terminare le operazioni
  • utilizzare l'istruzione COMMIT per rendere le transazioni permanenti
  • utilizzare l'istruzione SAVEPOINT assieme all'istruzione ROLLBACK TO per annullare parte delle transazioni
  • utilizzare l'istruzione ROLLBACK per annullare intere transazioni
  • specificare l'opzione RELEASE per liberare risorse e disconnettere il database
  • utilizzare l'istruzione SET TRANSACTION per impostare le operazioni di sola lettura
  • utilizzare l'istruzione clausola FOR UPDATE o LOCK TABLE per ignorare il blocco di default

Per ulteriori informazioni sulle istruzioni SQL discusse in questo capitolo, consultare Oracle11 Server - SQL Reference.

Come iniziare e terminare le transazioni[modifica]

Si comincia una transazione con la prima istruzione SQL eseguibile (oltre la CONNECT) del vostro programma. Quando termina una transazione, la prossima istruzione SQL eseguibile inizia automaticamente un'altra transazione. Così ogni istruzione eseguibile è parte di una transazione. Poiché non possono essere riavvolte (rollback) e non richiedono commit, istruzioni SQL di tipo dichiarativo non vengono considerate essere parte di una transazione.

Si termina una transazione nei due modi seguenti:

  • inserendo una istruzione di COMMIT o di ROLLBACK, con o senza opzione RELEASE. Questa rende permanenti o cancella le modifiche fatte al database.
  • inserendo una istruzione di definizione dati, o istruzione dichiarativa (ALTER, CREATE, o GRANT, ad esempio) che lancia automaticamente un COMMIT prima e dopo la sua esecuzione. Questo rende implicitamente permanenti le modifiche fatte al database.

Una transazione ha termine anche quando c'è un errore di sistema o la vostra sessione utente viene inaspettatamente terminata a causa di problemi software, hardware o una interruzione forzata. Oracle "riavvolge" la transazione.

Se il vostro programma si pianta a metà di una transazione, Oracle rileva l'errore e riavvolge la transazione. Se il vostro sistema operativo ha un errore, Oracle ripristina il database al suo stato iniziale (cioè allo stato prima della transazione).

Come usare l'istruzione COMMIT[modifica]

Si utilizza l'istruzione COMMIT per apportare modifiche permanenti al database. Fino a quando le modifiche non vengono confermate, gli altri utenti non possono accedere i dati modificati: essi li vedono come erano prima la transazione iniziasse. L'istruzione COMMIT non ha alcun effetto sui valori delle variabili host o sul flusso di controllo del programma. In particolare, la dichiarazione COMMIT

  • rende permanenti tutte le modifiche apportate al database durante la transazione in corso
  • rende questi cambiamenti visibili ad altri utenti
  • cancella tutti i punti di salvataggio (savepoint, vedi la sezione successiva)
  • rilascia tutti i blocchi di riga e di tabella, ma non analizza i blocchi
  • chiude i cursori referenziati in una clausola CURRENT OF, quando MODE = {ANSI | ANSI14}, chiude tutti i cursori espliciti.
  • termina la transazione.

Quando MODE = {ANSI13 | ORACLE}, cursori espliciti non citati in una clausola CURRENT OF rimangono aperti tra i vari COMMIT. Questo può aumentare le prestazioni. Per un esempio, vedere "Fetching Across Commits".

Poiché sono parte della normale elaborazione, le istruzioni di COMMIT devono essere poste in linea, cioè sul percorso principale che attraversa il vostro programma. Prima che il programma termini, esso deve confermare esplicitamente le modifiche in sospeso. In caso contrario, Oracle le ripristina.

Nell'esempio seguente, viene confermata la transazione e ci si disconnette da Oracle:

 > EXEC SQL COMMIT WORK RELEASE;

La parola chiave opzionale WORK garantisce la compatibilità ANSI.

L'opzione RELEASE libera tutte le risorse di Oracle (locks e cursori) detenute dal programma e si disconnette dal database.

Non è necessario far seguire una dichiarazione di definizione dati con un'istruzione COMMIT, perché le dichiarazioni di definizione dei dati lanciano di commit in modo automatico, prima e dopo l'esecuzione. Quindi, sia che abbiano esito positivo o negativo, l'operazione prima viene confermata (COMMIT).

SQL*Plus[modifica]

Come dichiarare una variabile

  SQL> variable foo number

Come inizializzarla

  SQL> exec :foo := 1
  
  PL/SQL procedure successfully completed.

Come stamparla

  SQL> print :foo
  
         FOO
  ----------
           1


Gestione eccezioni[modifica]

Gli errori trattati direttamente da Oracle sono questi (limitatamente a quelli che mi sono trovato a gestire):

  • TOO_MANY_ROWS una select ritorna molte righe che tentiamo di mettere dentro ad una variabile scalare
  • NO_DATA_FOUND una select non ritorna righe con le quali riempire una variabile scalare
  • ZERO_DIVIDE abbiamo tentato di dividere per zero.

Tipicamente un frammento pl/sql che gestisce un errore è fatto così

       e_no_group exception; -- posso definire anche eccezioni mie
       
       begin
       
       select 
               p.campo1 into w_campo1
       from 
               tabella p,
       where 
               condizioni
       
       ...
       exception  -- qui comincia la sezione di trattamento delle eccezioni
       
       when too_many_rows then
               w_err_code := -20120;
               w_err_msg := 'Molti record trovati in corrispondenza alla stessa condizione: ' || o_cod_err || '-' || o_desc_err;
               rollback;
               raise_application_error(w_err_code, w_err_msg);
       
       when no_data_found then
               w_err_code := -20121;
               w_err_msg := 'Nessun record trovato in corrispondenza alla condizione: ' || o_cod_err || '-' || o_desc_err;
               rollback;
               raise_application_error(w_err_code, w_err_msg);
       
       when e_no_group then -- posso definire anche eccezioni mie
               w_err_code := -20122;
               w_err_msg := 'Gruppo null trovato in corrispondenza al profilo: ' || o_cod_err || '-' || o_desc_err;
               rollback;
               raise_application_error(w_err_code, w_err_msg);
       
       when others then
               w_err := sqlerrm; -- qui lascio che Oracle definisca codice ed errore
               w_err_msg := 'Errore non gestito: ' || w_err ;
               rollback;
               raise_application_error(w_err_code, w_err_msg);
       end;