Archivio per la categoria Oracle

Caricare file csv come tabella Oracle

Se avete già fatto questa operazione con Access, MySql, SqlServer o che ne so io, dimenticate di trovare una guida del tipo “fate tasto destro e…” perchè in Oracle tasto destro non l’hanno nemmeno implementato, non so se esiste qualche interfaccia Oracle sensibile al tasto destro ma penso di no.

Al dilà di quello che leggerete sotto su come caricare un csv in Oracle, sappiate che non c’è scritta tutta una serie di cose, tipo che se nel csv avete testi che vanno a capo non funziona, che lettere accentate o caratteri strani vi causeranno problemi anche su installazioni di oracle in italiano, che probabilmente passerete del tempo a sistemare i maledetti dati che vi sono arrivati a seguito di un commerciale che si sarà venduto qualcosa al grido di “vabbè ma tanto poi i dati li carichiamo da excel”.
L’unico vero consiglio che vi posso dare è il seguente: non andate in giro a dire che caricate fogli excel in Oracle, perchè non è cosi.

Ad ogni modo vediamo come caricare il maledetto csv o excel che ci ha mandato il cliente, dopo averlo con cura riempito di dati incoerenti, mal formattati, colorati e incasellati stile battaglia navale.

Per caricare un file csv, o un excel salvato in csv, è innanzitutto necessario copiare il file in una directory Oracle registrata.
Per conoscere quali directory sono registrate in Oracle, basta eseguire questa query:
select * from all_directories
con questa query possiamo sapere il nome della directory e il relativo percorso su filesystem.
Scegliere una delle directory, ad esempio DATA_PUMP_DIR e copiare il file csv nel relativo percorso.
A questo punto bisogna creare una tabella collegata a dati esterni, con un numero e tipo di campi coerenti con il file csv di partenza, come nell’esempio seguente.
Supponendo che il file csv abbia 3 campi, Nome, Cognome, Indirizzo, lo script di creazione sarà
CREATE TABLE DATIESTERNI_XLS (
NOME VARCHAR2(50),
COGNOME VARCHAR2(50),
INDIRIZZO VARCHAR2(100)
)

Organization external
(type oracle_loader
default directory DATA_PUMP_DIR
access parameters (records delimited by newline
fields terminated by ‘;’
MISSING FIELD VALUES ARE NULL )
location (‘fileesterno.csv’))
reject limit 15000 ;
Come si nota, il nome della tabella è indifferente, mentre il tipo e la dimensione dei campi deve essere uguale o superiore(la dimensione) ai dati contenuti nel csv.
“records delimited by newline” indica che la terminazione del record è identificata dal carattere di fine riga/nuova linea
mentre “fields terminated by ‘;'” indica quale separatore cercare per identificare i valori di ogni singolo campo.
“location (‘fileesterno.csv’)” è invece il nome del file csv da importare, senza l’intero percorso.
Una volta lanciato lo script, verrà creata la tabella in ogni caso, e verranno creati due file di log, nella cartella in cui è contenuto il file csv.
Il primo è un log della procedura, che riporta eventuali errori di importazione per ogni singola riga, e si chiamerà, in questo esempio, DATIESTERNI_XLS_xxxx_xxxx.log.
Il secondo file riporta le righe del csv che non sono state importate in seguito ad errori di caricamento, e si chiamerà, in questo esempio, DATIESTERNI_XLS_xxxx_xxxx.bad.
ATTENZIONE: finchè non facciamo nessuna query di selezione, Oracle non cercherà di importare i dati e quindi, non vedendo log di errori, potreste pensare che sia andato tutto ok, ma non è cosi. Dovete fare almeno una SELECT perchè effettivamente siano caricati i dati in tabella.

