Il comando SET UNUSED COLUMN viene usato per rende la colonna non più disponibile. E' una cancellazione logica perché viene aggiornato il DataDictionary. Può essere visto come un drop virtuale, perché il comando Drop Column richiede alcuni minuti ad Oracle per esaminare ogni riga e per cancellare fisicamente la colonna. Conviene  eseguire il comando DROP in un secondo momento, in un periodo di bassa attività del database. R e g o l e Vengono droppate le constraints e gli indici definiti sulla colonna. La colonna non può essere più recuperata anche se si esegue l'istruzione Rollback. Una volta eseguito il comando UNUSED, è possibile creare un'altra colonna, nella stessa tabella, con il medesimo nome. S i n t a s s i ALTER TABLE   nome_tabella   SET UNUSED COLUMN   nome_colonna1; ALTER TABLE   nome_tabella   SET UNUSED   (nome_colonna1,.., nome_colonnaM); Esiste una vista del Dizionario Dati che contiene il nome delle tabelle e il numero di colonne in stato

La funzione TO_YMINTERVAL converte una stringa nel tipo di dato INTERVAL YEAR TO MONTH. S i n t a s s i TO_YMINTERVAL ('a-m') dove a e m sono l'anno e il mese. Esempio select to_yminterval ('04-05') from dual; Restituisce un intervallo di 4 anni e 5 mesi. Per questo motivo è usato spesso per sommare un intervallo ad una data. select sysdate, sysdate + to_yminterval ('04-05') from dual;

La classica relazione parent-child prevede due tabelle, una padre e uno figlio in join tra loro, invece nel caso di HIERACHICAL DATA abbiamo una sola tabella (o vista) in self-join ed è possibile avere una relazione parent–child–grandchild–great-grandchild e così via. Vediamo un esempio di una tale relazione gerarchica. Consideriamo la seguente tabella ORGANIGRAMMA. S i n t a s s i SELECT LEVEL, EMPLOYEE_ID, TITLE  FROM ORGANIGRAMMA   START WITH EMPLOYEE_ID = 1   CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID; L'istruzione START WITH definisce la radice (root node); la colonna specificata deve identificare in maniera univoca un record pertanto conviene che sulla colonna sia definita una primary key. CONNECT BY definisce la relazione di self-join e cioè individuano le colonne in join. La parola chiave PRIOR può essere posta davanti ad una delle due colonne e definisce come è attraversato l'albero: CONNECT BY REPORTS_TO = PRIOR EMPLOYEE_ID l'albero è attra

