Oracle SQLcl

Anche se la mia diffidenza mi spinge ad essere ancora prudente e a tenermi pronto per un piano di ritorno, sto passando da SQL*Plus a SQLcl. Il client a linea di comando alternativo a SQL*Plus facente parte di una suite che comprende tra gli altri il client grafico SQLDeveloper e SQL Data Modeler, sembra adesso abbastanza maturo ed compatibile quasi al 100% con SQL*Plus, del quale però estende le funzionalità e l’usabilità. Fra l’altro il pacchetto è anche più leggero. Rimane qualche minuscolo dettaglio che mi crea qualche turbamento ma direi che è ora di fare questo salto. Una delle cose che mi ha turbato è stata il problema del set autotrace di cui ho parlato nel post precedente, problema però che è comune a SQL*Plus, quindi è un falso problema. Trovo poi comodo la modalità di output “ansiconsole”, attivabile con il comando “set sqlformat ansiconsole”. Questa modalità ha il difetto che non fa funzionare le direttive “compute” o “break on”. Volevo aggirare il problema aggiungendo negli script il comando di salvataggio delle impostazioni su file (con il comando “store”), il cambiamento di impostazione per sqlformat e infine il caricamento delle impostazioni salvate. Purtroppo però con il comando STORE non mi viene salvata l’impostazione di sqlformat, quindi il trucco non funziona.

Per il resto il prodotto sviluppato da Jeff Smith e dai suoi collaboratori sembra proprio valido

Riferimenti:

Annunci

SET AUTOTRACE

Si vede che sono un po’ fuori allenamento e dimentico le cose, quelle che imparai anni fa con tanto impegno e che registrai su questo blog proprio per evitare che ciò accadesse. E’ il caso di quanto scrissi in questo post,  l’unica scusante è che si tratta di un post di 12 anni fa; non posso ammetterla come scusante ma non posso neppure accettare di dimenticare cose che mi servono ancora nel mio lavoro.

Nei giorni scorsi stavo analizzando una query sottopostami da un collega, per mia sventura ho pensato di analizzare il piano di esecuzione usando l’opzione “set autotrace” di sqlplus, per fare più velocemente. Poi in realtà ho provato un passaggio a SQLcl che sembra un prodotto decisamente maturo e in grado di superare molti limiti di SQL*Plus anche se ho trovato qualche problemino con alcuni vecchi script. Premetto che il db su cui stavo facendo i test è un Oracle 12.1. A un certo punto noto una anomalia, il piano di esecuzione rimaneva lo stesso ma le statistiche, in particolare i “consistent gets” diminuivano molto dopo la prima esecuzione. A quel punto sono tornato sui miei passi ed ho utilizzato esplicitamente la cara vecchia “dbms_xplan.display_cursor” e mi sono accorto che in effetti la query aveva la particolarità di ottenere, grazie alle funzionalità di “adaptive query optimization”, un piano diverso alla seconda esecuzione.  Chiaramente il fatto di utilizzare un “explain plan” per calcolare il piano di esecuzione tagliava fuori questa casistica confondendomi. Per la verità quando mi sono accorto dell’inghippo stavo utilizzando SQLcl e quindi ho fatto una breve ricerca, imbattendomi in questo “twitt“; a questo punto ho fatto un test, anche se su un database su cui non c’erano gli stessi dati e la particolarità del cambio dati non si ripresenta. Ho abilitato il trace della sessione, abilitato il set autotrace, ho eseguito la query, fermato il trace e analizzato il trace. Dentro il trace ci ho trovato la solita “explain plan for” per cui ho chiesto informazioni commentando il twitt. Anche con l’ultima versione, 19.2 di SQLcl io registro lo stesso comportamento, quindi continuerò ad utilizzare la cara vecchia “dbms_xplan.display_cursor”

External Table Preprocessor e l’importanza di leggere bene

In questi giorni ho avuto la necessità di fare un sistema semplice di monitoraggio di un database server. Una delle cose che volevo verificare era lo spazio su disco e il server era con sistema operativo winzoz. Da una rapida ricerca su come fare un monitoraggio direttamente via SQL sono capitato sul solito AskTom con questa discusssione.  La fortuna mi aveva sorriso perché li c’erano due tecniche che mi permettevano di risolvere il problema in maniera brillante; la prima è quella di usare la direttiva “PREPROCESSOR” delle External Tables; la seconda era una script per powershell che estrae i dati sui dischi della macchina.  Mi sono gingillato un po’ con powershell, che è sì una strumento utile e potente ma continuo a pensare sia un po’ complesso per le normali esigenze; forse è solo perché io non sono un gran programmatore e non mi sono mai impegnato per studiare bene l’argomento.