Se la procedura è andata completamente a buon fine, eseguendo la query
select count(*) from DATIESTERNI_XLS;
otterremo lo stesso numero di righe del file csv.
N.B: se la prima riga del csv contiene le intestazioni di colonna, esse verranno importate come una normale riga dati, per cui è bene toglierla dal file csv editandolo con il notepad.
Fatto questo, la tabella DATIESTERNI_XLS è utilizzabile come una normalissima tabella Oracle IN SOLA LETTURA.
E’ anche vero che se apriamo il file csv e modifichiamo una riga, automaticamente essa verrà riportata nella tabella collegata.
Se si desidera utilizzare la tabella anche in scrittura, basta creare una tabella identica a quella collegata, con il comando
CREATE TABLE DATIESTERNI AS SELECT * FROM DATIESTERNI_XLS
In questo modo avremo una tabella Oracle disponibile al 100%, anche in scrittura, non più collegata al file excel e gestita con i normali meccanismi di storage di Oracle.

Annunci

Lascia un commento

Recyclebin di Oracle

A partire dalla versione 10g, Oracle ha implementato il recyclebin, che non è altro che il cestino.
Chi non ha mai sbrasato una tabella fondamentale del suo schema, piena zeppa di dati più che fondamentali, perdendo diversi anni di vita nello sgomento, pensando a come insabbiare il tutto o scaricare immediatamente la responsabilità su qualche meccanismo di backup/ripristino andato a male? evidentemente alla Oracle qualcuno ha fatto presente questo scenario, e quindi si sono inventati il recyclebin, e quindi tanto di cappello, considerando che parliamo di database.
Ad ogni modo, chissà come, chissà quando, chissà quale algoritmo lunare avranno inventato, ma guarda che strano cancello roba e non mi si libera lo storage, ma che problema ci sarà, chi sia stato non si sa…. ed ecco svelato ilcano. Il recyclebin rinomina gli oggetti cancellati, loggandosi ovviamente la cancellazione e togliendoli dalle consuete viste di sistema che listano oggetti.

Piccola digressione: non ho ancora capito se una tabella in oracle si può rinominare o no. diversi tool di gestione database consentono la rinomina, ma di fatto dietro le quinte creano una nuova tabella, ci travasano i dati e cancellano la vecchia, il che potrebbe anche fare incazzare più di qualcuno, tra indici, statistiche, ottimizzazioni e cavoli a merenda.

Ad ogni modo, strumento utilissimo che vi salva appunto le terga nel momento in cui cancellate per sbaglio una oggetto. Attenzione, parliamo di oggetti, non di dati, per cui tutto questo discorso vale solo se cancellate ad esempio un intera tabella, non certo se fate un bel DELETE FROM, da quello non vi salva certo il recyclebin ma dovete affidarvi ai meccanismi di rollback, flashback, flash gordon e capitan america che ritenete più opportuni, ammesso che li abbiate mai attivati sul vostro db, visto che su Oracle qualsiasi virgola muovi serve un team di architetti di sistema con almeno 3 anni di servizio sulla USS Enterprise.

Per cui, caro amico, se hai fatto DELETE o TRUNCATE sui dati e speravi di aver trovato ciò che ti avrebbe salvato, una pacca sulla spalla non te la leva nessuno, puoi riprendere a googlare e a sudare freddo.

Vediamo Come funziona

Ci sono due viste di RB(chiameremo RB il recyclebin): USER_RECYCLEBIN e DBA_RECYCLEBIN.
Di defaut, se leggete RECYCLEBIN, state leggendo USER_RECYCLEBIN. Sempre di default, il RB
è abilitato di default dalla 10g, ma si può disabilitare dal parametro RECYCLEBIN a livello di sistema o di sessione.
Come dicevamo, quando droppiamo un oggetto, questo viene rinominato, e ovviamente tutti gli altri oggetti eliminati a cascata(indici, chiavi, trigger, ecc), con un nome che comincia con BIN$.

Vediamo un esempio, creiamo una tabella con un record di prova
SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values (‘Version1′, sysdate);

1 row created.

SQL> select * from tst ;

COL ROW_CHNG
———- ——–
Version1 16:10:03
Se il RB è attivo, droppando la tabella, la ritroveremo nella vista RECYCLEBIN:
SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 from recyclebin
SQL> /

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————- —– — ——————-
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12

