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.

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

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..

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

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');