Consultas útiles

Aquí van algunas de esas consultas que nos podrían ser útiles en el día a día.
Copien, peguen y guarden bien a mano... Espero les sean de utilidad...

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

Caracter de escape

Como ya bien sabemos % y _ son caracteres de escape que sirven para generar patrones.

% = equivale a O o N caracteres

_= equivale a O o 1 caracter

Nos podemos encontrar en situaciones en las que en el WHERE de una consulta queramos buscar caracteres que contengan _ o % por lo que necesitaremos un caracter de escape que le diga a Oracle que esos dos  caracteres no son caracteres especiales. 

Se hace de esta forma:

SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';

Matar sesiones Oracle en Linux

A veces necesitamos matar sesiones de la base de datos Oracle, muchas veces estas sesiones se quedan “colgadas” e inactivas incluso por días, otras veces aunque están en estado activo se nos pide que las eliminemos.

Para matar una sesión contamos con el comando ALTER SYSTEM KILL SESSION 'sid, serial#'

Previamente debemos de conocer el sid y el serial# de la sesión que deseamos eliminar, en este caso queremos "matar" al usuario USER1:

SQL: select sid, serial#, username, status from v$session;

       SID    SERIAL# USERNAME   STATUS
---------- ---------- ---------- --------
       370        226            ACTIVE
       373        557 USER1      INACTIVE
       381         90 SYS        ACTIVE
       383          1            ACTIVE
       385          1            ACTIVE
       386          7            ACTIVE
       389          3            ACTIVE
       390          3            ACTIVE
       391          4            ACTIVE
       393          1            ACTIVE
       394          1            ACTIVE
       395          1            ACTIVE
       396          1            ACTIVE
       397          1            ACTIVE
       398          1            ACTIVE
       399          1            ACTIVE
       400          1            ACTIVE

17 rows selected.

El comando para eliminar el sid 373 es el siguiente:

SQL: alter system kill session '373,557';

System altered.

Sin embargo hay ocasiones en que ALTER SYSTEM KILL SESSION no libera los bloqueos que tenía la sesión que matamos. Esto sucede cuando una sesión no puede ser interrumpida hasta que terminie la operación que está realizando. En este caso, la sesión mantiene todos los recursos que obtuvo de nuestro servidor hasta que termina la operación. Normalmente, la sesión que ejecutó el ALTER SYSTEM KILL SESSION recibe el mensaje: “the session has been marked to be terminated”; y la sesión aparece en v$session con status “KILLED”

SQL> select sid, serial#, username, status from v$session;

       SID    SERIAL# USERNA STATUS
---------- ---------- ------ --------
       370        108        ACTIVE
       373        557 USER1  KILLED
       381         90 SYS    ACTIVE
       383          1        ACTIVE
       385          1        ACTIVE
       386          7        ACTIVE
       389          3        ACTIVE
       390          3        ACTIVE
       391          4        ACTIVE
       393          1        ACTIVE
       394          1        ACTIVE
       395          1        ACTIVE
       396          1        ACTIVE
       397          1        ACTIVE
       398          1        ACTIVE
       399          1        ACTIVE
       400          1        ACTIVE

17 rows selected.

Para poder matar el proceso del usuario en Linux, lo hacemos con un kill -9, conociendo previamente el proceso del usuario.

*** EN EL CASO QUE ESTÉN BAJO CON ORACLE BAJO WINDOWS, SUGIERO REVISAR COMANDO ORAKILL.EXE ***

Primeramente encontramos el thread con el siguiente query (debemos de conocer el thread previamente a ejecutar el comando ALTER SYSTEM KILL SESSION, de otra manera Oracle perdería la referencia al thread en cuestión):

SQL> select p.spid Thread, s.username Username, s.program
  2  from   v$process p, v$session s
  3  where  p.addr = s.paddr and s.username is not null;

THREAD       USERNAME   PROGRAM
------------ --------  -------------
364          SYS       sqlplus.exe
4524         USER1     sqlplus.exe

El comando por lo tanto sería:

kill -9 4524

A continuación en v$session vemos que ya no existe la sesión del usuario QUICK

SQL> select sid, serial#, username, status from v$session;


       SID    SERIAL# USERNAME   STATUS
---------- ---------- ---------- --------
       370        226            ACTIVE
       381         90 SYS        ACTIVE
       383          1            ACTIVE
       385          1            ACTIVE
       386          7            ACTIVE
       389          3            ACTIVE
       390          3            ACTIVE
       391          4            ACTIVE
       393          1            ACTIVE
       394          1            ACTIVE
       395          1            ACTIVE
       396          1            ACTIVE
       397          1            ACTIVE
       398          1            ACTIVE
       399          1            ACTIVE
       400          1            ACTIVE

