Salta el contingut
 

Emmagatzematge

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Emmagatzematge en Oracle DB

Oracle DB separa l'emmagatzematge lògic, que és el que veuen els objectes de la base de dades, de l'emmagatzematge físic, que són els fitxers del disc. Aquesta separació permet ampliar o moure l'espai sense modificar les taules que hi ha guardades.

En aquests apunts es veu, en primer lloc, com es gestionen els tablespaces i, després, com s'assignen a les bases de dades, als usuaris i als objectes.

Tablespaces i datafiles

Un tablespace és una unitat lògica d'emmagatzematge on es guarden els objectes de la base de dades. Cada tablespace està format per un o més datafiles, que són els fitxers físics del disc on s'escriuen realment les dades.

Creació d'un tablespace (CREATE TABLESPACE)

En crear un tablespace, s'indiquen els seus datafiles i els paràmetres d'emmagatzematge per defecte, com la mida inicial i el creixement automàtic dels extents:

CREATE TABLESPACE vendes
    DATAFILE '/opt/oracle/oradata/XE/XEPDB1/vendes_01.dbf' SIZE 1M
    DATAFILE '/opt/oracle/oradata/XE/XEPDB1/vendes_02.dbf' SIZE 1M AUTOEXTEND ON NEXT 100K MAXSIZE 5M
    DEFAULT STORAGE (INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 3);

El diccionari de dades permet consultar la informació dels tablespaces i dels seus datafiles:

SELECT tablespace_name, block_size, status, contents
    FROM dba_tablespaces
    WHERE tablespace_name = 'VENDES';

SELECT file_id, file_name, tablespace_name, bytes/1024 AS size_kb,
       autoextensible, maxbytes/1024 AS max_size_kb
    FROM dba_data_files
    WHERE tablespace_name = 'VENDES';

SQL Developer també proporciona informes sobre l'emmagatzematge de la base de dades, disponibles al menú Informe > Administració de la base de dades > Emmagatzematge (Figura 1).

Informes d'emmagatzematge en SQL Developer

Figura 1. Informes d'emmagatzematge en SQL Developer.

Documentació oficial: CREATE TABLESPACE – Oracle DB

Gestió dels datafiles

Si el tablespace es queda sense espai, es pot afegir un nou datafile amb ALTER TABLESPACE, sense necessitat d'aturar la base de dades:

ALTER TABLESPACE vendes
    ADD DATAFILE '/opt/oracle/oradata/XE/XEPDB1/vendes_03.dbf'
    SIZE 8M AUTOEXTEND ON NEXT 128K MAXSIZE 20M;

En canvi, moure un datafile requereix que el fitxer no estiga en ús. Per això, cal seguir aquests passos:

  1. Desconnectar la base de dades.

    SHUTDOWN IMMEDIATE;

  2. Moure el fitxer a la nova ubicació en el sistema de fitxers.

    mv /opt/oracle/oradata/XE/XEPDB1/vendes_02.dbf \
   /opt/oracle/oradata/XE/XEPDB1/vendes/vendes_02.dbf

  3. Iniciar la base de dades en mode MOUNT.

    STARTUP MOUNT;

  4. Canviar la ubicació del datafile amb ALTER DATABASE.

    ALTER DATABASE
    RENAME FILE '/opt/oracle/oradata/XE/XEPDB1/vendes_02.dbf'
    TO '/opt/oracle/oradata/XE/XEPDB1/vendes/vendes_02.dbf';

  5. Obrir la base de dades.

    ALTER DATABASE OPEN;

Finalment, es pot canviar la ubicació per defecte dels futurs datafiles amb el paràmetre DB_CREATE_FILE_DEST:

ALTER SYSTEM SET db_create_file_dest = '/new/path/to/datafiles' SCOPE=BOTH;
SHOW PARAMETER db_create_file_dest;

Documentació oficial: ALTER TABLESPACE – Oracle DB

Eliminació d'un tablespace (DROP TABLESPACE)

Un tablespace es pot eliminar amb DROP TABLESPACE. Les clàusules addicionals indiquen que també s'eliminen els objectes que conté, els seus datafiles i les restriccions que hi fan referència:

DROP TABLESPACE vendes INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Aquesta operació elimina definitivament les dades i els fitxers del tablespace.

No es pot eliminar el tablespace per defecte de la base de dades: abans cal assignar-ne un altre amb ALTER DATABASE DEFAULT TABLESPACE.

Documentació oficial: DROP TABLESPACE – Oracle DB

Bases de dades (Pluggable Databases)

A partir de la versió 12c, Oracle DB utilitza una arquitectura multiinquilí. Una base de dades contenidora (Container Database, CDB) pot allotjar diverses bases de dades connectables (Pluggable Databases, PDB), cadascuna amb els seus propis usuaris, esquemes i tablespaces. En l'edició XE, per exemple, la PDB per defecte és XEPDB1.

Cada PDB té un tablespace per defecte, que s'assigna als usuaris que es creen sense indicar-ne cap. Com que en Oracle DB els objectes es guarden en el tablespace per defecte del seu propietari, aquest és el tablespace on acaben els objectes creats sense la clàusula TABLESPACE.

