Oracle e dba_segments

Oggi ho dovuto recuperare i dati necessari a creare i tablespace per la migrazione di diversi DB Oracle sotto un'unica istanza.
Per capire quali tablespace erano utilizzati dagli utenti oggetto di migrazione ho utilizzato la tabella dba_segments con la seguente query

SELECT owner, segment_type, tablespace_name , count(*)
FROM dba_segments 
GROUP BY owner, segment_type, tablespace_name 
ORDER BT owner

che mi ha dato evidenza di dove gli oggetti dei vari utenti erano.
Con l'utilizzo poi di dba_data_files ho estratto i data file associati.

Creare un utente con i relativi tablespace in Oracle

Negli ultimi due giorni mi sono cimentato con manipolazione di datafile, tablespace e utenti su Oracle 11gR2.
Prima di iniziare è sempre utile verificare quali datafile e tablespace sono presenti sul sistema andando a interrogare le tabelle DBA_TABLESPACES, DBA_DATA_FILES, , DBA_TEMP_FILES.
Se non ci sono tablespace adatti ad ospitare lo schema per l'utente che si intende creare è necessario predisporre un nuovo tablespace con relativo datafile:

CREATE TABLESPACE <TBS_NAME> DATAFILE '<DBF_FILENAME>' SIZE <SIZE>

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

E' possibile agire in maniera analoga anche per creare un temporary tablespace:

CREATE TEMPORARY TABLESPACE <TEMP_TBS_NAME> TEMPFILE '<TEMP_DBF_FILENAME>' SIZE <SIZE>

REUSE;

Se i tablespace esistono, ma non sono abbastanza grandi, è possibile aggiungere datafile:

ALTER TABLESPACE <TBS_NAME> ADD DATAFILE '<DBF_FILENAME>' SIZE <SIZE>;

Oppure estendere i datafile:

ALTER DATABASE DATAFILE '<DBF_FILENAME>' RESIZE <NEW_SIZE>;

Le stesse attività possono essere fatte anche su i Temporary Tablaspace cambiano la parola DATAFILE in TEMPFILE.

Una volta che i tablespace e i datafile sono stati creati è possibile procedere alla creazione di un utente:

CREATE USER <USER> IDENTIFIED BY <PASSWORD>

DEFAULT TABLESPACE <TBS_NAME>

TEMPORARY TABLESPACE<TEMP_TBS_NAME>;

Una volta creato l'utente è necessario procedere all'assegnazione degli opportuni grant:

GRANT <GRANT1>, <GRANT2>, <GRANT3>, <GRANTN> TO <USER>;

Ora è possibile utilizzare l'utente per i proprio scopi.

Oracle: Abilitare e disabilitare Archive Log 10g/11g

Mi è capitato oggi di trovarmi di fronte a problemi di spazio su un database Oracle in ambiente di collaudo.
Andando a esplorare ho notato che il problema era legato agli archive log che dopo anni di sedimentazione avevano occupato una cosa come 60G di spazio.
Essendo il database non di produzione e essendo in fase di dismissione, per non sapere né leggere né scrive, mi sono prodigato per la disattivazione della modalità archive.
Come sempre google mi ha risolto il problema con questo vademecum 
Ovviamente per risolvere il mio problema è bastata la sola parte di disabilitazione composta dai seguenti passi:

1. Verifica dello stato degli archive log tramite il comando archive log list
2. Se attivo spegnere il database con shutdown immediate
3. Far ripartire il database con startup mount
4. Disabilitare l'archiving con il comando alter database noarchivelog
5. Aprire il database con alter database open
6. Verifica dello stato degli archive log tramite il comando archive log list 

La verifica dello stato degli archive log tramite il comando archive log list restiruisce un output come questo se disabilitato:

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u02/app/oracle/oradata/orcl/arch

Oldest online log sequence     26

Current log sequence           28




Oppure come questo se abilitato:

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u02/app/oracle/oradata/orcl/arch

Oldest online log sequence     26

Next log sequence to archive   28

Current log sequence           28