16 rows selected.

Y con ésto, finalmente nos liberamos de esas molestas sesiones en estado KILLED que ocupan recursos en nuestra Base de Datos Oracle.

CRS-0223: Resource 'xxx' has placement error

Me encontré con este error luego de instalar DG4ODBC y reiniciar el RAC... y lo peor era un RAC que estaba en Producción!!! He aquí el primer consejo, y que está en la tapa del libro... no tocar algo que está funcionando y menos aún hacer pruebas sobre eso... :)

Luego de algunos minutos y con los latidos a 200 por minuto, y viendo que el status de las instancias no era ONLINE... las mostraba como unknown, y obviamente no podía conectarme...

crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora.prod.db application ONLINE ONLINE
ora....d1.inst application ONLINE UNKNOWN
ora....d2.inst application ONLINE UNKNOWN
ora....SM1.asm application ONLINE ONLINE racdb1
ora....B1.lsnr application ONLINE ONLINE racdb1
ora.racdb1.gsd application ONLINE ONLINE racdb1
ora.racdb1.ons application ONLINE ONLINE racdb1
ora.racdb1.vip application ONLINE ONLINE racdb1
ora....SM2.asm application ONLINE ONLINE racdb2
ora....B2.lsnr application ONLINE ONLINE racdb2
ora.racdb2.gsd application ONLINE ONLINE racdb2
ora.racdb2.ons application ONLINE ONLINE racdb2
ora.racdb2.vip application ONLINE ONLINE racdb2

Intenté levantarlas nuevamente... con crs_start, con srvctl...
y nooo, no había caso... error!

PRKP-1001 : Error starting instance prod1 on node racdb1
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource 'ora.prod1.racdb1.inst' has placement error.

Se me complicó... dónde está la salida?!?!
Intenté levantarlas como si fueran BDs stanalone primero prod1 y luego prod2...

> export ORACLE_SID=prod1
> sqlplus /nolog
>> connect / as sysdba
>> startup;

y levantaron !!! la instancia 1 y la 2...

Bueh... salimos del paso... no se ven una a otra como un RAC pero al menos accedo a los datos... simplemente cambio las propiedades de las aplicaciones que hacen uso del RAC, y que empiecen a apuntar a una de las instancias, sin necesidad de FAILOVER ni BALANCEO... por lo menos para empezar...

Luego de un par de días de darle vueltas al asunto, encontré googleando, alguien que recomendaba matar los procesos crsd y probar levantar las instancias nuevamente... y fue lo que hice, y funcionó!

> kill - 9 --> en ambos nodos
> srvctl start instance -i prod1 -d prod --> luego con prod2

y listo!!! todo ONLINE nuevamente...

crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora.prod.db application ONLINE ONLINE racdb1
ora....d1.inst application ONLINE ONLINE racdb1
ora....d2.inst application ONLINE ONLINE racdb2
ora....SM1.asm application ONLINE ONLINE racdb1
ora....B1.lsnr application ONLINE ONLINE racdb1
ora.racdb1.gsd application ONLINE ONLINE racdb1
ora.racdb1.ons application ONLINE ONLINE racdb1
ora.racdb1.vip application ONLINE ONLINE racdb1
ora....SM2.asm application ONLINE ONLINE racdb2
ora....B2.lsnr application ONLINE ONLINE racdb2
ora.racdb2.gsd application ONLINE ONLINE racdb2
ora.racdb2.ons application ONLINE ONLINE racdb2
ora.racdb2.vip application ONLINE ONLINE racdb2

JOIN eando...

Hace casi un año que no agregaba ningún post... hoy quizás sea la excepción... o no...

Estaba pensando de que podía hablar (escribir en realidad). Se me cruzó por la mente describir los diferentes tipos de joins que podemos utilizar en una sentencia sql, y eso es lo que intentaré hacer...

Los diferentes tipos de joins y sus aplicaciones:

INNER JOIN
Es el join "mas común", por decirlo de alguna forma. Se utiliza cuando se quiere matchear dos tablas que tienen valores en común en una o más columnnas.
Se podría utilizar para matchear el id_pais de la tabla CLIENTES, con el id_pais de la tabla PAISES; eso nos traería los clientes que tengan asociado algún id_pais existente en la tabla PAISES.

OUTER JOIN

