Conceptos Clave y Comandos Esenciales de Bases de Datos Oracle

Diccionario y Conceptos Fundamentales de Oracle

• Diccionario (sql.bsq, v:catalog.sql): Vistas (user, all, dba, v$; se soportan sobre sentencias SQL, solo contienen la sentencia Create or Replace view as select), dueño (sys).
• DDL: Produce cambios estructurales; las DCL y DML dependen de cómo funciona el motor.
• Vistas simples: Una sola tabla, sin modificadores, sin grupos, sin joins. Las DML se permiten si hay columnas con reglas de integridad. Con FORCE se obliga a crear la vista.
• Rowid: Identificador único de la base de datos.
• Sentencia: Fases de parse, execute y fetch.
• LATCH: Mecanismo de serialización.
• PrivS: Privilegios de índole administrativo con los que gestionar la base de datos.
• PO: Establecen permisos para la manipulación de objetos de la base de datos.
• Schema: Organización de los objetos.
• Tablespace: Organiza cómo se guardan los objetos en disco.
• Segmento: Espacio lógico ocupado por un objeto (ej. tabla).
• Datafile: Espacio físico que ocupa el segmento.
• Extend: Conjunto de bloques contiguos.
• Conexión: Dedicada, compartida (para procesos rápidos que no requieran consultas complejas y con muchos usuarios), o ambas.
• PGA: Información de ordenamiento de datos, de conexión del servidor (SP) y parámetros SQL.
• Bloque PL: Declarativo, ejecutable y de excepciones.

Transacciones y Control de Concurrencia

Las transacciones se rigen por las propiedades ACID: atomicidad, coherencia, aislamiento y durabilidad. Si el sistema falla, la transacción incompleta se revierte.

• T-DML: Conjunto de sentencias DML desde el último COMMIT o ROLLBACK hasta el siguiente.
• T-DDL: Cada sentencia equivale a una transacción y es autocommit.
• T-DCL: Gestión de control de datos.
• Commit: Confirma y finaliza la transacción.
• Rollback: Todos los cambios efectuados por las DML en la transacción son descartados y esta finaliza.
• Savepoint: Punto de salvaguarda. Si hay varios puntos de salvaguarda y se retorna a uno de los primeros, los posteriores se deshechan.
• Bloqueo: En una transacción, los registros involucrados son bloqueados (si no lo están ya por otra). Previene la interacción destructiva entre transacciones con el menor nivel restrictivo posible, asegurando la consistencia a nivel de registro (no por columnas). Toda DML genera bloqueos.
• DeadLock: Error de aplicación que, si se extiende, puede congelar la base de datos (hang database).
• Dirty Reads (Lectura sucia): Una transacción lee datos de otra transacción que aún no ha terminado.
• Non-Repeatable Reads (Fuzzy): Una transacción relee datos que leyó previamente y encuentra que han sido modificados o borrados por otra transacción.
• Phantom Reads: Una transacción vuelve a ejecutar una consulta y encuentra que otra transacción ha insertado nuevos registros que cumplen con la condición.
• Niveles de Aislamiento:
  • Read Committed: Una consulta ejecutada en una transacción ve únicamente los datos confirmados por otras transacciones que hicieron COMMIT.
  • Serializable: Todas las sentencias de la transacción ven los datos que tuvieron COMMIT al momento de empezar la misma.

Arquitectura y Almacenamiento

• SGA: Conjunto de estructuras de memoria principal compartidas por todos los procesos.
• DBC: Conjunto de bloques de Oracle en memoria principal.
• Shared Pool (SP): Incluye el data dictionary cache y el library cache.
• Controlfile: Contiene la información de consistencia de la base de datos (aprox. 100 MB, principal en disco). Si el sistema cae, el proceso SMON revisa el controlfile, compara la última sesión y, si algo falla, tumba el sistema.
• Archivelog: Copias antiguas de los online redologs.
• Almacenamiento Externo: Máquinas con discos dedicados como SAN (Storage Area Network) y NAS (Network Attached Storage).
• ASM (Automatic Storage Manager): Administrador de discos duros de Oracle. Para que dos máquinas instancien una misma base de datos se necesita la suite Oracle Grid (OG).
• Alta Disponibilidad: Uso de clusters activo-activo o activo-pasivo.
• Niveles RAID:
  • RAID 10: Mínimo 4 discos, luego crece de 2 en 2.
  • RAID 0: Segmentación de datos en N discos, alta velocidad de lectoescritura.
  • RAID 1: Dos discos en espejo.
  • RAID 5: Algoritmo de paridad, capacidad entre 80-85%, balance costo-eficiencia. Los bloques de paridad no se leen en operaciones de lectura ordinarias, solo ante errores.
• Procesos de Background: DBWR (Database Writer), LGWR (Log Writer), CKPT (Checkpoint), SMON (System Monitor), PMON (Process Monitor) y ARCN (Archiver).

Ejemplos de Sentencias SQL y Comandos

-- Consultas de vistas y tablas
SELECT * FROM user_objects WHERE object_type = 'VIEW';
SELECT * FROM user_views;

-- Creación de tablespace
CREATE TABLESPACE TBS_CONTAB_DATA DATAFILE 'C:\ORACLEXE\DF_CONTAB.DBF' SIZE 15M;

-- Creación de tablas y consultas de índices
CREATE TABLE hr.prueba AS SELECT * FROM hr.employees;
SELECT * FROM user_indexes WHERE table_name = 'PRUEBA';

SELECT 'INSERT INTO empleados (s,sd) VALUES (' || employee_id || ',' || first_name || ');' FROM employees;
SELECT * FROM df WHERE rownum <= 5;

-- Creación de vista con FORCE y CHECK OPTION
CREATE OR REPLACE FORCE VIEW sd(a, a, s, d) AS SELECT a, a, s, ds FROM dd WITH CHECK OPTION;

-- Permisos y restricciones
GRANT SELECT ON tabla TO juanita;
SELECT * FROM user_constraints WHERE table_name = 'PRUEBA';

-- Creación de usuario y funciones de fecha
CREATE USER contab IDENTIFIED BY cont DEFAULT TABLESPACE users;
SELECT first_name, last_name, MONTHS_BETWEEN(sysdate, hire_date) FROM employees ORDER BY 3 DESC;

-- Consultas al diccionario de datos
SELECT * FROM all_users;
SELECT * FROM all_constraints ac;
SELECT * FROM all_cons_columns;
SELECT * FROM all_all_tables;

-- Gestión de privilegios y roles
GRANT CREATE SESSION TO contab;
GRANT CREATE TABLE TO contab;
GRANT SELECT ON cuentas TO pper;
REVOKE SELECT ON cuentas FROM pper;
CREATE ROLE roladmin;
DROP ROLE roladmin;

-- Consultas agrupadas (como HR y SYS)
-- Como HR:
SELECT object_type, COUNT(1) FROM user_objects GROUP BY object_type;

-- Como SYS:
SELECT object_type, COUNT(1) FROM dba_objects WHERE owner = 'HR' GROUP BY object_type;

-- Control de transacciones con Savepoint
SAVEPOINT a;
ROLLBACK TO SAVEPOINT a;