Da notare che i dati sono ancora in tabella e possono essere recuperati con una normale query, ovviamente usando il nuovo nome assegnato alla tabella:
SQL> alter session set nls_date_format=’HH24:MI:SS’ ;

Session altered.

SQL> select * from “BIN$HGnc55/7rRPgQPeM/qQoRw==$0” ;

COL ROW_CHNG
———- ——–
Version1 16:10:03

Proprio per questo motivo è possibile tornare indietro e annullare il drop, facendo un “flashback drop”. Il comando è FLASHBACK TABLE… TO BEFORE DROP, e come scaltramente starete pensando, non fa altro che riportare la tabella al nome originale :
SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL ROW_CHNG
———- ——–
Version1 16:10:03

SQL> select * from recyclebin ;

no rows selected
Proprio per questo se droppiamo una tabella anche di dimensioni sensibili non vedremo liberarsi spazio nel tablespace. Di fatto i dati non si sono mossi da dov’erano, e non lo faranno finchè non decideremo di eliminare definitivamente la tabella dal nostro RB. Per farlo basta il comando PURGE. Vedete nell’esempio come dopo il purge l’oggetto non compaia piu nel RB, il che vuol dire che è stato definitivamente eliminato, e quindi non più ripristinabile.
SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 from recyclebin
SQL> /

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————- ————————- — — ——————-
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12

SQL> purge table “BIN$HGnc55/7rRPgQPeM/qQoRw==$0” ;

Table purged.

SQL> select * from recyclebin ;

no rows selected

Se vi siete sbagliati pure qua, lasciate stare, o si è fatto veramente troppo tardi, o dovevate andare a letto prima, oppure avete il cervello bruciato da qualche acido importante.
Ma proseguiamo…

Ci sono vari modi di fare il purge. facendo PURGE RECYCLEBIN si svuota il recyclebin dell’utente (ricordate il sinonimo RECYCLEBIN corrisponde a USER_RECYCLEBIN), mentre se siamo dba possiamo fare PURGE DBA_RECYCLEBIN e cancellare tutto il cancellabile, in un impeto di megalomania.

Se non sappiamo o non possiamo disattivare il recyclebin, ma abbiamo comunque bisogno di sfruttare al massimo il nostro tablespace o la nostra quota utente, il RB ci viene in aiuto, per quello che può. In sostanza, quando abbiamo finito lo spazio, il RB comincia a cancellare gli oggetti a partire dai meno recenti, quel tanto che basta per far spazio ai nuovi oggetti e dati che stiamo scrivendo. Se il nostro tablespace è AUTOEXTEND ON, ovvero si autoestende, prima di estendere un datafile il RB cancellerà degli oggetti. Il che vuol dire che l’ingombro del cestino non influirà sulla larghezza massima del tablespace, anche se è autoestendibile.

…e se cancello più volte una tabella con lo stesso nome?

A parte il fatto che probabilmente se lo stai facendo sei un po confuso, proviamo a farlo:
SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values (‘Version1’, sysdate);

1 row created.

SQL> drop table tst;

Table dropped.

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values (‘Version2’, sysdate);

1 row created.

SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————- —– — — ——————-
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12
BIN$HGnc55/8rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:19:53
Query the two dropped tables to verify that they are different:

SQL> select * from “BIN$HGnc55/7rRPgQPeM/qQoRw==$0”;

COL ROW_CHNG
———- ——–
Version1 16:10:03

SQL> select * from “BIN$HGnc55/8rRPgQPeM/qQoRw==$0” ;

COL ROW_CHNG
———- ——–
Version2 16:19:45

Come vedete dai risultati interrogando il RB otteniamo due tabelle droppate, a orari differenti ovviamente. Se facciamo un FLASHBACK DROP per la tabella TST, quale versione ripristinerà Oracle?
SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst;

COL ROW_CHNG
———- ——–
Version2 16:19:45

