Recopilación scripts y consultas útiles de Oracle

http://www.dataprix.com/oracle_decode_con_like

¿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?
Incluyo en este primer post un listado de consultas, la mayoría sobre las vistas del diccionario de Oracle, y extraídas de la web Cibermanuales.com , y animo a todo el que quiera añadir otras que considere de utilidad a responder el post publicando las suyas, a ver si entre todos creamos un repositorio que podamos consultar desde cualquier lugar para facilitarnos la vida, o sacarnos de algún que otro apuro.

•• 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
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 sobre el Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos)
select * from dictionary
•• Consulta Oracle SQL que muestra los datos de una tabla especificada (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 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 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 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'
•• 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
•• 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 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 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 los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...)
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 MB FROM DBA_EXTENTS
group by SEGMENT_TYPE
•• 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
•• 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

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

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…