Estas consultas confirman qué instancia está arriba y cómo está repartida su memoria.

sqlplus sys/oracle as sysdba
SQL> show sga
SQL> show user
SQL> select instance_name, status from v$instance;
SQL> select name, open_mode from v$database;

Ubica cada componente en la parte de la arquitectura a la que pertenece.

SQL> select status, name from v$controlfile;
SQL> select name, value from v$parameter where name = 'control_files';
SQL> show parameter control_files

-- Multiplexar: agregar una tercera copia
SQL> alter system set control_files =
       'C:\ORADATA\CDB\CONTROL01.CTL',
       'C:\FRA\CDB\CONTROL02.CTL',
       'D:\ORADATA\CONTROL03.CTL' scope=spfile;
SQL> shutdown immediate
-- copiar CONTROL01.CTL como D:\ORADATA\CONTROL03.CTL en el sistema operativo
SQL> startup

SQL> select group#, status, member from v$logfile;
SQL> select group#, sequence#, status from v$log;
SQL> alter system switch logfile;

SQL> alter database add logfile group 4 ('D:\ORADATA\REDO04A.LOG') size 50M;
SQL> alter database add logfile member 'E:\ORADATA\REDO01B.LOG' to group 1;
SQL> alter database drop logfile member 'E:\ORADATA\REDO01B.LOG';
SQL> alter database drop logfile group 4;

SQL> select name, log_mode from v$database;
SQL> select dest_name, status, destination from v$archive_dest where status = 'VALID';
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;     -- o: alter database noarchivelog;
SQL> alter database open;
SQL> alter system switch logfile;

SQL> select tablespace_name, status from dba_tablespaces;
SQL> select tablespace_name, file_name, bytes from dba_data_files;

SQL> create tablespace datos_app
       datafile 'D:\ORADATA\PDB1\datos_app01.dbf' size 100M
       extent management local uniform size 1M;

SQL> create undo tablespace undo02
       datafile 'D:\ORADATA\undo02.dbf' size 50M;

SQL> create temporary tablespace temp02
       tempfile 'D:\ORADATA\PDB1\temp02.dbf' size 50M
       extent management local uniform size 1M;

SQL> alter database datafile 'D:\ORADATA\PDB1\datos_app01.dbf' resize 250M;
SQL> alter tablespace datos_app add datafile 'D:\ORADATA\PDB1\datos_app02.dbf' size 100M;

-- mover un datafile
SQL> alter tablespace datos_app offline normal;
--   (mover el archivo en el sistema operativo)
SQL> alter tablespace datos_app rename datafile
       'D:\ORADATA\PDB1\datos_app02.dbf' to 'E:\ORADATA\datos_app02.dbf';
SQL> alter tablespace datos_app online;

SQL> alter tablespace datos_app drop datafile 'E:\ORADATA\datos_app02.dbf';
SQL> drop tablespace datos_app including contents and datafiles;

Estima cuántos extents y bloques ofrece un tablespace con extents uniformes y si alcanza para el volumen de datos previsto.

C:\> sqlplus /nolog
C:\> sqlplus sys/oracle@cdb as sysdba
SQL> connect sys/oracle@pdb_ventas as sysdba
SQL> show con_name
SQL> show con_id
SQL> show user
SQL> desc dba_tables

SQL> show pdbs
SQL> select name, open_mode, con_id from v$pdbs;
SQL> alter pluggable database pdb_ventas open;
SQL> alter pluggable database pdb_ventas close;
SQL> alter pluggable database all open;
SQL> alter pluggable database all close;
SQL> alter pluggable database pdb_ventas save state;

SQL> select instance_name, con_id from v$instance;
SQL> select file_name, tablespace_name, con_id from cdb_data_files order by tablespace_name;
SQL> select file_name, tablespace_name from dba_data_files;
SQL> select file_name, tablespace_name from dba_temp_files;
SQL> select group#, member, con_id from v$logfile;
SQL> select count(*) from cdb_objects;
SQL> select count(*) from dba_objects;

SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;

SQL> startup mount       -- llega directo a MOUNT
SQL> startup             -- llega directo a OPEN
SQL> select instance_name, status from v$instance;
SQL> select name, open_mode from v$database;

SQL> shutdown normal
SQL> shutdown transactional
SQL> shutdown immediate
SQL> shutdown abort

Completa la matriz marcando SÍ o NO en cada celda y luego presiona Verificar.

CREATE TABLE almacen.producto (
  id_producto   NUMBER(6),
  descripcion   VARCHAR2(80)  NOT NULL,
  precio_lista  NUMBER(8,2),
  estado        CHAR(1)       DEFAULT 'A',
  fec_registro  DATE          DEFAULT SYSDATE
) TABLESPACE datos_app;

COMMENT ON TABLE  almacen.producto IS 'Catálogo de productos';
COMMENT ON COLUMN almacen.producto.id_producto IS 'Llave primaria';

DESC producto
DROP TABLE producto CASCADE CONSTRAINTS PURGE;
TRUNCATE TABLE producto;

CREATE OR REPLACE VIEW v_producto_activo AS
  SELECT id_producto, descripcion, precio_lista
  FROM   producto WHERE estado = 'A';

CREATE SEQUENCE seq_producto START WITH 1 INCREMENT BY 1 NOCACHE;
INSERT INTO producto (id_producto, descripcion)
  VALUES (seq_producto.NEXTVAL, 'Taladro percutor');
