Inicio / Apuntes / Informática /

Tablespaces, Segmentos, Deshacer, Tablas e Índices en Oracle: Guía de Administración de Bases de Datos

Informática12,18 KB

Tablespace y Archivos de Datos

- Forma lógica: una BD a la vez; uno o más archivos de datos; subdividido en unidades lógicas de almacenamiento.

- Archivos de datos: forma física; pertenece a un tablespace y a una BD; repositorios de datos de objetos del esquema.

* TBS SYSTEM: se crea con la BD; contiene el diccionario de datos; contiene el segmento de deshacer SYSTEM.

* TBS no SYSTEM: segmentos separados; facilita la administración del espacio; controla el espacio del usuario.

TBS gestionados localmente

- Extensiones libres del tablespace registradas mediante bitmap.

- Cada bit corresponde a un bloque o grupo; valor del bit: libre o en uso.

- Reduce el contenido del diccionario; no necesita fusión; no genera deshacer al producirse una asignación libre.

CREATE TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

TBS gestionados por diccionario

- Las extensiones gestionan el diccionario de datos.

- Actualiza la tabla al asignar o liberar extensiones; la fusión es necesaria.

- Puede tener cláusula de almacenamiento.

CREATE TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

Operaciones básicas

Crear TBS:

CREATE TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 100M;

Migrar TBS por diccionario a local:

EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

Tablespace de deshacer (UNDO)

CREATE UNDO TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 40M;
  • Utiliza las cláusulas DATAFILE y EXTENT MANAGEMENT.
  • Se utiliza para guardar segmentos de deshacer.
  • No debe contener otro objeto.
  • Las extensiones se gestionan de forma local.

Tablespace temporal

CREATE TEMPORARY TABLESPACE nombre TEMPFILE '/u01/oradata/nombre.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
  • Operaciones de ordenación.
  • Compartido por múltiples usuarios.
  • No debe contener ningún objeto permanente.
  • Extensiones gestionadas localmente.

Tablespace temporal por defecto:

CREATE DATABASE ...; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp2;

El tablespace por defecto no puede: borrarse hasta asignar otro por defecto; ponerse offline; modificarse para convertirlo en permanente.

Buscar tablespace temporal: SELECT * FROM DATABASE_PROPERTIES;

Tablespace de solo lectura y estados

ALTER TABLESPACE nombre READ ONLY;

Genera punto de control.

ALTER TABLESPACE nombre OFFLINE;

Con el tablespace offline/online se pueden borrar objetos.

Cambiar valores de almacenamiento

ALTER TABLESPACE nombre MINIMUM EXTENT 2M;
ALTER TABLESPACE nombre DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

Autoextensión

CREATE TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Consultar: DBA_DATA_FILES

Cambiar tamaño y añadir archivos

ALTER DATABASE DATAFILE '/u01/oradata/nombre.dbf' RESIZE 200M;
ALTER TABLESPACE nombre ADD DATAFILE '/u01/oradata/nombre.dbf' SIZE 200M;

Mover un tablespace

Requisitos: estar offline y que exista el archivo de destino.

ALTER TABLESPACE nombre RENAME DATAFILE '/u01/oradata/nombre.dbf' TO '/u02/oradata/nombre.dbf';

Borrar tablespace

DROP TABLESPACE nombre INCLUDING CONTENTS AND DATAFILES;

Gestión OMF (Oracle Managed Files)

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata/';
CREATE TABLESPACE nombre DATAFILE SIZE 20M;
ALTER TABLESPACE nombre ADD DATAFILE;

Información de tablespaces

  • Tablespaces: DBA_TABLESPACES, V$TABLESPACE
  • Archivos de datos: DBA_DATA_FILES, V$DATAFILE
  • Archivos temporales: DBA_TEMP_FILES, V$TEMPFILE

Relaciones y Estructura de Almacenamiento: Segmentos

Tipos de segmentos: tablas, particiones de tablas, agrupamientos, índices, tablas organizadas por índice, particiones de índices, segmentos de deshacer, segmentos temporales, segmentos LOB, tablas anidadas y segmento de arranque (prioridad).

Cláusula STORAGE: valor por defecto del tablespace.

  • Asigna extensiones al segmento: al crearse, ampliarse o modificarse.
  • Desasigna extensiones: al borrarse, modificarse o truncarse.

Bloque de la BD

Definido al crear el tablespace mediante DB_BLOCK_SIZE (tamaño por defecto). Formado por uno o más bloques del sistema operativo. Soporta múltiples tamaños: la BD puede crearse con tamaño estándar y tamaños no estándar (hasta 4 bloques no estándar). El tamaño puede tener valores múltiplos de 2 entre 2 y 32 KB.

  • Estándar: se define al crear la BD (DB_BLOCK_SIZE); utilizado por los tablespaces SYSTEM y TEMPORARY. DB_CACHE_SIZE especifica el tamaño de la caché de buffers; mínimo un granulo (4 o 16 MB); valor por defecto 48 MB.
  • No estándar: DB_nK_CACHE_SIZE (de 2 a 32); mínimo un granulo.
CREATE TABLESPACE nombre DATAFILE 'nombre.dbf' SIZE 10M BLOCKSIZE 4K;

Contenido de un bloque de BD

Cabecera, espacio libre y datos. Parámetros de utilización: INITRANS, MAXTRANS, PCTFREE, PCTUSED.

Gestión de espacio

Gestión automática:

CREATE TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

Gestión manual: permite configurar con los parámetros PCTFREE, PCTUSED y FREELIST.

Información de almacenamiento

