Tips Básicos Oracle

Hola compañeros de la shell,

En esta entrada trataremos de recopilar un compendio de queries útiles para gestores Oracle que puedan servirnos independientemente del modelo de datos de la aplicación que utilice el gestor.

-- Version gestor Oracle
SELECT *
FROM PRODUCT_COMPONENT_VERSION;

-- Adaptación de TOP para Oracle.
SELECT *
FROM (
 SELECT *
 FROM MyTbl ORDERBY Fname
 )
WHERE ROWNUM = 1

-- BCK de tablas para oracle
CREATE TABLE new_table AS
SELECT *
FROM old_table

-- Informar una tabla con el contenido de otra
INSERT INTO TABLA_DESTINO
SELECT *
FROM TABLA_ORIGEN;

-- Muestra la definición de los campos de una tabla, así como sus keys y su ddl
describe NOMBRE_DE_LA_TABLA

-- Listado de indices
SELECT *
FROM all_indexes
WHERE table_name = 'NOMBRE_DE_TABLA'

-- Listado de indices y columna en la que aplican.
SELECT table_owner
 ,index_name
 ,column_position pos
 ,substr(column_name, 1, 40) column_name
FROM all_ind_columns
WHERE table_name = upper('NOMBRE_DE_TABLA')
ORDER BY table_owner
 ,index_name
 ,pos;

-- Ver constraints de una tabla de oracle
SELECT OWNER
 ,table_name
 ,column_name
 ,constraint_name
 ,constraint_type
 ,search_condition
 ,STATUS
FROM all_constraints
JOIN all_cons_columns using (
 OWNER
 ,table_name
 ,constraint_name
 )
WHERE table_name = 'NOMBRE_DE_LA_TABLA';

-- Ver todos los usuarios de un gestor Oracle:
SELECT username
FROM all_users

-- Ver tablespace de usuarios (entendemos que los tablespace son porciones de disco asociados a schemas oracle)
SELECT *
FROM USER_TABLESPACES

-- Ver número de objetos creados por tablespace
SELECT TABLESPACE_NAME
 ,count(*) AS NUMERO_OBJETOS
FROM USER_SEGMENTS
GROUP BY TABLESPACE_NAME

-- Listado de tablas que corresponden a un table_space.
SELECT *
FROM user_tables
WHERE TABLESPACE_NAME = 'nombre_del_table_space'

-- Listado de tablas ordenado por número filas
SELECT TABLE_NAME
 ,NUM_ROWS
FROM user_tables
ORDER BY NUM_ROWS DESC

-- Tamaño de tablas en oracle
SELECT segment_name
 ,bytes
 ,bytes / 1024 / 1024 EspacioMB
FROM user_segments
WHERE segment_name LIKE ('%')
ORDER BY EspacioMB DESC;

-- Tamaño de segmento de datos de usuario.
SELECT sum(EspacioMB)
FROM (
 SELECT bytes / 1024 / 1024 EspacioMB
 FROM user_segments
 WHERE segment_name LIKE ('%')
 ORDER BY EspacioMB
 );

-- Espacio libre y ocupado en GB de los diferentes tablespaces:
SELECT USED.TABLESPACE_NAME
 ,USED.USED_BYTES AS "USED SPACE(IN GB)"
 ,FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
FROM (
 SELECT TABLESPACE_NAME
 ,TO_CHAR(SUM(NVL(BYTES, 0)) / 1024 / 1024 / 1024, '99,999,990.99') AS USED_BYTES
 FROM USER_SEGMENTS
 GROUP BY TABLESPACE_NAME
 ) USED
INNER JOIN (
 SELECT TABLESPACE_NAME
 ,TO_CHAR(SUM(NVL(BYTES, 0)) / 1024 / 1024 / 1024, '99,999,990.99') AS FREE_BYTES
 FROM USER_FREE_SPACE
 GROUP BY TABLESPACE_NAME
 ) FREE ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);

-- Vistas accesibles por un usuario
SELECT OWNER AS schema_name
 ,view_name
FROM sys.all_views
ORDER BY OWNER
 ,view_name;

-- Auditoria creación vistas
SELECT *
FROM all_views
WHERE view_name = 'VIEW_NAME'

-- Auditoria creacion de objetos
SELECT *
FROM all_objects t
WHERE
 --t.object_type = 'VIEW' 
 t.object_name = 'OBJECT_OR_TABLE_NAME'
 AND t.STATUS = 'VALID'

-- Obtener tablas que contengan en su nombre string
SELECT *
FROM all_objects T
WHERE T.OBJECT_TYPE = 'TABLE'
 AND t.OBJECT_NAME LIKE ('%USER%')
 AND t.STATUS = 'VALID'

-- Buscar nombre de campo en todas las tablas del modelo de datos
SELECT TABLE_NAME
 ,COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%NOMBRE_CAMPO%'

-- Comprobar el set de caracteres o charset (set de caracteres como, por ejemplo utf)
SELECT value
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET'

-- Cambiar schema
ALTER SESSION

SET CURRENT_SCHEMA = NOMBRE_SCHEMA

-- Listar todos los procedimientos almacenados a los que el usuario que lanza la query tiene acceso
SELECT OWNER
 ,object_name
FROM all_objects
WHERE object_type = 'PROCEDURE'

-- Obtener el SID del gestor oracle contra el que hemos conectado.
SELECT sys_context('userenv', 'instance_name')
FROM dual;

-- Obtener el resto de campos de una tabla una vez seleccionado uno
SELECT T.CAMPO_1
 ,T.*
FROM TABLE T

-- Queries relacionadas con campos lob
  -- Obtener campos lob definidos como securefile
  SELECT *
  FROM all_lobs
  WHERE securefile = 'YES'

  -- Tamaño de un campo lob 
  SELECT dbms_lob.getlength("NOMBRE_CAMPO_LOB")
  FROM NOMBRE_TABLA;

-- Ver definicion/query de creacion de una vista
select DBMS_METADATA.GET_DDL('VIEW','NOMBRE_VISTA', 'NOMBRE_SCHEMA') FROM DUAL

-- Comparar dos tablas

(
		SELECT *
		FROM TABLA_ORIGINAL MINUS
		SELECT *
		FROM TABLA_ORIGINAL_BCK
		)

UNION ALL

(
	SELECT *
	FROM TABLA_ORIGINAL_BCK MINUS
	SELECT *
	FROM TABLA_ORIGINAL
	);

-- Agrupar datos por un periodo de tiempo concreto de un timestamp 
-- (Artículo original stackoverflow: https://stackoverflow.com/questions/5297372/sql-select-group-by-a-period-of-time-timestamp)

select to_char(msg_date, 'X'),count(*)
from msg
 group 
    by to_char(msg_date, 'X')

-- Auditoria de indices creados en tablespace TABLESPACE_X

SELECT TO_CHAR (CREATED,'YYYYMMDD'),COUNT(*)
FROM all_objects t
WHERE
t.object_type = 'INDEX' AND
t.object_name IN (SELECT INDEX_NAME FROM all_indexes WHERE TABLESPACE_NAME='TABLESPACE_X' AND STATUS = 'VALID')
AND t.STATUS = 'VALID'
GROUP BY TO_CHAR (CREATED,'YYYYMMDD')
ORDER BY TO_CHAR (CREATED,'YYYYMMDD') DESC

Espero que os sirva.

Un saludo desde el otro lado de la pantalla negra.