Oracle ripristinerà sempre la versione più recente dell’oggetto droppato.
Per ripristinare una versione meno recente dell’ultima droppata, possiamo rilanciare il comando di flashback drop finchè non otteniamo la versione che ci interessa, oppure possiamo lanciare il comando sulla versione esatta da ripristinare. Droppiamo di nuovo due volte la tabella e ne ripristiniamo dal RB la versione meno recente, chiamandola per nome (occhio cambia solo /7 e /9 tra i nomi delle due versioni):
SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————- —— — — ——————-
BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:10:12
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00
To flashback to the first version, refer to the BIN$… name of the first version of TST:

SQL> flashback table “BIN$HGnc55/7rRPgQPeM/qQoRw==$0” to before drop;

Flashback complete.

SQL> select * from tst;

COL ROW_CHNG
———- ——–
Version1 16:10:03
Cosi facendo la versione meno recente è stata ripristinata in TST e non è più presente nel RB, mentre è invece presente la seconda tabella droppata, che non abbiamo flashbackato.
SQL> select object_name, original_name, operation, can_undrop as “UND”, can_purge as “PUR”, droptime
2 from recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION UND PUR DROPTIME
—————————— ————– ——— — — ——————-
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST DROP YES YES 2006-09-01:16:21:00
Oggetti Dipendenti

Ovviamente droppando una tabella, droppiamo anche tutti gli oggetti dipendenti da essa(indici, constraints, triggers, ecc). Con il RB abilitato, droppando la tabella, Oracle la rinomina insieme a tutti gli oggetti correlati, mantenendo intatte le relazioni tra essi. I trigger e gli indici ad esempio vengono modificati per puntare al nuovo nome della tabella droppata (quello che comincia con BIN$), mentre le stored procedure verranno invalidate
Ad esempio, aggiungiamo un indice, droppiamo la tabella e vediamo cosa troviamo nel cestino:
SQL> truncate table tst;

Table truncated.

SQL> insert into tst values (‘Version3′, sysdate);

1 row created.

SQL> create index ind_tst_col on tst(col);

Index created.

SQL> select * from tst;

COL ROW_CHNG
———- ——–
Version3 16:26:10

SQL> drop table tst ;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”, droptime
2 from recyclebin
3 order by droptime;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
—————————— ————– —— — — ——————-
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:27:36
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL INDEX NO YES 2006-09-01:16:27:36
The RECYCLEBIN views have a few other columns that make the relationship between TST and IND_TST_COL clear:

SQL> select object_name, original_name, type, can_undrop as “UND”,
2 can_purge as “PUR”, droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
—————————— ————— —– — — ——————- ———– ————
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00 233032 233032
BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:27:36 233031 233031
BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL INDEX NO YES 2006-09-01:16:27:36 233031 233434
Troviamo quindi nel RB anche l’indice questa volta, rinominato. Se flashbackiamo la nostra tabella TST, noteremo che anche l’indice viene ripristinato, ma Oracle non lo rinomina col nome originale, e lo lascia col nome che aveva nel RB, ovvero BIN$xxx, come possiamo vedere:
SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL ROW_CHNG
———- ——–
Version3 16:26:10

SQL> select index_name from user_indexes where table_name=’TST’ ;

INDEX_NAME
——————————
BIN$HGnc55/+rRPgQPeM/qQoRw==$0
Se droppiamo di nuovo la tabella TST, l’indice verrà droppato con il nome originale cambiato, ovvero quello con cui era nominato nel RB quando era stato già droppato in precedenza, e già qui abbiamo fatto un bel casotto, ma ripeto siamo sempre nella zona dormitopoco/ètardi/acidi, per cui è giusto anche cosi:
SQL> drop table tst;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”,
2 droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
—————————— ————— —– — — ——————- ———– ————
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00 233032 233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:31:43 233031 233031
BIN$HGnc56AArRPgQPeM/qQoRw==$1 BIN$HGnc55/+rRP INDEX NO YES 2006-09-01:16:31:43 233031 233434
gQPeM/qQoRw==$0

Notate il campo CAN_UNDROP e CAN_PURGE, riportarti per praticità come UND E PUR. Un indice non può essere flashbackato senza la sua tabella, per cui non lo si può flashbackare se non flashbackando la sua tabella. Se ne può invece fare il purge separatamente dalla tabella.
Vediamo come facendo il purge del solo indice e ripristinando la tabella, non troviamo più l’indice:
SQL> purge index “BIN$HGnc56AArRPgQPeM/qQoRw==$1” ;