Il nome di un oggetto del database, come le tabelle, deve rispettare le seguenti regole: La lunghezza del nome deve essere compresa tra 1 e 30 caratteri Il primo carattere deve essere una lettera Dopo il primo carattere sono ammessi numeri, lettere, il simbolo del dollaro($), underscore (_) e hash mark  o hash symbol (#) Non usare parole chiave proprie di Oracle come Select, Char, File, IS...

CLOB è l'acronimo di Character Large Object ed è un tipo di dato utilizzato per memorizzare caratteri. I dati di tipo carattere possono essere sigle-byte o multi-byte e sono supportati caratteri di larghezza fissa e variabile. Non deve essere specificata la dimenisone di una colonna di una tabella di tipo CLOB. Ad esempio se eseguo la DML seguente si ha un errore alla riga 4: CREATE TABLE EMAIL (   IDEMAIL NUMBER,   OGGETTO VARCHAR2(10),   MESSAGGIO CLOB(2000),   COL4 BLOB ); Esempio contenuto della colonna CLOB: CREATE TABLE EMAIL (   IDEMAIL NUMBER,   OGGETTO VARCHAR2(10),   MESSAGGIO CLOB ); insert into EMAIL values (101, 'pubb_sito','qui può esserci un documento di testo anche scritto in html....<p>vai al sito</br>www.oralinu.eu</p>'); Per vedere la dimensione di una riga della colonna contenete il LOB: select  tab1.* ,    DBMS_LOB.GetLength("COL4")/1024     as   SizeKB from   email   tab1 where idemail=   144120

Per cambiare la password di un utenza eseguire il comando: SQL> ALTER USER sh IDENTIFIED BY s h; Può essere fatta dall'utente sh o da sys o da un utenza che ha i privilegi di DBA Se si utilizzano le virgolette la password è case sensitive. SQL>  ALTER USER  sh  IDENTIFIED BY <password> ; Per bloccare un utenza: ALTER USER hr  ACCOUNT LOCK IDENTIFIED BY  hr; Per sbloccare un utenza: ALTER USER hr  ACCOUNT UNLOCK IDENTIFIED BY hr; Per forzare un utente a cambiare la password al primo login: ALTER USER username  IDENTIFIED BY <password temporanea>   PASSWORD EXPIRE ; Cambiare il tablespace di deafult di uno schema/utente: ALTER USER  username DEFAULT TABLESPACE <nome_tablespace>;

Il comando SET è un comando SQL di controllo delle variabili d'ambiente. Se si utilizza SET DEFINE OFF il client\tool come SQL*PLUS tratta le variabili all'interno della sessione in corso come un "literal Character". Al contrario il comando SET DEFINE ON permette di riutilizzare le variabili all'interno della sessione. Su Toad il comando deve essere eseguito come uno script. E s e m p i o SQL> select '..estraiamo il numero &A' as estrazione from dual; Immettere un valore per a: 10 Eseguendo come Script i seguenti comandi: SQL> SET DEFINE OFF ; SQL> select '..estraiamo il numero &A' as estrazione from dual; Se impostiamo ad ON, viene richiesto di inserire un valore per la variabile. SQL> S ET DEFINE ON ; SQL> select '..estraiamo il numero &A' as estrazione from dual; Immettere un valore per a: 12

SYSDATE è una funzione di Single-Row che non richiede argomenti. Restituisce l'attuale data e l'ora del sistema operativo su cui risiede il database. Il tipo di dato restituito è DATE e il formato restituito dipende dal valore del parametro di inizializzazione NLS_DATE_FORMAT. Non è possibile utilizzare questa funzione nella costraint di tipo CHECK. CURRENT_DATE restituisce la data corrente della sessione nella time zone definita secondo il calendario Gregoriano. select SYSDATE, CURRENT_DATE from DUAL; 11-12-2013 09:25:44 11-12-2013 09:25:44 Utiliziamo le funzioni DBTIMEZONE e SESSIONTIMEZONE per visualizzare il valore "Time Zone" del Database e della Sessione. select DBTIMEZONE, SESSIONTIMEZONE from DUAL; +00:00 +01:00 +00:00 Europe/Berlin Modifichiamo il valore "Time Zone" della sessione. ALTER SESSION SET TIME_ZONE = '-8:0'; oppure ALTER SESSION SET TIME_ZONE = 'America/Los_Angeles'; select DBTIMEZONE, SESSIONTIMEZON

Java Database Connectivity fornisce un meccanismo per connettere i programmi Java al database. Andare su Strumenti --> Preferenze --> Database --> JDBC di terze parti Aggiungere i driver db2; in dettaglio il files db2jcc.jar Andare su Nuova connessione dove compare la nuova voce DB2 a destra di Oracle.

Un sinonimo è l'alias di una tabella o vista sulla quale possono essere eseguite istruzioni SQL e come le Viste non memorizza dati. S i n t a s s i CREATE [PUBLIC] SYNONYM nome_sinonimo FOR nome_oggetto; E' utile per accedere ad una tabella o vista indipendentemente dalla conoscenza dello schema e del db. Ad esempio per eseguire la seguente query: select * from hr.employees@prod; L'utente deve accedere al db "prod" e conoscere lo schema HR. Se si crea un sinonimo pubblico: CREATE PUBBLIC SYNONYM emp FOR hr.employees@prod; allora un utente, che ha la grant di select sul sinonimo, deve semplicemente eseguire la query: select * from emp; Un sinonimo PUBBLICO non è uno schema object, pertanto non è necessario che sia definito il prefisso di uno schema e può avere lo stesso nome di un altro schema object; quindi quando viene eseguita una istruzione su quell'oggetto, Oracle cercherà prima l'oggetto nello schema locale e se non lo trova cerca un s

Per cambiare la configurazione del proxy occorre accedere al LAN Setting da IE> Tolls> Internet Options> Connections> LAN Settings> Proxy server. Creiamo sul Desktop uno Shortcut al LAN Setting. Pulsante destro sul Desktop > New > Shortcut Inserire in "Type the location of the item" la stringa seguente: %windir%\System32\rundll32.exe shell32.dll,Control_RunDLL inetcpl.cpl,,4 Cliccare su Next. Inserire un nome in "Type a name for this shortcut", ad esempio Lan setting e cliccare su Finish. Posizionarsi il cursone del mouse sullo shortcut, pulsante destro, Properties e verificare la presenza dei parametri seguenti: Potete cambiare l'icona da "Change Icon.." e assegnarli una keyboard shortcut inserendola nel campo "Shortcut key". Invece dal Prompt dei comando per accedere al pannello Internet Properties lanciare il comando: C:\Winows\System32> control inetcpl.cpl,,4

Per eliminare l'icona di notifica di aggiornamento di Toad visibile in basso a destra Andare sotto View\Toad Options Sotto la sezione StartUp e selezionare "Don't automatically check for update...". Selezionare Apply e riavviare Toad; l'icona di notifica non compare più nella barro di stato. 

Una SUBQUERY è nota anche come INNER-QUERY, SUB-SELECT e NESTED-SELECT. Può essere usata: - nella clausola FROM in una Select Statement - nella clausola HAVING in una Select Statement - nella clausola WHERE in una Select Statement - nella clausola WHERE in una tutte le istruzioni DML - come un campo nella clausola SELECT di una Select Statement La subquery è eseguita prima della Outer-query che la richiama. Può restituire zero o più righe; se 0 righe allora il valore restituito dalla subquery è NULL e la Outer- query non restituisce alcun risultato. Se è utilizzata con un operatore di disuguaglianza nella Outer-query,deve essere selezionata solo una colonna nella clausola Select della subquery. Può apparire su entrambi i lati di un operatore di confronto. Una subquery è chiamata single-row subquery quando la Inner-query restituisce un solo valore alla query principale. Una multiple-row subquery può contenere funzioni di gruppo e se NULL è un possibile risultato della subq

L'istruzione TRUNCATE è un comando DDL che opera a livello di Data Dictionary e impatta sulla struttura della tabella e non il suo contenuto anche se come effetto ha quello di eliminare i dati. Attenzione vengono troncati anche gli Indici associati alla tabella. Dopo il comando Truncate non è possibile né eseguire Rollback né l'istruzione FlashBack Table perché i dati non vengono conservati nell' Undo Datafile. Non è possibile troncare una tabella se contiene una colonna referenziata da una foreign key constraint in un altra tabella.  Occorre prima disabilitare la foreign key. S i n t a s s i TRUNCATE TABLE <table_name>; Quando viene creata una tabella viene allocato un Extent nei data files e quando vengono eseguite le insert l'extent viene riempito. Una volta pieno, vengono allocati nuovi extent per la suddetta tabella. Tutti gli extent appartenenti alla stessa tabella definiscono un Segment . Nel Data Dictionary oltre ad essere tracciato l'allocaz

Una TRANSAZIONE consiste di una o più istruzioni DML e termina con l'esecuzione del comando COMMIT e ROLLBACK. Se viene eseguita una COMMIT tutte le modifiche diventano permanenti e visibili a tutte le sessioni. Fino a quel momento solo la sessione che aveva eseguito la DML poteva vedere le modifiche. Non è possibile annidare le transazioni perché Oracle non permette di eseguire una transazione mentre è in corso la prima. Le istruzioni DDL e DCL sono loro stesse transazioni perché il loro codice contiene una DML e una Commit finale. Quindi se uno stesso utente ha una transazione in corso e successivamente esegue una DDL, la transazione verrà committata. DDL è l'acronimo di Data Definition Language e comprende le istruzioni Create, Alter, Drop e Rename. DCL è l'acronimo di Data Control Language  e comprende le istruzioni Grant e Revoke. Se una transazione generata da un comando DML è in corso e si esce dal tool senza eseguire una COMMIT o ROLLBACK, la transazione te

Una sequence è un sistema di generazione automatica di interi in ordine discendete o ascendente con un intervallo tra numeri di qualsiasi dimensione. La sequenza può ciclare quando si è raggiunto un limite. Ha un nome e non è associato con una particolare tabella o colonna. Per ottenere il successivo valore della sequenza basta far riferimento al suo nome. Dopo che un numero viene rilasciato, non viene emesso di nuovo lo stesso a meno che la sequenza sia definita come ciclica. S i n t a s s i CREATE SEQUENCE <schema>.<nome_sequence> START WITH   <N> INCREMENT BY <N> MINVALUE     <N>  MAXVALUE     <N>  | NOMAXVALUE CACHE         <N>   [ CYCLE | NOCYCLE ];  MAXVALUE specifica il massimo valore che la sequenza può generare e deve essere più grande di MINVALUE che del valore di START. Di default vale 10^27 per sequenze ascendenti e -1 per discendenti. MINVALUE specifica il minimo valore che la sequenza può generare e deve

Top 'N' Analysis è fondamentalmente una query che estrae n righe da un insieme di risultati. E s e m p i o Selezionare i primi 3 impiegati per salario. select * from ( select last_name ,first_name, salary from employees order by salary desc nulls last  ) where rownum <= 3;

Le SELECT statements secondo lo standard ANSI SQL:1999 (American National Standards Institute) hanno la seguente sintassi: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name1, nome_colonna2)] | [JOIN table2 ON (table1.column_name = table2.column_name and table1.column_name2 = table2.column_name2)] | [LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)] | [CROSS JOIN table2]; La sintassi tradizionale di Oracle attinente alle join è la seguente: SELECT table1.column, table2.column FROM table1, table2 [WHERE (table1.column_name = table2.column_name)] | [WHERE (table1.column_name(+)= table2.column_name)] |         -- RIGHT OUTER JOIN [WHERE (table1.column_name) = table2.column_name) (+)] ;    -- LEFT OUTER JOIN NATURAL JOIN  tutte le colonne della tabella source e target che hanno lo stesso nome sono automaticamente associate pertanto non è necessario specificare le colonne in join. Se l

