Lenguaje Oracle SQL, PL/SQL y desarrollo

Lenguaje Oracle SQL, PL/SQL y desarrollo Dataprix 8 Septiembre, 2009 - 09:49

Cómo hacer o mejorar sentencias Oracle SQL, utilizar PL/SQL y los procedimientos almacenados, o cualquier cosa relacionada con el desarrollo o explotación de bases de datos Oracle

Construcción de scripts Oracle SQL con ayuda del diccionario

Construcción de scripts Oracle SQL con ayuda del diccionario Carlos 7 Junio, 2007 - 23:12

Es bastante habitual si se trabaja con bases de datos que a menudo se tenga que realizar alguna tarea de creación o alteración de estructuras, análisis, recompilación, etc. sobre objetos de la base de datos. Para ello se suele crear un script con numerosas sentencias DDL, en las que la mayoría de las veces lo único que cambia es el nombre del objeto a tratar.

Crear sentencia Oracle SQL que genera sentencias Oracle

En estos casos puede ahorrarnos mucho trabajo la utilización del diccionario de la base de datos para construir estas sentencias dinámicamente. Pondremos como ejemplo la creación de un nuevo campo para almacenar la fecha de creación de los registros en todas las tablas de un esquema de una base de datos ORACLE. Para ello utilizaríamos la siguiente sentencia de SQL Oracle:

SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD FECHA_CREACION DATE DEFAULT SYSDATE;' 
FROM ALL_TABLES WHERE OWNER ='HR'; 

El resultado de esta sentencia SQL Oracle sería algo como esto, las sentencias SQL que queremos utilizar en realidad:

ALTER TABLE HR.DEPARTMENTS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.EMPLOYEES ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.JOB_HISTORY ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.JOBS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.LOCATIONS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.REGIONS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.COUNTRIES ADD FECHA_CREACION DATE DEFAULT SYSDATE;

 

Crear script con Oracle SQL para ejecutar sentencias SQL

Ahora sólo restaría guardar estas sentencias en un script y ejecutarlo, o lanzarlas directamente desde la aplicación que utilicemos para interactuar con nuestra base de datos.

Para el que tenga que (o prefiera) trabajar desde un terminal o linea de comandos, la manera de hacer esto mismo con SQLPLUS sería la siguiente:

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL C:\campo_auditoria.sql
SQL> SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD FECHA_CREACION DATE DEFAULT SYSDATE;'
     FROM ALL_TABLES WHERE OWNER ='HR';
SQL> SPOOL OFF;
SQL> SET FEEDBACK ON
SQL> SET HEADING ON 

Y finalmente ejecutar el script generado, aunque es recomendable una revisión previa de las sentencias generadas:

 SQL> @C:\campo_auditoria.sql 

 

Estructura de la Dimension Tiempo y Script de carga con Oracle SQL

Estructura de la Dimension Tiempo y Script de carga con Oracle SQL Carlos 29 Agosto, 2009 - 23:47

Con este script de Oracle SQL se crea una tabla DIM_TIEMPO y se rellena con los valores comprendidos entre las fechas que se indiquen en las variables FechaDesde y FechaHasta. Puede ser muy útil para la creación de la tabla de tiempo de cualquier Data Warehouse.

Esta es la versión para una base de datos Oracle, con Oracle SQL, que se suma a las que han creado anteriormente il_masacratore y Dario Bernabeu para Microsoft SQL Server y Oracle MySQL en sus respectivos blogs:

Estructura de la Dimensión Tiempo y Script de carga para Ms SQL Server

Estructura de la Dimensión Tiempo y Procedure de carga para MySQL

----------------------------------------------------
-- SQL de Creación de la tabla DIM_TIEMPO  --
----------------------------------------------------
drop table DIM_TIEMPO;
create table DIM_TIEMPO
(
    FechaSK number not null,
    Fecha date not null PRIMARY KEY,
    Año number not null,
    Trimestre number not null,
    Mes number not null,
    Semana number not null,
    Dia number not null,
    DiaSemana number not null,
    NTrimestre varchar2(7) not null,
    NMes varchar2(15) not null,
    NMes3L varchar2(3) not null,
    NSemana varchar2(10) not null,
    NDia varchar2(6) not null,
    NDiaSemana varchar2(10) not null
);
------------------------------------------------------------
-- Script Oracle SQL de carga de los datos entre fechas --
------------------------------------------------------------
DECLARE
   FechaDesde date;
   FechaHasta date;
BEGIN
    --Borrar datos actuales, si fuese necesario
    --TRUNCATE TABLE DIM_TIEMPO
    --Rango de fechas a generar: del 01/01/2006 al 31/12/Año actual+2
    FechaDesde := TO_DATE('20060101','YYYYMMDD');
    FechaHasta := TO_DATE((TO_CHAR(sysdate,'YYYY')+2 || '1231'),'YYYYMMDD'); 
    WHILE FechaDesde <= FechaHasta LOOP
      INSERT INTO DIM_TIEMPO
      (
        FechaSK,
        Fecha,
        Año,
        Trimestre,
        Mes,
        Semana,
        Dia,
        DiaSemana,
        NTrimestre,
        NMes,
        NMes3L,
        NSemana,
        NDia,
        NDiaSemana
      )
      VALUES
      (
        to_char(FechaDesde,'YYYYMMDD'),                      
        FechaDesde,
        to_char(FechaDesde,'YYYY'),
        to_char(FechaDesde, 'Q'),
        to_char(FechaDesde,'MM'),
        to_char(FechaDesde,'WW'),
        to_char(FechaDesde,'DD'),
        to_char(FechaDesde,'D'),
        'T'||to_char(FechaDesde, 'Q')||'/'||to_char(FechaDesde,'YY'),
        to_char(FechaDesde,'MONTH'),
        to_char(FechaDesde,'MON'),
        'Sem '||to_char(FechaDesde,'WW')||'/'||to_char(FechaDesde,'YY'),
        to_char(FechaDesde,'DD MON'),
        to_char(FechaDesde,'DAY')
      ); 
      --Incremento del bucle
      FechaDesde := FechaDesde + 1;
    END LOOP;
END;

 

Como cada uno se adaptará el formato de las fechas al que más le convenga, aprovecho para adjuntar esta tabla de ayuda obtenida de Oradev. Contiene descripciones de la sintaxis que se puede utilizar en las máscaras de formato de fechas de las funciones TO_CHAR y TO_DATE de Oracle:

Format mask Description
CC Century
SCC Century BC prefixed with -
YYYY Year with 4 numbers
SYYY Year BC prefixed with -
IYYY ISO Year with 4 numbers
YY Year with 2 numbers
RR Year with 2 numbers with Y2k compatibility
YEAR Year in characters
SYEAR Year in characters, BC prefixed with -
BC BC/AD Indicator *
Q Quarter in numbers (1,2,3,4)
MM Month of year 01, 02...12
MONTH Month in characters (i.e. January)
MON JAN, FEB
WW Weeknumber (i.e. 1)
W Weeknumber of the month (i.e. 5)
IW Weeknumber of the year in ISO standard.
DDD Day of year in numbers (i.e. 365)
DD Day of the month in numbers (i.e. 28)
D Day of week in numbers(i.e. 7)
DAY Day of the week in characters (i.e. Monday)
FMDAY Day of the week in characters (i.e. Monday)
DY Day of the week in short character description (i.e. SUN)
J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH Hournumber of the day (1-12)
HH12 Hournumber of the day (1-12)
HH24 Hournumber of the day with 24Hours notation (1-24)
AM AM or PM
PM AM or PM
MI Number of minutes (i.e. 59)
SS Number of seconds (i.e. 59)
SSSSS Number of seconds this day.
DS Short date format. Depends on NLS-settings. Use only with timestamp.
DL Long date format. Depends on NLS-settings. Use only with timestamp.
E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE The full era name
FF The fractional seconds. Use with timestamp.
FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM Fill Mode: suppresses blianks in output from conversion
FX Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only
RM The Roman numeral representation of the month (I .. XII)
RR The last 2 digits of the year.
RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC Century. BC dates are prefixed with a minus.
CC Century
SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH Spelled and ordinal format; 1 results in first.
TH Converts a number to it's ordinal format. For example 1 becoms 1st.
TS Short time format. Depends on NLS-settings. Use only with timestamp.
TZD Abbreviated time zone name. ie PST.
TZH Time zone hour displacement.
TZM Time zone minute displacement.
TZR Time zone region
X Local radix character. In america this is a period (.)

Adjunto procedimiento completo de PL/SQL de Oracle al que se le adicionan 3 campos: periodo que no es relevante, fecha de corte que es el ultimo dia del trimestre de la fecha, la fecha de corte anterior que es la fecha del anterior trimestre.

Espero les sirva.