Il punto però è che mi sono incagliato per un paio d’ore perché non riuscivo a far funzionare l’interrogazione sulla external table con la direttiva PREPROCESSOR. Ottenevo sempre un misterioso errore di permessi sul file di testo su cui la external table doveva accedere. Alla fine sono capitato sulla nota del supporto Oracle “Database External Tables Fails with KUP-04001 On Windows (Doc ID 2046669.1)”. La nota spiega come l’errore che io continuavo ad avere era causato da un baco della versione 12.1.0.2 su windows e come sia sufficiente aggiungere la direttiva “DISABLE_DIRECTORY_LINK_CHECK”. A quel punto mi è venuta voglia di sbattere la testa sul muro. In effetti sulla discussione di AskTom avevo visto quella strana direttiva, avevo però letto molto in fretta. Poi mi ero perso con l’adattamento dello script per powershell e così mi ero dimenticato di quella particolarità. Aggiunta la direttiva il trucco della external table che chiama lo script funziona benissimo. Se avessi letto con maggiore attenzione la discussione originale avrei risparmiato tempo.

Export datapump ORA-12899 con parametro VERSION

Facendo un export datapump  su un Oracle 12.1.0.2 con il parametro VERSION=11.2 ho avuto questo errore:

ORA-39126: Error fatal inesperado de worker en KUPW$WORKER.FIXUP_MASTER_TABLE_EXPORT [TABLE_DATA:”SCHEMANAME”.”SYS_EXPORT_SCHEMA_02″]
ORA-12899: el valor es demasiado grande para la columna “SYS”.”KU$_DATAPUMP_MASTER_11_2″.”PROCESSING_STATUS” (real: 3, maximo: 1)

Una rapida ricerca mi ha portato alla nota del supporto Oracle “ORA-12899 During Expdp With VERSION parameter In 12.1.0.2 (Doc ID 2051535.1)”

La nota riporta come il problema sia riconducibile a un bug identificato e per il quale è disponibile una patch. Il fattore scatenante pare siano le impostazioni internazionali, infatti nel caso riportato nella nota il linguaggio è tedesco. Nel mio caso è spagnolo ma l’errore è quasi identico. La cosa che mi ha lasciato veramente perplesso è che nella nota riportano tre possibili soluzioni:

  1. installazione della patch;
  2. upgrade a oracle 12.2;
  3. impostazione del parametro VERSION=12.1

Ora, avrei da ridire sulla 3. nel mio caso ho utilizzato il parametro version=11.2 non per vezzo ma per necessità di importare il dump su una versione 11.2

Il fatto però è che in altri casi non ho avuto questo problema; se la causa è il settaggio di NLS_LANG, ho pensato, proviamo a impostarla a un valore comune, quindi ho provato a impostare NLS_LANG=AMERICAN_AMERICA, ho rilanciato l’export e l’operazione è andata a buon fine. Non mi risulta che la cosa possa creare problemi sul dump e quindi mi sfugge come mai questa soluzione non venga proposta come “work-around”

 

JSON in Oracle: Introduzione

JSON è la sigla per JavaScript Object Notation. Si tratta di un formato per lo scambio dati molto semplice, basato su un sottoinsieme del linguaggio di programmazione JavaScript. E’ un formato facile da leggere e capire per gli umani e per le macchine. Oserei dire che in questo aspetto è un ottimo concorrente di XML. Qui viene riportato un confronto tra le due rappresentazioni: https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-data.html#GUID-C347AC02-31E4-49CE-9F74-C7C0F339D68E.
L’idea che al momento mi sono fatto è che diventa un’ottima alternativa a XML in caso di dati con struttura molto semplice. La definizione del formato JSON è tanto semplice da stare in una paginetta abbastanza corta: https://www.json.org/
Negli anni JSON pare essere diventanto sempre più popolare e diffusamente usato, assieme ai cosiddetti database NoSQL. Questo formato è talmente diffuso che anche Oracle ne ha gradualmente introdotta la gestione sul suo database relazionale. L’introduzione è avvenuta con la versione 12cR1, penso come estensione delle funzionalità XML DB, o perlomeno ne ha aggiunto la documentazione al relativo manuale. (https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246). Un’ottima sintesi del manuale Oracle viene fornita da Tim Hall: https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1