Il comando SAVEPOINT è utilizzato all'interno di una transazione per dividerla in più sezioni. Interviene per gestire le azioni del comando ROLLBACK. Permette di effettuare il rollback fino ad un certo punto e di applicare le modifiche apportate prima di quel punto. S i n t a s s i SAVEPOINT nome_savepoint; E s e m p i o Eseguiamo un Update e una Delete della tabella locations. update locations set postal_code = '00140' where location_id = 1000; SAVEPOINT a; delete locations where location_id = 1100; ROLLBACK TO a; COMMIT; La COMMIT verrà eseguita solo sull' Update mentre verrà eseguito il ROLLBACK della Delete. Se viene inserito un altro Savepoint con lo stesso nome, il primo Savepoint verrà trascurato. Es e m p i o update locations set postal_code = '00145' where location_id = 1000; Insert into locations values (4000, 'via bello','99999','Bologna','Italia','IT'); SAVEPOINT a; Insert into locations va

Le virgolette alte doppie o doppi apici (QUOTATION MARKS) vengono usate per rinominare una colonna di una tabella. E s e m p i o SELECT 12 +10 "TOT"   from dual; TOT ---- 22 Il singolo apice (SINGLE QUOTATION MARKS) al contrario serve nella definizione dei Character Literal, che non è altro che una sequenza di caratteri racchiusi da apici singoli. E s e m p i o SELECT 'Matt Murdock' "NOME" from dual; NOME ------------ Matt Murdock Se la stringa Character Literal contiene un singolo apice compare il messaggio di errore seguente: SELECT  'Matt Murdock's power'  FROM dual; ORA-00923: parola chiave FROM non trovata dove prevista 00923. 00000 -  "FROM keyword not found where expected" Questo perché Oracle interpreta il carattere s come il nome della colonna "s" e si aspetta dopo il carattere s la clausola FROM. Per risolvere il problema basta aggiungere un singolo apice prima del carattere s ma l'utilizzo