CREATE OR REPLACE PROCEDURE CARGADIMTIEMPO IS
tmpVar NUMBER;
FechaDesde date;
FechaHasta date;
FechaDesdeStr VARCHAR2(8);
err_num NUMBER;
err_msg VARCHAR2(255);
BEGIN
tmpVar := 0;
FechaDesde := TO_DATE('19941231','YYYYMMDD');
FechaHasta := TO_DATE('20181231','YYYYMMDD');
WHILE FechaDesde <= FechaHasta LOOP
FechaDesdeStr := to_char( FechaDesde, 'YYYYMMDD') ;
INSERT INTO DIM_TIEMPO
(
FechaSK,
Fecha,
Año,
Trimestre,
Mes,
Semana,
Dia,
DiaSemana,
NTrimestre,
NMes,
NMes3L,
NSemana,
NDia,
NDiaSemana,
Periodo,
FechaCorte,
FechaCorteAnt
)
VALUES
(
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD') ,'YYYYMMDD'),
FechaDesde,
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MM'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'WW'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DD'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'D'),
'T'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q')||'/'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MONTH'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MON'),
'Sem '||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'WW')||'/'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DD MON'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DAY'),
CASE
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN 3
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN 6
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN 9
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN 12
END,
CASE
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN TO_DATE('31/03/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN TO_DATE('30/06/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN TO_DATE('30/09/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN TO_DATE('31/12/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
END,
CASE
WHEN to_char(to_date(FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN TO_DATE('31/12/' || to_char(to_number(to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'))-1), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN TO_DATE('31/03/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN TO_DATE('30/06/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN TO_DATE('30/09/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
END
);
-- Incremento del bucle
commit ;
FechaDesde := FechaDesde + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
err_num := SQLCODE;
err_msg := SQLERRM;
DBMS_OUTPUT.put_line('Error Problemas :'||TO_CHAR(err_num) || ' ' || err_msg );
DBMS_OUTPUT.put_line(err_msg);
END CARGADIMTIEMPO;

Gestionar dos empresas con el mismo usuario

Gestionar dos empresas con el mismo usuario ana.janez 13 May, 2014 - 11:14

Hola a todos.

Soy nueva con oracle y tengo una duda. Necesito crear con forms una aplicacion que gestione dos empresas. Un usuario se conecta y podrá elegir entre dos empresas. Dependiendo de la empresa que elija se le mostraran los datos correspondientes. Las dos empresas tienen las mismas estructura tablas pero con datos diferentes.

Muchas gracias por vuestra ayuda

Independientemente de si se trata de una aplicación de Oracle Forms o no, lo que comentas me parece una decisión de diseño que se ha de tomar al nivel del modelo de datos, de las tablas de la base de datos.
Yo creo que si las dos empresas manejan los mismos datos lo más indicado es incluir en cada tabla un identificador de la empresa, que enlace a una tabla maestra de empresas. Así cualquier aplicación podrá identificar los datos que corresponden a cada empresa para mostrarlos o no a quien corresponda, o en función del entorno o las selecciones que se hayan hecho.

Espero haberte ayudado.

En respuesta a por Carlos

[quote=ana.janez]

Hola de nuevo,

Lo primero es agradecer tu ayuda. Si he entendido bien la cuestión sería duplicar todas las tablas de la base de datos y añadir un campo extra en cada tabla en el que se indique el nombre de la empresa a la que pertenece. Mi pregunta es, ese campo extra que identifica la empresa debe considerarse parte de una primary key o no es necesario?

gracias de nuevo.[/quote]

En respuesta a por ana.janez

No del todo, de duplicar nada, cada tabla te ha de servir para las dos empresas, por eso cada registro ha de tener el campo identificador de la empresa y, efectivamente lo normal es que este id de empresa forme parte de la clave primaria.

La opción de duplicar tablas también es factible, pero entonces sería el nombre de la tabla lo que te indicaría si los datos que contiene son de una empresa u otra, y entonces no sería necesario que las tablas tuvieran un campo id de la empresa. De todas maneras, esta segunda opción desperdicia mucho espacio, es muy poco flexible, y a la larga seguro que te complica el mantenimiento, yo te recomendaría la primera, la misma tabla para almacenar los registros de ambas empresas.

Insert entre bases de datos remotas enlazadas por dblink de Oracle

Insert entre bases de datos remotas enlazadas por dblink de Oracle cfb 19 Agosto, 2008 - 22:28

Para hacer un insert con Oracle SQL desde una tabla de una base de datos TablaBD1 a otra base de datos TablaBD2 a través de un dblink debería haber dos maneras:
 

1- Crear el database link en la base de datos origen BD1 y hacer el insert hacia la tabla de la base de datos destino BD2 a través de este dblink

SQL en la base de datos Oracle BD1:

CREATE [PUBLIC] DATABASE LINK BD1toBD2_dblink
CONNECT TO usuario2
IDENTIFIED BY password2
USING 'BD2';

INSERT INTO TablaBD2@BD1toBD2_dblink
(SELECT * FROM TablaBD1);

* Para simplificar suponemos que las tablas tienen la misma estructura
 

2- Crear el database link en la base de datos destino BD2 y hacer el insert seleccionando los registros de la tabla de la base de datos origen BD1 a través de este dblink:

SQL en la base de datos Oracle BD2:

CREATE [PUBLIC] DATABASE LINK BD2toBD1_dblink
CONNECT TO usuario1
IDENTIFIED BY password1
USING 'BD1';

INSERT INTO TablaBD2
(SELECT * FROM TablaBD1@BD2toBD1_dblink);

 

Yo hasta ahora he utilizado siempre el SQL de la segunda opción para enlazar bases de datos Oracle, supongo que porque parece más simple utilizar un dblink para seleccionar datos de una tabla de una base de datos remota que para insertarlos remotamente. Pues parece que lo mejor es buscar las cosas simples porque he podido comprobar que la primera opción no funciona, por lo menos a mi.

He probado a hacerlo creando con SQL un dblink entre dos bases de datos Oracle 10g y la inserción no se realizaba. Lo curioso es que la sentencia SQL, en lugar de devolverme un error me devolvía un mensaje de '0 registros insertados', cuando haciendo la SELECT sí que obtenía registros.

Yo he llegado a la conclusión de que la opción de insertar datos directamente en una tabla de una base de datos Oracle remota a través de un database link no funciona, hay que hacer una SELECT desde la base de datos 'destino'. Para simplificar, la @ ha de estar en la tabla de la sentencia SQL de SELECT, no en la de INSERT.

Si alguien tiene una explicación mejor le agradecería que lo comentara, porque el tema me parece bastante curioso.

 

Saludos Cordiales,
Tengo dos base de datos en Oracle 9i y necesito efectuar replicacion de datos entre ellos, ahora mismo uso DBLINK para mantener actualizada la información, pensamos cambiar a vistas materializadas, pero recientemente eschamos el STREAMS de Oracle, lo ha utilizado? podria sugerirme algunos link o información al respecto? el problema básico es que tengo una TABLA A en una DB 1 que constantemente se incrementa en el número de registros (Ocurren INSERT constantemente) y debemos actualizar la TABLA B en una BD 2. Cuando insertamos en la TABLA B cada vez que se inserte en la TABLA A se quedan conexiones abiertas que nunca se cierran y degrada el performance de la BD 1 y de la BD 2.
Puedes Ayudarnos?
Gracias de antemano

En respuesta a por Robely (no verificado)

Las vistas materializadas pueden ser un buena opción para gestionar esta replicación, pero si las inserciones son muy frecuentes donde vas a notar mejora, con cualquier método que utilices para refrescar, es si en lugar de actualizar cada vez que insertas en la tabla origen, lo haces de manera agrupada, seleccionando los registros que han cambiado cada cierto tiempo para hacer un sólo insert cada x tiempo en lugar de un insert/update por registro creado o modificado.

Sobre STREAMS de Oracle yo no los he utilizado nunca, así que si los pruebas y nos explicas qué tal funcionan te lo agradezco.

Sobre vistas materializadas, te enlazo un artículo que explica cómo utilizar las vistas materializadas, e incluye enlaces a la documentación oficial de Oracle, espero que te ayude.

Y si te quedan dudas, en el foro tenemos abierto un tema sobre vistas materializadas de Oracle.

Saludos,

En respuesta a por Manuel1222 (no verificado)

Hola Manuel.

El DBLink es para utilizarlo desde la instancia de base de datos en la que se define, y desde esta poder enlazar con otras, pero de manera unidireccional, tanto para modificaciones como para consultas.

Es decir, que desde las bases de datos que se enlazan con el database link no se puede utilizar ni se ve este mismo dblink para conectar con la primera. Para hacerlo habria que definir un nuevo dblink en la otra base de datos.

Saludos!

 

Amigo necesito realizar un insert desde una bd SQL Server a Oracle, como hago para ejecutar o hacer un llamado a un Stored Procedure que realice un insert en la BD Oracle  desde una Bd SQL Server

Oracle 10g: Posible optimización de volcado masivo de datos

Oracle 10g: Posible optimización de volcado masivo de datos il_masacratore 12 Febrero, 2010 - 18:06

 

En Oracle SQL, se pueden optimizar ejecuciones batch que hagan un volcado masivo de datos en una misma tabla usando sentencias de insert o update por registro dentro de un bloque. Se pueden optimizar con el uso de parámetros (si el cliente lo permite) o si usamos odbc con bind variables.

Recordemos los pasos que sigue Oracle para procesar una consulta:

  1. Validación Sintáctica
  2. Validación Semántica
  3. Optimización
  4. Generación del QEP (Query Execution Plan)
  5. Ejecución del QEP (Query Execution Plan)

Las sentencias SQL 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. Pero hay que ir con ojo ya que 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 Oracle SQL que se van a ejecutar repetidas veces y que no ofrezcan muchas dudas en cuanto a los posibles planes de acceso que puede generar.

En casos probados como el siguiente el tiempo de ejecución se reduce hasta en un 90% en una inserción de 100.000 registros...

 

SQL para Creación de la tabla necesaria para la prueba:

create table PRUEBA
(
 NUM number(22),
 TEXTO varchar(100)
);

 

SQL de Carga con parámetros por valor:

declare
 v_i number;
begin
 loop
 INSERT INTO PRUEBA VALUES (3, '50'); 
 v_i := v_i + 1;
 exit when v_i > 1000000;
 end loop;
rollback;
end;

 

SQL de Carga con parámetros usando bind variables

declare
 v_i number;
begin
 loop
 execute immediate 'INSERT INTO PRUEBA VALUES (:x, :y)' using 3, '50'; 
 v_i := v_i + 1;
 exit when v_i > 1000000;
 end loop;
rollback;
end;

Adjunto los resultados de la prueba realizada antes de aplicar cambios en producción (un viernes por la tarde )

 

FILAS POR VALOR CON BIND VARIABLES
10000 5,3350 0,4370
100000 58,5160 6,1000
1000000 (A)570,1060 (B)54,1950

 

 

 

Oracle Flashback Query

Oracle Flashback Query Juan_Vidal 13 Septiembre, 2011 - 09:53

 

Revisamos brevemente en este post la funcionalidad flashback query que aporta el gestor de BBDD de Oracle desde su versión 9i.

Básicamente se trata de un tipo de sql de Oracle que accede a datos que existían en la base de datos en un momento anterior, pero que en el momento en el que se ejecuta la sql pueden no existir o haber sufrido modificaciones. Para ello, Oracle utiliza los datos que quedan disponibles durante un tiempo en el segmento de UNDO. Este segmento, como es sabido, almacena los datos anteriores a una serie de modificaciones. Se utiliza para asegurar la consistencia en la lectura de una consulta previa a la confirmación de las modificaciones (commit) y pueden ser utilizados en una posible recuperación (rollback).

Gráfico ejecución flashback query de Oracle

 

La sentencia de Oracle SQL flashback query nos permite ver datos de la tabla que han sido borrados o modificados. Ejecutando una flashback query accedemos a datos de una foto de datos consistentes en un punto determinado, especificando para ello la hora del sistema o bien el número de cambio del sistema (SCN). La base de datos debe estar configurada para trabajar en  Automatic Undo Management (AUM). Para ello revisar los siguientes parámetros de la base de datos Oracle:

undo_management = auto
undo_tablespace = UNDOTBS001   (tablespace que alberga el segmento de undo)
undo_retention = 3600 (tiempo en segundos que tenemos retenido el dato en el segmento de undo)

 

Hay que tener en cuenta respecto al parámetro undo_retention que si el tablespace de UNDO no es lo suficientemente grande como para mantener ese tiempo todas las transacciones, el gestor de base de datos las va a sobreescribir. Igualmente, considerar que para poder ejecutar el comando flashback query de Oracle SQL debemos tener permisos sobre el package BDMS_FLASHBACK. Para ello:

 sys> grant execute on dbms_flashback to usuario1;

 

Veamos un ejemplo. Supongamos que queremos realizar una consulta SQL sobre una tabla que contiene facturas de clientes y queremos acceder a los datos de un cliente que previamente hemos borrado:

sys> select to char(sysdate, ‘dd-mm-yyyy hh24:mi’) fecha_sistema from dual;
fecha_sistema
-------------------------
12-09-2011 12:05

sys> delete from t_facturas where cod_cliente = ‘00125’;
4 registros borrados

sys> commit;

 

Media hora después ejecutamos:

sys> select to char (sysdate, ‘dd-mm-yyyy hh24:mi’) fecha_sistema from dual
fecha_sistema
-------------------------
12-09-2011 12:35
sys>  exec dbms_flashback.enable_at_time (to_date('12-09-2011 12:05, 'DD-MM-YYYY HH24:MI'));

sys> select to char (sysdate, ‘dd-mm-yyyy hh24:mi’) fecha_sistema from dual
fecha_sistema
-------------------------
12-09-2011 12:35
sys> exec dbms_flashback.enable_at_time (to_date(’12-09-2011 12:05, ‘DD-MM-YYYY HH24:MI’)); 
Procedimiento PL/SQL terminado correctamente.

sys> select * from t_facturas where cod_cliente = ‘00125’;
……
……
……
…… 
4 registros seleccionados.

sys> execute dbms_flasback.disable; 

sys> select count(*) from t_facturas where cod_cliente = ‘00125’; 
count(*) 
------ 
0 

 

Se puede obtener lo mismo accediendo por el número de cambio SCN:

sys> select dbms_flashback.get_system_change_number from dual; 
GET_SYSTEM_CHANGE_NUMBER 
------------------------ 
1307125 

sys> exec dbms_flashback.enable_at_system_change_number(1307125); 
Procedimiento PL/SQL terminado correctamente

 

Existe también la posibilidad de emplear la sentencia ‘select ... as of...’

sys> select * from t_facturas where cod_cliente = ‘00125’ as of timestamp to_timestamp (’12-09-2011 12:05', ‘DD-MM-YYYY HH24:MI’);
sys> select * from t_facturas where cod_cliente = ‘00125’ as of scn 1307125; 

 

Hay que tener en cuenta que mientras la sesión está en el modo Flashback Query, solo podemos ejecutar sentencias SELECT. Las sentencias SQL de actualización (insert, delete y update) no están permitidas.

En el trabajo diario con esta opción es útil el uso de tablas temporales para trabajar con los datos recuperados:

sys> create table t_facturas_ant as      (select * from t_facturas       where cod_cliente = ‘00125’       as of timestamp to_timestamp (’12-09-2011 12:05, ‘DD-MM-YYYY HH24:MI’);

 

Otras opciones Flashback Query

A continuación listamos algunas funcionalidades que aporta Oracle relacionadas con operaciones Flashback Query:

  • Flashback Version Query: Acceso al histórico de cambios de una tabla.
  • Flashback Transaction Query: Acceso al histórico de cambios de una transacción determinada.
  • Flashback Table: Acceso a datos anteriores, pero para una única tabla.
  • Flashback Drop: Recuperar una tabla borrada (‘papelera reciclaje’).
  • Flashback Database: Permite dejar la BBDD tal y como se encontraba en un tiempo pasado. Similar a restaurar un backup, pero con las limitaciones temporales de los procesos flashback, aunque mucho más rápido que recuperar la copia del backup.  Es necesario tener el modo flashback activado, así como la flash recovery area.

Algunas de estas opciones requieren que el gestor sea 'Enterprise Edition'.

Se trata, como se ha dicho, de una sentencia de Oracle SQL bastante útil, con múltiples opciones de “rebobinado” y que nos puede sacar de más de un apuro.

www.datademy.es

 

Recopilación scripts y consultas útiles de Oracle

Recopilación scripts y consultas útiles de Oracle Carlos 14 Marzo, 2007 - 00:33

¿Quién no tiene su chuleta de consultillas útiles que se suelen utilizar en el día a día, y en nuestras aventuras y desventuras con la base de datos?

Listado de consultas SQL de Oracle, la mayoría sobre las vistas del diccionario de Oracle, para obtener información sobre objetos de la base de datos propios o a los que tiene permisos de acceso si se trata de un usuario normal, y sobre objetos de cualquier usuario si el usuario es DBA.

La idea es crear un pequeño un repositorio para poder consultar desde cualquier lugar para facilitarnos la vida, o sacarnos de algún que otro apuro.

Consultas para usuarios de Oracle

Consultas que se pueden hacer a nivel de usuario sin privilegios de administrador, suelen ir también sobre el diccionario de datos de Oracle, pero retornan datos de los objetos a los que el usuario tiene acceso

-- Consulta Oracle SQL sobre el Diccionario de datos 
-- Con esta vista se consultan los nombres de las tablas o vistas del diccionario a las que el usuario tiene acceso, útil para saber dónde buscar información sobre objetos a los que el usuario tiene acceso

select * from dictionary where table_name like '%TABLES%'
-- Consulta Oracle SQL que muestra los metadatos de la/s tabla/s especificada/s por nombre
-- (en este caso todas las tablas que lleven la cadena "XXX")

select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%'

-- Consulta Oracle SQL que muestra las descripciones de los campos de una tabla especificada 
   (en este caso todas las tablas que lleven la cadena "XXX")

select * from ALL_COL_COMMENTS where upper(table_name) like '%XXX%'

-- Consulta Oracle SQL para conocer las tablas propiedad del usuario actual

select * from user_tables
-- Consulta Oracle SQL para conocer todos los objetos propiedad del usuario conectado a Oracle

select * from user_catalog
-- Consulta Oracle SQL para conocer los productos Oracle instalados y la versión

select * from product_component_version
-- Consulta Oracle SQL para conocer los roles y privilegios por roles

select * from role_sys_privs
-- Consulta Oracle SQL para conocer las reglas de integridad y columna a la que afectan

select constraint_name, column_name from sys.all_cons_columns
-- Consulta Oracle SQL para conocer las tablas de las que es propietario un usuario, en este caso "xxx"

SELECT table_owner, table_name from sys.all_synonyms where table_owner like '%xxx%'

-- Consulta Oracle SQL como la anterior, pero de otra forma más efectiva 
   (tablas de las que es propietario un usuario)

SELECT DISTINCT TABLE_NAME
FROM ALL_ALL_TABLES
WHERE OWNER LIKE '%HR%'

-- Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta sin incluir los índices de la misma

select sum(bytes)/1024/1024 MB from user_segments
where segment_type='TABLE' and segment_name='NOMBRETABLA'
-- Consulta Oracle SQL para conocer el tamaño ocupado por una tabla concreta incluyendo los índices de la misma

select sum(bytes)/1024/1024 Table_Allocation_MB from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name from user_indexes where table_name='NOMBRETABLA'))
-- Consulta Oracle SQL para conocer el tamaño ocupado por una columna de una tabla

select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB from NOMBRETABLA-- Consulta Oracle SQL para obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...

SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD'
order by object_name

 

Consultas para DBAs de Oracle

Consultas sobre vistas de sistema, normalmente requieren permisos de DBA o administrador

-- Consulta Oracle SQL sobre la vista que muestra el estado de la base de datos

select * from v$instance
-- Consulta Oracle SQL que muestra si la base de datos está abierta

select status from v$instance
-- Consulta Oracle SQL sobre la vista que muestra los parámetros generales de Oracle
-- Parámetros de Oracle, valor actual y su descripción

SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1

select * from v$system_parameter
-- Consulta Oracle SQL para conocer la Versión de Oracle

select value from v$system_parameter where name = 'compatible'
-- Consulta Oracle SQL para conocer la Ubicación y nombre del fichero spfile

select value from v$system_parameter where name = 'spfile'
-- Consulta Oracle SQL para conocer la Ubicación y número de ficheros de control

select value from v$system_parameter where name = 'control_files'
-- Consulta Oracle SQL para conocer el Nombre de la base de datos

select value from v$system_parameter where name = 'db_name'
-- Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle 
-- Para visualizarla es necesario entrar con privilegios de administrador

select osuser, username, machine, program
from v$session
order by osuser
-- Consulta Oracle SQL que muestra el número de conexiones actuales a Oracle 
-- agrupado por aplicación que realiza la conexión

select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program
order by Numero_Sesiones desc
-- Consulta Oracle SQL que muestra los usuarios de Oracle conectados 
-- y el número de sesiones por usuario

select username Usuario_Oracle, count(username) Numero_Sesiones
from v$session
group by username
order by Numero_Sesiones desc
-- Consulta Oracle SQL que muestra propietarios de objetos y número de objetos por propietario

select owner, count(owner) Numero
from dba_objects
group by owner
-- Consulta Oracle SQL para el DBA de Oracle que muestra los tablespaces, 
-- el espacio utilizado, el espacio libre y los ficheros de datos de los mismos

Select t.tablespace_name "Tablespace", t.status "Estado",
    ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
    ROUND((MAX(d.bytes)/1024/1024) -
    (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
    ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
    t.pct_increase "% incremento",
    SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC
-- Consulta Oracle SQL que muestra los usuarios de Oracle y datos suyos (fecha de creación, estado, id, nombre, tablespace temporal,...)
Select * FROM dba_users
-- Consulta Oracle SQL para conocer tablespaces y propietarios de los mismos

select owner, decode(partition_name, null, segment_name,
segment_name || ':' || partition_name) name,
segment_type, tablespace_name,bytes,initial_extent,
next_extent, PCT_INCREASE, extents, max_extents
from dba_segments
Where 1=1 And extents > 1 order by 9 desc, 3
-- Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó

select distinct vs.sql_text, vs.sharable_mem,
vs.persistent_mem, vs.runtime_mem, vs.sorts,
vs.executions, vs.parse_calls, vs.module,
vs.buffer_gets, vs.disk_reads, vs.version_count,
vs.users_opening, vs.loads,
to_char(to_date(vs.first_load_time,
'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time,
rawtohex(vs.address) address, vs.hash_value hash_value ,
rows_processed , vs.command_type, vs.parsing_user_id ,
OPTIMIZER_MODE , au.USERNAME parseuser
from v$sqlarea vs , all_users au
where (parsing_user_id != 0) AND
(au.user_id(+)=vs.parsing_user_id)
and (executions >= 1) order by buffer_gets/executions desc
-- Consulta Oracle SQL para conocer todos los tablespaces

select * from V$TABLESPACE
-- Consulta Oracle SQL para conocer la memoria Share_Pool libre y usada

select name,to_number(value) bytes
from v$parameter where name ='shared_pool_size'
union all
select name,bytes
from v$sgastat where pool = 'shared pool' and name = 'free memory'
-- Cursores abiertos por usuario

select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3
-- Consulta Oracle SQL para conocer los aciertos de la caché (no debería superar el 1 por ciento)

select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');
-- Sentencias SQL completas ejecutadas con un texto determinado en el SQL

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and upper(d.sql_text) like '%WHERE CAMPO LIKE%'
ORDER BY c.sid, d.piece
-- Una sentencia SQL concreta (filtrado por sid)

SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
and sid = 105
ORDER BY c.sid, d.piece
-- Consulta Oracle SQL para conocer el tamaño ocupado por la base de datos

select sum(BYTES)/1024/1024 MB from DBA_EXTENTS
-- Consulta Oracle SQL para conocer el tamaño de los ficheros de datos de la base de datos

select sum(bytes)/1024/1024 MB from dba_data_files
-- Consulta Oracle SQL para conocer el espacio ocupado por usuario

SELECT owner, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by owner
-- Consulta Oracle SQL para conocer el espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero

SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_NAME
order by 2 desc
-- Consulta Oracle SQL para conocer el espacio ocupado por los diferentes segmentos 
   (tablas, índices, undo, rollback, cluster, ...)

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_TYPE

 


 

Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.


 

Saludos a todos.. Tengo una pregunta, en esta página se indica como ver

los datos de un tablespace desde Oracle Enterprise Manager,

pero ¿Se lo puede hacer via comandos desde SQL * Plus?..

 

En respuesta a por estyom (no verificado)

 En este mismo tema tienes la respuesta. Entre las consultas SQL de la entrada inicial hay varios que consultan datos de tablespaces, que se encuentran en vista dba_tablespaces.

Otro ejemplo podría ser una query como esta:


select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS, CONTENTS, LOGGING, FORCE_LOGGING
from   dba_tablespaces
where TABLESPACE_NAME like 'mitablespace%';

 

Para comparar dentro de un DECODE con parte de un texto del contenido de un campo, es decir, para poder utilizar un like u otras funciones en lugar de la igualdad que toma por defecto el DECODE se puede hacer lo siguiente:

SELECT DECODE(CAMPO, 
              (select CAMPO from dual where CAMPO like 'A%'), 
              'Campo comienza por A', 
              (select name from dual where name like 'B%'), 
              'Campo comienza por B', 
              'Campo no comienza ni por A ni por B') 
FROM TABLA;

Cuando un tablespace se queda sin espacio se puede ampliar creando un nuevo fichero de datos, o ampliando uno de los existentes.

Para consultar el espacio ocupado por cada datafile se puede utilizar la consulta de la lista anterior:

•• Consulta Oracle SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:
SELECT t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC

Una vez que localizamos el datafile que podríamos ampliar ejecutaremos la siguiente sentencia para hacerlo:

ALTER DATABASE
    DATAFILE '/db/oradata/datafiles/datafile_n.dbf' AUTOEXTEND
    ON NEXT  1M MAXSIZE  4000M

Con esta sentencia, el datafile continuaría ampliándose hasta llegar a un máximo de 4Gb.

Si preferimos crear un nuevo datafile porque los que tenemos ya son demasido grandes, una sentencia que podríamos utilizar es la siguiente:

ALTER TABLESPACE "MiTablespace"
    ADD
    DATAFILE '/db/oradata/datafiles/datafile_m.dbf'  SIZE 
    100M AUTOEXTEND
    ON NEXT  1M MAXSIZE  1000M

Crearíamos un nuevo fichero de datos de 100 Mb, y en modo autoextensible hasta 1000 Mb. Por supuesto, el path especificado debe ser el específico de cada base de datos, y se debe utilizar para todo el proceso un usuario con privilegios de DBA.

Para matar una sesión de Oracle hay que utilizar, con un usuario con permisos de DBA, el comando

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Para obtener el SID y el SERIAL# que necesitamos se puede utilizar la consulta:

SELECT p.*, s.*
FROM v$session s, v$process p
WHERE p.addr(+)=s.paddr
ORDER BY SID

Esta consulta devolvería los datos de todas las sesiones abiertas, se pueden restringir los resultados a las sesiones que interesen añadiendo condiciones en el where.

Si el número de sesiones que hay que eliminar es elevado, se puede utilizar esta misma consulta para crear las sentencias necesarias dinámicamente:

SELECT 'alter system kill session '''||s.sid||','||p.serial#||''';'
FROM v$session s, v$process p
WHERE p.addr(+)=s.paddr
AND s.username='USER'; (por ejemplo)

Sobretodo cuidado con la condición que se incluye en el where, ya que si no se especificara nada, por ejemplo, se matarían todas las sesiones de la base de datos.

Para crear un script con estas sentencias consultar Construcción de scripts con ayuda del diccionario

En respuesta a por Carlos

Gente, capas que hay gente que ya sabia de estas funciones pero bueno simplemente para dejarlo plasmado en alguna pagina y le pueda llegar a ser de gran utilidad a todo el mundo. Lo que voy a postear me llevo un tiempo encontrarlo y la idea con esto es lograr que sea mas facil encontrarlo y ademas que funcione porque hay mucha basura dando vueltas en la internet.

una funcion muy util que recien encontre hoy dia es: SYS_CONTEXT.

NOTA: USERENV es un nombre que describe la sesion actual y con ella y la funcion sys_context se puede conseguir:

•• identificador del cliente
  SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER') FROM dual;

•• nombre del esquema donde uno esta conectado
     SELECT sys_context('userenv', 'CURRENT_SCHEMA') FROM dual;

•• ID del esquema donde uno se encuentra conectado
     SELECT sys_context('USERENV', 'CURRENT_SCHEMAID') FROM dual;

•• nombre de la base de datos.
     SELECT sys_context('USERENV', 'DB_NAME') FROM dual;

•• nombre del host
     SELECT sys_context('USERENV', 'HOST') FROM dual;

•• nombre de la instancia
     SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual;

••los formatos de moneda, fechas.
    SELECT sys_context('USERENV', 'NLS_CURRENCY') FROM dual;   SELECT sys_context('USERENV', 'NLS_DATE_FORMAT') FROM dual;

•• nombre del territorio. ejemplo : 'AMERICA';
    SELECT sys_context('USERENV', 'NLS_TERRITORY') FROM dual;

•• server host nome
     SELECT sys_context('USERENV', 'SERVER_HOST') FROM dual;

•• ID de la session del usuario
     SELECT sys_context('USERENV', 'SESSION_USERID') FROM dual;

•• SID (session number) util para matar sesiones luego con el numero.
     SELECT sys_context('USERENV', 'SID') FROM dual;

•• LISTA DE PAQUETES UTL (utilidades de oracle, envio de mails mediante SMTP entre otros)

     SELECT * FROM ALL_OBJECTS
     WHERE OBJECT_NAME LIKE '%UTL_%'
         AND OBJECT_TYPE = 'PACKAGE'

•• LISTA DE PAQUETES DBMS (otras utilidades del Data Base Manager System)

     SELECT * FROM ALL_OBJECTS
      WHERE OBJECT_NAME LIKE '%DBMS_%'
          AND OBJECT_TYPE = 'PACKAGE'

 estas dos listas digamos son utiles como para ver las que hay y luego investigar un poco mas como se utilizan en internet.

• Envio de e-mail a multiples usuarios mediante la utilidad UTL_SMTP.

Otra cosa que me costo mucho encontrar y en definitiva me lo termino contando un amigo del trabajo es como utilizar el paquete UTL_SMPT para mandar un mail a multiples recipientes. (a varias personas)

Mi idea es unicamente explicar este problema, como usarlo se los dejo para que lo investiguen que no es muy dificil.

si se tiene los mails en un VARCHAR de la siguiente manera

alguien@xxx.com;alguien1@xxx.com;alguien2@xxx.com;alguien3@xxx.com;alguien4@xxx.com

una vez hecho los pasos :

   g_mail_conn := utl_smtp.open_connection (p_mailhost,p_mailport);   -- <-- apertura de conexion.
   utl_smtp.helo(g_mail_conn,p_mailhost);
   utl_smtp.mail(g_mail_conn,p_sender);

hay que definir el recipiente con la funcion utl_smtp.rcpt(); tener en cuenta que este recipiente se va a instanciar en nuestro caso 5 veces (una vez por cada e-mail y no todos en uno solo OJO!)

osea : utl_smtp.rcpt( p_mail_conn , alguien@xxx.com );
          utl_smtp.rcpt( p_mail_conn , alguien@xxx.com1 );
          utl_smtp.rcpt( p_mail_conn , alguien@xxx.com 2);
          utl_smtp.rcpt( p_mail_conn , alguien@xxx.com 3);
          utl_smtp.rcpt( p_mail_conn , alguien@xxx.com4 );

para cortar los mails concatenados en un VARCHAR y cumplir el proposito anterior pueden usar este procesos que me toco hacer que hace lo anteriormente descrito.

PROCEDURE Pi_Prepare_Recip_Mail ( p_mail_conn IN OUT utl_smtp.connection,
                                                      p_rec_mails IN VARCHAR2)
  IS 
       MAILS      VARCHAR2(1000)  := p_rec_mails;
       SingleMail VARCHAR2(255);
       --
       NO_MORE_MAILS BOOLEAN := FALSE;
       EOM           NUMBER;          -- end of mail.
       BOM           NUMBER := 1;     -- begin of mail.
       COC           NUMBER ;         -- number of characters.
       MAIL_NUMBER   NUMBER := 1;       
 BEGIN        
       LOOP
           EOM := INSTR(MAILS,';',1,MAIL_NUMBER);   
           IF EOM = 0 THEN   
               EOM := LENGTH(MAILS) + 1;
               NO_MORE_MAILS := TRUE;
           END IF;
           COC := (EOM) - BOM;
           SingleMail := SUBSTR(MAILS,BOM,COC);
           utl_smtp.rcpt( p_mail_conn , SingleMail );   -- <-- recipiente de salida.        
           EXIT WHEN NO_MORE_MAILS;
           MAIL_NUMBER := MAIL_NUMBER + 1 ;
           BOM := EOM + 1;
         END LOOP;
 END Pi_Prepare_Recip_Mail;

se instancia este procesos pasandole la conexion de mail y la lista de mails osea:

p_rec_mail VARCHAR2(300) := alguien@xxx.com;alguien1@xxx.com;alguien2@xxx.com;alguien3@xxx.com;alguien4@xxx.com;

 instancia -->   Pi_Prepare_Recip_Mail( g_mail_conn , p_rec_mail );

En si es dificil de entender y mas de explicar, trate de ser lo mas claro posible. Cualquier duda sobre este tema escriban.

•• cambiar el lenguaje de la fecha.
select to_char(sysdate,'day', 'NLS_DATE_LANGUAGE=Spanish') from dual
select to_char(sysdate,'month', 'NLS_DATE_LANGUAGE=Spanish') from dual

•• correr las estadisticas para una determinada tabla (esto aumenta la velocidad de respuesta de las tablas en 
   aquellos casos donde el volumen de registros es considerablemente grande)
     ANALIZE TABLE <nombre de la tabla> COMPUTE STATISTICS;
     -- realizarlo en un porcentaje (en un 25 %)
     ANALIZE TABLE <nombre de la tabla> COMPUTE STATISTICS SAMPLE 25 percent;
     -- realizarlo solo para una determinada cantidad de registros)
     ANALIZE TABLE <nombre de la tabla> COMPUTE STATISTICS SAMPLE 1000 rows;

•• ponerle comentarios a las tablas y a sus columnas para que el significado de cada campo tenga mayor
   comprencion.
     COMMENT ON TABLE <table_name> IS 'comentario a escribir';
     COMMENT ON COLUMN <table_name>.<column_name> IS 'comentario a escribir aca';

Con esto hay para divertirse un buen rato..... nunca termina de sorprenderme Oracle...

Si hiciste las consultas hace tiempo y no tenías activada la auditoría de Oracle sobre el usuario que las ejecutó no creo que las puedas recuperar.

Para poder saber las sentencias SQL que ha ejecutado un usuario tienes que decir previamente a la base de datos que haga un seguimiento de las mismas, se ha de auditar al usuario.

Es una medida de seguridad, y si se abusa de ella puede llegar a afectar negativamente al rendimiento de la base de datos, por lo que deberías aplicarla sólo si es necesario. Por supuesto, necesitarás un usuario con privilegios de DBA para poder hacerlo.
En el documento Guide to auditing in Oracle applications, elaborado por www.integrigy.com, se explica bastante bien cómo activar la auditoría en bases de datos Oracle y las diferentes opciones que hay.

También puedes consultar la información del tema que hemos abierto sobre auditoría, seguimiento y seguridad en Oracle.

A partir de la versión 10g, con la consola web de administración (Enterprise Manager Console) también tienes la opción de consultar sentencias SQL ejecutadas durante las últimas 24 horas. Seguramente no te solucionará tu actual problema, pero para analizar ejecuciones y problemas más recientes siempre va bien.

 

En respuesta a por eliviced (no verificado)

Gracias por la aportación, pero más arriba betorey_24 ya nos explica cómo enviar mails con Oracle, y en castellano ;)

De todas maneras la página de BC que enlazas es una buena referencia, muchas veces he acabado en ella buscando cómo solucionar algún tema de Oracle.

Para hacer que nuestra consulta nos devuelva sólo los n primeros registros, y no saturar ni servidores ni aplicaciones cliente cuando trabajamos con tablas grandes, en Oracle tan sólo hay que añadir la condición where rownum < n a la sentencia SQL.

Por ejemplo:

SELECT * FROM tabla_ventas
WHERE rownum < 100;

Aunque nuestra tabla de ventas tenga 10.000.000 de registros, esta consulta sólo nos devolverá los primeros 99 que encuentre.

Hola Carlos, he encontrado cosas muy interesnates aqui y tambien veo que mucha ayuda, espero me puedas ayudar con un problemita que tengo. Ya tengo listo el proceso que me manda el correo desde oracle a varias persona y con un texto fijo que mando como parametro, pero me falta saber como mandar el resultado de una consulta por el mismo correo ¿Me puedes ayudar?

 

Gracias.

Estimado, una pregunta, existira alguna vista o consulta en Oracle que ayude a Saber dentro de las sesiones activas o inactivas cuales son o fueron los ultimos procesos que se ejecutaron (updates, select, delete, etc)? Gracias por tu pronta respuesta! Saludos!

En respuesta a por oquiroz (no verificado)

 Las sesiones actuales las puedes consultar con la vista v$session y las sentencias SQL que se ejecutan con v$sqltext.
Para enlazar estas dos vistas has de utilizar el campo sql_hash_value de la primera y hash_value de la segunda, y luego aplica las condiciones que te interesen.

En los scripts anteriores hay algún ejemplo de consulta de sentencias. Para sacar las sentencias que se están ejecutando podrías hacer algo así:

SELECT c.sid, c.status, d.piece, c.serial#, c.username, d.sql_text
FROM v$session c, v$sqltext d
WHERE c.sql_hash_value = d.hash_value
ORDER BY c.sid, d.piece

La ordenación por los campos sid y piece es importante porque las sentencias están 'troceadas' en diferentes registros.

En respuesta a por Carlos

 Hola Carlos,

Una consulta. Estoy queriendo subir a memoria(buffer_pool keep) ciertas tablas, pero quiero saber que tablas podrian ser, me han dicho que pueden ser las que mas se utilizan, y que sean pequeñas. Por eso queria saber si existe un query para sacar las tablas mas consultadas. O si tu tienes alguna regla de buena practica para subir las tablas a memoria. Muchas gracias de antemano por la respuesta.

Saludos,

Elizabeth

En respuesta a por eliviced

Hola Elizabeth,

 

Lo que pides si que se puede hacer porque lo hice hace tiempo, pero no encuentro el script que usé :S

Por si te sirve de ayuda era algo relacionado con el LRU(Menos Recientemente Usada) y con el MRU (Mas Recientemente Usada) ambos del Buffer Caché, ya que es aconsejable para tablas pequeñas y consultadas muy frecuentemente y pocas veces modificadas.

Saludos

En respuesta a por drakoniano

Las recomendaciones que te han dado me parecen correctas, Elizabeth. Sólo comentarte que controles bien la memoria utilizada y esas cosas, todo lo que se haga en memoria es delicado.

Gracias por la aportación, drakoniano, tiene pinta de ser un buen método. A ver si hay suerte y localizas el script..

En la entrada de blog Cómo obtener la lista de tablas con más movimiento (insert, update) en Oracle, il_masacratore publica un script que creo que también puede servir para tus propósitos, sólo te falta sacar también el tamaño de las tablas, y quedarte con las más pequeñas..

 

Hola amigos, alguien sabe que software se puede usar para ver graficamente el contenido de un paquete es decir :
el paqueteI tiene dos procesos: "proceso1","proceso2"; el proceso1 contiene: 3 Cursores y presenta 3 funciones o algo asi, cuando de un click a cada objeto que compone el paquete desplegar el cuerpo del codigo.

help me pls.

saludos

Usa Oracle SQL Developer, es gratuito y te lo puedes descargar desde la página de Oracle.

Yo lo uso y la verdad es que le veo algunos errores bastante considerables como por ejemplo

que no se pueda abrir dos sesiones a la vez o que aunque canceles una consulta, a veces,

no se cancela pero para lo que quieres te vale de sobra.

Complementando un poco la información espero les sirva esto.

MUESTRA LOS ROLES ASIGNADOS AL USUARIO ACTUAL

SELECT granted_role "Rol", admin_option "Admin" FROM user_role_privs;

 

MUESTRA LOS PRIVILEGIOS A NIVEL SISTEMA DEL USUARIO ACTUAL 

SELECT privilege "Privilegio", admin_option "Admin" FROM user_sys_privs;

 

VERIFICAR LOS ROLES DE CUALQUIER USUARIO DE LA DB, CONECTADO COMO SYS O DBA

SELECT * FROM DBA_ROLE_PRIVS

 

Saludos

 

 

Hola Carlos.. A lo mejor tu me puedes ayudar, se que hay una combinacion de memoria para que el porcentaje de aciertos de una base de datos oracle 9i no este muy baja, pero he manejado muchas hipotesis y no he podido rendir mi base de datos a un nivel optimo ( que no baje del 90% del porcentaje de aciertos) actualmente la tengo como en 60%, que puedo hacer para que el porcentaje de aciertos no sea tan bajo..

En respuesta a por ABEL

Te recomiendo aumentar el database buffer cache. El criterio que sigue para los aciertos es:

Cache Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))

Para saber como esta configurada tu base de datos :

 select physical_reads, db_block_gets, consistent_gets name

from v$buffer_pool_statistics;

 

Si no funciona:

 La relación entre paradas para acceder al redo log y accesos en caché.

 

select name, value

from v$sysstat

where name='redo entries' OR

           name = 'redo log space requests';S

 

Con una relación superior a 1:5.000 es necesario ampliar el tamaño del buffer de redo.

Los aciertos de Library Cache:

 

select sum(pins-reloads) / sum(pins) from v$librarycache;

 

Con un valor menor a 95% se recomienda aumentar el tamaño de la Shared Pool.

Los aciertos en el diccionario de datos

 

select sum(gets), sum(getmisses), (sum(getmisses)/sum(gets))*100

from v$rowcache;

 

Con un porcentaje de fallos (getmisses) frente al de aciertos (gets) mayor de 10-15% es necesario aumentar la Shared Pool y estudiar las consultas lanzadas para asegurarse de que usan parámetros.

 

Espero que te sirva

me podrian ayudar con el siguiene scrip en oracle.....

necesito obtener el nombre del primary key y los campos que lo conforman

 

select constraint_name,
(select column_name from all_cons_columns where constraint_name=p.constraint_name)columna

from all_constraints p
where owner = 'SYS' and
      table_name = 'RULE_SET_IEUAC$' and
      constraint_type = 'P'

 

me genera un error cuando la subconsulta genera dos campos

Muy útiles estos scripts.

Añado uno relativo a consulta de espacio disponible, ya habeís listado alguno, pero este me parece sencillo y de utilidad también: 

SELECT SYSDATE AS FECHA_ACT, DEDICADO.TABLESPACE "TABLESPACE", ROUND (DEDICADO.ESPACIO, 2) "ESPACIO DEDICADO (GB)", ROUND (LIBRE.ESPACIO, 2) "ESPACIO LIBRE (GB)", ROUND (DEDICADO.ESPACIO - LIBRE.ESPACIO, 2) "ESPACIO USADO (GB)", LPAD (ROUND ((LIBRE.ESPACIO / DEDICADO.ESPACIO) * 100, 2) || '%', 6, ' ') "% ESPACIO LIBRE" FROM (SELECT DDF.TABLESPACE_NAME "TABLESPACE", SUM (DDF.BYTES) / 1024 / 1024 / 1024 "ESPACIO" FROM DBA_DATA_FILES DDF WHERE DDF.TABLESPACE_NAME IN ('tablespace1', ' tablespace2') GROUP BY DDF.TABLESPACE_NAME) DEDICADO, (SELECT DFS.TABLESPACE_NAME "TABLESPACE", SUM (DFS.BYTES) / 1024 / 1024 / 1024 "ESPACIO" FROM DBA_FREE_SPACE DFS WHERE DFS.TABLESPACE_NAME IN ('tablespace1', ' tablespace2') GROUP BY DFS.TABLESPACE_NAME) LIBRE WHERE DEDICADO.TABLESPACE = LIBRE.TABLESPACE ORDER BY LIBRE.ESPACIO / DEDICADO.ESPACIO ASC

Hola !!!

     Tengo un problema que no se como resolver, tengo un procedimiento que le estoy mandando como parametro el nombre de una funcion, dentro del procedimiento quiero que se ejecute la funcion pero no se como ejecutarla, lo que tengo en el codigo es algo asi:

 

              resultado_funcion := p_nombre_funcion;

 

pero esto solo le asigna a la variable resultado_funcion el nombre de la funcion que tiene el parametro p_nombre_funcion.

 

Como puedo hacer para que se ejecute la funcion ???

 

Gracias y Saludos.

En respuesta a por isunza

Este caso requiere de uso SQL dinámico con la instrucción EXECUTE IMMEDIATE, ésta es muy potente, ya que permite el uso de parámetros con gran efeciencia. Recuerda revisar el prerequisito de roles para que se pueda ejecutar las funciones o procedimientos.

saludos

mabefa

Muchas gracias , es muy util todo lo que han puesto.

Una consulta, en mi servidor hay dos instancias y las consultas que han dado son a la instancia que

se conecta (por balanceo), hay alguna manera de hacer una consulta que retorne los resultados de ambas instancias?

O algun query para cambiar de instancia al instante sin hacer una nueva conexion? muchas gracias

 HOLA, 

 

Alguien me puede decir como puedo debuguear unas consultas, me estoy iniciando en los scripts, pero me gustaria poder ir viendo como se van tomando los datos de mi consulta,

 

Gracias.

Que tal Carlos!

Tengo una super duda, sabes tengo un concurrent que al ejecutarse demora demasiado y se me queda colgado por muchas horas, tendrás una consulta con la cual en base al Request ID o algun dato pueda obtener en que consulta esta atorado el proceso o que es lo que esta haciendo?

 

De antemano te lo agradezco.

 

Atte. Mane

En respuesta a por JDInsane

Mírate las consultas que obtienen información de la vista v$session, como por ejemplo:

-- Consulta Oracle SQL sobre la vista que muestra las conexiones actuales a Oracle
select osuser, username, machine, program
from v$session
order by osuser

 

-- Consulta Oracle SQL que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones
from v$session
group by program

 

Con estas consultas puedes localizar los identificadores que necesite para investigar despues más a fondo sobre la consulta: cursores abiertos, recursos que consume, etc.

También te puede ir muy bien consultar la información de la vista v$sqlarea, que te muestra información sobre las últimas consultas ejecutadas. Filtrando por usuario, fecha o contenido de la sentencia SQL, si es corta (si no se trunca y has de utilizar otra vista), puedes filtrar información sobre la/s consulta/s que te interesen.

-- Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
select distinct        
  vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts,       
  vs.executions, vs.parse_calls, vs.module, vs.buffer_gets, vs.disk_reads, vs.version_count,       
  vs.users_opening, vs.loads, to_char(to_date(vs.first_load_time,       
  'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time,        
  rawtohex(vs.address) address, vs.hash_value hash_value ,        
  rows_processed , vs.command_type, vs.parsing_user_id ,        
  OPTIMIZER_MODE , au.USERNAME parseuser 
from v$sqlarea vs , all_users au 
where (parsing_user_id != 0) AND (au.user_id(+)=vs.parsing_user_id) 

 

 

Hola espero me puedan ayudar, necesito un query que pueda sacar los roles que tiene asignado cada usuario, es posible? si asi fuera se los agradeceria-

 

saludos

Hola,

estoy en busca de una consulta o herramienta que me permita obtener un listado de todas las tablas que son utilizadas por un Package, Procedure o Function..

 

Desde ya, gracias por la ayuda..

 

Saludos

Fermín

En respuesta a por fgall001

Hola Fermín,

Echa un vistazo a este otro tema del foro de Oracle http://www.dataprix.com/forum/2010/02/obtener-lista-objetos-package-oracle-0

Creo que lo que se comenta te puede servir para buscar objetos de packages utilizando el diccionario de Oracle, o herramientas como Oracle SQL Developer.

Saludos,

 Buenas tardes, habra alguna manera de crear un script de toda la base de datos Oracle 10g ??

Esto es para trasladarla a otro servidor. Por supuesto se puede con un dmp, pero yo quiero hacerlo por medio de un script, si es posible...

 

Gracias.

 Estimado Carlos, 

Quisiera saber si tu o alguno de los users me pueden ayudar, necesito en una DB saber cuales son los esquemas y cuales son sus respectivas tablas y tablespace y además de eso calcular el espacio que están ocupando cada una.   Otra consulta: ¿Todos los usuarios que se crean en oracle tienen asociado un esquema?   Si consulto a dba_user puedo diferenciar cual es un usuario con esquema y sin esquema?   Gracias por tu sitio que ha sido de mucha ayuda en mi inicio en Oracle.   De antemano muchas gracias por tu tiempo.   Saludos Francisco  

En respuesta a por andychile

Hola Francisco

En las consultas de arriba tienes las vistas que te dan la información sobre todas tablas de la base de datos, y también sobre los tablespace y el espacio que ocupan, combinando la información de esas vistas creo que ya puedes sacar la información que necesitas.

En realidad el usuario y el esquema son prácticamente lo mismo. Teóricamente el esquema es el usuario más sus objetos, y se crea cuando creas el primer objeto del usuario. Sobre la diferenciación que comentas, la verdad es que no le veo mucho sentido, supongo que podrías consultar si el usuario tiene algún objeto creado. Para qué la necesitas?

 

En respuesta a por Carlos

Encontré la consulta que necesito para ver las tablas con sus tablespace y su espacio. La había revisado pero no la había encontrado, después de tu explicación y de leer del tema ya pude asimilar el tema del usuario/esquema por lo que la consulta que te hice arriba al respecto ya está saldada.

 

Muchas gracias por tu tiempo.

Un abrazo desde Chile!

Saludos

 Buenas,

 

Requiero eliminar o revocar el permiso PUBLIC de los paquetes de DBA_TAB_PRIVS (según las mejores prácticas) con la siguiente consulta ejemplifico un SELECT para verificar el dato:

 

SELECT *FROM DBA_TAB_PRIVS WHERE TABLE_NAME= 'DBMS_RANDOM' AND GRANTEE='PUBLIC';

 

Así lo revoco (consulta x): REVOKE EXECUTE ON DBMS_RANDOM FROM PUBLIC;

 

Preguntas:

 

1. Pero la base de datos Oracle se reinicia cuando lo intento revocar, a que se debe?

2. Cómo podría ejecutar un ALTER para que sea restringido dicho paquetes? o con solo ejecutar la consulta x por default se vuelve restringido el paquete?

 

Les agradezco las respuestas, por cierto toda la información de arriba me ha servido mucho.

 

Muchas gracias

 

Saludos,

Estimado, agradecería puedas ayudarme, deseo crear un script para activar multi cuncurrencias, desde ya agradezco tu ayuda.

buenos dias una pregunta, hay una Consulta Oracle SQL para conocer la Ubicación del fichero init.ora ?

Hola agradeciendo la buena voluntad para aclarar dudas, tengo una duda con respecto si es existe una tabla en Oracle que registre el usuario quien ejecuto un package con su procedimiento de almacenado en Oracle, gracias.

Hola yo tengo diversas cuestiones 1 por que cuando cree tablas en el Oracle enterprice manager no se podian ver poniendo el comando en sql plus? tube que hacerlas por comando para que se vieran 2 cual es el codigo que me permite saber el DSN? 3 cual es el codigo que me permite saber cual es el DSI?

Hola Carlos! quisiera ver si me podes dar una mano con un problema. Tengo un dblink entre una base 10 y una 11, y a veces de forma aleatoria, por lo general al principio del día, consultas que usan este dblink se quedan esperando bastante tiempo, han llegado a demorar mas de 20 minutos, pero cuando anda normal tardan menos de 1 segundo. El oem veo: Wait event "SQL*Net message from dblink" in wait class "Network" was consuming significant database time. 96,8 Wait class "Network" was consuming significant database time. 97,4 Las métricas "Database Time Spent Waiting (%)" están en 65.48835 para la clase de evento "Network" Revisamos la red y no tenemos nada que pudiera influir. Tenes alguna idea de que me puede pasar? Desde ya muchas gracias!

Saludos, este tema me ha resultado de mucha ayuda para mi proyecto, de antemano te felicito por la ayuda. Por otro lado, quisiera que alguien me ayude, necesito una consulta que me retorne el tipo de datos de cada campo de una tabla. Si alguien me ayuda lo agradecería muchísimo. Gracias y muy buen blog!

Saludos, ya encontré como obtener el tipo de datos de una columna, ademas incluye el tamaña, el propietario, si acepta o no NULL y varios otros datos interesantes respecto a la una tabla La consulta es esta: SELECT COLS.*, COM.COMMENTS FROM SYS.ALL_TAB_COLUMNS COLS LEFT JOIN SYS.ALL_COL_COMMENTS COM ON COLS.TABLE_NAME = COM.TABLE_NAME AND COLS.COLUMN_NAME = COM.COLUMN_NAME WHERE COLS.OWNER = USER AND COM.OWNER = USER AND COLS.TABLE_NAME = 'NombreDeLaTabla'; Tambien adjunto estas otras consultas, que le pueden servir a alguien Obtener el código de una función SELECT dbms_metadata.get_ddl('FUNCTION', 'Nombre de la funcion', USER) FROM dual; Obtener el código de un procedimiento almacenado SELECT dbms_metadata.get_ddl('PROCEDURE', 'Nombre del procedimiento', USER) FROM dual; Obtener el script de una tabla SELECT dbms_metadata.get_ddl('TABLE', 'Nombre de la tabla', USER) FROM dual; Obtener el script de creacion de un table space SELECT dbms_metadata.get_ddl('TABLESPACE', 'Nombre del tablespace') FROM dual; Obtener el codigo de un paquete SELECT dbms_metadata.get_ddl('PACKAGE', 'Nombre del paquete', USER) FROM dual; Obtener el codigo de una secuencia SELECT dbms_metadata.get_ddl('SEQUENCE', 'SQ_EMPLEADO', USER) FROM dual;

Hola Pomball, te paso una query sencilla. select t.table_name, t.comments, tc.column_name, tc.data_type||decode(tc.data_type,'NUMBER','('||data_precision||')','DATE','','('||data_length||')' ) data_Type, tc.NULLABLE, cc.comments from user_tab_comments t, user_tab_columns tc, user_col_comments cc where t.table_name=tc.TABLE_NAME and tc.TABLE_NAME=cc.table_name and tc.COLUMN_NAME=cc.column_name order by tc.TABLE_NAME, tc.COLUMN_ID

Buenas tardes, Estoy haciendo un export de los objetos de una base de datos de un esquema en especifico y necesito identificar los objetos a migrar, esto no lo quiero hacer manualmente, por lo cual quisiera saber si existe una forma de identificar los objetos relacionados a una tabla determinada. Por ejemplo : tengo la siguiente tabla. PRODUCTOS la cual tiene asocuado un trigger llamado TR_ID_BI el cual tiene referenciado la secuencia: SEC_BI entonces lo que no quiero hacer es por cada tabla identificar los objetos en el navegador del pl-sql Existe alguna consulta que me devuelva el trigger y la secuencia usados en esa tabla? saludos,

Buenas noches, mi pregunta es la siguiente, tengo un campo en una tabla que se llama cd_Grupo, y este campo lo tengo que comparar en otra tabla, pero no nada mas es un campo, son dos cd_Grupo_or y cd_Grupo_be, como puedo hacer que en cuando traiga el valor de la primera tabla, ese mismo compare con los otros dos campos de la otra tabla, espero haberme dado a entender, gracias.

En respuesta a por Alexrp (no verificado)

Si te he entendido bien, lo que puedes hacer es comparar con la concatenación de los dos campos. Algo así:

select * from tabla1 where cd_Grupo in (select cd_Grupo_or || cd_Grupo_be from tabla2);

Saludos,

auxilio carlos esta consulta me toma mas de 3 min... pero le quito el group by y sale en milisegundos.. pero de ley tiene q agruparse la informacion por q tiene demasiados datos: SELECT 'ALCATEL PACIFICO' AS TECNOLOGIA, ALCAP.CENTRAL, sum(ALCAP.DURACION_SEG) AS DURACION, count(ALCAP.ID) AS LLAMADAS, to_char(to_date(ALCAP.HORA_HH_MI_SS,'HH24MISS'),'HH24') AS HORA, alcap.I_DIA_SEMANA AS DIA_DE_SEMANA, 'DIA '||substr(ALCAP.FECHA_YY_MM_DD,5,2) AS FECHA_DEL_DIA FROM WF_USR_PACIF_RES_ALCATEL ALCAP WHERE ALCAP.FECHA_YY_MM_DD BETWEEN '131001'AND '131031' GROUP by ALCAP.CENTRAL, ALCAP.FECHA_YY_MM_DD, ALCAP.I_DIA_SEMANA, to_char(to_date(ALCAP.HORA_HH_MI_SS,'HH24MISS'),'HH24') order by ALCAP.CENTRAL, ALCAP.FECHA_YY_MM_DD, ALCAP.I_DIA_SEMANA, to_char(to_date(ALCAP.HORA_HH_MI_SS,'HH24MISS'),'HH24') desc;

En respuesta a por Karol1819 (no verificado)

Bueno, las agrupaciones tienen un coste, y supongo que en cada grupo no te deben 'caer' demasiados registros. Podrías reducir algo el coste intentando reducir las conversiones de fechas, o evitando la ordenación, si es posible.

Buenos tardes como puedo Implementar Scripts para identificar sesiones de base de datos y ebs. Me podrias ayudar

Por favor lo que necesito es leer los archivos de un directorio y guardar los nombres de los archivos en otro archivo para luego trabajar con los datos del archivo creado. Ademas luego de haber leído los archivos necesito cambiar su nombre a manera de tener un estado de archivos leídos y no leídos aun.
Gracias si alguien me puede echar una mano.

Como podria consultar desde plsql el tamaño de un archivo que se encuentra en una ruta de mi maquina.

Saludos

Carlos buenos días, quisiera saber cual es la mejor forma de cambiar el tipo de datos de number a varchar2, conociendo también que la tabla contiene cerca de 20 millones de registros. lo que planteo es crear un campo dummy y volcar toda la info ahi, alterar el campo,luego vaciar el campo, y volver la info al campo original por ultimo dropear el campo. Esto es lo mas optimo? gracias por la respueta

En respuesta a por Patric (no verificado)

Hola Patric

No acabo de ver bien el orden. ¿Primero alterar el campo y luego vaciarlo? Si el campo tiene datos creo que te dará error. Te copio un ejemplo del foro de la OTN de un método para cambiar el tipo de datos de un campo apoyándote en otro campo auxiliar:

SQL> create table "USER" (UserId varchar2(5))
Table created.
SQL> insert into "USER" values ('12345')
1 row created.
SQL> alter table "USER" add (UserId_tmp number(5))
Table altered.
SQL> update "USER" set userid_tmp=userid
1 row updated.
SQL> alter table "USER" set unused column userid
Table altered.
SQL> alter table "USER" rename column userid_tmp to userid
Table altered.
SQL> alter table "USER" drop unused columns
Table altered.
SQL> desc "USER"
TABLE USER
Name Null? Type
----------------------------------------- -------- ----------------------------
USERID NUMBER(5)

 

Otra opción que te podría funcionar más rápido, si tienes espacio y no hay muchas restricciones sobre la tabla, es utilizar como apoyo una tabla entera en lugar de un campo en la misma tabla. Tendrías que hacer una copia de la tabla original, y después truncar la original, modificar el tipo de datos del campo con un alter, y finalmente hacer un insert desde la copia con un TO_CHAR para el campo que quieres cambiar de tipo.

 

De todas maneras, yo haría igualmente el backup de la tabla que vas a modificar.

 

En respuesta a por Carlos

Buenas tardes carlos o cualquier que me pueda colaborar le explico con un ejemplo tengo muchos procedimiento que se manejan en form 10g y otros desde php, resulta que hay algun procedimiento no se cual que me esta borrando unos datos en una tabla me gustaria saber si esto es posible y como

 

quiero hacer un trigger que me diga que usario la fecha y que procedmiento esta invocando sea el insert el update o delete de una tabla

yo ya he hecho trigger y guarda datos normal cuando inserta actualiza o borra y guardo lo que habia antes o despues el usuario que lo hizo y la fecha hasta aqui todo perfecto, pero hay una forma de saber que procedimiento o funcion fue el que invoca la tabla aaaa de modo que el trigger capture cual fue ese procedimiento o funcion (osea el nombre) y lo guardo en mi disparador de la base, de datos de modo que asi puedo saber en donde esta el error y corregirlo mas rapido y no estar mirando todos los ciento de procedimiento que pueda existir y ahi si corregirlo, osea como se dice ir al grano, con ese trigger que quiero hacer es guardar que PROCEDIMIENTO o FUNCION (su nombre) invoca la tabla aaa

 

muchas gracias a todos estos son mis correos por si me puede escribir le estaria agradecido

 

asanchez@unet.edu.ve

lalcubo@gmail.com

Estimados, Antes que nada felicitarlo por tan útil foro, actualmente utilizo SQL Developer 4.0.3 para crear cuentas pero en los próximas semanas realizare creaciones masivas en estos momentos estoy utilizando tres sentencias las cuales me solicitan ingresar el ID de usuario 3 veces, lo que quiero lograr es que si existe algún botón para exportar algún archivo con ID masivos

Estimados, Ante todo felicitar por este gran aporte, mi consulta es que trabajo con Oracle Developer versión 4 y realizo creaciones de cuentas con 3 sentencias pero en un tiempo mas realizare creaciones masivas como puedo hacerlo existirá alguna opción de subir algún archivo y ejecutar con alguna sentencia todo... espero me puedan ayudar se los agradezco de antemano por tomarse el tiempo de leerme

Hola tengo una duda acerca de crear un nuevo esquema en una base de datos.
existe un servidor d base de datos que tiene 3 esquemas (son de 3 docentes) y ha llegado un nuevo docente asi que necesito crear un nuevo esquema pero no se que pasos seguir. En internet encuentro diversas formas y confunde.
manejo este servidor desde la terminal.

Hola, muy interesante tu sitio, tengo una pregunta que quizas es tonta pero soy nuevo en esto y es la siguiente.

¿Existe una forma de ver que afectaciones tiene la accion de un boton en una aplicacion en la base de datos?

por ejemplo que al dar click en un check y salvar las modificaciones, saber que realizo a nivel base de datos si ejecuto algun procedimiento almacenado?

 

saludos y disculpa por la pregunta un poco fuera de lugar

En respuesta a por javier bautista (no verificado)

Hola Javier

Si el botón ejecuta un procedimiento almacenado puedes revisar el código del procedimiento para comprobar qué es lo que hace, o ejecutar tú directamente el procedimiento desde la base de datos, si estás seguro de que no vas a estropear nada.

Si el procedure es muy complejo, y cuesta seguirlo, puedes al menos buscar las sentencias SQL que incluye y ver sobre qué tablas se hacen inserts, updates o deletes, y consultar después los datos de esas tablas para comprobar qué es lo que ha cambiado.

Saludos,

Como saber con una conslta si algun proceso: paquete, procedimiento o funcion esta siendo utilizada por algun usuario

 

 

Saludos....

En respuesta a por Franco Ayala (no verificado)

Hola Franco

 

Podrías probar este script que he encontrado en AskTom. Te muestra lo que cada usuario está ejecutando, y podrías adaptarlo buscando en sql_text partes del SQL de los packages o funciones que te interese controlar.

 

---------------- showsql.sql -------------------------- 
column status format a10 
set feedback off 
set serveroutput on 

select username, sid, serial#, process, status 
from v$session 
where username is not null 
/ 

column username format a20 
column sql_text format a55 word_wrapped 

set serveroutput on size 1000000 
declare 
x number; 
begin 
for x in 
( select username||'('||sid||','||serial#|| 
') ospid = ' || process || 
' program = ' || program username, 
to_char(LOGON_TIME,' Day HH24:MI') logon_time, 
to_char(sysdate,' Day HH24:MI') current_time, 
sql_address, LAST_CALL_ET 
from v$session 
where status = 'ACTIVE' 
and rawtohex(sql_address) <> '00' 
and username is not null order by last_call_et ) 
loop 
for y in ( select max(decode(piece,0,sql_text,null)) || 
max(decode(piece,1,sql_text,null)) || 
max(decode(piece,2,sql_text,null)) || 
max(decode(piece,3,sql_text,null)) 
sql_text 
from v$sqltext_with_newlines 
where address = x.sql_address 
and piece < 4) 
loop 
if ( y.sql_text not like '%listener.get_cmd%' and 
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') 
then 
dbms_output.put_line( '--------------------' ); 
dbms_output.put_line( x.username ); 
dbms_output.put_line( x.logon_time || ' ' || 
x.current_time|| 
' last et = ' || 
x.LAST_CALL_ET); 
dbms_output.put_line( 
substr( y.sql_text, 1, 250 ) ); 
end if; 
end loop; 
end loop; 
end; 
/ 

column username format a15 word_wrapped 
column module format a15 word_wrapped 
column action format a15 word_wrapped 
column client_info format a30 word_wrapped 

select username||'('||sid||','||serial#||')' username, 
module, 
action, 
client_info 
from v$session 
where module||action||client_info is not null; 

Saludos,

select * from sys.dba_users order by username como sacar los datos mas ordenados en tablas sale esto

USERNAME USER_ID PASSWORD ------------------------------ ---------- ------------------------------ ACCOUNT_STATUS LOCK_DATE EXPIRY_DA -------------------------------- --------- --------- DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED ------------------------------ ------------------------------ --------- PROFILE INITIAL_RSRC_CONSUMER_GROUP ------------------------------ ------------------------------ EXTERNAL_NAME -------------------------------------------------------------------------------- PASSWORD E -------- -

 

Hola tengo una consulta como puedo averiguar dentro de un esquema todas las tablas que ya no se estan usando por ejemplo sacar un listado con todas las temporales creadas por el usuario que ya no se registran o no tienen movimientos por años saludos y gracias.

Hola a todos expertos DBA,

Necesito llevar a cabo una analisis sobre archivos SQL REDO y deseo encontrar especificamente sobre este REDO datos relacionados a una Columna y Valor que se me han indicado buscar, actualmente mi proceso es muy rudimentario, y busco dentro de todo el REDO posibles coincidencias con el campo.

Select * FROM LOGMINER_MONITOR WHERE (LGMN_SQL_REDO LIKE '%589%' and LGMN_SQL_REDO LIKE '%SEGU466%' ) and LGMN_TIMESTAMP between to_date('03/07/2017 00:00:00','dd/mm/yyyy hh24:mi:ss') and to_date('08/07/2017 23:59:59','dd/mm/yyyy hh24:mi:ss') ​ORDER BY LGMN_TIMESTAMP DESC

Se me solicita buscar específicamente en la columna Historics el Valor 598 y el otro valor de la columna INVDS = SEGU466.

Podrian ayudarme eh dado de topes, ya que no soy nuevo en temas de BD.

Agradecere el apoyo.

Estoy realizando un query donde debo acceder a tablas que esta en otras 2 maquinas con un usuario y password distinto; y no quieren crear un dblink

Quisiera saber en oracle como extraer solo los atributos que no son nulos. Ejemplo tengo una tabla de trabajo para realizar un update a otra tabla (maestra), los campo son en común, pero solo se actualizarán de la tabla maestra aquellos campos que en la tabla de trabajo esten poblados, es decir debo descartar lo que sean null. Por favor me podrian ayudar??

Estimado, Espero se encuentre bien, Quisiera saber la forma de realizar un update de una tabla maestra desde una tabla de trabajo en donde hay campos en común pero solo se actualizarán los campos que no estan null en la tabla de trabajo. ya que asi se registraron.

Buenas tardes! Quisiera saber cómo consultar los logins de la Base de Datos que no han tenido actividad/conexión en la BD en determinado tiempo con el fin de depurar usuarios inactivos.

Hola, quisiera su ayuda para saber como hacer que los UPDATE a la tabla "X" se tomen para la base de datos "Y" sin importar desde que base de datos se ejecute o que base de datos tengan como default. La base de datos que utilizo es Oracle SqlDeveloper. Espero puedan ayudarme >_<)

Buenas tardes, gracias por los tips, me sirvieron de mucho, consulte a otro compañero sobre el tema y me comento que para ver los objetos del usuario actual era mucho mejor consultar a la vista dbaobject, mi pregunta es cual es la diferencia entre consultar a esta vista y a las tablas: user_objects, user_tables,....etc?

En respuesta a por katy (no verificado)

Hola katy

La diferencia de cara a consultar información está simplemente en la información que te muestran. Las vistas del diccionario suelen mostrar información cruzada sobre varias tablas, pero si tú tienes suficiente con la información que te da una tabla como las de user_objects o user_tables, o prefieres hacer tu misma una query que haga joins entre esas tablas para sacar la información que necesitas no tienes porqué utilizar las vistas.

Y si la vista ya se ajusta a la info que precisas, pues trabajo que te ahorras :)

Consulta Oracle SQL para conocer los aciertos de la caché (no debería superar el 1 por ciento) select sum(pins) Ejecuciones, sum(reloads) Fallos_cache, trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos from v$librarycache where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

hola Carlos,

Muchas gracias, estas consultas que compartes son un tesoro para mi. 

Espero puedas ayudarme con esta cuestión:

¿Cómo saber que usuario creo un objeto (no el owner del objeto), sino el usuario que lanzó la consulta para su creación?

por ejemplo, yo Berem con mi user lanzo el sql:   CREATE TABLE SCHEMA_DATA.TABLA_PRUEBA ....

 

owner = SCHEMA_DATA

object: Table TABLE_PRUEBA

user_que_creo_object: Berem

 

Existe alguna consulta con la que pueda ver esto?

 

gracias por tu valioso aporte.

Buen dia. 

Necesito realizar un ALTER MODIFY a una TABLA GLOBAL TEMPORAL pero Oracle me dice que el recurso está ocupado y no puede ejecutarse.

Pueden ayudarme a identificar las sesiones Activas o Inactivas que tienen la tabla ocupada. lo hice por V$sql_text y V$session me sale cualquier cantidad de sesiones, les doy killer y aun asi el recuroso está ocupado. 

 

Gracias. 

SSRS: #Error en una celda de importe decimal de reporte utiliza Oracle SQL

SSRS: #Error en una celda de importe decimal de reporte utiliza Oracle SQL il_masacratore 27 Abril, 2011 - 16:41

Hasta el momento desconozco exactamente como o donde se detalla cada tipo de error en la ejecución de un informe de reporting services de Microsoft SQL Server. He tratado con derivados de falta de permisos, procesados incompletos de cubos pero hasta ahora ningun #Error en una celda por que sí.

El error en cuestión me aparece en la ejecución de un pequeño informe que tira de un origen de datos ODBC contra una base de datos Oracle donde se muestran totales (sumas, no porcentajes) y me ha sorprendido mucho la falta de detalle sobre el error que se produce. Para más dificultad, encima es en una combinación de parámetros concreta (las n ejecuciones anteriores han funcionado) y no en toda la columna sino en una celda. Además arrastra todo subtotal o total en el que se incluya...

Después de mirar el log del servidor de Reporting Services, después de comprobar la consulta en un cliente externo con la misma, tras pensar mal del formato me da por comprobar en el diseñador de informes el origen de datos, me hago una prueba con parámetros al tuntun y bien, pero al poner los valores problemáticos consigo que se al menos aparezca el error:

Error al leer datos del conjunto de resultados de la consulta. OCI-22053: error de desbordamiento.

Pues nada, encontrado esto ya estoy más feliz ya que el error es que ado.net+oracle+odbc no lleva muy bien los números de 38 digitos...

Para esto podemos hacer un workaround que consiste en usar en la consulta la función trunc(número, decimales) de Oracle SQL para que nos trunque el valor en su parte decimal donde nosotros decidamos.

 

Traducción de terminología Oracle - DB2 LUW

Traducción de terminología Oracle - DB2 LUW Oscar_paredes 25 Marzo, 2011 - 12:52

Con la versión 9.7 de DB2 LUW, IBM hace un guiño a todos los DBA's de Oracle, mucho más numerosos en el mercado que los DBA's de DB2.

Para ello, en la versión 9.7 de DB2 LUW ha introducido modos de compatibilidad de Oracle que permiten realizar tareas en DB2 con la facilidad y conocimiento que tienen los DBA de Oracle. Sin embargo, es importante conocer la traslación de terminología entre Oracle y DB2 si tienes la intención de meterte en el mundo IBM DB2.

En este primer artículo sobre equivalencias entre IBM DB2 y Oracle, relaciono una serie de elementos para que esa introducción sea sencilla y se pueda leer la documentación de IBM DB2 fácilmente. Entre ellos, terminología general de estas bases de datos, versiones, utilidades y vistas.

 

COMPONENTES GENERALES
ORACLE DB2 LUW
Instance Instance
File/Datafile Container
Database Database
Tablespace Tablespace
Schema Schema
Table Table
Index Index
View View
Trigger Trigger
Packages Modules
Stored Procedures Stored Procedures
SQL Plus DB2 CLP
Data Block Data Page
Dictionary Catalog
Alert Log Diag log
Redo Log Log File
Segments Space Consumming Objects
SGA Instance/DB Shared Memory

 

VISTAS CATÁLOGO
ORACLE DB2 LUW

ALL_
USER_
DBA_
V$
GV$

SYSIBM.*
SYSSTAT.*
SYSCAT.*

 

UTILIDADES
ORACLE DB2 LUW
RMAN
IMPORT
EXPORT
SQL*loader
DB_VERIFY
ANALYZE
BACKUP
IMPORT
EXPORT
LOAD
RESTORE
REORG
REORGCHK
RUNSTATS

 

VERSIONES
ORACLE DB2 LUW
EXPRESS EDITION EXPRESS-C
STANDARD EDITION ONE EXPRESS EDITION
STANDARD EDITION WORKGROUP EDITION
ENTERPRISE EDITION ENTERPRISE SERVER EDITION

 

Espero que os sea útil.

 

Oscar Paredes

IT Manager
Oracle DBA

oscar.paredes@dataprix.com 

www.db2util.es

 

 

 En el artículo de IBM developerWorks Leverage your Oracle 11g skills to learn DB2 9.7 for Linux, UNIX and Windows hacen una comparativa muy buena de las similitudes y diferencias entre los dos motores, a nivel de arquitectura, objetos, gestión de memoria, etc.

Aunque todo el artículo es muy recomendable, sólo los dos esquemas de comparación a nivel de estructura de sistema que copio a continuación ya pueden resultar de gran ayuda:

 

Figure 1. Oracle system structure on Linux, UNIX, and Windows
Diagram shows instances on an Linux/UNIX/Windows machine. Within the instance is a database, which contains system tablespace, default temporary tablespace, undo tablespace, logs, and user tablespaces with their tables and indexes. 

Figure 2. DB2 on Linux, UNIX, and Windows system structure

Diagram shows instances on LUW machine. Within each instance is catalog, tempace, userspace database configuration file, logs, and tables. 

 

UPDATE con JOIN en ORACLE SQL

UPDATE con JOIN en ORACLE SQL hminguet 8 Julio, 2008 - 10:16

Supongamos que queremos actualizar en nuestra base de datos ORACLE el campo de costes de la tabla de hechos FAC_TABLE con el coste unitario de nuestra tabla de COSTES.

Con Oracle SQL podemos hacerlo de dos maneras:

  • Consulta Lenta, pero si es para pocos datos o para lanzarlo esporádicamente nos puede valer
update FAC_TABLE ft set COSTE_UNITARIO =
   (select distinct COSTE_UNITARIO
    from COSTES ct
    where (ft.id_articulo = ct.id_articulo); 

 

  • La mejor manera es esta, y el rendimimento es óptimo si tiene constraints)
UPDATE (SELECT ft.COSTE_UNITARIO AS old_coste,
               ct.COSTE_UNITARIO AS new_coste 
        FROM FAC_TABLE ft INNER JOIN COSTES ct ON ft.id_articulo = ct.id_articulo)
       ) SET old_coste = new_coste;

Para que esta segunda opción funcione necesitamos tener UNIQUE or PRIMARY KEY constraint en ct.id_articulo.

Si no tienes esta constraint, puedes utilizar el hint /*+BYPASS_UJVC*/ después de la palabra UPDATE (bypass update join view constraint).

El rendimiento aumenta si tenemos la constraint pero aún sin ella debe correr mucho más que la primera opción.

 

Espero que os ayude.

Héctor Minguet.

 

He tenido la oportunidad de probar este tipo de update con tablas grandes, de varios millones de registros, y realmente funciona como comentas.
He lanzado un update con la primera opción y he decidido cancelarlo cuando he visto que comenzaba a afectar negativamente al rendimiento de la base de datos. El tiempo estimado que me daba la consola de Enterprise Manager para terminar era de 1 hora y media.

Después de cancelar he probado la segunda opción y me he encontrado con el error ORA-01779 porque no tenía una clave única definida sobre el campo de la tabla con la que hacía la join. Como la tabla era demasiado grande para crear un índice único sin estudiarlo primero, he probado la opción de incluir el hint /*+BYPASS_UJVC*/ (para hacer esto hay que asegurarse antes de que la correspondencia es realmente de 1:1, si no podemos obtener resultados inesperados), y el update se ha realizado correctamente en menos de 15 minutos, una diferencia considerable.

Ahora a ver si alguien se anima y nos cuenta la mejora que se obtiene con la segunda opción, pero creando una clave única en la tabla 'enlazada', y sin utilizar el hint.

En respuesta a por Carlos

 

Hola Hector y Carlos, 

 

Excelente hint, lo he utilizado por años sin ningun problema.

Pero hoy me paso el sig. error:

 

Tengo una tabla PRIMARIA 

* PREPAQ_MIC

 

Y tambien tengo dos vistas.

* VW_DATACRED

* VW_CNCH_MPIO

 

Esta ultima vista esta definida asiI:

CREATE OR REPLACE FORCE VIEW FIRA.VW_CNCH_MPIO
(CDGPROG, CDGEF, CDGMU)
AS
select CDGPROG, CDGEF, trim(column_value)   CDGMU
              from (    SELECT   CDGPROG, CDGEF, APO.VALORES  CDGMU
                        FROM FIRA.APL_APOYO  APO
                        WHERE CDGPROG = 'CNCH' AND CDGMU = 'VAR'
                        ORDER BY CDGEF, CDGMU
                   ) t,
                   xmltable(('"' || replace(CDGMU, ',', '","') || '"'));

Con el sig. script UPDATE, intento modificar un campo de la tabla primaria (PRE.APOYO) con el valor de la vista anterior

UPDATE /*+BYPASS_UJVC*/
    (
        SELECT PRE.ESQUEMA, PRE.STATUS, PRE.NUMCONTROL, PRE.CLNS, PRE.CDGRG, PRE.CDGCLNS, PRE.CICLO, PRE.CDGCL, PRE.CANTENTRE, PRE.SUSTRAE, PRE.MOTIVORECHAZO, PRE.APOYO AS PRE_APOYO,
               VDC.CDGEF, VDC.CDGMU, VDC.MUNICIPIO, VDC.CDGLO, VDC.LOCALIDAD,  VDC.POB_LOC, VDC.GRADMARG, 
               VDC.ACTIVIDAD, VDC.CVESECTOR, VDC.NOM_SECTOR, VDC.CDGSUBSEC, VDC.NOM_SUBSECTOR, 
               CNCH.CDGPROG AS APOYO_CNCH
            FROM PREPAQ_MIC     PRE,
                 VW_DATACRED    VDC,
                 VW_CNCH_MPIO   CNCH  
            WHERE
                PRE.CLNS = VDC.CLNS AND PRE.CDGCLNS = VDC.CDGCLNS AND PRE.CICLO = VDC.CICLO AND PRE.CDGCL = VDC.CDGCL
                AND VDC.CDGEF = CNCH.CDGEF AND VDC.CDGMU = CNCH.CDGMU
                AND TRIM(PRE.APOYO) IS NULL
    ) SET PRE_APOYO = APOYO_CNCH

Sin embargo al ejecutar el script señala el siguiente error:

ORA-01031: Privilegios insuficientes.

La vista fue creada por el mismo usuario que lo ejecuta (FIRA) y tienen permiso de crear tablas (grant create table to FIRA)

Ojala me puedas ayudar

Saludos!

En respuesta a por arzamm@yahoo.com

No sé si la manera en que están definidas las vistas puedan influir en algo, y también es importante que te asegures de que la correspondencia de valores en la join es de 1 a 1.

Para localizar el problema yo probaría a simplificar la consulta. Para saber si tiene alguna relación con las vistas podrías crear tablas físicas a partir de los valores de una consulta de las vistas, y validar sobre estas tablas que no tengas registros duplicados antes de probar el update con las tablas.

Otra cosa que te podría afectar es el IS NULL mezclado con las joins. Puedes definir las joins en formato FROM .. INNER JOIN .. ON, como en el ejemplo, y dejar TRIM(PRE.APOYO) IS NULL sólo en la parte del WHERE.

Espero que localices pronto dónde está el problema, y que nos lo expliques.

Un saludo,

En respuesta a por Carlos

En mi caso me daba el error: 

Error SQL: ORA-01031: privilegios insuficientes 01031. 00000 -  "insufficient privileges".

La causa es que tenia dos esquemas diferentes y en uno de ellos no tenia permisos de escritura sobre una de las tablas. Al sacar los datos del esquema que no tenia permisos, para una tabla temporal en el esquema que si tenia permisos, hizo el update correctamente, ya con las dos tablas en mi mismo esquema.

Hola se agradece este bypass.

Justamente tenía que actualizar unas tablas que tenian correlativos repetidos y tenian que ser secuenciales, realicé una tabla temporal y despues aplicar un update. De igual forma tenia que hacer un procedimiento almacenado pero en fin... con este bypass, todo bien. 

El tiempo de respuesta en la actualizacion el descuebe GRACIAS!!! 

gracias.

atte.

Maricela de CHILE

Gracias.

Lo utilice para una actualizacion de varios campos y se comporto de maravilla.

 

En respuesta a por Luis H (no verificado)

Utilice un script con este hint /*+BYPASS_UJVC*/ y funciona una muy bien... Bajo un script de 9 hs a 6 segungos....
Pero cuando lo quiero utilizar dentro de un Package aparece un error "PL/SQL: ORA-01031: Insufficient privileges"
¿ALGUIEN PUEDE AYUDARME CON ESTE TEMA?

DESDE YA MUCHAS GRACIAS.

En respuesta a por bardellica (no verificado)

Bueno lo mas probable es que sea esto, mira una cosa son los permisos que tienes tu o tu usario y otra los permisos que tiene el paquete por ejemplo digamos que tu tienes el usuario au0001 y cuando corres esto si puedes hacerlo debido a tus permisos, pero cuando pones esto dentro del paquete que esta en el esquema au0002 y dicho esquema no tiene esos priviliegios, pues es claro que te truena aun si tu como usuario au0001 si tengas el permiso y seas tu el que corre el paquete , el paquete truena por ser el o su esquema mejor dicho el que no tiene los permisos.

Hola intente hacerlo de las dos maneras, el update tradicional y con el hint, con el primero se tarda bastante, lo cancele, con el segundo me marca el error ORA-01031 - Insufficient privileges, lo estoy haciendo en el mismo esquema, la tabla de la que quiero actualizar tiene llave unique en el campo que uso para unir con la tabla que tiene la información ¿A que se debe que con uno no me marque el error y con el segundo si?

Muchisimas gracias por tu aporte, estoy iniciando con Oracle y fue muy útil tu ayuda!!!! que tengas un excelente día!

La explicación es muy buena y me ha servido en varios proyectos cuando la base de datos es Oracle 10g, sin embargo en Oracle 11g el hint ha desaparecido y no se puede utilizar, he leído algo acerca del tema y en todos lados lo que dice es que debo cambiar la instrucción y no utilizar más el hint bypass_ujvc.

¿Como han hecho para mitigar la eliminación del hint? ¿Es correcto mejor hacerlo por merge?

Gracias!

En respuesta a por Diego (no verificado)

Pues sí, si a partir de la versión 11g R2 el HINT BYPASS_UJVC ya no es válido porque Oracle lo ha dejado como deprecated hay que dejar de utilizarlo, y además hay que tenerlo en cuenta en upgrades o migraciones desde versiones anteriores, ya que si se utilizaba este HINT en versiones anteriores a la 11g, al lanzar las queries que contengan el hint BYPASS_UJVC el analizador devolverá un error y la query fallará.

Una buena alternativa, tal como ya apuntas, es utilizar un MERGE para este tipo de operaciones en que se haya recurrido al HINT al no disponer de una clave primaria para el campo por el que se hace la join de la SELECT del UPDATE, aunque si se puede conseguir la clave primaria o única para ese campo, seguramente el UPDATE será más rápido.

Al utilizar el MERGE, hay que asegurarse igualmente de que la JOIN entre tabla origen y destino sea INNER, que no existan duplicados en las tablas para esos registros, porque entonces podemos encontrarnos el error ORA-30926, o efectos inesperados en los resultados. Si se diera el caso, habría que anular los duplicados eliminándolos antes de hacer la join, o modificando la query añadiendo un group by para aplicar un SUM, MAX, MIN u otra operación sobre el campo con valores duplicados.

 


Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.


Hola!

Necesito algo de ayuda con un Update de toda una columna..

La idea es actualizar la columna reintegro de la tabla tarjeta, la cual está relacionada con otras tablas (empresa, certificado,persona,institucion) donde ley=S de la tabla institucion

Les comento las consultas que he realizado sin éxito:

==================
update tarjeta set reintegro ='0'
where (select reintegro, ley_anterior from tarjeta , institucion where ley='S')
==================
==================
Update tarjeta SET reintegro= '0'
INNER JOIN empresa on tar_emp_codigo = empcodigo
INNER JOIN certificado on empcodigo = cem_empcodigo
INNER JOIN persona on per_documento = cem_perdocumento
INNER JOIN institucion on per_inscodigo = inscodigo
WHERE (ley='S')
==================

Me podrían ayudar?

Gracias

Hola buenas tardes, no se si este sea el tema correcto pero tengo una problema muy extraño, tengo una tabla la cual si le hago un select con el esquema propietario de devuelve los datos correctamente pero al tratar de hacer un insert o un update con el mismo propietario me dice que tabla o vista no existe. no se si me pueden ayudar a descubrir el problema. tengo oracle 11gR2.

Hola, me ha sido muy útil este foro, acá encontré la solución a mi problema. Gracias! SALUDOS!! ;)

Vistas materializadas de Oracle para optimizar un Datawarehouse

Vistas materializadas de Oracle para optimizar un Datawarehouse Carlos 13 Agosto, 2008 - 09:21

Como las cargas de un Data warehouse se realizan de manera periódica, y además es habitual la creación de tablas agregadas para mejorar la eficiencia y tiempo de respuesta de nuestros informes, un recurso de optimización física que puede aportar grandes mejoras es la utilización de vistas materializadas.

Qué es una vista materializada 

La vista materializada no es más que una vista, definida con una sentencia SQL de Oracle, de la que además de almacenar su definición, se almacenan los datos que retorna, realizando una carga inicial y después cada cierto tiempo un refresco de los mismos.

Vistas materializadas en Oracle Enterprise Manager

Así, si tenemos un Datawarehouse que se actualiza diariamente, podríamos utilizar vistas materializadas para ir actualizando tablas intermedias que alimenten nuestros esquemas de DWH, o directamente para implementar tablas agregadas que se refrescarán a partir de nuestras tablas base. 

La creación de este tipo de vistas con Oracle SQL no es tan compleja como puede parecer, lo más importante es tener claro cada cuánto tiempo queremos actualizar la información de las vistas, y qué método de refresco utilizar.

También tendremos que asegurarnos de que nuestra licencia de base de datos nos permite utilizarlas (ha de ser una versión Enterprise).

 

Sintaxis básica de Oracle SQL para la creación de una vista materializada

CREATE MATERIALIZED VIEW mi_vista_materializada
 [TABLESPACE mi_tablespace]
 [BUILD {IMMEDIATE | DEFERRED}] 
 [REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicio] NEXT fecha_intervalo } | 
          {COMPLETE | FAST | FORCE} ] 
 [{ENABLE|DISABLE} QUERY REWRITE] AS 
     SELECT t1.campo1, t2.campo2 
     FROM mi_tabla1 t1 , mi_tabla2 t2 
     WHERE t1.campo_fk = t2.campo_pk AND …

 

Comentarios sobre las diferentes opciones de la sentencia Oracle SQL de creación de la vista:

  • Carga de datos en la vista

BUILD IMMEDIATE:
  Los datos de la vista se cargan en el mismo momento de la creación

BUILD DEFERRED:
  Sólo se crea la definición, los datos se cargarán más adelante. Para realizar esta carga se puede utilizar la función REFRESH del package DBMS_MVIEW:
      begin
         dbms_mview.refresh('mi_vista_
materializada');
      end;
 

  • Método y temporalidad del refresco de los datos

 Cada cuánto tiempo se refrescarán:

    REFRESH ON COMMIT:
      Cada vez que se haga un commit en los objetos origin definidos en la select 

      REFRESH ON DEMAND:
        Como con la opción DEFERRED del BUILD, se utilizarán los procedures REFRESH, REFRESH_ALL_MVIEWS o REFRESH_DEPENDENT del package DBMS_MVIEW 

        REFRESH [START WITH fecha_inicio] NEXT fecha_intervalo:
          START WITH indica la fecha del primer refresco (fecha_inicio suele ser un SYSDATE)
                          NEXT indica cada cuánto tiempo se actualizará (fecha_intervalo podría ser SYSDATE +1 para realizar el refresco una vez al día)

         

        • De qué manera se refrescarán

        REFRESH COMPLETE:
        El refresco se hará de todos los datos de la vista materializada, la recreará completamente cada vez que se lance el refresco

        REFRESH FAST:
        El refresco será incremental, es la opción más recomendable, lo de fast ya da una idea del porqué.
        Este tipo de refresco tiene bastantes restricciones según el tipo de vista que se esté creando.
        Se pueden consultar en General Restrictions on Fast Refresh de la documentación oficial de Oracle

        Una de las cosas importantes a tener en cuenta es que para poder utilizar este método casi siempre es necesario haber creado antes un LOG de la Vista materializada, indicando los campos clave en los que se basará el mantenimiento de la vista.

        Se utiliza la instrucción de Oracle SQL "CREATE MATERIALIZED VIEW LOG ON":

           CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen      
           WITH PRIMARY KEY      
           INCLUDING NEW VALUES; 

        REFRESH FORCE:
        Con esta opción se indica que si es posible se utilice el metodo FAST, y si no el COMPLETE. 

        Para saber si una vista materializada puede utilizar el método FAST, el package DBMS_MVIEW proporciona el procedure EXPLAIN_MVIEW 

        • Activación de la reescritura de consultas para optimizar el Data warehouse

        ENABLE QUERY REWRITE:
        Se permite a la base de datos la reescritura de consultas

        DISABLE QUERY REWRITE:
        Se desactiva la reescritura de consultas

        La opción QUERY REWRITE es la que más vamos a utilizar si queremos las vistas materializadas para optimizar nuestro Data warehouse.
        Esta opción permite crear tablas agregadas en forma de vistas materializadas, y que cuando se lance una SELECT la base de datos pueda reescribirla para consultar la tabla o vista que vaya a devolver los datos solicitados en menos tiempo, todo de manera totalmente transparente al usuario

        Lo único que hay que hacer es crear las tablas agregadas como vistas materializadas con QUERY REWRITE habilitado.

         

        Ejemplos de vistas materializadas de Oracle

        Son muchas combinaciones, pero la sentencia final no es tan compleja.

         

        Primer paso de la ETL de un Data warehouse

        Si quisiéramos crear con SQL de Oracle una vista materializada de una tabla que se refresque un día a la semana, y de manera incremental haríamos lo siguiente: 

        CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen
        WITH PRIMARY KEY INCLUDING NEW VALUES;
        
        CREATE MATERIALIZED VIEW mi_vista_materializada
        REFRESH FAST NEXT SYSDATE + 7 AS
           SELECT campo1, campo2, campo8
           FROM mi_tabla_origen
           WHERE campo2 > 5000;

         

        Esta vista podría servirnos para alimentar la carga de un Data Mart que se realizara semanalmente. Podríamos programarla para que se refrescara justo antes del inicio del proceso de carga, o como primer paso en la ETL, y ya tendríamos los datos necesarios actualizados, e independientes del origen de datos (no tendríamos que molestar más al operacional). Otra ventaja a tener en cuenta es que si hay algún problema con el acceso a los datos de origen, si no los hemos eliminado, en la vista materializada aún tendremos los datos del último refresco, con lo que aunque el refresco fallara no nos encontraríamos un error que truncara la carga de nuestro Data Warehouse, o una tabla vacía.

        Por supuesto, en las condiciones del WHERE de la sentencia SQL de creación podríamos seleccionar sólo los registros necesarios, sólo los del último mes, etc.

         

        Tablas agregadas para optimizar el Data Warehouse

        Otro ejemplo importante sería la utilización de vistas materializadas para la creación de tablas agregadas. 

        -- Oracle SQL para crear agregadas con materilized views
        CREATE MATERIALIZED VIEW ventas_agregadas_mv
        BUILD IMMEDIATE REFRESH COMPLETE
        ENABLE QUERY REWRITE AS
           SELECT id_producto, sum(importe) total_ventas
           FROM ventas GROUP BY id_producto;

         

        Con esta sencilla sentencia de Oracle SQL se crearía una tabla agregada de total de ventas por producto de una supuesta tabla de ventas que seria la tabla de hechos.

        A nivel de sesión también habría que asegurarse de que la opción QUERY_REWRITE estuviera activada. Por si acaso, desde SQL Plus, se puede habilitar con la sentencia SQL:

        SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

        Si ahora dentro de esta sesión se ejecuta la sentencia SQL

        SQL> SELECT sum(importe)
             FROM ventas;

        la base de datos preparará el plan de ejecución teniendo en cuenta la vista materializada creada e internamente realizará la selección sobre la vista ventas_agregadas_mv.

        Una manera sencilla de comprobarlo, aparte de examinar el plan de ejecución, o de comparar tiempos antes y después de la creación de la vista, o desactivando el QUERY_REWRITE, es comprobar que esta query SQL devuelve resultados en el mismo tiempo que la query

        SELECT sum(importe)
        FROM ventas_agregadas_mv;

         

        Para consultar más detalles, o la sintaxis SQL completa de la creación de vistas materializadas Oracle, el capítulo Create Materialized View del manual de referencia SQL de Oracle es un buen recurso.

        Con Oracle Enterprise Manager (OEM), o con la consola web de la base de datos también se pueden crear las vistas materializadas de una manera más asistida, pero igualmente es importante tener claros los conceptos antes de hacerlo.

        Vistas materializadas en Oracle Enterprise Manager

         

         


        Libros de Administración Oracle (DBA) y PL/SQL

        ¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.

        Los libros que ves a continuación son una selección de los que a mi me parecen más interesantes para aprender administración y desarrollo PL/SQL, teniendo en cuenta precio y temática, espero que te puedan ser de utilidad:

        • eBooks de Oracle gratuítos para la versión Kindle, o muy baratos (menos de 4€):