Consultas útiles admin BBDD Oracle

-- Obtenido de http://www.ots.ac.cr/tech/node/318

•• 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
Propietarios de objetos y número de objetos por propietario
SELECT owner, COUNT(owner) Numero
FROM dba_objects
GROUP BY owner
ORDER BY Numero DESC

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