Di seguito gli operatori single-row: = , <>, <, >

L'operatore UNION restituisce tutte le righe da due query ed eliminale righe duplicate. Di default il risultato è in ordine DESC secondo le colonne della istruzione Select. S i n t a s s i select colonna1, ...colonnau from tabella1 UNION select colonna1, ...colonnau from tabella2; R e g o l e - il numero di colonne selezionate deve essere lo stesso - il tipo di dati delle colonne selezionate deve essere dello stesso tipo di dati - i nomi delle colonne non è necessario che siano identici - l'operatore Union opera su tutte le colonne selezionate - i valori Null non vengono ignorati durante il controllo dei duplicati L' operatore UNION ALL restituisce le righe dalle due query senza ordinarle e senza rimuovere i duplicati. Restituisce le righe raggruppate da ogni query secondo il loro ordine naturale. Il comportamento può essere modificato aggiungendo la clausola ORDER BY alla fine della statement e seguita dai nomi delle colonne o dagli alias indicata nella p

Gli Operatori Multiple-Row utilizzati per confrontare una lista di valori o il risultato di una subquery: IN             corrisponde all'uguaglianza NOT IN     esclude gli elementi di una lista ANY        restituisce le righe che uguagliano un qualsiasi valore della lista ALL         restituisce le righe che uguagliano tutti i valori della lista Se si utilizzano subquery come nell'esempio seguente select * from table1 where column1 > ALL (select column from table2 where...); I suddetti operatori hanno il seguente significato: < ANY minore del più alto > ANY maggiore del più basso = ANY equivale a IN > ALL maggiore del più alto < ALL minore del più basso L'operatore NOT può essere utilizzato con IN, ANY e ALL.