Index purged.

SQL> select object_name, original_name, type, can_undrop as “UND”, can_purge as “PUR”,
2 droptime, base_object, purge_object
3 from recyclebin
4 order by droptime;

OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME BASE_OBJECT PURGE_OBJECT
—————————— ————– —– — — ——————- ———– ————
BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:21:00 233032 233032
BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST TABLE YES YES 2006-09-01:16:31:43 233031 233031
Now, if we restore the table, it will be restored without the index:

SQL> flashback table tst to before drop;

Flashback complete.

SQL> select * from tst ;

COL ROW_CHNG
———- ——–
Version3 16:26:10

SQL> select index_name from user_indexes where table_name=’TST’ ;

no rows selected
Per quanto riguarda i segmenti LOB, quelli creati ad esempio con i campi BLOB E CLOB, il procedimento del RB è sempre lo stesso, salvo che non se ne può fare il purge singolarmente. Sono ripristinati e cancellati sempre e solo in seguito al flashback o al purge della tabella di appartenenza.

Limitazioni

Ci sono delle limitazioni da tener presente, smanettando con l’RB.
Gli Indici Bitmap non finiscono nel RB, per cui se li avete droppati sono cancellati, e se ripristinate la tabella di origine non ve li ritroverete.
Stessa cosa per i logs delle viste materializzate, e per le foreign keys verso altre tabelle, quando droppate la tabella ve le perdete.

Se per problemi di spazio il RB comincia a fare il purge degli oggetti più vecchi, a parità di posizione, droppa prima gli indici. Ovviamente se ripristinate una tabella i cui indici sono stati droppati, verrà ripristinata senza indici…

Disabilitare il Recyblebin

Per non usare il RB in una singola drop, basta specificare l’opzione PURGE alla fine del drop, e l’oggetto verrà definitivamente cancellato senza passare dal RB:
SQL> purge recyclebin;

Recyclebin purged.

SQL> select * from recyclebin;

no rows selected

SQL> create table my_new_table (dummy varchar2(1));

Table created.

SQL> drop table my_new_table purge;

Table dropped.

SQL> select * from recyclebin;

no rows selected

Se disabilitiamo il RB a livello di sessione, con ALTER SESSION SET RECYCLEBIN=OFF, è la stessa cosa di fare ciascun drop mettendo il PURGE alla fine, per l’intera sessione o finchè non lo riattiviamo. Nulla ci impedisce comunque di ripristinare oggetti messi nel RB prima che lo disabilitassimo.

In conclusione…

Che dire, sicuramente il recyclebin è uno strumento utile che ci mette una rete di sicurezza in più ai casini che possiamo fare nella fretta o nella disattenzione del momento. E’ anche vero che ripristinando tabelle con indici e trigger dovremo ripassare a rinominare, ma almeno i dati sono salvi, ed è quello l’importante.
Certo è che, come i classici avvisi di conferma di cancellazione, se clicchiamo più volte senza leggere non c’è sistema che ci possa salvare se non li backup, quando c’è e quando è possibile ripristinarlo.
Mi è capitato di droppare l’intero schema SYSTEM di un cliente una volta, semplicemente per aver inavvertitamente scrollato con la rotella del mouse sul selettore delle connessioni del tool che utilizzavo per gestire il db, e quando me ne sono accorto ci mancava giusto mezzo schema di roba, per cui… parlo con cognizione di causa 😉

Lascia un commento

Backup e Ripristino con Oracle

Backup e Ripristino con Oracle

Piccola ma doverosa premessa sui backup di Oracle: come detto in altre occasioni, non c’è “tasto destro – backup”.