SELECT seq_producto.CURRVAL FROM dual;

CREATE SYNONYM prod FOR almacen.producto;

SELECT column_name, data_type, data_length
FROM   user_tab_columns WHERE table_name = 'PRODUCTO';

SELECT file_id, block_id, blocks FROM dba_extents
WHERE  owner = 'ALMACEN' AND segment_name = 'PRODUCTO' AND segment_type = 'TABLE';

CREATE TABLE socio (
  id_socio        NUMBER(6)    CONSTRAINT pk_socio PRIMARY KEY,       -- nivel de columna
  nombre          VARCHAR2(40) CONSTRAINT socio_nom_nn NOT NULL,
  dni             CHAR(8),
  fec_inscripcion DATE         CONSTRAINT socio_fins_nn NOT NULL,
  CONSTRAINT uk_socio_dni UNIQUE (dni)                                -- nivel de tabla
);

CREATE TABLE reserva (
  id_reserva  NUMBER(6),
  id_socio    NUMBER(6) NOT NULL,
  hora_inicio NUMBER(2),
  hora_fin    NUMBER(2),
  monto       NUMBER(6,2),
  CONSTRAINT pk_reserva        PRIMARY KEY (id_reserva),
  CONSTRAINT fk_reserva_socio  FOREIGN KEY (id_socio) REFERENCES socio (id_socio),
  CONSTRAINT ck_reserva_horas  CHECK (hora_fin > hora_inicio)
);

ALTER TABLE reserva ADD CONSTRAINT ck_reserva_monto CHECK (monto >= 0);
ALTER TABLE reserva ADD CONSTRAINT fk_reserva_cancha
  FOREIGN KEY (id_cancha) REFERENCES cancha (id_cancha) ON DELETE CASCADE;
ALTER TABLE reserva DROP CONSTRAINT ck_reserva_monto;

SELECT table_name, constraint_name, constraint_type, r_constraint_name
FROM   user_constraints WHERE table_name = 'RESERVA';

SELECT constraint_name, column_name, position
FROM   user_cons_columns WHERE table_name = 'RESERVA';

SELECT index_name, index_type, uniqueness FROM user_indexes;

Sistema de reservas de canchas con las tablas SOCIO, CANCHA y RESERVA. Predice el resultado de cada sentencia.

CREATE INDEX cli_dni_ix ON cliente (dni) TABLESPACE indices01;
CREATE BITMAP INDEX vta_region_bx ON venta (region);

ALTER INDEX cli_dni_ix REBUILD TABLESPACE indices02;
DROP INDEX vta_region_bx;

SET TIMING ON
SELECT COUNT(1) FROM venta WHERE region = 'SUR';

SELECT table_name, index_name, index_type, uniqueness
FROM   user_indexes WHERE table_name = 'VENTA';

Ajusta las características de la columna y observa la recomendación. El gráfico es un modelo didáctico de afinidad, no una medición real del optimizador.

CREATE USER caja01 IDENTIFIED BY Clave#2026
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 50M ON users
  PASSWORD EXPIRE;

ALTER USER caja01 IDENTIFIED BY NuevaClave1;
ALTER USER caja01 QUOTA UNLIMITED ON users;
ALTER USER caja01 ACCOUNT LOCK;
ALTER USER caja01 ACCOUNT UNLOCK;
DROP USER caja01 CASCADE;

SELECT username, account_status, profile FROM dba_users;

-- privilegios de sistema
GRANT CREATE SESSION TO caja01;
GRANT CREATE TABLE TO supervisor WITH ADMIN OPTION;
REVOKE CREATE TABLE FROM supervisor;

-- privilegios de objeto
GRANT SELECT ON ventas.pedido TO caja01, caja02;
GRANT UPDATE (estado, fec_entrega) ON ventas.pedido TO supervisor;
GRANT SELECT, INSERT ON ventas.pedido TO supervisor WITH GRANT OPTION;
GRANT SELECT ON ventas.producto TO PUBLIC;
REVOKE SELECT, INSERT ON ventas.pedido FROM supervisor;

Escribe una sentencia de seguridad y el analizador identificará su tipo, sus opciones y sus consecuencias.

CREATE ROLE rol_cajero;
GRANT CREATE SESSION TO rol_cajero;
GRANT SELECT, INSERT ON ventas.comprobante TO rol_cajero;
GRANT rol_cajero TO caja01, caja02;
REVOKE rol_cajero FROM caja02;

SELECT * FROM role_sys_privs WHERE role = 'ROL_CAJERO';
SELECT * FROM role_tab_privs WHERE role = 'ROL_CAJERO';
SELECT * FROM user_role_privs;

CREATE PROFILE prf_soporte LIMIT
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME    1
  PASSWORD_LIFE_TIME    30
  PASSWORD_GRACE_TIME   5
  SESSIONS_PER_USER     2
  IDLE_TIME             60;

ALTER USER soporte01 PROFILE prf_soporte;
ALTER SYSTEM SET resource_limit = TRUE;
SHOW PARAMETER resource

SELECT * FROM dba_profiles WHERE profile = 'PRF_SOPORTE';
SELECT username, account_status, profile FROM dba_users WHERE username = 'SOPORTE01';

Ocho situaciones reales de administración. Elige la mejor acción antes de que se acabe el tiempo.