L'operatore MINUS restituisce soltanto le righe presenti nella prima query e non nella seconda ed elimina i duplicati.  Il risultato è ordinato secondo le colonne della Select, da sinistra a destra, ed in ordine ascendente a meno che non si usi la clausola ORDER BY. L'operatore esegue entrambe le query, ordina i risultati e restituisce solo il primo risultato non presente nel secondo. S i n t a s s i select colonna1, ...colonnau from tabella1 MINUS select colonna1, ...colonnau from tabella2;

L' operatore INTERSECT restituisce soltanto le righe comuni a più query ed elimina i duplicati. Non ignora i valori NULL. Il numero di colonne e il tipo di dato deve essere lo stesso per tutte le Select presenti nella query. Il risultato è ordinato secondo le colonne della Select, da sinistra a destra ed in ordine ascendente a meno che non si usi la clausola ORDER BY. S i n t a s s i select colonna1, ...colonnau from tabella1 INTERSECT select colonna1, ...colonnau from tabella2;

La regola ON DELETE CASCADE è utilizzata a livello di Foreign Key. Permette di eseguire l'istruzione DELETE di righe di una tabella padre referenziata senza dover cancellare prima le corrispondenti righe della tabella figlio. Ogni DELETE delle righe della tabella padre cancella anche le corrispondenti righe della tabella figlio. E s e m p i o CREATE TABLE ESEMPIO1    (      IDESE1 number,    NOME varchar2(10 byte),    COGNOME varchar2(10 byte),     constraint ESE1_PK primary key (IDESE1)    ); 198    Donald            OConnell 199    Douglas          Grant 200    Jennifer          Whalen 201    Michael          Hartstein 202    Pat                Fay 203    Susan            Mavris 204    Hermann        Baer 205    Shelley          Higgins 206    William          Gietz CREATE TABLE ESEMPIO2    (      IDESE2 number,    NOME varchar2(10 byte),    COGNOME varchar2(10 byte),    constraint ESE2_PK FOREIGN KEY (IDESE2) REFERENCES ESEMPIO1(IDESE1)    ); 204