Se utiliza si se desea que el resultado no solo contenga los registros que cumplen con la condición del join, sino también cualquier registro que no cumpla de una tabla o de las demás.
Por ejemplo, se puede usar para obtener todos los clientes de la tabla CLIENES con su país asociado de la tabla PAISES, incluyendo aquellos clientes que no tienen un país asociado.

CROSS JOIN
Utilizado cuando se quiere matchear todos los registros de una tabla con cada registro de otra tabla. Este tipo de join se lo conoce como producto cartesiano.

SELF JOIN
Se aplica para matchear una tabla consigo misma. Se puede usar self join cuando una columna de una tabla debe referenciar una columna diferente en la misma tabla.
...

Transportable Tablespaces

Con esta utilidad, lo que buscamos básicamente es bajar los tiempos de pasaje de datos, más específicamente al transferir un tablespace de una BD a otra, o al recuperarlo de un estado anterior (recover).

Para poder transportar un tablespace, éste debe ser self-contained (auto-contenido), es decir, no debe contener objetos que referencien a otros objetos en diferentes tablespaces.

Cómo chequeamos si el tablespace es self-contained?

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'nombre_tablespace', incl_constraints => TRUE);

Luego chequeamos la vista transport_set_violations para ver si existe alguna violación...

SELECT * FROM transport_set_violations


RESPALDANDO EL TABLESPACE A TRANSPORTAR:

Como primer paso, luego de estos chequeos, debemos crear el "respaldo" del tablespace a transportar.

Seteamos el tablespace en cuestión en modo READ ONLY:

ALTER TABLESPACE nombre_tablespace READ ONLY;

Ejecutamos el siguiente export:

exp parfile=export_parfile.txt

-- contenido archivo export_parfile.txt
userid="sys/pass as sysdba"
transport_tablespace=y
tablespaces=(nombre_tablespace)
file=plug_in_nombre_tablespace_ts.dmp
log=plug_in_nombre_tablespace_ts.log
statistics=NONE

Respaldamos los datafiles que componen el tablespace, ésto lo podemos hacer simplemente con el comando de copia del sistema operativo (xcopy para Win o cp para Linux) o como más cómodo nos quede.

-- ejemplo respaldo bajo Linux
cp tablespace_datafile*.dbf /Respaldo

Ya con ésto tenemos todo lo necesario para poder levantar el mismo tablespace en otra BD o simplemente para utilizarlo como respaldo.

Ahora deberíamos llevar nuevamente el tablespace a modo READ WRITE para retornar a la "normalidad":

ALTER TABLESPACE nombre_tablespace READ WRITE;


RESTAURANDO EL TRANSPORTABLE TABLESPACE:

Ahora intentaremos describir cómo hacer para levantar el respaldo que realizamos en el capítulo anterior.

Si el tablespace que vamos a restaurar ya existe en nuestra BD destino, debemos eliminarlo:

DROP TABLESPACE nombre_tablespace INCLUDING CONTENTS AND DATAFILES;

Restauramos los datafiles del "respaldo", que tan cuidadosamente habíamos guardado...

-- ejemplo copia desde respaldo, supongamos orcl es la BD
cp /Respaldo/tablespace_datafile*.dbf  $ORACLE_BASE/oradata/orcl

Luego, debemos "enchufar" (no se me ocurrió otro término para traducir plug-in) el tablespace:

imp parfile=plug_in_nombre_tablespace_ts.txt

-- contenido archivo plug_in_nombre_tablespace_ts.txt
userid="sys/pass as sysdba"
transport_tablespace=y
file=plug_in_nombre_tablespace_ts.dmp               -- dmp generado en el respaldo
log=plug_in_nombre_tablespace_ts.log
tablespaces=(nombre_tablespace)
tts_owners=schema_origen
fromuser=schema_origen
touser=schema_destino
datafiles=(
'$ORACLE_BASE/oradata/orcl/tablespace_datafile1.dbf ',      -- declaramos todos los datafiles del ts
$ORACLE_BASE/oradata/orcl/tablespace_datafile2.dbf '       -- en su ubicación correspondiente
...
)

Luego de terminado el import, colocamos el tablespace en modo READ WRITE:

ALTER TABLESPACE nombre_tablespace READ WRITE;

Si seguimos los pasos al pie de la letra, deberíamos poder realizar los transportes de tablespaces sin mayores problemas. Espero que haya sido de utilidad!

Vientos de cambio...

Qué mes difícil este setiembre!!!
Vaivenes, complicaciones, interrogantes, dudas...
Ahora soplan vientos de cambio... a enfrentarlos!!!
Disculpen, nada tiene ésto que ver con Oracle... o si?