Apro una piccola parentesi con le impressioni e l’idea che mi sono fatto al momento, da novizio di JSON. Presumo che JSON si sia diffuso molto in contesti particolari, dove c’era l’esigenza di gestire dati con strutture molto semplici, assieme a questo formato mi sembra si sia diffuso l’uso di database NoSQL che hanno la capacità di gestire in modo più efficente questi dati e che in alcuni casi al costo di non soddisfare i requisiti di un database relazionali (ad esempio transazioni ACID) riescono ad avere migliori prestazioni e migliore “scalabilità”. Si tratta quindi di sistemi che sono in grado di gestire un carico crescente di lavoro senza andare in sofferenza. Ora, quello che si fa con JSON si può benissimo fare con un database relazionale, anzi, si tratta di implementare schemi molto semplici, per fare interrogazioni analoghe a quelle tipiche che si fanno su dati in formato JSON, su una analoga struttura relazionale bastano query SQL molto banali, non parliamo di join, raggruppamenti ecc. Il fatto di essere “schemaless” è un falso vantaggio o problema, anche in un database relazionale posso aggiungere a piacere colonne al volo a una tabella, senza problemi; è tutto nel come si scrive le applicazioni, se la mia applicazione è scritta come una volta si raccomandava, il fatto che in una tabella da un momento all’altro ci sia un campo in più non fa nessuna differenza. Ciò che un database come Oracle forse non può fare bene è gestire questi dati con prestazioni elavate, perché Oracle è appunto un database relazionale vero, ne rispetta tutte le regole, la visione dei dati è sempre consistente e in più Oracle ha una infrastruttura di monitoraggio e manutenzione. Per andare incontro al mercato e all’esigenza di interfacciarsi con il mondo anche Oracle si è adeguata; certo, trovo buffo dover fare su un database relazionale manovre per estrarre e manipolare dei dati quanto si possono usare dei semplici comandi sql, alla portata di qualunque programmatore degno di questo nome. E’ normale che Oracle abbia aggiunto il supporto a questo formato, per facilitare lo sviluppo di funzionalità di scambio dati con sistemi esterni senza dover forzare continue trasformazioni da un modello JSON a un modello relazionale e viceversa. Chiusa per ora la parentesi.

Alla base del modello dati JSON ci sono insiemi di coppie “chiave”-“valore”, dove la chiave è un’etichetta, il nome di una variabile, un identificativo a cui è associato appunto un valore. Il valore può essere un tipo base e in JSON sono previsti solo numeri, stringhe, valori booleani (true o false) o null. Altrimenti il valore può essere ricorsivamente un altro insieme di coppie chiave-valore oppure un array che si identifica usando come delimitatori dei suoi elementi le parentesi quadre. Un oggetto base, invece è delimitato dalle parentesi graffe. Quindi ecco un primo esempio di oggetto JSON:

{“nome” : “cristian”}

Se ci sono più coppie vengono separate da virgole:

{“nome” : “cristian”, “matricola” : 1234}

L’etichetta, come tutte le stringhe, anche quando sono valori, vanno racchiuse tra apici doppi. Ampliando e applicando ricorsivamente la struttura possiamo arrivare a un esempio più complesso (tratto dal manuale Oracle):

{“PONumber” : 1600,
“Reference” : “ABULL-20140421”,
“Requestor” : “Alexis Bull”,
“User” : “ABULL”,
“CostCenter” : “A50”,
“ShippingInstructions” : {…},
“Special Instructions” : null,
“AllowPartialShipment” : true,
“LineItems” : […]}