In realtà non è cosi tragica la faccenda, ma è bene capire un paio di concetti per poter andare tranquilli.
Oracle raggruppa gli oggetti in SCHEMI, che però non sono gli stessi schemi di SqlServer, ma sono semplicemente l’insieme di tutti gli oggetti di un Utente. Ogni oggetto viene salvato logicamente in un TABLESPACE, il quale salva fisicamente oggetti e dati in uno o più datafile su filesystem, in tempi e modi che non staremo qui a vedere, altrimenti facciamo notte ( in realtà non li so, e comunque è già notte adesso).

Fin dalle versioni più vecchie di Oracle, il modo classico per effettuare e ripristinare un backup è utilizzare le utility imp ed exp. Non fanno altro che fare dei file compressi con le istruzioni di DDL e di DML necessarie per ricreare tutto quello che è stato backuppato.
Dalla versione 10g c’è anche un altro modo, il DATA PUMP. La grossa differenza tra i due metodi è che imp/exp crea/legge un file di backup dal nostro filesystem, e quindi da un client Oracle, mentre il DATA PUMP legge e scrive file di backup dal filesystem del server Oracle o da una posizione di rete raggiungibile dal server Oracle.
Altra differenza è che per eseguire un FULL export con il DATA PUMP c’è bisogno di alcuni permessi, e in generale le cartelle logiche di oracle che vedremo dopo sono soggette a permessi. E’ vero che la directory di default utilizzata dal DATA PUMP è leggibile e scrivibile da tutti gli utenti per default (credo, non sono sicuro), ma non è detto che sia utilizzata quella, e quindi c’è bisogno di darsi o farsi dare i permessi per utlizzarla.
Dal punto di vista operativo queste solo le grosse differenze, mentre dal punto di vista delle possibilità, il DATA PUMP risolve una delle grandi limitazioni dell’imp/exp, ovvero i remap.

In tutti quei casi (per me è quasi la regola) in cui non dobbiamo ripristinare un backup di uno schema dello stesso utente, con stesso tablespace, l’imp/exp ci crea difficoltà. L’imp infatti quando va a ricreare gli oggetti dello schema, cerca di attribuirne la proprietà all’utente originale, che potrebbe ad esempio non esistere. In alcuni casi si riesce comunque a fare l’import ricorrendo a degli espedienti togliendo e mettendo permessi qua e la, per forzare la scrittura, ma comunque non viene mai un lavoro fatto bene e perfettamente funzionante. A maggior ragione quando si parla di passare da un tablespace all’altro l’imp da i numeri del tutto.

A questo punto ci viene in aiuto il DATA PUMP, che invece gestisce egregiamente lo spostamento di schemi e tablespace, tramite i parametri REMAP_SCHEMA E REMAP_TABLESPACE che vedremo dopo. Non si capisce invece per quale motivo il data pump non importi anche i tipi utente, ma questa è un’altra storia, tenete presente però che non lo fa e quindi in caso vanno ricreati tramite script… o almeno io non ho trovato il modo di farlo.

Il DATA PUMP addirittura tenta di ricreare l’utente dello schema esportato, ma non mi pare il caso di spingersi cosi oltre per non andare a creare un utente insomma…

Bene detto questo vediamo come esportare e importare uno schema con i due tool.

IMP/EXP
Per esportare uno schema “vecchia maniera”, apriamo il prompt del dos e scriviamo

C:/> exp utente/password@connessione

EXP/IMP SU DATABASE 11G R2
Può succedere di incappare in un problema lavorando sulla versione 11G R2 di Oracle, ovvero che eseguendo l’export di una schema, vengano esportate solo le tabelle con almeno una riga al loro interno.
La ragione di questo comportamento è la nuova funzionalità della versione 11.2, ovvero la “Deferred Segment Creation” – la creazione di una tabellla è differita fino al primo insert eseguito su di essa.
Il risultato di questa funzionalità è che le tabelle vuote di fatto ancora non hanno allocato segmenti di storage e quindi non risultano nella vista “dba_segments” che il tool di export utilizza per listare le tabelle da esportare.
Questa funzionalità è controllata dal parametro deferred_segment_creation, che ha TRUE come default.
Se si imposta a FALSE, da quel momento in poi ogni tabella appena creata allocherà immediatamente dei segmenti e verrà quindi esportata dal tool di export anche se vuota.
ATTENZIONE: tutte le tabelle già create continueranno a non essere esportate fino al primo insert.
Per ovviare a questo problema, si può eseguire questo comando da pl/sql:
Alter table allocate extent;
in questo modo la tabella alloca il suo segment e quindi verrà esportata.
Per praticità, si può ricavare la lista di tabelle presenti in una schema e autocostruirsi la lista dei comandi da lanciare per ogni tabella dello schema:
SELECT ‘Alter table ‘ || table_name || ‘ allocate extent;’ FROM ALL_TABLES WHERE OWNER = ”;
Se copiamo e incolliamo il risultato di questa query in pl/sql e lo eseguiamo, ogni tabella del nostro schema allocherà il suo primo segment, se non lo avesse, e potrete quindi eseguire l’export completo.