NLS_DATE_FORMAT specifica il formato di default del tipo DATE da usare con le funzioni TO_CHAR e TO_DATE. Il valore di default è daterminato da NLS_TERRITORY. E' possibile visualizzare il valore impostato a livello di sessione e globale. A livello di Sessione select value from NLS_SESSION_PARAMETERS where parameter = 'NLS_DATE_FORMAT'; VALUE --------------------- DD-MM-YYYY HH24:MI:SS E' possibile modificarlo a livello di sessione con il comando: ALTER SESSION SET NLS_DATE_FORMAT= 'YYYYMMDD';  A livello Globale select value from NLS_DATABASE_PARAMETERS where parameter ='NLS_DATE_FORMAT'; VALUE --------------------- DD-MON-RR

Per questo post è stata utilizzata la versione 11.6.0.43 di Toad. Andare sotto View --> Toad Options --> Data Grids --> Visual Inserire in corrispondenza di "Null column text" il carattere che si vuole visualizzare. Nel mio caso ho inserito il testo (null).

L'istruzione MERGE permette di eseguire o una INSERT o una UPDATE o una DELETE di una tabella target a seconda che venga soddisfatta una join condition con una tabella source o il risultato di una select. S i n t a s s i MERGE INTO target_table TRG USING source_table SRC  ON ( TRG.column_name1 = SRC.column_name1 AND TRG.column_name2 = SRC.column_name2... ) WHEN MATCHED THEN  UPDATE SET  TRG.column_namei = SRC.column_namei, TRG.column_namej = SRC.column_namej, ....... WHEN NOT MATCHED THEN  INSERT  (TRG.column_namei, TRG.column_namej,...)  VALUES  (SRC.column_namea, SRC.column_nameb,..) WHERE SRC.column_name...; Al posto della tabella source può esserci una SELECT.

L'istruzione INSERT serve per inserire righe in una tabella perché in un database i dati vengono memorizzati sotto forma di righe nelle tabelle. S i n t a s s i INSERT INTO  Table_name [(column1, column2,..)] VALUES (value1, value2...) ; INSERT INTO (select colum1, column2,... from Table_name [where ....] ) VALUES (value1, value2...); I campi "value" possono essere anche il risultato di una subquery (esempio select max(region_id) from regions). Se non vengono selezionate le colonne della tabella si parla di positional notation e la INSERT esegue il match tra l'ordine dei valori inseriti con l'ordine di definizione delle colonne della tabella. L'istruzione fallisce se i valori inseriti e i tipi di dato delle colonne sono differenti. E s e m p i o select * from hr.regions; REGION_ID, REGION_NAME ---------------------------------------- 1                       Europe 2                       Americas 3                       Asia insert into

Se si è in possesso di una quota su un altro tablespace, è possibile creare una nuova tabella a partire da una già esistente, in un altro tablespace, inserendo l'istruzione TABLESPACE <tablespace name> dopo il comando CREATE TABLE. CREATE TABLE nome_tabella TABLESPACE altro_tablespace AS SELECT * FROM old_temporary_table WHERE amount<5000;

Connettersi come SYSDBA. E' possibile effettuare il backup del control file su un file binario. SQL>  alter database backup controlfile to '/path/control.bkp';  Con il seguente comando invece viene creato un file di trace contenente gli script sql per ricreare il control file. SQL>  alter database backup controlfile to trace as '/path/ctrlfile.trc'; Se non si inserisce il path vedere l'alert log per determinare il nome e il path del trace file. SQL> alter database backup controlfile to trace;

Il post è stato testato su Oracle 11gR2. Connettersi come SYSDBA SQL> sqlplus /nolog connect /as sysdba ; Per verificare quale file è stato usato allo start del db eseguire il comando: SQL> show parameter spfile NAME     TYPE      VALUE --------------------------------- spfile string Se VALUE è Null vuol dire che è stato utilizzato pfile. Quando si usa spfile il risultato è il seguente: SQL> show parameter spfile; NAME        TYPE                   VALUE ------------------------------------------------------------------------- ----------- ----- spfile         string              /u01/app/oracle/product/18/db_1/dbs/spfileorcl.ora E' possibile effettuare il back up di spfile in un'altra directory con il seguente comando: SQL>  create pfile='/tmp/init_<sid>.ora'  from spfile; Con il seguente comando creiamo file init<sid>.ora (chiamato pfile) nella direcotry di deafult $ORACLE_HOME/dbs SQL>  create pfile='initorcl.ora' from spfile