Per gestire il tipo dato “JSON” in Oracle non hanno introdotto un nuovo tipo dato, gli oggetti JSON possono essere messi in campi con tipo dato VARCHAR2, CLOB o BLOB. E’ stato introdotto un vincolo “IS JSON” applicabile alla colonna destinata a contenere i documenti JSON (https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-in-oracle-database.html#GUID-F6282E67-CBDF-442E-946F-5F781BC14F33). Il vincolo fa si che oracle verifichi che all’interno della colonna venga inserito un documento di tipo JSON valido. Il vincolo può essere rafforzato con la specifica “STRICT” che fa rende il vincolo più rigoroso (https://docs.oracle.com/database/121/ADXDB/json.htm#GUID-951A61D5-EDC2-4E30-A20C-AE2AE7605C77)

Con questo concludo questo post introduttivo, ne seguiranno altri con esempi e approfondimenti.

Oracle Instant Client e SQL*Loader

Sarò breve, volevo solo condividere la soddisfazione che ho provato nel vedere che finalmente all'”instant client“, il client leggero e portatibile per database Oracle è stato aggiunto il pacchetto che include gli eseguibili per import export, vecchi e datapump ma soprattutto, quello che trovo più utile, è stato aggiunto l’eseguibile di SQL Loader, il programma per caricare file di testo su database. In passato confesso di aver fatto qualche tentativo di utilizzarlo senza dover installare il client completo, però non ci ero mai riuscito. Mentre trovo poco utile la disponibilità degli eseguibili exp,imp, expdp,impdp, ritengo che il caricamento di file CSV o simili dal proprio pc sia un’operazione che capita abbastanza di frequente. Viceversa devo ancora capire l’utilità degli eseguibili di export e import data pump che richiedono sempre che il file di dump si trovi sul database server su cui deve essere creato anche un oggetto “directory”.

Oracle Data Redaction

Uno degli argomenti che ho studiato per conseguire la certificazione a Oracle 12 è stato questo modulo che va precisato che, a parte per le soluzioni cloud di Oracle, richiede la licenza per l’opzione “Oracle Advanced Security” che include un sacco di funzionalità legate alla gestione della “sicurezza”.

Al solito, quanto scrivo è tratto dalla documentazione ufficiale di Oracle e da miei test.

Oracle Data Redaction è un modulo che permette di mascherare (redact) dati restituiti dalle query lanciate dalle applicazioni. Il mascheramente avviene al momento dell’esecuzione delle query (runtime) senza influire sui vincoli di integrità o sulla modalità di memorizzazione dei dati, quindi è un sistema che può essere applicato a sistemi già in produzione. Per le sue caratteristiche è una componente complementare ad altri soluzioni facendi parte del pacchetto “Oracle Database Solutions”.

Oracle Data Redaction non fa cose poi tanto complesse, maschera i dati al volo sulla base di “policy” che stabiliscono cosa mascherare, come e quando. Il cosa tipicamente è la colonna di una tabella o vista. Il come ricade nelle sei seguenti possibilità:

  1. Full redaction (default, DBMS_REDACT.FULL)
  2. Partial redaction (DBMS_REDACT.PARTIAL)
  3. Regular Expressions (DBMS_REDACT.REGEXP)
  4. Random (DBMS_REDACT.RANDOM)
  5. No redaction (DBMS_REDACT.NONE)
  6. Nullify (DBMS_REDACT.NULLIFY) (da 12.2)

La creazione, la configurazione e la gestione delle policy avviene tramite il pacchetto PL/SQL DBMS_REDACT. Il pacchetto contiene varie costanti e le procedure per creare, modificare, abilitare, disabilitare o rimuore le policy.

Le policy mascherano i dati quanto una espressione fornita risulta vera. L’espressione può essere un banale “1=1” che indica sempre vero oppure qualcosa che si basa su varibili di contesto (context).

Non entro nel dettaglio mi limito a riportare un esempio di utilizzo con espressioni basate su “application context”).

L’esempio si basa su questo schema: c’è un utente “amministratore” che gestisce gli “application context” e le policy di data redaction, lo chiamo sysadmin_ctx come sul manuale; c’è un utente/schema che possiede una tabella con dati “sensibili”, lo chiamo “cristian” e infine c’è un utente che deve poter accedere ai dati della tabella con l’esclusione della colonna con la parte di dato “sensibile”, questo utente si chiama “lettore”. L’utente cristian ha una tabella che ho chiamato test1 con questo tracciato:

Name Type
----------- -------------------
ID NUMBER(10)
NOME VARCHAR2(40 CHAR)
COGNOME VARCHAR2(40 CHAR)
USERNAME VARCHAR2(40 CHAR)
PASSWORD VARCHAR2(171 CHAR)
INIZIO DATE
FINE DATE

 

Un primo esempio banale di policy è basato sul “ROLE” assegnato all’utente:

EXEC DBMS_REDACT.DROP_POLICY(OBJECT_SCHEMA=>'CRISTIAN',OBJECT_NAME=>'TEST1', policy_name => 'cristian_pol_3');
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'CRISTIAN',
object_name => 'TEST1',
column_name => 'PASSWORD',
policy_name => 'cristian_pol_3',
function_type => DBMS_REDACT.FULL,
function_parameters => NULL,
policy_description => 'nasconde password',
column_description => 'users passwords',
expression => 'sys_context(''SYS_SESSION_ROLES'',''LETTORE_ROLE'')=''TRUE''');
END;
/

Il parametro “espression” specifica l’espressione che stabilisce quando la policy deve essere applicata e i dati mascherati. Il parametro function_type=> DBMS_REDACT.FULL specifica che il mascheramento deve essere completo, in questo caso per default le stringhe vengono sostituite da uno spazio vuoto.

LETTORE@svil183p1 > select sys_context('SYS_SESSION_ROLES','LETTORE_ROLE') FROM DUAL;

SYS_CONTEXT('SYS_SESSION_ROLES','LETTORE_ROLE')
------------------------------------------------------------------------------------------------------------------------
TRUE

LETTORE@svil183p1 > select id,password from cristian.test1;

ID PASSWORD
---------- -------------------------
44
1
102
21
61
81
82
101
103
100
141
142
143
320
361
242
380
480
500
520
540
640
641
120
163
451
200
561
580
600
140
280
220
319
340
400
560
620

38 rows selected.

Se si vuole applicare modalità più sofisticate si possono definire appositi “context”:

CREATE OR REPLACE PACKAGE set_lettore_ctx_pkg IS 
PROCEDURE set_lettore_id; 
END; 
/
CREATE OR REPLACE PACKAGE BODY set_lettore_ctx_pkg IS
PROCEDURE set_lettore_id 
IS 
BEGIN 
DBMS_SESSION.SET_CONTEXT('cri_ctx_test2', 'lettore_id', 1); 
END;
END;
/
CREATE CONTEXT cri_ctx_test2 USING set_lettore_ctx_pkg;

CREATE TRIGGER set_lettore_ctx_trig AFTER LOGON ON DATABASE
BEGIN
if USER='LETTORE' THEN 
sysadmin_ctx.set_lettore_ctx_pkg.set_lettore_id;
end if;
END;
/

E quindi ecco un altro esempio di policy:

EXEC DBMS_REDACT.DROP_POLICY(OBJECT_SCHEMA=>'CRISTIAN',OBJECT_NAME=>'TEST1', policy_name => 'cristian_pol_3');
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'CRISTIAN',
object_name => 'TEST1',
column_name => 'PASSWORD',
policy_name => 'cristian_pol_3',
function_type => DBMS_REDACT.REGEXP,
regexp_pattern => '.',
regexp_replace_string =>DBMS_REDACT.RE_REDACT_WITH_SINGLE_X,
regexp_position => DBMS_REDACT.RE_BEGINNING,
regexp_occurrence => DBMS_REDACT.RE_ALL,
regexp_match_parameter => DBMS_REDACT.RE_CASE_INSENSITIVE,
policy_description => 'nasconde password',
column_description => 'users passwords',
expression => 'SYS_CONTEXT(''cri_ctx_test2'',''lettore_id'') = 1');
END;
/

 

In questo caso oltre all’espressione ho modificato la modalità di mascheramento usando un caso banale di espressione regolare, qui chi ha dimestichezza con le espressioni regolari si può sbizzarrire, questo per mascherare pezzi di numeri di carte di credito, indirizzi email ecc.

LETTORE@svil183p1 > select id,password from cristian.test1;

ID PASSWORD
---------- -------------------------
44 XXXXXXX
1 XXXXXXX
102 XXXXXXX
21
61 XXXXXXX
81 XXXXXXX
82 XXXXXXX
101 XXXXXXX
103
100 XXXXXXX
141 XXXXXXX
142 XXXXXXX
143 XXXXXXX
320 XXXXXXX
361 XXXXXXX
242
380
480 XXXXXXX
500 XXXXXXX
520 XXXXXX
540 X
640 XXXXXXX
641 XXXXXXX
120
163 XXXX
451 XXXXXXX
200 XXXXXXX
561 XXXXXXX
580 XXXXXXX
600
140 XXXXXXX
280 XXXXXXX
220 XXXXXXX
319 XXXXXXX
340
400
560 XXXXXXX
620 XXX