Creació d'una PDB (CREATE PLUGGABLE DATABASE)

Es pot crear una nova PDB a partir de la plantilla pdbseed. La clàusula DEFAULT TABLESPACE n'indica el tablespace per defecte:

CREATE PLUGGABLE DATABASE pdb_vendes
    ADMIN USER admin_vendes IDENTIFIED BY "oracle"
    FILE_NAME_CONVERT = ('/opt/oracle/oradata/XE/XEPDB1/pdbseed/',
                         '/opt/oracle/oradata/XE/XEPDB1/pdb_vendes/')
    DEFAULT TABLESPACE USERS
    DEFAULT TEMPORARY TABLESPACE TEMP;

Les PDB existents es poden consultar a la vista CDB_PDBS:

SELECT pdb_name, status, con_id, creation_time FROM cdb_pdbs;

L'estat de cada PDB (si està oberta o no) es pot comprovar amb:

SHOW PDBS;
SELECT name, open_mode, restricted FROM v$pdbs;

Per defecte, en reiniciar el contenidor, les PDB no s'obrin automàticament. Perquè ho facen, cal guardar-ne l'estat:

ALTER PLUGGABLE DATABASE pdb_vendes SAVE STATE;
SELECT con_name, state FROM dba_pdb_saved_states;

Documentació oficial: CREATE PLUGGABLE DATABASE – Oracle DB

Canvi del tablespace per defecte (ALTER DATABASE)

Per canviar el tablespace per defecte d'una PDB existent, s'utilitza l'ordre ALTER DATABASE DEFAULT TABLESPACE, connectat a la PDB corresponent:

ALTER SESSION SET CONTAINER = XEPDB1;
ALTER DATABASE DEFAULT TABLESPACE vendes;

Aquesta ordre no mou cap objecte: només canvia on es creen els objectes nous. A més, els usuaris que tenien assignat l'antic tablespace per defecte passen a tindre el nou.

El tablespace per defecte de la base de dades es pot consultar a la vista DATABASE_PROPERTIES:

SELECT property_value
    FROM database_properties
    WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
--------------
VENDES

Documentació oficial: ALTER DATABASE – Oracle DB

Tablespace per defecte d'un usuari

També es pot assignar un tablespace per defecte a un usuari concret, en crear-lo o posteriorment. En aquest cas, cal concedir-li també una quota en el tablespace, ja que sense quota l'usuari no hi pot crear objectes:

CREATE USER venedor IDENTIFIED BY "venedor"
    DEFAULT TABLESPACE vendes
    QUOTA UNLIMITED ON vendes;

ALTER USER venedor DEFAULT TABLESPACE users;

El tablespace per defecte de cada usuari es pot consultar a la vista DBA_USERS:

SELECT username, default_tablespace
    FROM dba_users
    WHERE username = 'VENEDOR';

Documentació oficial d'Oracle DB

Taules i índexs

Encara que cada usuari tinga un tablespace per defecte, cada taula o índex es pot guardar en un tablespace diferent. Això permet, per exemple, posar els índexs en un disc més ràpid que les dades.

Creació en un tablespace concret

Per crear una taula o un índex en un tablespace concret, s'utilitza la clàusula TABLESPACE. L'usuari propietari ha de tindre quota en aquest tablespace:

CREATE TABLE productes (
    id      NUMBER PRIMARY KEY,
    nom     VARCHAR2(50),
    preu    NUMBER(10,2)
) TABLESPACE vendes;

CREATE INDEX idx_productes_nom ON productes (nom) TABLESPACE vendes;

Les vistes USER_TABLES i USER_INDEXES permeten consultar a quin tablespace pertany cada objecte:

SELECT table_name, tablespace_name FROM user_tables WHERE table_name = 'PRODUCTES';
SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'PRODUCTES';

Documentació oficial d'Oracle DB

Canvi de tablespace (ALTER TABLE ... MOVE i ALTER INDEX ... REBUILD)

Per moure una taula a un altre tablespace, s'utilitza ALTER TABLE ... MOVE TABLESPACE. Els índexs, en canvi, no es mouen: es reconstrueixen en el nou tablespace amb ALTER INDEX ... REBUILD TABLESPACE:

ALTER TABLE productes MOVE TABLESPACE users;
ALTER INDEX idx_productes_nom REBUILD TABLESPACE users;

En moure una taula, els seus índexs queden en estat UNUSABLE i s'han de reconstruir.

Mentre un índex està inutilitzable, les consultes que el necessiten fallen o són molt més lentes. Per això, després de moure una taula, cal comprovar l'estat dels seus índexs:

SELECT index_name, status FROM user_indexes WHERE table_name = 'PRODUCTES';

Des de la versió 12.2, la clàusula UPDATE INDEXES (ALTER TABLE productes MOVE TABLESPACE users UPDATE INDEXES;) manté els índexs utilitzables durant el moviment.

Documentació oficial d'Oracle DB

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez