Además de las tablas de la base de datos permanentes, Oracle permite la creación de tablas temporales para mantener datos propios y exclusivos a una sesión Oracle determinada. Estos datos permanecerán en el sistema sólo durante el tiempo que dure la transacción o sesión involucrada. No obstante, al igual que para las tablas permanentes, la definición de las tablas temporales se almacena en las tablas del sistema.
La sentencia CREATE GLOBAL TEMPORARY TABLE crea una tabla temporal Oracle cuya temporalidad puede ser definida a nivel de transacción (los datos existen mientras se realiza la transacción) o a nivel de sesión (los datos existen mientras dura la sesión). Los datos en una tabla temporal son propios y privativos de la sesión Oracle que la está utilizando. Una sesión Oracle determinada puede ver y modificar los datos que durante dicha sesión se insertaron en la tabla temporal, pero estos datos no son accesibles desde otra sesión diferente. Como es lógico, la sentencia LOCK no tiene efecto sobre las tablas temporales ya que cada sesión hace uso de sus propios datos.
Así pues, los datos almacenados en una tabla temporal son visibles sólo para la sesión de Oracle que inserta datos dentro de dicha tabla. Para especificar si los datos de una tabla temporal son por sesión o por transacción, a la hora de crear la definición de la tabla, utilizaremos la cláusula ON COMMIT DELETE ROWS para indicar que la temporalidad es a nivel de transacción, o la cláusula ON COMMIT PRESERVE ROWS si queremos que la temporalidad sea a nivel de sesión.
A continuación podéis ver un ejemplo de comando SQL para crear una tabla temporal en Oracle:
CREATE GLOBAL TEMPORARY TABLE temp_listado ( nombre VARCHAR2(40), fecha_nacimiento DATE ) ON COMMIT PRESERVE ROWS
Asimismo, si ejecutamos una sentencia TRUNCATE sobre una tabla temporal, los datos que se truncarán serán los de la propia sesión desde la que se ejecuta la sentencia. Los datos que hayan podido ser insertados desde otras sesiones que estén utilizando la misma tabla, no se verán afectados por la sentencia TRUNCATE.
Los datos de una tabla temporal Oracle se borran automáticamente en el caso de que la sesión termine, bien porque el usuario desconecte, bien porque la sesión termine de una de manera anormal al producirse algún tipo de fallo.
Las tablas temporales admiten la creación de índices con el comando CREATE INDEX. Dichos índices, como las tablas, son también temporales, y los datos en el índice permanecen en el sistema mientras la tabla temporal existe.
También se pueden crear views y triggers sobre tablas temporales.
Asignación de segmentos
Para las tablas temporales, Oracle no asigna un segmento justo en el momento en que son creadas, cosa que si que hace para las tablas permanentes. Por contra, los segmentos son asignados cuando se realiza el primer INSERT.
Ser pueden ejecutar sentencias DDL (ALTER TABLE, DROP TABLE, CREATE INDEX,...) sobre las tablas temporales, pero sólo si no hay ninguna sesión que la esté utilizando en ese momento y que ya haya realizado un INSERT sobre la misma. La tabla temporal queda liberada en el momento en que la sesión termina, o cuando se ejecuta una sentencia COMMIT o ABORT si la temporalidad de la tabla fue definida a nivel de transacción.
De igual forma, los segmentos se liberan al final de la transacción o al final de la sesión según se haya definido la temporalidad de la tabla.
Artículos relacionados: Tablas externas en PLSQL.
27 comentarios:
esta buena la información pero falta más ejemplos, no tanto texto ... gracias de todas maneras
Si nutres la informacion con ejemplos sera masa visitada tu pagina.
Ciertamente este artículo se merecía incluir algún ejemplo del comando a utilizar para crear una tabla temporal en una base de datos Oracle.
Para aquellos que lo habéis pedido, ya tenéis el correspondiente ejemplo.
Entiendo entonces que una tabla temporal se maneja igual que una tabla normal y la única diferencia es que los datos son privados a tu sesión o transacción. No puedes generarla automáticamente a partir de una cláusula SELECT como en otros gestores de bases de datos sino que la defines con el resto de tablas de la aplicación.
Está bien, creo que las voy a usar :)
Ok en suma son una tablas fisicas que guarda datos por sesion o nivel de transaccion, se que son otras BD pero por ejemplo en sql server e informix si se usan verdaderas tablas temporales que solo existen mientras dura la sesion que las crea luego se dropean, se crean con "select .... insert into......" son rapidisimas por que estan en memoria, las de oracle son igual de rapidas ?, yo tengo una consulta(select) que demora mucho asi aplique join con indices, no use comodines etc, y quiero pasarla a un SP espero que se se redusca el tiempo de ejecucion.
Christian, no se trata de tablas físicas, lo único que se almacena es la definición de la tabla pero los datos almacenados son privativos de la sesión y desaparecen al terminar la misma. Lo datos se almacenan en memoria y son más rápidas que las tablas convencionales, no obstante no están enfocadas a manejar volúmenes de datos muy grandes.
Hola que tal interesante eso de las tablas temporales pero tengo una duda tengo que crear la tabla temporal cada vez que ejecuto un procedimiento almacenado o la tengo que crear antes de llamar el procedimiento???
@Julius: La tabla temporal es casi igual a una tabla normal: la creas una sola vez y la utilizas las que necesites. Lo único "temporal" en ella son las filas con datos.
Es justo al revés que en SQL Server, donde la tabla desaparece después de usarla y la tienes que crear cada vez.
Gracias kAlvaro entonces voy a empezar a utilizar para generar algunos reportes que ocupo y que tenia que esperar a que terminara, en sql server si las tenia que crear cada vez que llamaba al procedimiento, muchas gracias.
Se agradece que otros lectores se adelanten y respondan a las preguntas antes que yo.
Efectivamente KAlvaro tiene razón, la estructura de las tablas temporales se crea una sola vez y lo único realmente temporal son los datos que se insertan en las mismas.
Hola
Sabrias donde puedo encontrar un ejemplo de GLOBAL TEMPORARY TABLE en un Store.
Todo los que encuentro requieren el uso de EXECUTE IMMEDIATE y no me sirve.
El problema del EXECUTE IMMEDIATE no esta en el CREATE, INSERT O DROPE sino el QUERY ya que
el EXECUTE IMMEDIATE no se banca devolver mas de un valor.
Detallo el problema:
me mandan un parametro a un STORE y debo usar ese parametro en un QUERY con la funcion IN.
Pense en crear una tabla temporal como en el ejemplo de lo que quiero hacer
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_t (c1 number) ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE 'INSERT INTO temp_t (c1) VALUES (51)';
EXECUTE IMMEDIATE 'INSERT INTO temp_t (c1) VALUES (52)';
EXECUTE IMMEDIATE 'INSERT INTO temp_t (c1) VALUES (53)';
SELECT COUNT(1)
FROM cliente c
WHERE c.id IN (SELECT tt.id
FROM temp_t);
EXECUTE IMMEDIATE 'DROP TABLE temp_t';
agradeceria cualquier ayuda
"jbellande gmail com"
Hola Juan, no entiendo muy bien por qué utilizas el comando EXECUTE INMEDIATE, tampoco entiendo por qué al final haces un DROP de la tabla temporal, lo bueno de las tablas temporales es que las creas una vez y las dejas en la base de datos Oracle para utilizarlas cuando sea necesario, manteniendo datos temporales y propios de la sesión hasta que esta se cierra.
Si quieres puedes contactarme por email en pepelublog[arroba]gmail[punto]com
Qué pasa si:
-tengo un usuario: user1
user1 se conecta y ejecuta un stored procedure que inserta 10 registros a la tabla temporal; simultaneamente, antes que termine la sesión o transacción user1(esta es otra persona conectandose desde otra pc, pero usa el mismo usuario) se conecta y ejecuta el mismo stored procedure e inserta 5 registros en la tabla temporal, si el el de la primera conexiòn hace un select le devolverá sólo los 10 registros que insertó o 15 registros? y al segundo que se conectó, como le va?
Gracias por el post,muy interesante...espero que me puedas responder...
Hola Jlrvpuma, aunque se use el mismo usuario, cada conexión a la base de datos Oracle creará dos sesiones independientes por lo que los datos en la tabla temporal también serán independientes, es decir, el usuarios de la primera conexión verá los 10 registros que insertó y no verá los 5 que insertó el de la segunda conexión y viceversa.
Muchas gracias por tu respuesta, me sirvió bastante.
He posteado un ejemplo de como insertar dinamicamente en una tabla temporal, por si a alguien le sirve.
y como creo una tabla temporal dentro de un package de oracle, pues me sale error
Jimmy, las tablas temporales se crean como una tabla normal y luego se pueden utilizar dentro de un package de oracle. La estructura de las tablas temporales se crea en la base de datos Oracle, dicha estructura no es temporal sino que se conserva, lo que son temporales son los datos que se almacenan en dicha tabla.
@Jimmy: si no nos enseñas tu código ni nos copias el mensaje de error, ¿qué esperas que te digamos?
Hola Pepelu! Me gustaría hacerte una pregunta. Estoy investigando para hacer una presentación en clase (corta, de unos 15 minutos), y he encontrado que en SQL Server existen 2 tipos de tablas temporales: las globales (visibles para cualquier usuario conectado) y las locales (sólo se utilizan en la conexión en la que fueron creadas).
¿En Oracle existen estos tipos?
El problema es que casi todo lo que encuentro hace referencia a SQL Server, y me gustaría hacer el trabajo genérico.
Muchas gracias.
Un saludo!!
Hola Fabi, el funcionamiento de la tablas temporales en las bases de datos Oracle es como lo explico en este artículo.
La definición de la tabla temporal es global y cualquier usuario con los permisos adecuados puede utilizarla. Sin embargo los datos de dichas tablas sólo son visibles a nivel de transacción (ON COMMIT DELETE ROWS) o a nivel de sesión (ON COMMIT PRESERVE ROWS), por lo tanto sólo son visibles para el usuario que creo la sesión en cuestión.
Muchísimas gracias Pepelu!! Me has sido de gran ayuda :)
Hola, poniendo esta instrucción al inicio de la procedure (después del IS) no hace falta el execute immediate en un package.
PRAGMA autonomous_transaction;
Saludo.
No se puede crear una tabla temporal como en sql #nombretabla y solo usar al momento de la ejecución.
Hola José Luis.
Soy lector asiduo de tu blog, muy bueno por cierto :)
Tengo un problema con una GTT. Necesito hacer un ALTER TABLE para meter 5 campos nuevos, pero el problema es que la GTT fue creada con la sentencia final de "on commit preserve rows", de modo que al hacer el ALTER TABLE, me salta el ORA-14450 (intento de acceso a una tabla temporal de transacciones ya en uso). He comprobado que la tabla no está bloqueada por ninguna sesión, y por lo que he leido por ahí esto salta por haber sido creada con la sentencia "on commit preserve rows", aunque no haya transsacciones en curso...
¿Sabes alguna manera de poder añadir esos campos en la tabla sin tener que dropear y volver a crear ..?
Pregunta de examen jajaj
Un saludete!!
Me contesto a mi mismo para decir que solucioné el tema.
Resulta que la sesión que estaba bloqueando la tabla estaba inactiva, por eso pasaba desapercibida...
Paso las sentencias que he usado para encontrar la maldita sesión y matarla :
SELECT * FROM v$lock
WHERE id1 = (SELECT object_id FROM all_objects WHERE owner =schema_name AND object_name =glb temp table)
SELECT * FROM v$session WHERE sid =above query result
ALTER SYSTEM KILL SESSION 'sid,serial#';
Después de esto pude hacer el ALTER TABLE sin problemas.
Saludos!!
Gracias, tenia el mismo problema que no podia editar la tabla. COn tu ayuda pude hacerlo.
Publicar un comentario