DBA_EXTENTS, DBA_SEGMENTS, DBA_TABLESPACES, DBA_DATA_FILES, DBA_FREE_SPACE

Gestión de Datos Deshacer (Undo)

Existen 2 tipos de gestión de datos deshacer: SMU automático y SMU manual (deshacer equivale a rollback).

Tipos de segmentos de deshacer

  • SYSTEM: para el tablespace SYSTEM.
  • Diferido: offline al instante; tablespace en offline.
  • No SYSTEM: otros tablespaces; modo automático (tablespace UNDO).

Modo manual:

  • Privado (única instancia)
  • Público (cualquier instancia)

SMU automático (UNDO)

Asignación de UNDO por instancia para trabajar con la instancia. El servidor Oracle mantiene el deshacer automáticamente en el tablespace UNDO.

UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = UNDOTBS
CREATE DATABASE nombre1 ... UNDO TABLESPACE nombre2 DATAFILE '/u01/oradata/nombre.dbf' SIZE 20M AUTOEXTEND ON;
CREATE UNDO TABLESPACE nombre DATAFILE '/u01/oradata/nombre.dbf' SIZE 20M;

Modificar:

ALTER TABLESPACE nombre ADD DATAFILE '/u01/oradata/nombre.dbf' SIZE 30M AUTOEXTEND ON;

Cambiar a tablespace UNDO:

ALTER SYSTEM SET UNDO_TABLESPACE = nombre;

Borrar:

DROP TABLESPACE nombre;

Parámetros

  • UNDO_SUPPRESS_ERRORS: en TRUE suprime errores.
  • UNDO_RETENTION: controla los datos de deshacer.
  • UNDO_POOL: define la cantidad de espacio permitido.

Información

DBA_ROLLBACK_SEGS, V$ROLLNAME, V$ROLLSTAT, V$UNDOSTAT, V$SESSION, V$TRANSACTION

Gestión de Tablas

Almacenamiento de datos de usuario: tabla regular, tabla particionada, tabla organizada por índice y agrupamiento.

Crear tablas temporales

CREATE GLOBAL TEMPORARY TABLE nombre AS SELECT * FROM nombre;

Cálculos

PCTFREE = ((tamaño medio de fila - tamaño inicial de fila) * 100) / tamaño medio de fila

PCTUSED = (100 - PCTFREE) - ((tamaño medio de fila * 100) / espacio disponible)

Operaciones

Cambiar parámetros de bloques y almacenamiento:

ALTER TABLE nombre PCTFREE 30 PCTUSED 50 STORAGE (NEXT 500K MINEXTENTS 2 MAXEXTENTS 100);

Asignación manual de extensiones:

ALTER TABLE nombre ALLOCATE EXTENT (SIZE 500K DATAFILE '/disk01/nombre.dbf');

Reorganización de tabla particionada:

ALTER TABLE nombre MOVE TABLESPACE nombre2;

Truncar tabla:

TRUNCATE TABLE nombre;

Borrar tabla:

DROP TABLE nombre CASCADE CONSTRAINTS;

Borrar columna:

ALTER TABLE nombre DROP COLUMN comuna CASCADE CONSTRAINTS CHECKPOINT 1000;

Renombrar columna:

ALTER TABLE nombre RENAME COLUMN columna TO nueva_columna;

Marcar columna como no usada:

ALTER TABLE nombre SET UNUSED COLUMN columna CASCADE CONSTRAINTS;

Borrar columnas no usadas:

ALTER TABLE nombre DROP UNUSED COLUMNS CHECKPOINT 1000;

Borrar columnas (continuar):

ALTER TABLE nombre DROP COLUMNS CONTINUE CHECKPOINT 1000;

Información

DBA_TABLES, DBA_OBJECTS

Gestión de Índices

Tipos de índices

  • Lógicos: columna única o concatenados; únicos o no únicos; basados en función; de dominio.
  • Físicos: particionados o no; B-TREE (clave normal o inversa); bitmap.

Estructura B-TREE: raíz, derivación, hoja, cabecera, longitud de la columna clave, valor de la columna clave y ROWID.

  • B-TREE: alta cardinalidad; actualización de clave de costo bajo; ineficaz para consultas con OR; útil en OLTP.
  • BITMAP: baja cardinalidad; claves costosas de actualizar; eficaz para consultas con OR; útil en almacenes de datos.

Creación

CREATE INDEX nombre ON nombre(parametro) PCTFREE 30 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indice;
CREATE BITMAP INDEX nombre ON nombre1(parametro) PCTFREE 30 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indice;

Operaciones

Cambiar parámetros:

ALTER INDEX nombre STORAGE (NEXT 400K MAXEXTENTS 100);

Asignar y desasignar extensiones:

ALTER INDEX nombre ALLOCATE EXTENT (SIZE 200K DATAFILE 'DISK6/nombre.dbf');
ALTER INDEX nombre DEALLOCATE UNUSED;

Reconstruir índice:

ALTER INDEX nombre REBUILD TABLESPACE indice;

Reconstruir online:

ALTER INDEX nombre REBUILD ONLINE;

Fusión de índice:

ALTER INDEX nombre COALESCE;

Validar:

ANALYZE INDEX nombre VALIDATE STRUCTURE;

Borrar índice:

DROP INDEX nombre;

Controlar uso:

ALTER INDEX nombre MONITORING USAGE;
ALTER INDEX nombre NOMONITORING USAGE;

Información

DBA_INDEXES, DBA_IND_COLUMNS, V$OBJECT_USAGE

Descargar

PDF Word HTML