Oracle: Vincoli e tabelle

In questo periodo sto leggendo il libro SQL Certified Expert Exam Guide di Steve O'Hearm e mi sono imbattuto in alcune tematiche che ritengo interessanti e che voglio segnare in questo blog per futura memoria.
Una di queste tematiche riguarda la creazione di vincoli (constraint) contestualmente alla creazione delle tabelle in Oracle.
In Oracle ci sono cinque tipologie di vincoli che poso essere definite nell'atto di creare una tabella:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

In realtà, con l'eccezione di NOT NULL, questi vincoli possono essere definiti tramite un comando di ALTER TABLE anche a valla della creazione di una tabella.
Il vincolo NOT NULL può essere definito solo "inline" sia in fasi di creazione di una tabella:

CREATE TABLE PORTS(
PORT_ID NUMBER,
PORT_NAME VARCHAR2(40) NOT NULL
);

oppure in fase di modifica:

ALTER TABLE PORTS MODIFY PORT_NAME NOT NULL;

Un'altra peculiarità di questo genere di vincolo è la possibilità di specificare un nome per il vincolo stesso:

CREATE TABLE PORTS(
PORT_ID NUMBER,
PORT_NAME VARCHAR2(40) CONSTRINT PORTS_NN NOT NULL
);

oppure:

ALTER TABLE PORTS MODIFY PORT_NAME CONSTRINT PORTS_NN NOT NULL

Quanto appena detto vale anche per gli altri vincoli:

CREATE TABLE VENDORS(
VENDOR_ID NUMBER PRIMARY KEY,
VENDOR_NAME VARCHAR2(30),
STATUS NUMBER(1) CONSTRAINT VENDORS_CHECK CHECK (STATUS IN (4,5)),
CATEGORY VARCHAR2(5)
);

I vincoli di PRIMARY KEY, FOREIGN KEY, CHECK e UNIQUE possono essere definiti anche in "out line":

CREATE TABLE PORTS(
PORT_ID NUMBER,
PORT_NAME VARCHAR2(40),
CONSTRAINT PORTS_PK PRIMARY KEY (PORT_ID)
);

oppure:

ALTER TABLE PORTS ADD CONSTRAINT PORTS_PK PRIMARY KEY (PORT_ID);

Oracle: free space on datafile

Per recuperare dello spazio disco è possibile restringere i DATAFILE di Oracle. Per farlo è sufficiente usare il comando:

ALTER DATABASE DATAFILE '<path_to_datafile>' RESIZE <size>;

Per non andare per tentativi con questa procedura SQL è possibile ricavare quanto spazio libero ci sia per ogni DATAFILE, raggruppando il tutto per TABLESPACE:

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80
 
SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name

Solaris: Memoria e Core

Oggi ho dovuto recuperare informazioni hardware per i sistemi Solaris. Riporto un po' dei comandi che ho utilizzato.
Per recuperare le informazioni relative alla memoria il comando è

prtconf | grep Mem

Per recuperare informazioni relative ai Core il comando è

psrinfo -pv

Oltre a questi comandi sono utili anche

uname -a

isainfo -kv

per recuperare informazioni sul sistema operativo.

Oracle: import di uno schema da un utente ad un altro.

Per questa attività è necessaria la creazione di una directory all'interno dell'istanza target di ORACLE su cui andrà messo il file da importare.

create or replace directory <TARGET_DIR> as '<path>';
grant read, write on directory <TARGET_DIR> to <user_target>;

I grant di read e write sulla directory vanno assegnati anche all'utente system.
La directory <path> sul File System deve essere accessibile in lettura e scrittura all'utente unix con cui il database viene eseguito (nel mio caso oracle).
Una volta superati questi check è sufficiente dare:

impdp system/<password> SCHEMAS=<schema> \
            remap_schema=<user_orig>:<user_target> \
            remap_tablespace=<user_orig>:<user_target> \
            directory=<TARGET_DIR> \
            dumpfile=<DMP_FILE> logfile=<LOG_FILE>