tareas del dba para un famoso gestor de bases de datos relacional
TRANSCRIPT
Tareas del DBA (Administrador de la base de
datos)
Las tareas que normalmente debe realizar el DBA son, entre otras:
1. Instalación del software de Oracle, normalmente en colaboración y coordinado con el administrador del sistema operativo. Esto incluye motor de la BD, actualizaciones, herramientas administrativas, cambios de hardware, etc...
2. Instalación de las aplicaciones, propias o ajenas, sobre oracle (CREATE
DATABASE). Esto incluye pruebas antes de pasar a producción. 3. Controlar la seguridad de la BD. Esto incluye la altas y bajas de usuarios (CREATE
USER), creación de roles (CREATE ROLE) y auditorias de seguridad.
4. Monitorización y optimización del rendimiento de la BD. Este incluye desde
modificar parametros del INIT.ORA hasta rehacer tablas o indices.
5. Backups (copias de seguridad). El DBA debe participar en la politica de copias de
seguridad y recuperación de datos.
6. Prevención de riesgos. Esto incluye estar informado de cambios de hardware y software en el servidor, programar mantenimientos, probar copias de seguridad etc...
7. Apoyar a los desarrolladores. Esto incluye el diseño del modelo de datos, la optimización (TUNNING), construcción de procedimientos almacenados y triggers, definición de estándares de diseño y nomenclatura.
8. Documentar todo lo relativo a la BD.
Create Database
Sirve para crea una base de datos.
Desde el punto de vista físico, una base de datos es, para oracle, un conjunto de ficheros, a saber:
datafiles, ficheros de datos, definidos en la creación de la base de datos.
log files, ficheros de log, definidos tambien en la creación de la base de datos.
init.ora, fichero de texto que contiene los parámetros de configuración de la base
de datos.
control files, ficheros de control, definidos en el init.ora
password file, fichero con la password del BDA y los operadores (todos los demas
usuarios estan definidos en tablas).
Asi para crear una base de datos, una vez instalado oracle, debemos seguir los siguientes pasos:
1) Definir ORACLE_SID
ORACLE_HOME = E:\Oracle\Product\10.0.0
ORACLE_SID = GESTION
2) Crear el fichero INIT.ORA
C:\>ORACLE_HOME\database\initGESTION.ora
control_files =
(/path/to/control1.ctl,/path/to/control2.ctl,/path/to/control3.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = GESTION
db_block_size = 8192
sga_max_size = 1073741824
sga_target = 1073741824
3) Definir fichero de passwords
$ORACLE_HOME\bin\orapwd file=ORACLE_HOME\database\pwdGESTION.ora
password=oracle entries=10
Podemos generar los pasos 2) y 3) con una sola instrucción:
oradim -new -sid GESTION -intpwd -maxusers 20 -startmode auto
-pfile E:\Oracle\Product\10.0.0\Database\initGESTION.ora
4) Arrancar la instancia
C:\>sqlplus / as sysdba
sql> startup nomount
5) Crea la base de datos con el nombre(o SID) GESTION y el char set WE8ISO8859P1
CREATE DATABASE GESTION
LOGFILE 'E:\OraData\GESTION\LOG1GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG2GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG3GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG4GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG5GESTION.ORA' SIZE 2M
EXTENT MANAGEMENT LOCAL
MAXDATAFILES 100
DATAFILE 'E:\OraData\GESTION\SYS1GESTION.ORA' SIZE 50 M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
'E:\OraData\GESTION\TEMP.ORA' SIZE 50 M
UNDO TABLESPACE undo DATAFILE 'E:\OraData\GESTION\UNDO.ORA' SIZE 50
M
NOARCHIVELOG
CHARACTER SET WE8ISO8859P1;
6) Ejecutar sql de creación: catalog.sql y catproc.sql
Sintaxis completa:
CREATE DATABASE nombreDB opciones
Donde las opciones:
DATAFILE filespec AUTOEXTEND OFF
DATAFILE filespec AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K |
M]
MAXDATAFILES int
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tablespace [TEMPFILE filespec]
[EXTENT MANAGEMENT LOCAL]
[UNIFORM [SIZE int K | M]]
UNDO TABLESPACE tablespace [DATAFILE filespec]
LOGFILE [GROUP int] filespec
MAXLOGFILES int
MAXLOGMEMBERS int
MAXLOGHISTORY int
MAXINSTANCES int
ARCHIVELOG | NOARCHIVELOG
CONTROLFILE REUSE
CHARACTER SET charset
NATIONAL CHARACTER SET charset
SET TIMEZONE = 'time_zone_region'
SET TIMEZONE = '{+|-} hh:mm'
FORCE LOGGING
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password
Se puede poner mas de un DATAFILE o LOGFILE separando los nombres de fichero
con comas DATAFILE filespec1, filespec2, filespec3
Si no se especifican claves, Oracle establece "change_on_install" para SYS y "manager" para SYSTEM.
Despues de crear la base de datos podemos cambiar entre los modos ARCHIVELOG, NOARCHIVELOG con la sentencia ALTER DATABASE.
Create User
Esta sentencia sirve para crear un usuario oracle.
Un usuario es un nombre de acceso a la base de datos oracle. Normalmente va asociado a una clave (password).
Lo que puede hacer un usuario una vez ha accedido a la base de datos depende de
los permisos que tenga asignados ya sea directamente (GRANT) como sobre algun rol que tenga asignado (CREATE ROLE).
El perfil que tenga asignado influye en los recursos del sistema de los que dispone un usuario a la hora de ejecutar oracle (CREATE PROFILE).
La sintaxis es:
CREATE USER username
IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS
'external_name'}
options;
Donde options:
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
QUOTA int {K | M} ON tablespace
QUOTA UNLIMITED ON tablespace
PROFILE profile_name
PASSWORD EXPIRE
ACCOUNT {LOCK|UNLOCK}
Crea un usuario sin derecho a guardar datos o crear objetos:
CREATE USER usuariolimitado IDENTIFIED BY miclavesecreta;
Crea un usuario con todos los derechos para guardar datos o crear objetos:
DROP USER miusuario CASCADE;
CREATE USER miusuario IDENTIFIED BY miclavesecreta
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON data;
CREATE ROLE programador;
GRANT CREATE session, CREATE table, CREATE view,
CREATE procedure,CREATE synonym,
ALTER table, ALTER view, ALTER procedure,ALTER synonym,
DROP table, DROP view, DROP procedure,DROP synonym,
TO conn;
GRANT programador TO miusuario;
Es neceario crear el usuario antes de asignar permisos con GRANT o un ROLE por
defecto.
Create Role
Esta sentencia sirve para crear un rol de usuario.
Un rol es una forma de agrupar permisos (o privilegios) para asignarlos luego a los
usuarios.
Cada usuario puede tener varios roles.
Ejemplo de creación de un rol:
CREATE ROLE MI_PROPIO_ROLE
Crea un rol sin password:
CREATE ROLE role NOT IDENTIFIED
Crea un rol con password:
CREATE ROLE role IDENTIFIED BY password
Crea un rol de aplicación:
CREATE ROLE role IDENTIFIED USING [schema.]package
Crea un rol basado en uno del S.O.:
ALTER ROLE role IDENTIFIED EXTERNALLY
Crea un rol basado en el servicio de directorio:
ALTER ROLE role IDENTIFIED GLOBALLY
Ejemplo para crear un script que asigna todos los permisos de actual esquema
SELECT decode(object_type,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES
ON'||&OWNER||'.',
'VIEW','GRANT SELECT ON '||&OWNER||'.',
'SEQUENCE','GRANT SELECT ON '||&OWNER||'.',
'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.',
'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.',
'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO
MI_PROPIO_ROLE ;'
FROM user_objects
WHERE
OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE',
'PACKAGE','FUNCTION')
ORDER BY OBJECT_TYPE
Alter system
Sirve para que el DBA interactue con el sistema de ejecución.
Para matar un sesión:
SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='NOMBRE-USUARIO';
ALTER SYSTEM KILL SESSION 'session-id,session-serial';
Actualizar estadísticas
Consiste en recopilar y actualizar el CATALOGO de oracle con datos estadísticos.
En las ultimas versiones, estos datos estadísticos son muy importantes para que
oracle pueda elegir el plan de ejecución más adecuado a cada sentencia (EXPLAIN PLAN).
Estos datos no pueden actualizarse en tiempo real porque penalizarías mucho el rendimiento general de la base de datos.
Se actualizan datos como el número de registros de una tabla, el tamaño de los
objetos, etc.
Actualmente hay dos formas de actualiza las estadísticas de las tablas e indices, con el comando ANALYZE y con el paquete DBMS_UTILITY.
Comando ANALYZE
El comando ANALYZE sirve para recopilar y actualizar el CATALOGO de oracle con datos estadísticos.
La cláusula COMPUTE hace un cálculo exacto de la estadísticas (tarda más en realizarse en ANALYZE)
La cláusula ESTIMATE hace una estimación partiendo del anterior valor calculado y
de un posible factor de variación.
La cláusula DELETE borra las anteriores estadísticas.
Para analizar tabla con sus indices:
ANALYZE TABLE T_PEDIDOS COMPUTE STATISTICS;
Para analizar solo la tabla:
ANALYZE TABLE T_PEDIDOS COMPUTE STATISTICS FOR TABLE;
Para analizar solo sus indices:
ANALYZE TABLE T_PEDIDOS COMPUTE STATISTICS FOR ALL INDEXES;
Para analizar tabla con sus indices por estimación mirando el 20% de las filas:
ANALYZE TABLE T_PEDIDOS ESTIMATE STATISTICS SAMPLE 20 PERCENT;
Para borrar las estadísticas:
ANALYZE TABLE T_PEDIDOS DELETE STATISTICS;
Sintaxis completa:
ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption
options
ANALYZE INDEX indice COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption
options
ANALYZE CLUSTER cluster COMPUTE|ESTIMATE|DELETE STATISTICS
options
donde ptnOption puede ser:
PARTITION (partion)
SUBPARTITION (subpartition)
y donde options puede ser:
VALIDATE STRUCTURE [CASCADE] [INTO tablename]
LIST CHAINED ROWS [INTO tablename]
COMPUTE|ESTIMATE STATISTICS FOR TABLE
COMPUTE|ESTIMATE STATISTICS FOR ALL COLUMNS
COMPUTE|ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS
COMPUTE|ESTIMATE STATISTICS FOR COLUMNS [SIZE int] column [SIZE
int]
cuando queremos hacer solo una estimación debemos elegir una de estas opciones:
... ESTIMATE STATISTICS SAMPLE n ROWS
... ESTIMATE STATISTICS SAMPLE n PERCENT
Al validar la estructura se realiza un control de la integridad y puede bloquear la
tabla/indice/cluster mientras esta en marcha.
El recálculo de las estadísticas necesita gran cantidad de espacio de forma temporal. Podriamos tener que incrementar el valor para SORT_AREA_SIZE.
Si usamos la clausula INTO para almacenar una lista de filas enlazadas, la tabla por defecto es CHAINED_ROWS.
Con el paquete DBMS_UTILITY
El comando ANALYZE esta disponible en todas las versiones de oracle, pero es más
comodo, más rapido y se obtienen mejores resultados si se usa el paquete DBMS_UTILITY y esta disponible a partir de la version 8.0.
DBMS_UTILITY.ANALYZE_SCHEMA('PROGRAMADOR','ESTIMATE', NULL, 10)
DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',NULL,20,NULL)
Audit
Audita una sentencia SQL o el acceso a un objeto específico de la base de datos.
Para una sentencia SQL:
AUDIT ALL|ALL PRIVILEGES|sentencia_sql|privilegio_sistema
[opciones]
Opciones:
BY user
BY proxy [ON BEHALF OF ANY|user]
BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL]
Para objetos:
AUDIT action on schema.object BY ACCESS|SESSION [WHENEVER [NOT]
SUCCESSFUL]
AUDIT action on DEFAULT BY ACCESS|SESSION [WHENEVER [NOT]
SUCCESSFUL]
AUDIT action on DIRECTORY dir_name BY ACCESS|SESSION [WHENEVER
[NOT] SUCCESSFUL]
Donde action es una de las siguientes:
ALTER, AUDIT, COMMENT, DELETE, EXECUTE, GRANT,
INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE
Es necesario habilitar la auditoria con el parámetro del init.ora AUDIT_TRAIL = YES
Catálogo
El catálogo de oracle es un conjunto de tablas y vistas que contienen la definición de la estructura lógica y física de la base de datos.
Esto incluye los objetos existentes, la situación de los datafiles, la configuración de los usuarios, etc.
Los nombres de las tablas de catálogo sigue la siguiente nomenclatura de prefijos:
DBA_ Objetos con información de administrador. Sólo accesibles por
usuarios DBA.
USER_ Objetos con información del usuario con el que estamos
conectado.
Proporcionan menos información que los objetos DBA_
ALL_ Objetos con información de todos los objetos en base de datos.
V_$ ó V$ Tablas virtuales
Existe una tabla de catálogo para cada tipo de objeto posible (en plural).
TABLES, VIEWS, SEQUENCES, TABLESPACES...
Sabiendo esto podemos ver algunos ejemplos:
DBA_TABLES Información para administradores de las tablas en base de
datos.
USER_VIEWS Información de las vistas creadas por el usuario desde el
que accedemos.
ALL_SEQUENCES Información de todas las secuencias existentes en base
de datos.
DBA_TABLESPACES Información de administración sobre los tablespaces.
USER_TAB_COLUMNS Todas las columnas de tabla en el usuario activo.
Los objetos de catálogo pueden estar relacionados entre ellos. Por ejemplo, el
objeto ALL_TABLES guarda una relación 1-N con el objeto ALL_TAB_COLUMNS: Una
tabla tiene N columnas.
Si se quiere que todos los usuarios tengan algún tipo de acceso a un objeto, podemos dar
ese privilegio al rol PUBLIC.
El catálogo público son aquellas tablas (USER_ y ALL_) que son accesibles por
todos los usuarios. Normalmente dan información sobre los objetos creados en la base de datos.
El catálogo de sistema (DBA_ y V_$) es accesible sólo desde usuarios DBA y
contiene tanto información de objetos en base de datos, como información específica de la base de datos en sí (versión, parámetros, procesos ejecutándose...)
Ciertos datos del catálogo de Oracle debe actualizarse para el buen funcionamiento de la base de datos (ANALYZE).
Disponemos de un par de tablas en el catálogo público que sirven al analista y al programador para crear comentarios sobre tablas y columnas.
ALL_TAB_COMMENTS Contiene los comentarios para tablas y vistas.
ALL_COL_COMMENTS Contiene los comentarios para las columnas de
tablas y vistas.
Estas tablas se mantienten por medio de la sentencia COMMENT.
Con todas esta información en la base de datos, podemos escribir procedimientos o scripts SQL para generar documentación o generar a su vez scripts.
Dependencias (Recompilación)
Las actualizaciones, parches y cambios de librerias pueden invalidar objetos del esquema.
Una vez realizados los cambios los objetos dependientes serán revalidados de forma AUTOMATICA cuando se usan.
Esto puede tardar un rato e incluso tardar un tiempo inaceptable por lo que lo logico es recompilar las dependencias antes de las llamadas de los usuarios.
Esto además permite descubrir si los cambios han afecteado al resto del codigo.
Identificación de objetos descompilados:
SELECT owner, object_type, object_name, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;
Con esta información podemos decidir que metodo seguir para recompilar.
Recompilación manual
Para un numero pequeño de objetos es probable que la recompilación manual sea suficiente.
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Hay que tener en cuenta que es necesario recompilar cabecera y cuerpo del
paquete por separado.
Alternativamente podemos usa el paquete DBMS_DDL:
EXEC DBMS_DDL('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
Este metodo esta limitado a los objetos PL/SQL por lo que no es aplicable a vistas.
Script a medida
En algunos casos puede ser interesante escribir un script para identificar y compilar objetos descompilados.
Un ejemplo para PACKAGE y PACKAGE BODY seria:
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner, object_name, object_type,
DECODE(object_type, 'PACKAGE', 1,'PACKAGE BODY',
2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name ||
'" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' ||
cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/
Con este metodo hay que tener mucho cuidado ya que puedes acabar recompilando
algunos objetos varias veces dependiendo del orden de ejecucion de la
recompilación.
Los procedimientos que nos da oracle y que están explicados a continuación si que tiene en cuenta el orden adecuado.
DBMS_UTILITY.COMPILE_SCHEMA
El procedimiento COMPILE_SCHEMA del paquete DBMS_UTILITY compila todos los
procedures, functions, packages, y triggers de un esquema. Hay que tener muy en
cuenta que tampoco recompila las vistas.
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'PROGRAMADOR');
UTL_RECOMP
El paquete UTL_RECOMP contiene 2 procedimientos para recompilar objetos descompilados.
RECOMP_SERIAL recompila los objetos uno por uno mientras que
RECOMP_PARALLEL recompila en paralelo usando el numero de threads especificado.
Para esquemas:
EXEC UTL_RECOMP.RECOMP_SERIAL('PROGRAMADOR');
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'PROGRAMADOR');-- 4 es el numero
de threads a utilizar
Para base de datos:
EXEC UTL_RECOMP.RECOMP_SERIAL(); --todos los esquemas
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);
Usando el valor de job_queue_processes
EXEC UTL_RECOMP.RECOMP_PARALLEL(); --se usa para theads el valor de
"job_queue_processes"
EXEC UTL_RECOMP.RECOMP_PARALLEL(NULL, 'PROGRAMADOR');
Restricciones del paquete:
La ejecucion en paralelo utiliza la cola de trabajos. Todos los trabajos se dehabilitan hasta que la operación termina.
Debe ejecutarse con el usuario SYS u otros con permisos de SYSDBA.
Deber exitir STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM.
El ejecutar comandos DDL a la vez puede provocar interbloqueos.
EXPLAIN PLAN (plan de ejecución)
Esta sentencia guarda el plan de ejecución para una sentencia SQL en una tabla.
Cada vez que ejecutamos una sentencia una de las cosas que hace oracle es crear
un plan de ejecución de la sentencia.
Un plan de ejecución define la forma en que oracle busca o graba los datos. Decide, por ejemplo, si va a usar o no los indices en una sentencia SELECT.
Esta es la sintaxis general:
EXPLAIN PLAN [SET STATEMENT_ID = 'text'] FOR sentencia;
Podemos usar nuestra propia tabla de explain:
EXPLAIN PLAN [SET STATEMENT_ID = 'text'] INTO
[esquema.]tabla@dblink FOR sentencia;
Si no definimos nuestra propia tabla se usa la tabla PLAN_TABLE.
Ejemplo:
DELETE PLAN_TABLE;
EXPLAIN PLAN FOR SELECT * FROM T_PEDIDOS WHERE CODPEDIDO = 5;
Para ver el resultado:
SELECT
SUBSTR (LPAD(' ', LEVEL-1) || OPERATION || ' (' || OPTIONS ||
')',1,30 ) "OPERACION",
OBJECT_NAME "OBJETO"
FROM PLAN_TABLE
START WITH ID = 0
CONNECT BY PRIOR ID=PARENT_ID;
Sobre todo hay que evitar búsquedas del tipo FULL TABLE SCAN.
Export
Es una utilidad que genera un archivo binario con toda la información de estructura y contenido de una base de datos.
Estos archivos sólo pueden ser leídos por la utilidad de importación de Oracle (IMPORT).
Incluye todas las definiciones de objetos y los datos que se deseen dentro de una base de datos.
En unix es simplemente exp.
EXP80 usuario/password [opciones...]
Para simplificar la utilización del comando, las opciones se pueden escribir en un archivo de texto y referenciar desde la línea de comandos de la siguiente manera:
EXP80 usuario/password PARFILE=archivo [opciones...]
Es posible obtener una lista de las opciones disponibles escribiendo:
EXP80 HELP=Y;
Usos:
Backup de la base de datos: Puede usarse para hacer una copia completa de la base
de datos, aunque no es lo mas eficiente.
Mover datos entre bases de datos: Los datos y objetos exportados desde una base de datos pueden perfectamente ser recuperados en otra diferente.
Reconstruir una base de datos: Si su base de datos tiene los tablespaces demasiado
fragmentados, ésta es una buena opción para volver a compactarlos.
Reorganizar los datafiles: Siguiendo la misma lógica anterior, también se puede
redistribuir la información en los archivos físicos que se desee.
Y las opciones son siempre del tipo PARÁMETRO=valor. Ya sea que se utilice un archivo paramétrico o no, la mayoría de los parámetros que se pueden utilizar en la sintaxis de este comando son:
BUFFER=bytes
Especifica el tamaño del buffer de copia (en bytes).
Si el valor es cero, entonces se recuperan las filas de a una.
COMPRESS=[Y o N]
Este parámetro indica cómo deberá tratarse la extensión inicial.
Con "Y", entonces toda la información se consolidará dentro de una
única extensión.
Con "N", se utilizarán los parámetros vigentes para la cláusula
STORAGE. Por defecto es "Y".
CONSISTENT=[Y o N]
Con "Y", espera a que la información que se está actualizando sea
confirmada.
Es una opción muy costosa en tiempo y recursos. Por defecto es "N".
CONSTRAINTS=[Y o N]
Indica si se exportan las restricciones de las tablas. Por defecto
es "Y".
FILE=nombre_archivo
Especifica el nombre del archivo de salida, es decir, del archivo de
exportación.
FULL=[Y o N]
Indica si se hace una exportación completa de la base de datos. Por
defecto es "N".
GRANTS=[Y o N]
Indica si se exportan los permisos (grants) de cada usuario sobre
los objetos exportados.
INDEXES=[Y o N]
Indica si se exportan los índices. Por defecto es "Y".
ROWS=[Y o N]
Indica si se exportan los datos ("Y") o solo la estructura de los
objetos ("N").
OWNER=usuarios
Es la lista de usuarios (esquemas) desde donde se realizará la
exportación. Se separan por coma.
TABLES=(tabla1, tabla2...)
Lista de tablas que se van a exportar.
Es válido cuando sólo se exporta un solo esquema de usuario.
Import
Realiza la carga de un archivo binario en la base de datos, previamente generado
con la utilidad EXPORT.
Las opciones de esta utilidad son similares a las de exportación.
Es posible obtener una lista de las opciones disponibles escribiendo:
IMP80 HELP=Y;
A continuación sólo presentaremos algunas de ellas, que no son comunes a ambos programas.
FROMUSER=usuario
Si un fichero de export contiene varios esquemas podemos seleccionar
que esquema importar.
TOUSER=usuario
Es el esquema de destino hacia donde se desean importar los objetos
desde el archivo.
IGNORE=[Y o N]
Con "Y" no se hará ninguna advertencia ni se detendrá al dar
errores.
Con "N" la importación se detendrá ante cualquier error.
TABLES=(tabla1, tabla2,...)
Es la lista de tablas que se desean importar desde el archivo.
Hints (Sugerencias al optimizador)
Los hints son parametros que pasamos a las sentencias SQL para influir en el optimizador de oracle.
SELECT /*+ HINT */ . . .
Toda consulta SELECT se ejecuta dentro del servidor en varios pasos. Para la
misma consulta, pueden existir distintos caminos para conseguir el mismo
resultados, por lo que el servidor es el responsable de decidir qué camino seguir para conseguir el mejor tiempo de respuesta.
La parte de la base de datos que se encarga de estas decisiones se llama
Optimizador. El camino seguido por el servidor para la ejecución de una consulta se
denomina "Plan de ejecución" (ver EXPLAIN PLAN).
Hay que tener en cuenta que:
1. si no es posible efectuar lo que se indica con el hint, Oracle lo ignorará,
2. los Hints fuerzan el uso del Optimizador por costes (a excepción de rule)
3. no afectan a subconsultas en la misma sentencia SQL.
Optimizador por reglas (RULE)
se basa en ciertas reglas para realizar las consultas. Por ejemplo, si se filtra por un campo
indexado, se utilizará el índice. Si la consulta contiene un ORDER BY, se utilizará un algoritmo Quick Sort, etc. No tiene en cuenta el estado actual de la base de datos, ni el número de usuarios conectados, ni la carga de datos de los objetos, etc. Es un sistema de optimización estático, no varía de un momento a otro.
Optimizador por costes (CHOOSE)
se basa en las reglas básicas, pero teniendo en cuenta el estado actual de la base
de datos. Es decir, tiene en cuenta el número de registros de las tablas, el número
de usuarios accediendo a ellas, etc. Por ejemplo, si se hace una consulta utilizando
un campo indexado, mirará primero el número de registros y si es suficientemente
grande entonces merecerá la pena acceder por el índice, si no, accederá directamente a la tabla.
Para averiguar el estado actual de la base de datos se basa en los datos del
catálogo público, por lo que es recomendable que esté lo más actualizado posible (a
través de la sentencia ANALYZE), ya que de no ser así, se pueden tomar decisiones
a partir de datos desfasados (la tabla tenía 10 registros hace un mes pero ahora tiene 10.000).
Oracle recomienda que todas las consultas se hagan por costes, aunque hay ciertos
casos en los que una consulta no se resuelve (o tarda mucho) por costes y por
reglas es inmediata. ¿Y cómo hacer que una consulta se ejecute por reglas o por
costes? Pues hay dos modos de forzar a Oracle a que utilice un optimizados.
La primera es modificando la sesión activa para que todas las consultas sean optimizadas de una manera:
ALTER SESSION SET OPTIMIZER_GOAL = [RULE|CHOOSE];
Con esto todas las consultas se ejecutarán utilizando el optimizador indicado. La otra manera es forzando a Oracle a que utilice un optimizador en una consulta concreta. Esto se hace a través de los “hits” o sugerencias. Un hint es un comentario dentro de una consulta SELECT que informa a Oracle del modo en que tiene que trazar el plan de ejecución. Los hint deben
ir junto detrás de la palabra SELECT: A continuación se muestra una lista de algunos de los hints posibles:
/*+ ALL_ROWS */ Pone la consulta a costes y la optimiza para que
consuma el menor número
de recursos posibles.
/*+ FIRST_ROWS */ Pone la consulta a costes la optimiza para
conseguir el mejor tiempo de
respuesta.
/*+ CHOOSE */ Pone la consulta a costes.
/*+ RULE */ Pone la consulta a reglas.
/*+ INDEX( tabla índice ) */ Fuerza la utilización del índice
indicado para la tabla indicada
/*+ ORDERED */ Hace que las combinaciones de las tablas se hagan en
el mismo orden en
que aparecen en el join.
Comando shutdown
Para una instancia de la base de datos.
SHUTDOWN
SHUTDOWN ABORT
SHUTDOWN IMMEDIATE
SHUTDOWN NORMAL
SHUTDOWN TRANSACTIONAL
SHUTDOWN TRANSACTIONAL LOCAL
Es necesario estar conectado como SYSDBA o SYSOPER.
Comando startup
Arranca una instancia de la base de datos.
STARTUP
STARTUP NOMOUNT
STARTUP MOUNT
STARTUP RESTRICT
STARTUP OPEN RECOVER
STARTUP PFILE = /PATH/TO/INIT.ORA
Es necesario estar conectado como SYSDBA o SYSOPER
SQL Trace (Trazas de ejecución)
Las trazas de ejecución de oracle sirven para mostrar las sentencias SQL y su plan de ejecución de programas que acceden a bases de datos Oracle.
No es necesario disponer del código fuente ni de la sentencia SQL para saber qué y cómo se ejecutado.
Básicamente, activar la traza de ejecución consiste en ejecutar un procedimiento
que tiene predefinido Oracle dentro del paquete DBMS_SYSTEM.
El procedimiento tiene la siguiente cabecera:
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid, serial#, TRUE/FALSE
);
Podemos obtener estos parámetros:
SELECT SID, SERIAL#, MACHINE, TERMINAL, PROGRAM
FROM SYS.V_$SESSION
WHERE USERNAME = 'usuario';
SID y SERIAL# definen la sesión a la cual queremos hacer con la traza. El tercer
parámetro indica si queremos activar/desactivar la traza de ejecución.
Una vez activada la traza con SET_SQL_TRACE_IN_SESSION, toda sentencia
ejecutada sobre la sesión indicada quedará registrada en un archivo .TRC,
normalmente bajo el directorio ORACLE_HOME/trace80
Este fichero contiene información detallada (pero ilegible) de los planes de
ejecución. Para conseguir una salida legible de esta traza se debe ejecutar la utilidad TKPROF.
Trazas con SQL*Plus
Otra forma de hacer trazas de ejecución es en SQL*Plus, con la instrucción:
SET AUTO TRACE ON/OFF
Una vez activada la auto-traza, toda sentencia ejecutada en SQL*Plus vendrá
acompañada de su plan y estadísticas de ejecución.
Si, además, queremos cronometrar el tiempo de ejecución de cada consulta, podemos activar el cronómetro con:
SET TIMING ON
SET
Configuración SQLPLUS.
SET TERM OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG
only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool
files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
-- HEADING = OFF will hide column headings
SET FEEDBACK OFF
-- FEEDBACK = ON will count rows returned
SET PAUSE OFF
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings
and page brks)
SET LINESIZE 80
-- LINESIZE = width of page (80 is typical)
SET VERIFY OFF
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
Tunning (optimización)
El objetivo de tunning es la puesta a punto (optimización) de las sentencias SQL, desde el punto de vista del tiempo de ejecución y consumo de recursos.
Podemos examinar el comportamiento de una sentencia SQL analizando su plan de ejecución:
1. con la sentencia EXPLAIN PLAN 2. con trazas TRACE
Para optimizar las sentencias SQL podemos: 1. Crear indices adecuados y fomentar su uso (CREATE INDEX). 2. Aplicar HINTS para modificar o influenciar las decisiones del optimizador. 3. Actualizar estadísticas periodicamente (ANALYZE).
4. Usar clausulas STORAGE adecuada en la creación de tablas (CREATE TABLE).
5. Usar EXPORT IMPORT.
6. Cuidar la programación.
En particular debemos prestar atención la sentencias SELECT que son más dadas a
la complejidad.
Indices
Respecto a los indices debemos tener en cuenta:
1. Crear índices sobre columnas lo más selectivas posibles (aquellas que reducen al máximo el espacio de búsqueda).
2. En el caso de los índices compuestos, el orden en el que se declaran estas columnas deberá ser de la más selectiva a la menos selectiva (siempre que sea posible).
3. En algunos casos es conveniente sustituir índices compuestos por varios índices
simples.
Programación
Respecto a la programación:
1. Limitar los accesos a tablas remotas.
2. Utilizar la cláusula UNION ALL en lugar de UNION siempre que sea posible.
3. Evitar el uso de llamadas a funciones PL/SQL en sentencias SQL.
4. En cambio para determinados problemas, puede ser útil el uso de procedimientos o funciones PL/SQL almacenados en la base de datos en lugar de una sentencia SQL (con un cursor p.ej.).
5. Considerar que hay distintas opciones para obtener el mismo resultado.
6. El orden de las tablas en el Join puede ser importante.
7. Se deben optimizar tambien las subconsultas.
8. Considerar en algunos casos alternativas al Join (consultas anidadas, cláusula exists subconsulta , outer-join etc...).
9. Revisar las consultas periodicamente, pueden no se ya optimas debido al constante cambio en el tamaño de las tablas, la distribución de los valores, el esquema etc....
Además
1. Gestión de las sentencias SQL que contienen vistas. Si una consulta contiene una vista, el optimizador tiene dos formas de actuar: resolver primero la vista y después la consulta o integrar la vista en el texto de la misma. Si se resuelve primero la vista, el resultado completo de la vista se determina en primer lugar y, el resto de las condiciones de la consulta se aplican como filtro. Dependiendo del
tamaño de las tablas involucradas puede resultar conveniente hacerlo de un modo u
otro. Debemos tener en cuenta que, si una vista contiene una operación de conjunto (GROUP BY, SUM, COUNT o DISTINCT), no podrá ser integrada en la consulta.
2. Considerar el uso de las bind variables: Las sentencias pueden recoger los parámetros por valor (where salario>1000) o una vez compilada la sentencia haciendo uso de Bind Variables (where salario>:b1). La ventaja de la segunda
opción es que Oracle compila un única vez la sentencia y reutiliza el código compilado para cada uno de los valores para los parámetros. En este segundo caso, Oracle no puede calcular el grado de selectividad de una consulta y, en su lugar, aplica un grado de selectividad por defecto (asociado a cada tipo de operación), lo cual puede dar lugar a decisiones "equivocadas". Por lo tanto, trabajando por costes es desaconsejable el uso de Bind Variables, salvo que trabajemos con sentencias que se van a ejecutar repetidas veces y que no ofrezcan muchas dudas en cuanto a
los posibles planes de acceso que puede generar.
3. Utilizar las cláusulas start with y connect by en el caso de consultas sobre datos
relacionados por alguna relación de herencia.
Tratamiento de usuarios y permisos para el
administrador de la base de datos (DBA)
Aqui esta definida la sintaxis de las sentencias que puede (y debe) usar el
administrador de la base de datos o DBA (Database administrator) de oracle como CREATE USER, CREATE ROLE, CREATE PROFILE, etc...
Es necesario tener clara la diferencia entre usuarios, roles, perfiles, permisos (privilegios) y limites.
Un rol es una forma de agrupar permisos (o privilegios) para asignarlos luego a los
usuarios.
Un permiso se puede asignar a un usuario o a un rol. Los permisos se asignan mediante la sentencia GRANT y se quitan mediante la sentencia REVOKE.
La forma correcta de trabajar es:
Crear uno o varios Role (CREATE ROLE)
Dar permisos de objeto y sistema al rol (GRANT ... TO mirole)
Dar el rol a los usuarios (GRANT mirole TO miusuario)
Esta forma de trabajar tiene varias ventajas:
La asignación de recursos es mas rapida.
Nuevos permisos asignados a un rol estaran inmediatamente disponibles a los
usuarios conectados.
Sin embargo los nuevos permisos asignados a un usuario necesitan de reconexión.
Excepciones:
El permiso de UNLIMITED TABLESPACE debe ser directo.
Permisos a usuarios que ejecutan trabajos en segundo plano (dbms_job.submit)
deben ser directos, cuando el trabajo comienza no hay roles activos.
Create profile
Esta sentencia sirve para crear un perfil de usuario.
Un perfil de usuario es una forma de limitar los recursos que puede utilizar un usuario.
Cada usuario puede tener un único perfil.
Antes de asignar un perfil a un usuario es necesario que este perfil exista en la
base de datos.
Un perfil se asigna en la creación de un usuario CREATE USER o modificandolo
ALTER USER.
Un ejemplo de script sería:
CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER 2 --
CPU_PER_SESSION 10000 -- decimas de segundo
CPU_PER_CALL 1 -- decimas de segundo
CONNECT_TIME UNLIMITED -- minutos
IDLE_TIME 30 -- minutos
LOGICAL_READS_PER_SESSION DEFAULT -- DB BLOCKS
LOGICAL_READS_PER_CALL DEFAULT -- DB BLOCKS
-- COMPOSITE_LIMIT DEFAULT --
PRIVATE_SGA 20M --
FAILED_LOGIN_ATTEMPTS 3 --
PASSWORD_LIFE_TIME 30 -- dias
PASSWORD_REUSE_TIME 12 --
PASSWORD_REUSE_MAX UNLIMITED --
PASSWORD_LOCK_TIME DEFAULT -- dias
PASSWORD_GRACE_TIME 2 -- dias
PASSWORD_VERIFY_FUNCTION NULL;
Los recursos que limitamos son recursos del kernel: uso de la CPU, duración de
sesion,...
Y tambien limites de uso de las claves de acceso (passwords): duración, intentos de
acceso, reuso, ...
Por ejemplo:
ALTER PROFILE default LIMIT IDLE_TIME 20;
Limita el perfil por defecto a 20 minutos. IDLE_TIME: Es el tiempo que puede estar
una sesión sin hacer nada antes de ser cerrada.
Create Role
Esta sentencia sirve para crear un rol de usuario.
Un rol es una forma de agrupar permisos (o privilegios) para asignarlos luego a los usuarios.
Cada usuario puede tener varios roles.
Ejemplo de creación de un rol:
CREATE ROLE MI_PROPIO_ROLE
Crea un rol sin password:
CREATE ROLE role NOT IDENTIFIED
Crea un rol con password:
CREATE ROLE role IDENTIFIED BY password
Crea un rol de aplicación:
CREATE ROLE role IDENTIFIED USING [schema.]package
Crea un rol basado en uno del S.O.:
ALTER ROLE role IDENTIFIED EXTERNALLY
Crea un rol basado en el servicio de directorio:
ALTER ROLE role IDENTIFIED GLOBALLY
Ejemplo para crear un script que asigna todos los permisos de actual esquema
SELECT decode(object_type,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES
ON'||&OWNER||'.',
'VIEW','GRANT SELECT ON '||&OWNER||'.',
'SEQUENCE','GRANT SELECT ON '||&OWNER||'.',
'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.',
'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.',
'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO
MI_PROPIO_ROLE ;'
FROM user_objects
WHERE
OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE',
'PACKAGE','FUNCTION')
ORDER BY OBJECT_TYPE
Create User
Esta sentencia sirve para crear un usuario oracle.
Un usuario es un nombre de acceso a la base de datos oracle. Normalmente va
asociado a una clave (password).
Lo que puede hacer un usuario una vez ha accedido a la base de datos depende de
los permisos que tenga asignados ya sea directamente (GRANT) como sobre algun rol que tenga asignado (CREATE ROLE).
El perfil que tenga asignado influye en los recursos del sistema de los que dispone un usuario a la hora de ejecutar oracle (CREATE PROFILE).
La sintaxis es:
CREATE USER username
IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS
'external_name'}
options;
Donde options:
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
QUOTA int {K | M} ON tablespace
QUOTA UNLIMITED ON tablespace
PROFILE profile_name
PASSWORD EXPIRE
ACCOUNT {LOCK|UNLOCK}
Crea un usuario sin derecho a guardar datos o crear objetos:
CREATE USER usuariolimitado IDENTIFIED BY miclavesecreta;
Crea un usuario con todos los derechos para guardar datos o crear objetos:
DROP USER miusuario CASCADE;
CREATE USER miusuario IDENTIFIED BY miclavesecreta
DEFAULT TABLESPACE data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON data;
CREATE ROLE programador;
GRANT CREATE session, CREATE table, CREATE view,
CREATE procedure,CREATE synonym,
ALTER table, ALTER view, ALTER procedure,ALTER synonym,
DROP table, DROP view, DROP procedure,DROP synonym,
TO conn;
GRANT programador TO miusuario;
Es neceario crear el usuario antes de asignar permisos con GRANT o un ROLE por
defecto.
Grant (dar permisos)
Esta sentencia sirve para dar permisos (o privilegios) a un usuario o a un rol.
Un permiso, en oracle, es un derecho a ejecutar un sentencia (system privileges) o a acceder a un objeto de otro usuario (object privileges).
El conjunto de permisos es fijo, esto quiere decir que no se pueden crear nuevos tipos de permisos.
Si un permiso se asigna a rol especial PUBLIC significa que puede ser ejecutado por
todos los usuarios.
Permisos para acceder a la base de datos (permiso de sistema):
GRANT CREATE SESSION TO miusuario;
Permisos para usuario de modificación de datos (permiso sobre objeto):
GRANT SELECT, INSERT, UPDATE, DELETE ON T_PEDIDOS TO miusuario;
Permisos de solo lectura para todos:
GRANT SELECT ON T_PEDIDOS TO PUBLIC;
Permisos de sistema (system privileges)
Los permisos de sistema mas importantes son CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE USER, ...
Sintaxis:
GRANT system_priv(s) TO {user, | role, |PUBLIC}
[IDENTIFIED BY password] [WITH ADMIN OPTION]
GRANT role TO {user, | role, |PUBLIC}
[IDENTIFIED BY password] [WITH ADMIN OPTION]
GRANT ALL PRIVILEGES TO {user, | role, |PUBLIC}
[IDENTIFIED BY password] [WITH ADMIN OPTION]
Podemos obtener la lista de permisos del sistema asi:
select * from system_privilege_map;
Los permisos de sistema son auditables.
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE
PROCEDURE,CREATE SYNONYM,
ALTER TABLE, ALTER VIEW, ALTER PROCEDURE,ALTER SYNONYM,
DROP TABLE, DROP VIEW, DROP PROCEDURE,DROP SYNONYM TO
programador;
Permisos sobre objetos (object privileges)
Los permisos sobre objetos mas importantes son: SELECT, UPDATE, INSERT,
DELETE, ALTER, DEBUG, EXECUTE, INDEX, REFERENCES
GRANT object_priv [(column, column,...)]
ON [schema.]object
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH
HIERARCHY OPTION]
GRANT ALL PRIVILEGES [(column, column,...)]
ON [schema.]object
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH
HIERARCHY OPTION]
GRANT object_priv [(column, column,...)]
ON DIRECTORY directory_name
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH
HIERARCHY OPTION]
GRANT object_priv [(column, column,...)]
ON JAVA [RE]SOURCE [schema.]object
TO {user, | role, |PUBLIC} [WITH GRANT OPTION] [WITH
HIERARCHY OPTION]
Con la opcion WITH HIERARCHY OPTION damos permisos sobre todos los
subojetos, incluso sobre los que se creen despues de ejecutar el GRANT.
Con la opción WITH GRANT OPTION damos permiso para que el que los recibe los pueda a su vez asignar a otros usuarios y roles.
La opción "GRANT ALL PRIVILEGES..." se puede escribir tambien como "GRANT ALL..."
Podemos obtener la lista de permisos de las tablas asi:
select * from all_tab_privs_made;
Notas
Los permisos del sistema pueden ser:
CREATE SESSION - Permite conectar a la base de datos
UNLIMITED TABLESPACE - Uso de espacio ilimitado del tablespace.
SELECT ANY TABLE - Consultas en tables, views, or mviews en
cualquier esquema
UPDATE ANY TABLE - Actualizar filas en tables and views en
cualquier esquema
INSERT ANY TABLE - Insertar filas en tables and views en
cualquier esquema
Permisos de administrador para CREATE, ALTER o DROP:
cluster, context, database, link, dimension, directory, index,
materialized view, operator, outline, procedure, profile, role,
rollback segment, sequence, session, synonym, table,
tablespace,
trigger, type, user, view.
Los roles predefindos son:
SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE,
IMP_FULL_DATABASE
SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE
AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - manejo de la cola
SNMPAGENT - Agente inteligente.
RECOVERY_CATALOG_OWNER - rman
HS_ADMIN_ROLE - servicios heterogeneos
mas cualquier rol de usuario que halla disponible Los roles CONNECT, RESOURCE y
DBA ya no deben usarse (aunque estan soportados).
Es posible asignar varios Object_Privs en un solo comando GRANT.
GRANT SELECT (empno), UPDATE (sal) ON pepe.tabla TO miusuario
Permisos del rol SYSDBA:
CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege
Permisos del rol SYSOPER:
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege
Cada tipo de objeto tiene su propio conjunto de permisos:
Tables: select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
Views: select, insert, update, delete, under, references, flashback, debug
Sequence: alter, select
Packeges, Procedures, Functions (Java classes, sources...): execute, debug
Materialized Views: delete, flashback, insert, select, update
Directories: read, write
Libraries:execute
User defined types: execute, debug, under
Operators: execute
Indextypes: execute
Revoke (quitar permisos)
Esta sentencia sirve para quitar permisos (o privilegios) a un usuario o a un rol.
No dejamos nada:
REVOKE ALL PRIVILEGES FROM miusuario;
Quitamos todo:
REVOKE ALL ON T_PEDIDOS FROM miusuario;
Sintaxis, quitar un rol asignado:
REVOKE role FROM {user, | role, |PUBLIC}
Quitar un permiso de sistema:
REVOKE system_priv(s) FROM {user, | role, |PUBLIC}
REVOKE ALL FROM {user, | role, |PUBLIC}
system_privs
ALTER ANY INDEX, BECOME USER, CREATE TABLE, DROP ANY VIEW
RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE
y mas...
Quitar un permiso de objeto:
REVOKE object_priv [(column1, column2..)] ON [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON [schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON DIRECTORY
directory_name
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
REVOKE object_priv [(column1, column2..)] ON JAVA [RE]SOURCE
[schema.]object
FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]
opciones:
object_privs
ALTER, DELETE, EXECUTE, INDEX, INSERT,
REFERENCES, SELECT, UPDATE, ALL PRIVILEGES
La opción FORCE, quita todos los privilegios y descompila todos sus objetos.
roles standard de Oracle:
SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE,
IMP_FULL_DATABASE
mas todos los roles definidos en la base de datos
Data Definition Language
Aqui esta definida la sintaxis de las sentencias del lenguaje de definicion de datos
(Data Definition Language o DDL) de oracle como CREATE TABLE, CREATE INDEX, CREATE USER, etc...,
Alter table
Sirve para cambiar la definición de una tabla. Podemos cambiar tanto columnas
como restricciones (ver CONSTRAINTS).
La sintaxis es:
ALTER TABLE [esquema.]tabla {ADD|MODIFY|DROP}...
Añadir una columna a una tabla:
ALTER TABLE T_PEDIDOS ADD TEXTOPEDIDO Varchar2(35);
Cambiar el tamaño de una columna en una tabla:
ALTER TABLE T_PEDIDOS MODIFY TEXTOPEDIDO Varchar2(135);
Hacer NOT NULL una columna en una tabla:
ALTER TABLE T_PEDIDOS MODIFY (TEXTOPEDIDO NOT NULL);
Eliminar una columna a una tabla:
ALTER TABLE T_PEDIDOS DROP COLUMN TEXTOPEDIDO;
Valor por defecto de una columna:
ALTER TABLE T_PEDIDOS MODIFY TEXTOPEDIDO Varchar2(135) DEFAULT
'ABC...';
Añade dos columnas:
ALTER TABLE T_PEDIDOS
ADD (SO_PEDIDOS_ID INT, TEXTOPEDIDO Varchar2(135));
Comment
Pone un comentario en le diccionario de datos.
Sintaxis para tablas y vistas:
COMMENT ON TABLE [esquema.]tabla IS 'comentario';
COMMENT ON TABLE [esquema.]vista IS 'comentario';
COMMENT ON TABLE [esquema.]vista_materilizada IS 'comentario';
Sintaxis para columnas:
COMMENT ON COLUMN [esquema.]tabla.columna IS 'comentario';
COMMENT ON COLUMN [esquema.]vista.columna IS 'comentario';
COMMENT ON COLUMN [esquema.]vista_materilizada.columna IS
'comentario';
Ejemplo de tabla y columna:
COMMENT ON TABLE T_PRODUCTOS IS 'Tabla de productos';
COMMENT ON COLUMN T_PRODUCTOS.numproduct IS 'Codigo de 6 digitos del
producto';
COMMENT ON COLUMN T_PRODUCTOS.desproduct IS 'Descripcion del
producto';
Para borrar un comentario hay que ponerle la cadena vacia ''.
COMMENT ON TABLE T_PRODUCTOS IS '';
COMMENT ON COLUMN T_PRODUCTOS.numproduct IS '';
Constraints
Para cambiar las restricciones y la clave primaria de una tabla debemos usar ALTER TABLE.
Crear una clave primaria (primary key):
ALTER TABLE T_PEDIDOS ADD CONSTRAINT PK_PEDIDOS
PRIMARY KEY (numpedido,lineapedido);
Crear una clave externa, para integridad referencial (foreign key):
ALTER TABLE T_PEDIDOS ADD CONSTRAINT FK_PEDIDOS_CLIENTES
FOREIGN KEY (codcliente) REFERENCES T_CLIENTES (codcliente));
Crear un control de valores (check constraint):
ALTER TABLE T_PEDIDOS ADD CONSTRAINT CK_ESTADO
CHECK (estado IN (1,2,3));
Crear una restricción UNIQUE:
ALTER TABLE T_PEDIDOS ADD CONSTRAINT UK_ESTADO
UNIQUE (correosid);
Normalmente una restricción de este tipo se implementa mediante un indice unico
(ver CREATE INDEX).
Borrar una restricción:
ALTER TABLE T_PEDIDOS DROP CONSTRAINT CON1_PEDIDOS;
Deshabilita una restricción:
ALTER TABLE T_PEDIDOS DISABLE CONSTRAINT CON1_PEDIDOS;
habilita una restricción:
ALTER TABLE T_PEDIDOS ENABLE CONSTRAINT CON1_PEDIDOS;
la sintaxis ALTER TABLE para restricciones es:
ALTER TABLE [esquema.]tabla
constraint_clause,...
[ENABLE enable_clause | DISABLE disable_clause]
[{ENABLE|DISABLE} TABLE LOCK]
[{ENABLE|DISABLE} ALL TRIGGERS];
donde constraint_clause puede ser alguna de las siguientes entradas:
ADD out_of_line_constraint(s)
ADD out_of_line_referential_constraint
DROP PRIMARY KEY [CASCADE] [{KEEP|DROP} INDEX]
DROP UNIQUE (column,...) [{KEEP|DROP} INDEX]
DROP CONSTRAINT constraint [CASCADE]
MODIFY CONSTRAINT constraint constrnt_state
MODIFY PRIMARY KEY constrnt_state
MODIFY UNIQUE (column,...) constrnt_state
RENAME CONSTRAINT constraint TO new_name
donde a su vez constrnt_state puede ser:
[[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
[RELY | NORELY] [USING INDEX using_index_clause]
[ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
[EXCEPTIONS INTO [schema.]table]
Borrar una restricción:
ALTER TABLE T_PEDIDOS DROP CONSTRAINT CON1_PEDIDOS;
Create Database
Sirve para crea una base de datos.
Desde el punto de vista físico, una base de datos es, para oracle, un conjunto de ficheros, a saber:
datafiles, ficheros de datos, definidos en la creación de la base de datos.
log files, ficheros de log, definidos tambien en la creación de la base de datos.
init.ora, fichero de texto que contiene los parámetros de configuración de la base de datos.
control files, ficheros de control, definidos en el init.ora
password file, fichero con la password del BDA y los operadores (todos los demas
usuarios estan definidos en tablas).
Asi para crear una base de datos, una vez instalado oracle, debemos seguir los siguientes pasos:
1) Definir ORACLE_SID
ORACLE_HOME = E:\Oracle\Product\10.0.0
ORACLE_SID = GESTION
2) Crear el fichero INIT.ORA
C:\>ORACLE_HOME\database\initGESTION.ora
control_files =
(/path/to/control1.ctl,/path/to/control2.ctl,/path/to/control3.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = GESTION
db_block_size = 8192
sga_max_size = 1073741824
sga_target = 1073741824
3) Definir fichero de passwords
$ORACLE_HOME\bin\orapwd file=ORACLE_HOME\database\pwdGESTION.ora
password=oracle entries=10
Podemos generar los pasos 2) y 3) con una sola instrucción:
oradim -new -sid GESTION -intpwd -maxusers 20 -startmode auto
-pfile E:\Oracle\Product\10.0.0\Database\initGESTION.ora
4) Arrancar la instancia
C:\>sqlplus / as sysdba
sql> startup nomount
5) Crea la base de datos con el nombre(o SID) GESTION y el char set WE8ISO8859P1
CREATE DATABASE GESTION
LOGFILE 'E:\OraData\GESTION\LOG1GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG2GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG3GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG4GESTION.ORA' SIZE 2M,
'E:\OraData\GESTION\LOG5GESTION.ORA' SIZE 2M
EXTENT MANAGEMENT LOCAL
MAXDATAFILES 100
DATAFILE 'E:\OraData\GESTION\SYS1GESTION.ORA' SIZE 50 M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
'E:\OraData\GESTION\TEMP.ORA' SIZE 50 M
UNDO TABLESPACE undo DATAFILE 'E:\OraData\GESTION\UNDO.ORA' SIZE 50
M
NOARCHIVELOG
CHARACTER SET WE8ISO8859P1;
6) Ejecutar sql de creación: catalog.sql y catproc.sql
Sintaxis completa:
CREATE DATABASE nombreDB opciones
Donde las opciones:
DATAFILE filespec AUTOEXTEND OFF
DATAFILE filespec AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K |
M]
MAXDATAFILES int
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tablespace [TEMPFILE filespec]
[EXTENT MANAGEMENT LOCAL]
[UNIFORM [SIZE int K | M]]
UNDO TABLESPACE tablespace [DATAFILE filespec]
LOGFILE [GROUP int] filespec
MAXLOGFILES int
MAXLOGMEMBERS int
MAXLOGHISTORY int
MAXINSTANCES int
ARCHIVELOG | NOARCHIVELOG
CONTROLFILE REUSE
CHARACTER SET charset
NATIONAL CHARACTER SET charset
SET TIMEZONE = 'time_zone_region'
SET TIMEZONE = '{+|-} hh:mm'
FORCE LOGGING
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password
Se puede poner mas de un DATAFILE o LOGFILE separando los nombres de fichero
con comas DATAFILE filespec1, filespec2, filespec3
Si no se especifican claves, Oracle establece "change_on_install" para SYS y "manager" para SYSTEM.
Despues de crear la base de datos podemos cambiar entre los modos ARCHIVELOG, NOARCHIVELOG con la sentencia ALTER DATABASE.
Global Temporary Tables
Crea una tabla temporal personal para cada sesion. Eso significa que los datos no
se comparten entre sesiones y se eliminan al final de la misma.
CREATE GLOBAL TEMPORARY TABLE tabla_temp (
columna datatype [DEFAULT expr] [column_constraint(s)]
[,columna datatype [,...]]
) {ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS};
Con la opcion ON COMMIT DELETE ROWS se borran los datos cada vez que se hace
COMMIT en la sesion.
Con la opcion ON PRESERVE DELETE ROWS los datos no se borran hasta el final de la sesion.
Create Index
Los indices se usan para mejorar el rendimiento de las operaciones sobre una tabla.
En general mejoran el rendimiento las SELECT y empeoran (minimamente) el
rendimiento de los INSERT y los DELETE.
Una vez creados no es necesario nada más, oracle los usa cuando es posible (ver EXPLAIN PLAN).
En oracle existen tres tipos de indices:
1)Table Index:
CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
ON [esquema.]table_name [tbl_alias]
(col [ASC | DESC]) index_clause index_attribs
2)Bitmap Join Index:
CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
ON [esquema.]table_name [tbl_alias]
(col_expression [ASC | DESC])
FROM [esquema.]table_name [tbl_alias]
WHERE condition [index_clause] index_attribs
3)Cluster Index:
CREATE [UNIQUE|BITMAP] INDEX [esquema.]index_name
ON CLUSTER [esquema.]cluster_name index_attribs
las index_clauses posibles son:
LOCAL STORE IN (tablespace)
LOCAL STORE IN (tablespace)
(PARTITION [partition
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause]
[STORE IN {tablespace_name|DEFAULT]
[SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
LOCAL (PARTITION [partition
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause]
[STORE IN {tablespace_name|DEFAULT]
[SUBPARTITION [subpartition [TABLESPACE tablespace]]]])
GLOBAL PARTITION BY RANGE (col_list)
( PARTITION partition VALUES LESS THAN (value_list)
[LOGGING|NOLOGGING]
[TABLESPACE {tablespace|DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause] )
INDEXTYPE IS indextype [PARALLEL int|NOPARALLEL] [PARAMETERS
('ODCI_Params')]
{Esto es solo para table index, no para bitmap join Index}
Y además index_attribs puede ser cualquier combinación de los siguientes:
NOSORT|SORT
REVERSE
COMPRESS int
NOCOMPRESS
COMPUTE STATISTICS
[NO]LOGGING
ONLINE
TABLESPACE {tablespace|DEFAULT}
PCTFREE int
PCTUSED int
INITRANS int
MAXTRANS int
STORAGE storage_clause
PARALLEL parallel_clause
Si usamos la opcion PARALLEL esta debe estar al final.
create index es una de las pocas sentencias que pueden usar nologging option.
create index requiere un segmento temporal si no hay espacio en memoria
suficiente.
Crear indices basados en funciones require que query_rewrite_enabled este a true y query_rewrite_integrity este a trusted.
Un ejemplo de indices basados en funciones para busquedas en mayusculas:
CREATE INDEX idx_case_ins ON my_table(UPPER(empname));
SELECT * FROM my_table WHERE UPPER(empname) = 'KARL';
Create Rollback Segment
Crea un Rollback Segment (RBS)
CREATE [PUBLIC] ROLLBACK SEGMENT rbs_name options
Opciones:
TABLESPACE tablespace_name
STORAGE storage_clause
Un RBS publico esta disponible para mas de una instancia.
Create Sequence
Crea un objeto capaz de darnos numeros consecutivos unicos.
CREATE SEQUENCE secuencia
INCREMENT BY n
START WITH n
{MAX VALUE n | NOMAXVALUE}
{MIN VALUE N | NOMINVALUE}
{CYCLE | NOCYCLE}
{CACHE N | NOCACHE}
{ORDER | NOORDER};
En realida es un generador de indentificadores unicos que no bloquea
transacciones.
Es muy util para generar primary keys.
Si no nos gusta perder números usamos NOCACHE.
CREATE SEQUENCE S_PROVEEDORES MINVALUE 1 START WITH 1
INCREMENT BY 1 NOCACHE;
Si nos interesa la velocidad:
CREATE SEQUENCE S_PROVEEDORES MINVALUE 1 START WITH 1
INCREMENT BY 1 CACHE 20;
Asi obtenemos el siguiente valor:
SELECT S_PROVEEDORES.NEXTVAL FROM DUAL;
Tambien podemos obtener el valor actual:
SELECT S_PROVEEDORES.CURRVAL FROM DUAL;
Create Synonym
Crea un sinonimo para algun objeto de la base de datos.
CREATE [OR REPLACE] [PUBLIC] SYNONYM [esquema.]sinonimo
FOR [esquema.]objeto [@dblink]
Con la opción 'PUBLIC' se crea un sinonimo público accesible a todos los usuarios,
siempre que tengan los privilegios adecuados para el mismo. (ver GRANT)
Sirve para no tener que usar la notación 'esquema.objeto' para referirse a un
objeto que no es propiedad de usuario.
CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS;
No es necesario recompilarlos cuando se redefinen las tablas, de hecho puedes existir sin que exista el objeto al que refererencian.
El acceso es un poco mas eficiente cuando se accede por sinonimos públicos.
Cuando en una sentencia no citamos el nombre del esquema, Oracle resuelve los
nombres en el siguiente orden:
usuario actual
private synonym
public synonym
Tambien podemos usarlo para cambiar el objeto que usamos sin tener que cambiar
la programacion.
Asi cambiamos la tabla:
CREATE PUBLIC SYNONYM T_PEDIDOS FOR PROGRAMADOR.T_PEDIDOS_PRUEBA;
Create Table
La estructura de la sentencia de creación de tablas es:
CREATE [GLOBAL TEMPORARY] TABLE [esquema.]tabla
columna datatype [DEFAULT expr] [column_constraint(s)]
[,columna datatype [,...]]
table_constraint
table_ref_constraint
[ON COMMIT {DELETE|PRESERVE} ROWS]
storage_options [COMPRESS int|NOCOMPRESS]
[LOB_storage_clause][varray_clause][nested_storage_clause]
[XML_type_clause]
Partitioning_clause
[[NO]CACHE] [[NO]ROWDEPENDENCIES] [[NO]MONITORING] [PARALLEL
parallel_clause]
[ENABLE enable_clause | DISABLE disable_clause]
{ENABLE|DISABLE} ROW MOVEMENT
[AS subquery]
Queda mas claro con unos ejemplos:
create table T_PRODUCTOS (
numproduct number,
desproduct varchar2(10)
)
Es posible definir restricciones (constraint) con la sentencia CREATE.
create table T_PEDIDOS(
numpedido number primary key
fecpedido date,
numclient references T_CLIENTES
)
Una clave primaria (primary key) necesita tener asociado un indice unico (unique index). Es
posible especificar el tablespace donde queremos crear el indice.
create table T_PEDIDOS(
numpedido number primary key
fecpedido date,
numclient references T_CLIENTES
constraint pk_pedidos (numpedido) using index tablespace ts_idx
)
Index organized tables (IOT)
create table iot_ (
a number,
b varchar2(10),
constraint pk_iot_ primary key (a, b)
)
organization index;
Organization external The following create table statement creates an external table.
create table (....)
organization external (
type oracle_loader
default directory some_dir
access parameters (
records delimited by newline
fields terminated by ','
missing field are values null
)
location ('some_file.csv')
)
reject limit unlimited;
Nested tables
create or replace type item as object (
item_id Number ( 6 ),
descr varchar2(30 ),
quant Number ( 4,2)
);
/
create or replace type items as table of item;
/
create table bag_with_items (
bag_id number(7) primary key,
bag_name varchar2(30) not null,
the_items_in_the_bag items
)
nested table the_items_in_the_bag store as bag_items_nt;
Adding a unique constraint for item_id:
alter table bag_items_nt add constraint uq_item_id unique(item_id);
Create Tablespace
Sirve para crear un tablespace.
Un tablespace es una unidad lógica de almacenamiento dentro de una base de datos oracle.
Es un puente entre el sistema de ficheros del sistema operativo y la base de datos.
Cada tablespace se compone de, al menos, un datafile y un datafile solo puede pertenecer a un tablespace.
Cada tabla o indice de oracle pertenece a un tablespace, es decir cuando se crea una tabla o indice se crea en un tablespace determinado.
Sintaxis:
CREATE [UNDO] TABLESPACE tablespace_name
DATAFILE Datafile_Options Storage_Options ;
Datafile_Options:
'filespec' [AUTOEXTEND OFF]
'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]]
La opción Autoextend Maxsize es por defecto UNLIMITED si no se especifica valor.
Storage_Options:
DEFAULT [COMPRESS|NOCOMPRESS] STORAGE storage_clause
MINIMUM EXTENT int {K|M}
BLOCKSIZE int K
LOGGING | NOLOGGING
FORCE LOGGING
ONLINE | OFFLINE
PERMANENT | TEMPORARY
EXTENT MANAGEMENT {DICTIONARY |
LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }
SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}
Create View
Esta sentencia sirve para crear una vista de una tabla o tablas.
Una vista es una tabla lógica basada en los datos de otra tabla.
Ejemplo:
CREATE VIEW V_PEDIDOS (NUMPEDIDO, FECPEDIDO, NUNCLIENTE, NOMCLIENTE)
FROM
SELECT A.NUMPEDIDO,A.FECPEDIDO,A.NUMCLIENTE, B.NOMCLIENTE
FROM T_PEDIDOS A, T_CLIENTE B
WHERE A.NUMCLIENTE=B.NUMCLIENTE;
Esta vista sacará lo datos de los pedidos con el nombre de cliente.
Al ser lógica no necesita espacio de almacenamiento para los datos. Ademas es
instantanea, una vez modificados los datos de las tablas origen, los tenemos
disponibles en la vista.
Tipos de datos (datatypes)
Para obtener la definición de datos de una tabla podemos ejecutar:
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DATA_SCALE
FROM COLS
WHERE TABLE_NAME = 'T_PEDIDOS';
En la definición de las columnas de las tablas podemos usar los siguientes tipos de
datos:
CHAR(n)
Cadena de caracteres de longitud fija, tiene un tamaño n bytes.
Si no se especifica n la ORACLE le da un tamaño de 255 bytes.
El tamaño máximo en BD es 2000 bytes y el mínimo 1 byte.
El tamaño máximo en PL/SQL es 32767 bytes y el minimo 1 byte.
CHARACTER es sinonimo de CHAR. Ver NCHAR.
VARCHAR2(n)
Cadena de caracteres de longitud variable, tiene un tamaño máximo de n bytes.
Es obligatorio especificar el tamaño.
El tamaño máximo en BD es 4000 bytes y el mínimo 1 byte.
El tamaño máximo en PL/SQL es 32767 bytes y el minimo 1 byte.
STRING y VARCHAR son sinonimos de VARCHAR2. Ver NVARCHAR2.
Usando VARCHAR2 en lugar de CHAR ahorramos espacio de almamcenamiento.
Un char(10) almacenará 'PEPE '
Un varchar2(10) almacenará 'PEPE'
En contra tiene que si se escriben muchas veces hay que hacer un mayor esfuerzo
de matenimiento del sistema para mantener la eficiencia (compactar).
NUMBER(p,s)
Número de p digitos de los cuales s son decimales.
No es obligatorio especificar el tamaño.
El tamaño de p va de 1 a 38 y el s desde -84 a 127.
El tamaño en PL/SQL 1E-130 .. 10E125.
Sinonimos:
numeros de coma fija: DEC,DECIMAL,NUMERIC
enteros:INTEGER (sinonimo de NUMBER(38)),INT,SMALLINT
coma flotante:DOUBLE PRECISION FLOAT REAL.
Ver tambien: PLS_INTEGER, BINARY_INTEGER
El valor 7,456,123.89 se almacenará como:
NUMBER(9) 7456124
NUMBER(9,1) 7456123.9
NUMBER(*,1) 7456123.9
NUMBER(9,2) 7456123.89
NUMBER(6) [error]
NUMBER(7,-2) 7456100
NUMBER 7456123.89
FLOAT 7456123.89
FLOAT(12) 7456000.0
DATE
Fecha válida.
Desde el 1 de enero del 4712 AC hasta el 31 de diciembre del 9999 DC. (en Oracle7
= 4712 DC)
LONG
Cadena de caracteres de longitud variable. Es una versión más grande de VARCHAR2.
El tamaño máximo en BD es 2 Gigabytes.
CLOB
Cadena de caracteres de longitud variable. Es una versión más grande de VARCHAR2.
El tamaño máximo en BD es 4 Gigabytes. Ver NCLOB.
Es recomendable usar CLOB o BLOB en lugar de LONG.
BLOB
Objeto binario de longitud variable. Es una versión más grande de RAW.
El tamaño máximo en BD es 4 Gigabytes.
BFILE
Puntero a un fichero en disco.
El tamaño máximo en BD es 4 Gigabytes.
TIMESTAMP (f)
El timestamp es un fecha que contiene un granularidad superior al tipo DATE, eso
significa que contiene fracciones de segundo.
Con f definimos el numero de digitos que queremos en la fracción de segundo. Asi, f puedes valer desde 0 hasta 9, el valor por defecto es 6.
SELECT SYSTIMESTAMP FROM DUAL;
Podemos usar WITH {LOCAL} TIMEZONE para grabar con el desplazamiento de
hora local.
INTERVAL YEAR (y) TO MONTH
Periodo de tiempo definido en años y meses donde y es el número de digitos del año. Puede valer de 0 a 9. (por defecto = 2)
INTERVAL DAY (d) TO SECOND (f)
Es un periodo de tiempo definido en dias, horas, minutos y segundos. d es el
máximo numero de digitos en el dia f es el máximo numero de digitos en el campo de segundos. d va de 0 a 9. (por defecto = 2) fva de 0 a 9. (por defecto = 6)
ROWID
Cadena hexadecimal que representa de forma única una fila en una tabla (pero no unica en
cualquier tabla). Ver función ROWID.
UROWID
Cadena hexadecimal que representa de forma única una fila ORDENADA en una tabla (pero no unica en cualquier tabla). Ver función ROWID.
RAW(n)
Objeto binario de longitud variable.
Es obligatorio especificar el tamaño.
El tamaño máximo en BD es 2000 bytes y el mínimo 1 byte.
El tamaño máximo en PL/SQL es 32767 bytes y el minimo 1 byte.
LONG RAW
Objeto binario de longitud variable.
El tamaño máximo en BD es 2 Gigabytes.
El tamaño máximo en PL/SQL es 32767 bytes y el minimo 1 byte.
RENAME
Cambia el nombre a una tabla, vista, secuencia o sinonimo privado.
RENAME old TO new
Para columnas de tablas:
CREATE TABLE tmp AS SELECT MyColOld MyColNew, col2, col3 FROM
MY_TABLE;
DROP TABLE MY_TABLE;
RENAME tmp TO MY_TABLE;
Tambien se puede:
ALTER TABLE MODIFY COLUMN...
ALTER TABLE DROP COLUMN...
ALTER TABLE SET UNUSED COLUMN...
Storage clause
Configuración del almacenamiento de tablas (CREATE TABLE), indices (CREATE INDEX),
etc... en oracle.
STORAGE opciones
opciones:
INITIAL int K | M
NEXT int K | M
MINEXTENTS int
MAXEXTENTS int
MAXEXTENTS UNLIMITED
PCTINCREASE int
FREELISTS int
FREELIST GROUPS int
OPTIMAL
OPTIMAL int K | M
OPTIMAL NULL
BUFFER POOL {KEEP|RECYCLE|DEFAULT}
storage (
initial 65536
next 1048576
minextents 1
maxextents 2147483645
pctincrease 0
freelists 1
freelist groups 1
optimal 7k
buffer_pool default
)
Esta clausula aparece al final de la definición de los objetos de almacenamiento de la base de datos (tablas, indices, etc...).
Cuando creamos un tablespace (CREATE TABLESPACE) podemos definir un storage
por defecto para los objetos que se creen dentro de el.
However, a default storage clause can not be specified for locally managed tablespaces.
Dictionary managed tablespaces allow to have a storage clause, but without freelists, freelist
groups and buffer_pool.
initial
Especifica el tamaño (en bytes) de la primera extensión.
next
Especifica el tamaño (en bytes) de la segunda extensión.
pctincrease
Especifica el % de incremento en el tamaño de las siguientes extensiones.
Especifica el incremento en el tamaño de las siguientes extensiones. El tamaño de
una nueva extension es el tamaño de la anterior multiplico por pctincrease. Debe ser 0 para reducir la fragmentación en los tablespaces.
minextents
Especifica el numero inicial de extensiones cuando se crea el objeto.
maxextents
Especifica el numero máximo de extensiones que el objeto puede tener.
freelists
Especifica el numero de freelists. Este parámetro solo se puede usar con CREATE
TABLE or CREATE INDEX.
freelist groups
Especifica el numero de freelist groups. Este parámetro solo se puede usar con
CREATE TABLE or CREATE INDEX.
buffer_pool
El valor de buffer_pool debe ser uno de: keep, recycle, default. Este parámetro solo
se puede usar con CREATE TABLE, CREATE INDEX, CREATE CLUSTER, ALTER TABLE, ALTER INDEX Y ALTER CLUSTER.
optimal
Solo se puede especificar para los rollback segments.
Data Manipulation Language
Aqui estan definidas las sentencias del lenguaje de manipulación de datos (Data
Manipulation Language o DML) de oracle, como SELECT, UPDATE, INSERT, DELETE, etc...
COMMIT
Guarda los cambios de la transacción en curso.
Libera los recursos bloqueados por cualquier actualización hecha con la transacción actual (LOCK TABLE).
COMMIT [WORK] [COMMENT 'comment_text']
COMMIT [WORK] [FORCE 'force_text' [,int] ]
Si ejecutamos:
DELETE FROM T_PEDIDOS WHERE COD_PEDIDO=15;
COMMIT;
Borrar un registro y guarda los cambios.
FORCE ....
Sentencia DELETE
Borra una o más filas de una tabla, dependiendo de la condición WHERE.
Para guardar cambios hay que ejecutar COMMIT;
Para cancelar el borrado podemos hacer ROLLBACK;
La sintaxis es la siguiente:
DELETE FROM nombre-tabla
[WHERE condición]
CUIDADO! Si no se pone condición de selección, borra todas las filas de la tabla.
Si ejecutamos:
DELETE FROM T_PEDIDOS;
Borrar toda la tabla.
Si ejecutamos: Borrar un registro.
DELETE FROM T_PEDIDOS WHERE COD_PEDIDO=15;
Sentencia INSERT
Añade filas a una tabla.
Para guardar los datos insertados hay que ejecutar COMMIT;
Para cancelar la insercción podemos hacer ROLLBACK;
Un formato posible es:
INSERT INTO nombre-tabla
VALUES (serie de valores)
El orden en el que se asignen los valores en la cláusula VALUES tiene que coincidir
con el orden en que se definieron las columnas en la creación del objeto tabla, dado que los valores se asignan por posicionamiento relativo.
Por ejemplo:
INSERT INTO T_PEDIDOS
VALUES (125,2,'PEPE');
Otra forma de usar la sentecia INSERT es:
INSERT INTO nombre-tabla (columna1, columna2.....)
VALUES (valor1, valor2....)
En este caso los valores se asignarán a cada una de las columnas mencionadas por posicionamiento relativo.
Es necesario que por lo menos se asignen valores a todas aquellas columnas que no admiten valores nulos en la tabla (NOT NULL).
Por ejemplo:
INSERT INTO T_PEDIDOS (CODPEDIDO,ESTADO)
VALUES (125,2);
Sentencia INSERT de múltiples filas
Para insertar un subconjunto de filas de una tabla en otra se escribe una sentencia INSERT con una SUBSELECT interna. Los formatos posibles son:
INSERT INTO nombre-tabla (columna1, columna2.....)
SELECT ([sentencia Select])
Asigna a las columnas los valores recuperados en la sentencia Select. Inserta en la
tabla todas las filas que se recuperen en la Select.
Por ejemplo:
INSERT INTO T_PEDIDOS (CODPEDIDO,ESTADO,NOMBRE)
SELECT CODPEDIDO+100,ESTADO,NOMBRE FROM T_PEDIDOS WHERE CODPEDIDO IN
(1,2,3);
Otra forma es:
INSERT INTO nombre-tabla SELECT * FROM nombre-tabla-fuente
En este caso las estructuras de las tablas tienen que ser iguales.
Este ejemplo copia hasta el pedido 100 en otra tabla:
INSERT INTO T_PEDIDOS_BAK
SELECT * FROM T_PEDIDOS WHERE CODPEDIDO<100;
Ambas tablas son iguales.
LOCK TABLE
Bloquea una tabla o vista.
LOCK TABLE [esquema.] table [opciones] IN lockmode MODE [NOWAIT]
LOCK TABLE [esquema.] view [opciones] IN lockmode MODE [NOWAIT]
donde opciones: PARTITION, SUBPARTITION, @dblink
donde lockmodes: EXCLUSIVE, SHARE, ROW EXCLUSIVE, SHARE ROW
EXCLUSIVE
ROW SHARE* | SHARE UPDATE*
Si no ponemos NOWAIT Oracle esperará hasta que la tabla este disponible.
Se pueden bloquear varias tablas en un solo comando si las escribimos separadas por comas.
LOCK TABLE tabla1,tabla2,tabla3 IN ROW EXCLUSIVE MODE;
Bloqueos por defecto
Las sentencias del DML pueden producir bloqueos sobre las filas de la tabla:
Una sentencia SELECT normal no bloquea filas.
Las sentencias INSERT, UPDATE o DELETE realiza un bloqueo ROW EXCLUSIVE de
las filas afectadas por el WHERE.
La sentencia SELECT ... FOR UPDATE NOWAIT realiza un bloqueo ROW EXCLUSIVE de las filas afectadas por el WHERE.
Las sentencias COMMIT y ROLLBACK desbloquean las filas bloqueadas anteriormente dentro de la transacción actual.
Aunque una fila este bloqueada (por otra transacción), siempre podemos hacer una SELECT sobre esa fila. Los valores retornados son los anteriores al bloqueo.
Las sentencias UPDATE Y DELETE pueden provocar o sufrir esperas si hay conflictos con otra
transacción.
Esta relacionado con el error ORA-00054.
Bloqueos Multiples en las mismas filas con LOCK TABLE
Ademas se pueden ejecutar distintos tipos de bloqueo:
ROW SHARE = Permite hacer bloqueos de tipo ROW EXCLUSIVE o ROW SHARE
o SHARE
sobre las filas bloqueadas.
ROW EXCLUSIVE = Permite hacer bloqueos de tipo ROW EXCLUSIVE o ROW
SHARE
sobre las filas bloqueadas.
SHARE ROW EXCLUSIVE = Permite hacer bloqueos de tipo ROW SHARE sobre
las filas bloqueadas.
SHARE = Permite hacer bloqueos de tipo ROW SHARE or SHARE sobre las
filas bloqueadas.
EXCLUSIVE = Solo permite hacer SELECT sobre las filas bloqueadas.
ROLLBACK
Deshace los cambios de la transacción en curso.
Libera los recursos bloqueados por cualquier actualización hecha con la transacción actual
(LOCK TABLE).
ROLLBACK [WORK] [TO [SAVEPOINT]'savepoint_text_identifier'];
ROLLBACK [WORK] [FORCE 'force_text'];
Si ejecutamos:
DELETE FROM T_PEDIDOS WHERE COD_PEDIDO=15;
COMMIT;
Borrar un registro pero cancela los cambios. Queda como si no hubiesemos hecho
nada.
SAVEPOINT
Sirve para marca un punto de referencia en la transacción para hacer un ROLLBACK parcial.
SAVEPOINT identificador;
Un ejemplo de uso es:
UPDATE T_PEDIDOS
SET NOMBRE='jorge'
WHERE CODPEDIDO=125;
SAVEPOINT solouno;
UPDATE T_PEDIDOS
SET NOMBRE = 'jorge';
SAVEPOINT todos;
SELECT * FROM T_PEDIDOS;
ROLLBACK TO SAVEPOINT todos;
COMMIT;
Solo guardamos la primera modificación.
Sentencia SELECT
La selección sobre una tabla consiste en elegir un subconjunto de filas que cumplan (o no)
algunas condiciones determinadas. La sintaxis de una sentencia de este tipo es la siguiente:
SELECT */ columna1, columna2,....
FROM nombre-tabla
[WHERE condición]
[GROUP BY columna1, columna2.... ]
[HAVING condición-selección-grupos ]
[ORDER BY columna1 [DESC], columna2 [DESC]... ]
Si ejecutamos:
SELECT * FROM T_PEDIDOS;
Nos da la salida:
COD_PEDIDO NOMBRE ESTADO
-------------------------------------------------
1 JUAN 0
2 ANTONIO 1
3 PEPE 0
...
* / columna1, columna2,.... Si se escribe *, selecciona todas las columnas. Si se desea
seleccionar sólo algunas columnas de la tabla, se debe poner los nombres de cada una de
ellas, separadas por una coma.
nombre-tabla Nombre de la(s) tabla(s) de la(s) cual(es) se van a seleccionar los
valores.
GROUP BY columna1, columna2....
Se utiliza para agrupar resultados por una determinada columna, específicamente cuando se
utilizan funciones de columna y los resultados se desean obtener por grupos (SQL lanza un
sort para generar los grupos).
HAVING condición-selección-grupos
Se utiliza con la cláusula “GROUP BY”, cuando se quiere poner condiciones al resultado de un
grupo.
ORDER BY colum1 [DESC], colum2 [DESC...]
Sirve para ordenar el resultado. Todas las columnas por las que se desee realizar el orden
tienen que encontrarse en la sentencia “Select” de la consulta. El orden de las columnas
puede ser ascendente, (por omisión, ASC), o descendente, (DESC).
SENTENCIA SELECT (JOIN)
Consiste en la unión de campos de dos o más tablas. Dichas tablas tendrán por lo menos una
columna común que sirva de nexo del join.
SELECT columna1, columna2,...
FROM nombre-tabla1, nombre-tabla2
columna1, columna2,... Para diferenciar las columnas con el mismo nombre se antepondrá el
nombre de la tabla a la que pertenecen, utilizando el punto como separador. Por ejemplo:
SELECT Tabla1.Columna2, Tabla2.Columna2, Columna3.....
FROM Tabla1, Tabla2
WHERE Tabla1.Columna1 = Tabla2.Columna1
La Columna1 de cada una de las tablas respectivas son las columnas
de nexo o columnas de join.
SENTENCIA SELECT DISTINCT
Recupera las filas de una tabla eliminando los valores de la columna duplicados.
SELECT DISTINCT columna1, columna2,....
FROM nombre-tabla1, nombre-tabla2
[GROUP BY columna1, columna2....]
[HAVING condición-selección-grupos]
[ORDER BY columna1 [DESC], columna2 [DESC]...
SENTENCIA SELECT TOP N FILAS DE UNA TABLA
En Oracle8i podemos usar la sintaxis siguiente, con una cláusula ORDER BY, para elegir filas
con los valores máximos o mínimos de un campo:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
FUNCIONES SOBRE COLUMNAS
COUNT. Indica el número de filas que cumplen una determinada condición, o el número de
valores diferentes que posee una columna.
COUNT(*) o COUNT(DISTINCT columna)
SUM. Suma los valores de una columna.
SUM(columna)
AVG. Entrega la media de los valores de una columna.
AVG(columna)
MIN. Entrega el valor mínimo de una columna.
MIN(columna)
MAX. Entrega el valor máximo de una columna.
MAX(columna)
SUBSELECTS
Permite realizar comparaciones con valores obtenidos en otra sentencia select anidada, a la
que se denomina “Subselect” o “Subselect interna”.
SELECT columna1>, columna2,....
FROM nombre-tabla1, nombre-tabla2
WHERE columna1 = (SELECT columna1
FROM nombre-tabla1, nombre-tabla2
WHERE condición)
(Cuando en la condición se pone el operador =, la subselect deberá recuperar un
sólo registro).
Sentencia TRUNCATE
Borrar todas las filas de una tabla o cluster.
TRUNCATE TABLE [esquema.]tabla
[{PRESERVE|PURGE} MATERIALIZED VIEW LOG]
[{DROP | REUSE} STORAGE]
TRUNCATE CLUSTER [esquema.]cluster
[{DROP | REUSE} STORAGE]
CLÁUSULA UNION
Mezcla los resultados de dos o más consultas individuales en una única tabla resultado, eliminando las filas duplicadas, si existieran.
SELECT columna1, columna2,....
FROM nombre-tabla1,...
WHERE condición1
UNION [ALL]
SELECT columna1, columna2,...
FROM nombre-tabla2,...
WHERE condición2
Oracle realiza primero cada una de las SELECTs escritas, generando una tabla
resultado por cada una de las consultas. Luego, une las dos tablas en una.
Las columnas de la tabla resultado poseen los nombres de las columnas de la primera sentencia SELECT que se ejecute.
ALL
Si se especifica ALL, el resultado de la consulta no elimina las filas duplicadas, si existieran.
Con ALL las uniones funcionan más eficientemente (TUNNING).
Sentencia UPDATE
Actualiza valores de una o más columnas para un subconjunto de filas de una tabla.
Para guardar cambios hay que ejecutar COMMIT;
Para cancelar la modificación podemos hacer ROLLBACK;
UPDATE nombre-tabla
SET columna1 = valor1 [, columna2 = valor2 ...]
[WHERE condición]
Actualiza los campos correspondientes junto con los valores que se le asignen, en el
subconjunto de filas que cumplan la condición de selección.
Si no se pone condición de selección, la actualización se da en todas las filas de la tabla.
Si se desea actualizar a nulos, se asignará el valor NULL.
En este ejemplo cambiamos el nombre y estado de un pedido:
UPDATE T_PEDIDOS
SET NOMBRE='JUAN',ESTADO=1
WHERE CODPEDIDO=125;
En este ejemplo cambiamos el estado de todos los pedidos:
UPDATE T_PEDIDOS
SET ESTADO=1;
En este ejemplo ponemos a nulo el nombre de un pedido:
UPDATE T_PEDIDOS
SET NOMBRE=NULL
WHERE CODPEDIDO=125;
WHERE condición
Cláusula opcional que se utiliza cuando se desea establecer una o varias condiciones para la sentencias SELECT, UPDATE y DELETE.
Las condiciones podrán tener:
- operadores aritméticos: =,<>,>,>=,<,<=
- operadores booleanos: AND, OR, NOT
- operadores especiales como: BETWEEN, IN, LIKE, IS NULL, EXISTS,
...
BETWEEN
Permite obtener todas las filas que se encuentran en un intervalo de valores.
nombre-columna BETWEEN limite-inferior AND limite-superior
IN
Permite obtener todas las filas que sean iguales a alguno de los valores descritos
por extensión.
nombre-columna IN (valor1, valor2, .....)
Es una forma corta de escribir ['x' = y OR 'x' = z...]
LIKE
Permite imponer condiciones sobre series de caracteres o parte de ellos.
- El símbolo “%” se utiliza como carácter de sustitución para indicar un número
indeterminado de caracteres. Depende de la base de datos en la que se esté
trabajando.
- El símbolo “_” se utiliza como carácter de sustitución para indicar un carácter en
una determinada posición. Depende de la base de datos en la que se esté trabajando. (Los caracteres % y _ pueden combinarse)
WHERE nombre-columna LIKE 'marti%' [encontrará 'martin' y
'martinez']
WHERE nombre-columna LIKE 'marti_' [encontrará 'martin' pero no
'martinez']
IS NULL
Obtiene todas las filas que contengan un valor nulo en una determinada columna.
Formato:
nombre-columna IS NULL.
También se puede negar con la partícula NOT, IS NOT NULL.
EXISTS
Devuelve TRUE si una subconsulta devuelve al menos una fila.
NOT
Todas las condiciones que se pueden utilizar, pueden negarse anteponiendo la
partícula NOT delante de los operadores especiales: IN, LIKE, BETWEEN.
Una sentencia SELECT, UPDATE y DELETE puede tener una o más condiciones
unidas por un operador booleano AND o OR.
Lenguaje de programación de Oracle PL-SQL
En esta sección se hace un repaso de las estructuras de programación de PL-SQL.
Estan descritas como estructuras como EXCEPTION, CASE, CURSOR...
CALL
Ejecuta un procedimiento o funcion desde el SQL.
CALL [schema.] item_to_call
CALL [schema.] [package.] item_to_call [INTO :host_variable
[[INDICATOR] :indicator_var] ]
CALL [schema.] [type.] item_to_call [INTO :host_variable
[[INDICATOR] :indicator_var] ]
Key
item_to_call: function [@dblink] (expr,...)
procedure [@dblink] (expr,...)
method [@dblink] (expr,...)
indicator_var: El valor o condición de la variable del host.
CALL place_order(453);
CASE
Sentencia condicional multiple
case
WHEN condition THEN
WHEN condition THEN
ELSE
END CASE;
Podemos poner tantas condiciones como queramos
declare
x number;
r varchar(100);
begin
case
WHEN x=1 THEN r:='aa';
WHEN x=2 THEN r:='bb';
ELSE r:='cc';
END CASE;
end;
CURSOR
Sentencia de recuperación de datos con un cursor.
DECLARE
CURSOR c1 IS
SELECT fields FROM table WHERE;
c1rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1rec;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
Un ejemplo:
DECLARE
CURSOR c1 IS
SELECT value FROM table WHERE code=1;
c1rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1rec;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
DESC[RIBE]
Sirve para obtener una descripción de una tabla, sinonimo, paquete o función. Debido a que
es un comando de sqlplus no necesita terminar con ;
DESC table
DESC view
DESC synonym
DESC function
DESC package
En Oracle 7 se pueden describir funciones indivuales de un paquete.
desc DBMS_UTILITY.GET_PARAMETER_VALUE
A partir de oracle 8 solo podemos describir paquetes completos. Es posible describir objetos
en otro esquema via dblink.
DESCRIBE user.table@db_link
El comando describe puede funcionar de forma recursiva hasta el nivel de profundidad
definido con:
SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF
Para mostrar esta configuración, ejecutar: SHOW DESCRIBE Una alternativa a al comando
DESC es usae el diccionario de datos. DESC MY_TABLE es equivalente a:
SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';
Column Comments
Para ver los comentarios de las columnas:
SELECT comments
FROM user_col_comments
WHERE table_name='MY_TABLE';
o tambien
SELECT 'comment on column '||table_name||'.'||column_name||' is
'''||comments||''';'
FROM user_col_comments
WHERE comments is not null;
Podemos escribir nuestro propio comando describe, creamos el fichero col.sql:
-- Lista todas la columnas de una tabla
select chr(9)||lower(column_name)||','
from USER_tab_columns
where table_Name = UPPER('&1');
Si queremos una lista de las columnas de EMP, escribimos:
@col emp
Produce una lista de columnas:
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
EXCEPTION
Sección de excepciones en le código PL-SQL
EXCEPTION
WHEN NO_DATA_FOUND THEN
code
WHEN OTHERS THEN
code
Podemos capturar las excepciones que nos interese
DECLARE
dummy NUMBER;
BEGIN
SELECT count(*) INTO dummy FROM dual;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
Algunas excepciones:
DUP_VAL_ON_INDEX ORA-00001
TIMEOUT_ON_RESOURCE ORA-00051
TRANSACTION_BACKED_OUT ORA-00061
INVALID_CURSOR ORA-01001
NOT_LOGGED_ON ORA-01012
LOGIN_DENIED ORA-01017
NO_DATA_FOUND ORA-01403
TOO_MANY_ROWS ORA-01422
ZERO_DIVIDE ORA-01476
INVALID_NUMBER ORA-01722
STORAGE_ERROR ORA-06500
PROGRAM_ERROR ORA-06501
VALUE_ERROR ORA-06502
CURSOR_ALREADY_OPEN ORA-06511
EXEC[UTE]
Ejecuta una funcion o un procedimiento PL/SQL.
EXEC statement
EXEC [:bind_variable :=] package.procedure;
EXEC [:bind_variable :=] package.function(parameters);
La longitud del comando EXEC no puede exceder el definido en SET LINESIZE. Si el comando
EXEC es muy largo podemos utilizar el caracter de continuación de SQL*Plus (un menos) -
EXEC :answer := EMP_PAY.BONUS('SMITH')
EXECUTE IMMEDIATE
Ejecuta una setencia SQL dinamica, es decir definida dentro de una cadena.
Tambien funciona con un bloque PL/SQL.
EXECUTE IMMEDIATE cadena_sql
[INTO {variable1,... | INTO registro}]
[USING [IN|OUT|IN OUT] bind_arg,...]
[RETURN[ING] INTO bind_arg,...];
cadena_sql: Es la variable con la cadena SQL o con el bloque PL/SQL.
Si "cadena_sql" termina en ';', se tratará como un bloque PL/SQL, en otro caso se trataran como sentencias DML (SELECT,INSERT,...) o DDL (CREATE TABLE,..).
variable1, variable2: Una variable por cada columna de respuesta de la cadena_sql.
registro: Variable de tipo registro que recibe la fila completa (TYPE o %ROWTYPE)
USING: Sirve para pasar parámetros a las sentencias. bind_arg: son variables o expresiones que contienen los parámetros de la sentencia.
Cuando se ejecuta la sentencia, se reemplaza cada variable en la sentencia sql (un
identificador con dos puntos delante, como :inicio) por su correspondiente valor
según su posición.
Se pueden pasar numeros, fechas y cadenas.
No se pueden pasar booleanos o literales de NULL pero si se puede pasar una
variable a nulo con el tipo de dato adecuado.
No se puede usar EXECUTE IMMEDIATE para consultas de multiples filas. Solo
podemos recoger una.
FUNCTION
Creación de función.
CREATE OR REPLACE FUNCTION f_name (params) RETURN returntype
IS
res returntype;
BEGIN
code...
RETURN res;
END;
Un ejemplo de función:
CREATE OR REPLACE FUNCTION f_desc(p_code in number)
RETURN char IS
v_res varchar2(100):='';
BEGIN
SELECT desc INTO v_des
FROM t_datos
WHERE code=:p_code;
return v_res;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return 'error';
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20665,'sqlerrm);
END;
IF
Sentencia condicional
IF cond1 THEN
code
END IF;
IF cond1 THEN
code
ELSE
code
END IF;
IF cond1 THEN
code
ELSIF cond2 THEN
code
ELSE
code
END IF;
Podemos poner tantas condiciones como queramos
declare
x number;
r varchar(100);
begin
IF x=1 THEN
r:='aa';
ELSIF x=2 THEN
r:='bb';
ELSE
r:='cc';
END IF;
end;
LOOP
Sentencia de bucle.
LOOP
;
END LOOP;
LOOP
EXIT WHEN ;
END LOOP;
LOOP
IF THEN
EXIT;
END IF;
END LOOP;
FOR IN 1 .. 10 LOOP
END LOOP;
FOR i IN 1 .. 10 LOOP
FOR j IN 1 .. 10 LOOP
EXIT outer WHEN ;
END LOOP;
END LOOP outer;
Podemos poner tantas condiciones como queramos
declare
x number;
r varchar(100);
begin
case
WHEN x=1 THEN r:='aa';
WHEN x=2 THEN r:='bb';
ELSE r:='cc';
END CASE;
end;
PROCEDURE
Creación de procedimiento.
CREATE OR REPLACE PROCEDURE p_name (params)
IS
-- Available in Oracle8i and above
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
code...
-- Upon successful completion, save all changes.
COMMIT;
EXCEPTION
-- For each error handler, don't forget the rollback!
WHEN OTHERS
THEN
ROLLBACK;
END;
Por ejemplo:
CREATE OR REPLACE PROCEDURE p_desc(p_code in number, p_dest in
number) AS
v_res varchar2(100):='';
BEGIN
SELECT desc INTO v_des
FROM t_datos
WHERE code=:p_code;
UPDATE t_dest SET res=:v_res
WHERE code=:p_code;
INSERT INTO t_dest (code,res)
VALUES (p_code,v_res);
END;
SET
Configuración SQLPLUS.
SET TERM OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG
only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool
files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
-- HEADING = OFF will hide column headings
SET FEEDBACK OFF
-- FEEDBACK = ON will count rows returned
SET PAUSE OFF
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings
and page brks)
SET LINESIZE 80
-- LINESIZE = width of page (80 is typical)
SET VERIFY OFF
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
SQLCODE
Función que nos devuelve el codigo de error asociado a la última excepción lanzada por la
base de datos. Solo debe usarse en la sección de manejo de excepciones.
EXCEPTION
WHEN NO_DATA_FOUND THEN
code
WHEN OTHERS THEN
code
Por ejemplo:
DECLARE
dummy NUMBER;
BEGIN
SELECT count(*) INTO dummy FROM dual;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha producido el error
- '||SQLCODE||' -ERROR- '||SQLERRM);
END;
SQLERRM
Función que nos devuelve el mensaje de error asociado a la última excepción lanzada por la
base de datos. Solo debe usarse en la sección de manejo de excepciones.
EXCEPTION
WHEN NO_DATA_FOUND THEN
code
WHEN OTHERS THEN
code
Por ejemplo:
DECLARE
dummy NUMBER;
BEGIN
SELECT count(*) INTO dummy FROM dual;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha producido el error
- '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Interfaz SQL PL/SQL (SQLPLUS)
Se trata de un interfaz de SQL y PL/SQL y de una herramienta de informes que viene tanto en la instalación de oracle como en la instalación de sql*net.
Como usar SQLPLUS
Se usa desde la linea de comandos. Las opciones disponibles son:
c:\> sqlplus /nolog
Ejecutar un script:
c:\> sqlplus user/password@database @script.sql
Un ejemplo de sesion:
c:\>sqlplus
SQL> connect usuario/password
SQL> select * from ejemplo;
SQL> disconnect
SQL> exit
Desde SQLPLUS podemos ejecutar:
Comandos SQLPLUS.
SQL> show user
Intrucciones SQL.
SQL> select * from dual;
Bloques de código PL/SQL.
SQL> begin
SQL> dbms_output.put_line('prueba');
SQL> end;
SQL> /
Funciones de Cadena de Oracle
En esta sección se hace un repaso de las funciones de cadena (string functions)
más utilizadas y algunos ejemplos.
Estan descritas funciones como CHR, ASCII, CONCAT...
Una lista completa de las funciones disponibles la podemos obtener con:
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD';
Funcion ASCII
Convierte el primer caracter en su codigo ascci en decimal.
Es la funcion contraria a CHR.
Si ejecutamos:
SELECT ASCII('V') FROM DUAL;
Devuelve
86
Funcion CHR
Esta función devuelve el caracter ASCII correspondiente al código que le pasamos de parámetro.
Es la funcion contraria a ASCII.
Si ejecutamos:
SELECT CHR(86) FROM DUAL;
Devuelve
V
Funcion CONCAT
Esta función devuelve una cadena unión de las que le pasamos como parámetro.
Es equivalente a char1||char2.
Si ejecutamos:
SELECT CONCAT('PEPE ','PEREZ') FROM DUAL;
Devuelve
PEPE PEREZ
Operador ||
Este operador devuelve una cadena unión de las que le colocamos a cada lado del operador.
Es equivalente a CONCAT(char1,char2).
Si ejecutamos:
SELECT 'PEPE '||'PEREZ' FROM DUAL;
Devuelve
PEPE PEREZ
Funcion LENGTH
Convierte conjuntos de caracteres.
CONVERT(char_to_convert, new_char_set, old_char_set)
Si ejecutamos:
SELECT CONVERT('ESPAÑA', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;
Devuelve
ESPA?A
Funcion INITCAP
Devuelve la cadena con cada palabra empezando con mayuscula y el resto en
minusculas. Se considera que las palabras estan separadas por espacios o
caracteres no alfanuméricos.
Si ejecutamos:
SELECT INITCAP('pepe perez') FROM DUAL;
Devuelve
Pepe Perez
Funcion INSTR
Devuelve la posicion de la emesima aparicion de char2 en Char1 buscando a partir
de la posicion n.
Si n es negativo, cuenta la posicion de arranque desde el final.
Por defecto n y m valen 1.
INSTR(char1, char2 [,n [,m]])
Si ejecutamos:
SELECT INSTR('PEPE PEREZ','PER') FROM DUAL;
Devuelve
6
Si ejecutamos:
SELECT INSTR('PEPE PEREZ','PE',1,2) FROM DUAL;
Devuelve
3
Funcion LENGTH
Numero de caracteres de una cadena. Si Char es de tipo CHAR, la longitud incluye los blancos del final.
LENGTH(char)
Si ejecutamos:
SELECT LENGTH('PEPE PEREZ') FROM DUAL;
Devuelve
10
Si ejecutamos:
SELECT LENGTH('PEPE PEREZ ') FROM DUAL;
Devuelve
15
Funcion LOWER
Devuelve la cadena en minúsculas.
Si ejecutamos:
SELECT LOWER('PEPE PEREZ') FROM DUAL;
Devuelve
pepe perez
Funcion LPAD
Rellena la cadena por la izquierda hasta la longitud n con el caracter definido, por defecto es blanco.
LPAD(char1, n [,char2])
Si ejecutamos:
SELECT LPAD('PEPE PEREZ',20) FROM DUAL;
Devuelve
PEPE PEREZ
Si ejecutamos:
SELECT LPAD('PEPE PEREZ',20,'-') FROM DUAL;
Devuelve
----------PEPE PEREZ
Funcion LTRIM
Por defecto, quita los blancos por la izquierda, aunque podemos definir que quite otros caracteres.
LTRIM(char [,set])
Si ejecutamos:
SELECT LTRIM(' PEPE PEREZ') FROM DUAL;
Devuelve
PEPE PEREZ
Funcion REPLACE
Devuelve la cadena char cambiando cada aparición de 'buscar' por 'cambiar'. Por defecto la cadena de reemplazo es nula.
REPLACE(char, buscar [,cambiar])
Si ejecutamos:
SELECT REPLACE('PEPE PEREZ','PE','PA') FROM DUAL;
Devuelve
PAPA PAREZ
Funcion RPAD
Rellena la cadena por la derecha hasta la longitud n con el caracter definido, por defecto es blanco.
RPAD(char1, n [,char2])
Si ejecutamos:
SELECT RPAD('PEPE PEREZ',20) FROM DUAL;
Devuelve
PEPE PEREZ
Si ejecutamos:
SELECT RPAD('PEPE PEREZ',20,'-') FROM DUAL;
Devuelve
PEPE PEREZ----------
Funcion RTRIM
Por defecto, quita los blancos por la derecha, aunque podemos definir que quite otros caracteres.
RTRIM(char [,set])
Si ejecutamos:
SELECT RTRIM('PEPE ')||' '||RTRIM('PEREZ ') FROM DUAL;
Devuelve
PEPE PEREZ
Funcion SOUNDEX
Devuelve una cadena con una representacion fonetica (inglesa) de char.
Permite comparar palabras que suenan igual (en ingles).
SOUNDEX(char)
Si ejecutamos:
SELECT SOUNDEX('PEPE PEREZ') FROM DUAL;
Devuelve
P567
Funcion SUBSTR
Devuelve una subcadena de char de n caracteres a partir de la posicion m.
Si m es positivo, empieza a contar desde el principio.
Si m es negativo, empieza a contar desde el final.
Si no ponemos n, devuelve hasta el final de la cadena.
SUBSTR(char, m [, n])
Si ejecutamos:
SELECT SUBSTR('PEPE PEREZ',6) FROM DUAL;
Devuelve
PEREZ
Si ejecutamos:
SELECT SUBSTR('PEPE PEREZ',6,3) FROM DUAL;
Devuelve
PER
Funcion TRANSLATE
Devuelve char cambiando cada caracter que hay en from por el correspondiente caracter en to.
Los caracteres que no estan en from no son reemplazados.
TRANSLATE(char, from, to)
Si ejecutamos:
select translate ('el perro de san roque...','rp','gt') from dual;
Devuelve
TRANSLATE('ELPERRODESANR
------------------------
el teggo de san goque...
Funcion TRIM
Esta función permite quitar los blancos por delante o por detras (o ambos a la vez)
de una cadena.
Con LEADING, quitamos por delante.
Con TRAILING quitamos por detras.
Con BOTH o no poniendo nada, quitamos de ambos lados.
Si no especificamos trim_character, se quitan los espacios en blanco.
Finalmente si solo ponemos trim_source quita blancos por delante y por detras.
TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source)
Si ejecutamos:
SELECT TRIM(' PEPE PEREZ ') FROM DUAL;
Devuelve
PEPE PEREZ
Funcion UPPER
Devuelve la cadena en mayusculas.
UPPER(cadena)
Si ejecutamos:
SELECT UPPER('Pepe Perez') FROM DUAL;
Devuelve
PEPE PEREZ
Funcion VSIZE
Numero de bytes de la representación interna de una cadena.
LENGTH(char)
Si ejecutamos:
SELECT LENGTH('PEPE PEREZ') FROM DUAL;
Devuelve
10
Si ejecutamos:
SELECT LENGTH('PEPE PEREZ ') FROM DUAL;
Devuelve
15
Funciones de agrupación (grupo)
En esta sección se hace un repaso de las funciones de agrupación (group functions) mas utilizadas y algunos ejemplos.
Estan descritas funciones como SUM, COUNT, MAX, ...
Pueden usarse combinadas:
SELECT PROVINCIA, COUNT(1), SUM(IMPORTE), MAX(IMPORTE), MIN(IMPORTE)
FROM T_PEDIDOS
GROUP BY PROVINCIA;
Funcion AVG
Determina la media del valor de una columna.
AVG([DISTINCT | ALL] n)
Si ejecutamos:
SELECT AVG(Columna) FROM T_PEDIDOS;
Devuelve MEDIA de un campo en una tabla.
134.5678
Funcion COUNT
Cuenta el numero de valores en un columna o el numero de filas en una tabla.
COUNT([DISTINCT | ALL] n)
Si ejecutamos:
SELECT COUNT(*) FROM T_PEDIDOS;
Devuelve el numero de filas en una tabla.
123
Si ejecutamos:
SELECT COUNT(DISTINCT Columna) FROM T_CLIENTES;
Devuelve el numero de valores distintos que tiene una columna.
13
Si ejecutamos:
SELECT COUNT(Columna) FROM T_FACTURAS;
Devuelve el numero de filas que tienen un valor no nulo.
12
Funcion MAX
Determina el mayor valor de una columna.
MAX([DISTINCT | ALL] n)
Si ejecutamos:
SELECT MAX(Columna) FROM T_PEDIDOS;
Devuelve el maximo valor de un campo en una tabla.
123
Funcion MIN
Determina el mayor valor de una columna.
MIN([DISTINCT | ALL] n)
Si ejecutamos:
SELECT MIN(Columna) FROM T_PEDIDOS;
Devuelve el menor valor de un campo en una tabla.
1
Funcion STDDEV
Determina la desviación estandar del valor de una columna.
STDDEV([DISTINCT | ALL] n)
Si ejecutamos:
SELECT STDDEV(Columna) FROM T_PEDIDOS;
Devuelve desviación estandar de un campo en una tabla.
14.6432
Funcion SUM
Determina el mayor valor de una columna.
SUM([DISTINCT | ALL] n)
Si ejecutamos:
SELECT SUM(Columna) FROM T_PEDIDOS;
Devuelve LA SUMA de un campo en una tabla.
14567
Funciones de fecha
En esta sección se hace un repaso de las funciones de fecha (date functions) mas utilizadas y algunos ejemplos.
Estan descritas funciones como ADD_MONTHS, MONTHS_BETWEEN...
Función ADD_MONTHS
Devuelve la fecha D mas N meses. N puede ser cualquier entero. Si d es el ultimo
dia del mes origen y el mes resultante tiene menos dias, el resultado es el ultimo dia del mes resultante. En otro caso el dia es el mismo.
ADD_MONTHS(d,n)
Si ejecutamos:
SELECT ADD_MONTHS(TO_DATE('31/10/2007','DD/MM/YYYY'),4) FROM DUAL;
Devuelve:
29/02/2008
Función CURRENT_DATE
Devuelve la fecha actual de la sesión actual.
CURRENT_DATE
Si ejecutamos:
SELECT CURRENT_DATE FROM DUAL;
Devuelve:
31/10/2007 12:29:47
Función CURRENT_TIMESTAMP
Devuelve la fecha y hora actual de la sesión actual.
CURRENT_TIMESTAMP
Si ejecutamos:
SELECT CURRENT_TIMESTAMP FROM DUAL;
Devuelve:
31/10/2007 12:41:53,787000 +02:00
Función LAST_DAY
Devuelve la fecha del último día del mes de la fecha D.
LAST_DAY(d)
Si ejecutamos:
SELECT LAST_DAY(TO_DATE('31/10/2007','DD/MM/YYYY')) FROM DUAL;
Devuelve:
31/10/2007
Funcion MONTHS_BETWEEN
Devuelve el numero de meses entre dos fechas d1 y d2. si d1 es posterior a d2, el resultado es positivo; si es anterior el resultado es negativo.
MONTHS_BETWEEN(d1, d2)
Si ejecutamos:
SELECT
MONTHS_BETWEEN(TO_DATE('31/10/2007','DD/MM/YYYY'),TO_DATE('01/06/200
7','DD/MM/YYYY'))
FROM DUAL;
Devuelve:
4,96774193548387
Función NEXT_DAY
Devuelve la fecha más cercana posterior a D cuyo dia de la semana es WD. WD puede ser LUNES, MARTES, MIÉRCOLES, JUEVES, VIERNES, SÁBADO, DOMINGO.
NEXT_DAY(d,wd)
Si ejecutamos:
SELECT NEXT_DAY(TO_DATE('31/10/2007','DD/MM/YYYY'),'sábado') FROM
DUAL;
Devuelve:
03/11/2007
Funcion ROUND
Devuelve la fecha d redondeada a la unidad especificada por el formato. Si se omite
el formato se redondea al dia mas cercano. Si el formato es 'YYYY' se redondea
arriba o abajo dependiendo del dia del año. Si es formato es 'MM' se redondea arriba o abajo dependiendo del dia del mes.
ROUND(d [,fmt])
Si ejecutamos:
SELECT ROUND(TO_DATE('31/10/2007','DD/MM/YYYY'),'YYYY') FROM DUAL;
Devuelve:
01/01/2008
Si ejecutamos:
SELECT ROUND(TO_DATE('20/04/2007','DD/MM/YYYY'),'YYYY') FROM DUAL;
Devuelve:
01/01/2007
Funcion TRUNC
Devuelve la fecha d truncada a la unidad especificada por el formato. Si se omite el
formato se trunca al dia mas cercano. Si el formato es 'YYYY' se trunca abajo
dependiendo del dia del año. Si es formato es 'MM' se trunca abajo dependiendo del dia del mes.
TRUNC(d [,fmt])
Si ejecutamos:
SELECT TRUNC(TO_DATE('31/10/2007','DD/MM/YYYY'),'YYYY') FROM DUAL;
Devuelve:
01/01/2007
Si ejecutamos:
SELECT TRUNC(TO_DATE('20/04/2007','DD/MM/YYYY'),'MM') FROM DUAL;
Devuelve:
01/04/2007
Funciones numéricas
En esta sección se hace un repaso de las funciones numéricas más utilizadas y
algunos ejemplos.
Estan descritas funciones como GREATEST, FLOOR, CEIL...
Función ABS
Valor absoluto de un numero.
ABS(n)
Si ejecutamos:
SELECT ABS(-89) FROM DUAL;
Devuelve:
89
Función CEIL
Redondea n hasta el valor superior.
CEIL(n)
Si ejecutamos:
SELECT CEIL(45.1242) FROM DUAL;
Devuelve:
46
Si ejecutamos:
SELECT CEIL(45.8242) FROM DUAL;
Devuelve:
46
Si ejecutamos:
SELECT CEIL(-45.8242) FROM DUAL;
Devuelve:
-45
Función EXTRACT
Extrae valores de una fecha o intervalo.
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { fecha | intervalo } )
Si ejecutamos:
SELECT EXTRACT(YEAR FROM TO_DATE('05/06/2007','DD/MM/YYYY')) FROM
DUAL;
Devuelve:
2007
Función FLOOR
Redondea n al valor inferior.
FLOOR(n)
Si ejecutamos:
SELECT FLOOR(45.1242) FROM DUAL;
Devuelve:
45
Si ejecutamos:
SELECT FLOOR(45.8242) FROM DUAL;
Devuelve:
45
Si ejecutamos:
SELECT FLOOR(-45.8242) FROM DUAL;
Devuelve:
-46
Función GREATEST
Devuelve el mayor de una lista de expresiones.
GREATEST(exp1, exp2, ...)
Si ejecutamos:
SELECT GREATEST(1,3,6,89,56,3) FROM DUAL;
Devuelve:
89
Función LEAST
Devuelve el menor de una lista de expresiones.
LEAST(exp1, exp2,...)
Si ejecutamos:
SELECT LEAST(1,3,6,89,56,3) FROM DUAL;
Devuelve:
1
Función MOD
Devuelve el resto de dividir M entre N. Devuelve M si N es 0.
MOD(m,n)
Si ejecutamos:
SELECT MOD(13,5) FROM DUAL;
Devuelve:
3
Función REMAINDER
Devuelve el resto de dividir m por n. Se calcula de la siguiente forma: m - (n * X) donde X es el entero más cercano a m / n .
REMAINDER(m,n)
Si ejecutamos:
SELECT REMAINDER(16,3) FROM DUAL;
Devuelve:
1
Si ejecutamos:
SELECT REMAINDER(20,3) FROM DUAL;
Devuelve:
-1
Si ejecutamos:
SELECT REMAINDER(15,6) FROM DUAL;
Devuelve:
3
Función ROUND
Redondea N hasta la posición M a la derecha del punto decimal. Por defecto M es 0.
Si M es negativo redondea hacia la izquierda del punto decimal. Por supuesto, M debe ser entero.
ROUND(n [,m])
Si ejecutamos:
SELECT ROUND(45.1242,2) FROM DUAL;
Devuelve:
45.12
Si ejecutamos:
SELECT ROUND(45.8242) FROM DUAL;
Devuelve:
46
Si ejecutamos:
SELECT ROUND(-45.8242) FROM DUAL;
Devuelve:
-46
Función SIGN
Si N < 0 devuelve -1, si N = 0 devuelve 0; si N > 0 devuelve 1.
SIGN(n)
Si ejecutamos:
SELECT SIGN(-89) FROM DUAL;
Devuelve:
-1
Función TRUNC
Trunca N hasta la posición M a la derecha del punto decimal. Por defecto M es 0. Si
M es negativo trunca hacia la izquierda del punto decimal (pone ceros). Por supuesto, M debe ser entero.
TRUNC(n [,m])
Si ejecutamos:
SELECT TRUNC(45.1242,2) FROM DUAL;
Devuelve:
45.12
Si ejecutamos:
SELECT TRUNC(45.8242) FROM DUAL;
Devuelve:
45
Si ejecutamos:
SELECT TRUNC(-45.8242) FROM DUAL;
Devuelve:
-45
Si ejecutamos:
SELECT TRUNC(45.1242,-1) FROM DUAL;
Devuelve:
40
Funciones de conversión
En esta sección se hace un repaso de las funciones de conversión (conversion functions) mas utilizadas y algunos ejemplos.
Estan descritas funciones como TO_CHAR, TO_NUMBER...
Funciones diversas muy útiles
Estan tambien descritas funciones como DECODE, NVL...
Función CASE
Evalua diferentes expresiones para dar un valor de salida.
CASE WHEN expr1 THEN val1 WHEN expr2 THEN val2 ... ELSE valn END
Si ejecutamos:
SELECT CASE WHEN ESTADO<=0 THEN 'bien' WHEN ESTADO>=1
AND ESTADO<=5 THEN 'regular' ELSE 'mal' END
FROM T_PEDIDOS;
Devuelve:
bien
bien
regular
mal
...
Función DECODE
Traduce una expresión a un valor de retorno. Si expr es igual a value1, la función
devuelve Return1. Si expr es igual a value2, la función devuelve Return2. Y asi
sucesivamente. Si expr no es igual a ningun valor la funcion devuelve el valor por defecto.
DECODE(expr, value1 [, return1, value2, return2....,] default )
Si ejecutamos:
SELECT DECODE(ESTADO,0,'bien',1,'regular','mal') FROM T_PEDIDOS;
Devuelve:
bien
bien
regular
mal
...
Función NULLIF
Esta función compara expr1 con expr2. Si son iguales devuelve NULL. Los argumentos pueden ser de cualquier tipo.
NULLIF(expr1, expr2))
Si ejecutamos:
SELECT NULLIF('peras','manzanas') FROM DUAL;
Devuelve:
peras
Pero si ejecutamos:
SELECT NULLIF('peras','peras') FROM DUAL;
Devuelve:
NULL
Función NVL
Si expr1 es nulo, devuelve expr2. Si expr1 no es nulo, devuelve expr1. Los argumentos pueden ser de cualquier tipo.
NVL(expr1, expr2))
Si ejecutamos:
SELECT NVL(ESTADO,0) FROM T_PEDIDOS;
Devuelve:
1
2
0
1
...
Función NVL2
Si expr1 no es nulo, devuelve expr2. Si expr1 es nulo, devuelve expr3. Los argumentos pueden ser de cualquier tipo.
NVL2(expr1, expr2, expr3))
Si ejecutamos:
SELECT NVL2(ESTADO,'hay valor','no hay valor') FROM T_PEDIDOS;
Devuelve:
hay valor
no hay valor
no hay valor
hay valor
no hay valor
hay valor
...
Funcion TO_CHAR
Convierte una fecha a una cadena o un número con el formato especificado.
TO_CHAR(date1 [,fmt]);
TO_CHAR(number1 [,fmt])
Si ejecutamos:
SELECT TO_CHAR(sysdate) FROM DUAL;
Devuelve:
15-JUN-01
Si ejecutamos:
SELECT TO_CHAR(sysdate, 'dd/mm/yyyy') FROM DUAL;
Devuelve:
15/06/2001
Si ejecutamos:
SELECT TO_CHAR(sysdate,'Mon') FROM DUAL;
Devuelve el nombre corto del mes:
Jun
Si ejecutamos:
SELECT TO_CHAR(sysdate, 'Month') FROM DUAL;
Devuelve el nombre largo del mes:
Junio
Si ejecutamos:
SELECT TO_CHAR(sysdate, 'DDD') FROM DUAL;
Devuelve los 3 digitos del dia del año:
058
Si ejecutamos:
SELECT TO_CHAR(123.456, '09999') FROM DUAL;
Devuelve:
00123
Si ejecutamos:
SELECT TO_CHAR(123.456, '09999.9') FROM DUAL;
Devuelve:
00123.5
Si ejecutamos:
SELECT TO_CHAR(123456, 'FM999,999,999') FROM DUAL;
Devuelve:
123,456
Funcion TO_DATE
Convierte una cadena en un valor de tipo DATE. Ver en TO_CHAR ejemplos de formato.
TO_DATE(char [,fmt])
Si ejecutamos:
SELECT TO_DATE('31/10/2007','DD/MM/YYYY') FROM DUAL;
Devuelve:
31/10/2007
Funcion TO_NUMBER
Convierte una cadena en un valor de tipo NUMBER. Ver en TO_CHAR ejemplos de formato.
TO_NUMBER(char [,fmt])
Si ejecutamos:
SELECT TO_NUMBER('123') FROM DUAL;
Devuelve:
123
Funciones de sistema y pseudocolumnas
En esta sección se hace un repaso de las pseudocolumnas y funciones del sistema más utilizadas y algunos ejemplos.
Estan descritas funciones como ROWNUM, ROWID...
Tabla DUAL
Es una tabla, creada durante la instalación, con una sola columna llamada DUMMY y
una sola fila de contenido 'X'. El propietario es SYS y puede ser accedida por cualquier usuario.
Si ejecutamos:
SELECT * FROM DUAL;
Devuelve:
DUMMY
--------
X
Se usa para generalmente para ejecutar funciones (ADD_MONTHS,...) y pseudocolumnas
(como SYSDATE,...). Es posible agregar y quitar registros y columnas pero ¡NO ES NADA
RECOMENDABLE!.
Tabla de parámetros de session
La tabla NLS_SESSION_PARAMETERS nos da los parametros de idioma de la sesión actual.
Si ejecutamos:
SELECT PARAMETER,VALUE FROM NLS_SESSION_PARAMETERS;
Devuelve:
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YY
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
Función Rowid
Rowid es una pseudocolumna que identifica de manera única una fila en una tabla, pero NO en cualquier tabla.
Si ejecutamos:
SELECT ROWID FROM DUAL;
Devuelve:
AB45FAOO....
Funcion ROWNUM
Rownum es una pseudocolumna. Numera los registros de un conjunto de resultados. Al
primer registro que cumple el criterio de la clausula where en una sentencia select se le da
rownum=1, y cada uno de los siguientes registros se le incrementa en 1.
select rownum, numproduct, descproduct
from
T_PRODUCTOS
where
numproduct>100
Es importante darse cuenta de que la primera fila siempre tiene rownum=1. Esto implica que
la siguiente consulta no devolvera filas:
select numproduct, descproduct
from
T_PRODUCTOS
where numproduct>100
and rownum > 5
Esto es porque la primera fila no puede cumplir estos dos criterios mutuamente excluyentes:
* rownum es 1 * rownum es 6 (rownum > 5) Lo mejor seria hacer una subconsulta del tipo:
select
numproduct, descproduct
from (
select
rownum r_, numproduct, descproduct
from
T_PRODUCTOS
where numproduct>100
)
where r_ > 5
Funcion SYSDATE
Devuelve la fecha y hora del servidor.
SYSDATE
Si ejecutamos:
SELECT SYSDATE FROM DUAL;
Devuelve:
18/01/2007 19:33:37
Funcion SYS_CONTEXT
Devuelve información de la sesión actual del usuario.
SYS_CONTEXT(namespace,param,[length])
Valores de parámetro:
ACTION Devuelve la posición en el módulo
AUDITED_CURSORID Devuelve el ID del cursor que ha lanzado
la auditoría
AUTHENTICATED_IDENTITY Devuelve la identidad usada en
la autentificación
AUTHENTICATION_DATA Datos de autentificación
AUTHENTICATION_METHOD Devuelve el método de autentificación
AUTHENTICATION_TYPE Devuelve como se ha autentificado el
ususario. Puede tener uno de estos valores: Database, OS, Network,
or Proxy
BG_JOB_ID Si la sesión se ha establecido desde un
proceso background de oracle, este parámetro devuelve el Job ID. En
otro caso devuelve NULL.
CLIENT_IDENTIFIER Devuelve el identificador de cliente
(global context)
CLIENT_INFO Devuelve la información cargada mediante
el paquete DBMS_APPLICATION_INFO
CURRENT_BIND Bind variables para auditoría
CURRENT_SCHEMA Devuelve el esquema por defecto usado en
el actual esquema
CURRENT_SCHEMAID Devuelve el identificador esquema por
defecto usado en el actual esquema
CURRENT_SQL Devuelve la SQL que ha lanzado el evento
de auditoría
CURRENT_SQL_LENGTH Devuelve la longitud de la SQL que ha
lanzado el evento de auditoría
CURRENT_USER Nombre del usuario actual
CURRENT_USERID Identificador del usuario actual
DB_DOMAIN Dominio de la base de datos del
parámetro de inicialización DB_DOMAIN
DB_NAME Nombre de la base de datos del parámetro
de inicialización DB_NAME
DB_UNIQUE_NAME Nombre de la base de datos del parámetro
de inicialización DB_UNIQUE_NAME
ENTRYID Identificador del la entrada de
auditoría disponible
ENTERPRISE_IDENTITY Identidad de la empresa
EXTERNAL_NAME Nombre externo del usuario de la base de
datos
FG_JOB_ID Si la sesión se ha establecido desde un
proceso foreground de oracle, este parámetro devuelve el Job ID. En
otro caso devuelve NULL.
GLOBAL_CONTEXT_MEMORY El número usado en el System Global Area
para acceer al contexto
GLOBAL_UID Identificador global de usuario del
Oracle Internet Directory. Devuelve NULL para todas las demás
entradas.
HOST Nombre de la máquina desde la que se ha
contectado el cliente.
IDENTIFICATION_TYPE Devuelve el modo en que ha creado el
esquema de usuario.
INSTANCE Identificador de la instancia actual
INSTANCE_NAME Nombre de la instancia actual
IP_ADDRESS Dirección IP de la máquina desde la que
esta contectado el cliente actual.
ISDBA Devuelve TRUE si el usuario tiene
privilegios de DBA si no devuelve FALSE.
LANG La abreviatura ISO del lenguaje
LANGUAGE El leguaje, territorio y conjunto de
caracteres de la sesión. El formato es el siguiente:
language_territory.characterset
MODULE Devuelve el nombre de aplicación
establecido mediante el paquete DBMS_APPLICATION_INFO o OCI.
NETWORK_PROTOCOL Protcolo de red usado
NLS_CALENDAR El calendario usado enThe calendar of
the current session
NLS_CURRENCY Moneda de la sesión actual.
NLS_DATE_FORMAT Formato de fecha de la sesión actual.
NLS_DATE_LANGUAGE Lenguaje usado para la fechas.
NLS_SORT Lenguaje de ordenación.
NLS_TERRITORY Territorio de la sesión actual.
OS_USER Usuario del sistema operativo.
POLICY_INVOKER Invocador de la política de seguridad a
nivel de fila.
PROXY_ENTERPRISE_IDENTITY DN del Oracle Internet
Directory.
PROXY_GLOBAL_UID Identificador de usuario global de
Oracle Internet Directory for enterprise.
PROXY_USER Nombre de usuario detras de SESSION_USER
PROXY_USERID Identificador de usuario detras de
SESSION_USER
SERVER_HOST Nombre del servidor donde se está
ejecutando la instancia de la base de datos.
SERVICE_NAME Nombre del servicio donde esta conectada
la sesión.
SESSION_USER Nombre del usuario de la base de datos
con el que esta contectada la sesión.
SESSION_USERID Identificador del usuario de la base de
datos con el que esta contectada la sesión.
SESSIONID Identificador de la sesión
SID Número de sesión
STATEMENTID El identificador de auditoría de
sentencia
TERMINAL Nombre del terminal
Por ejemplo:
select sys_context('USERENV', 'DB_NAME') from dual;
devuelve: mydatabase
Funcion USER
Devuelve el identificador de usuario de la sesión actual.
select USER from dual;
Funcion USERENV
Devuelve información de la sesión actual del usuario.
USERENV(param)
Valores de parámetro:
CLIENT_INFO Devuelve la información cargada mediante el paquete
DBMS_APPLICATION_INFO
ENTRYID Identificador de entrada
INSTANCE Identificador de la instancia actual
ISDBA Devuelve TRUE si el usuario tiene privilegios
de DBA si no devuelve FALSE.
LANG La abreviatura ISO del lenguaje
LANGUAGE El leguaje, territorio y conjunto de caracteres de la
sesión. El formato es el siguiente: language_territory.characterset
SESSIONID Identificador de la sesión
TERMINAL Nombre del terminal
Por ejemplo:
select USERENV('LANGUAGE') from dual;
devuelve: SPANISH_SPAIN.WE8ISO8859P1