DATA PUMP
Esportazione (prima di lanciare trepidanti vedere sotto):
SO> expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
dove dmpdir è:
SQL> CREATE DIRECTORY dmpdir AS ‘/opt/oracle’;
SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Oracle cmq mette a disposizione una cartella di default il cui percorso può essere scoperto tramite l’istruzione:
SQL> SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;
Lanciare ora l’istruzione expd con i giusti parametri.
Importazione(prima di lanciare trepidanti vedere sotto):
SO> impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp
Dove dmpdir è la stessa cosa di prima quindi se si cambia istanza ORACLE va rifatta la procedura.
Questa istruzione ripristinerà il dump con i valori di tablespace e schema di origine.
Per cambiare tablespace esiste il parametro REMAP_TABLESPACE:
SO> impdp system/oracle REMAP_TABLESPACE=tbs1:tbs2 DIRECTORY=dmpdir DUMPFILE=scott.dmp
Per cambiare schema esiste il parametro REMAP_SCHEMA:
SO> impdp system/oracle REMAP_SCHEMA=sch1:sch2 DIRECTORY=dmpdir DUMPFILE=scott.dmp
Ovviamente i parametri riguardanti tablespace e schema possono essere usati in congiunzione per un’ esperienza migliore, inoltre sarebbe cosa buona e giusta lanciare le due istruzioni (expdp e impdp) mettendo come credenziali quelle dell’utente owner dello schema di esportazione/importazione e non system o sys.

Edit:

può capitare di ricevere un errore di questo tipo :
ORA-31633: unable to create master table “QUALCOSA.SYS_IMPORT_FULL_XX”

Banalmente, questo errore può essere dovuto a una precedente importazione andata male, per cui in quel caso è opportuno pulire tutti gli oggetti creati nel nostro schema.
Un modo veloce che uso di solito per farlo è droppare e ricreare l’utente, se i grant da dare sono pochi o se abbiamo a disposizione uno script per ricrearlo.
Un’alternativa è quella di eseguire una query simile a questa:

SQL> select ‘drop ‘ || object_type || ‘ “NOSTROSCHEMA”.”‘ || object_name ||'”;’ from all_objects where owner = ‘NOSTROSCHEMA’;

Il risultato di questa query è una serie di drop degli oggetti dello schema che abbiamo indicato. Alternativamente, se doveste avere problemi a leggere la vista all_objects, potete eseguirla loggati come l’utente dello schema da cancellare e usare USER_OBJECTS invece di ALL_OBJECTS.
Potete a questo punto copiare/incollare il risultato in un nuovo script ed eseguirlo.
Noterete che in effetti non è lo stato dell’arte di uno script di drop, perchè non tutti gli oggetti supportano direttamente il drop, o magari la sequenza dei drop porta a droppare un oggetto che ha riferimenti in altri oggetti non ancora droppati.
In realtà potreste andare a leggere delle viste di sistema più mirate come USER_TABLES, USER_VIEWS, USER_TRIGGERS e cosi via, ma lo scopo di questo script è sbrigarsi a cancellare tutto e rifare l’import, per cui basta eseguirlo piu volte finchè non otterrete solo messaggi che vi avvisano che l’oggetto da droppare non c’è più.
Come dire, dirty and fast!

Lascia un commento