Archivos de la categoría SQL´s útiles

En esta categoría se englobarán consultas SQL útiles para la administración de ORACLE.

Saber que objetos de otros usuarios están creados en el tablespace SYSTEM

– Sacar los objetos de otros usuarios que se han creado en el tablespace system por error

SELECT owner, segment_name, segment_type, tablespace_name 
FROM dba_segments
WHERE owner NOT IN ('SYS','SYSTEM') AND tablespace_name = 'SYSTEM';

– en versiones 8.1.7

SELECT owner, segment_name, segment_type, tablespace_name 
FROM dba_segments
WHERE owner NOT IN ('SYS','SYSTEM','AURORA$JIS$UTILITY$','ORDSYS') AND tablespace_name = 'SYSTEM';

Saber cuanto ocupa una Base de datos ORACLE

En la siguiente entrada de Blog os dejo una consulta que nos dice lo que ocupa una Base de datos ORACLE. Esto nos puede ser útil para hacer previsiones a la hora de realizar Backups.

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
SELECT   round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,    round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
    round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,    round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
GROUP BY free.p
/

Consulta usando like y que no diferencie mayúsculas de minúsculas

El “problemilla” que se plantea es el siguiente …

En una base de datos, en un campo en concreto, puede aparecer un texto en mayúsculas, y el mismo texto también en minúsculas. Debía encontrar la manera de hacer una consulta con la cláusula LIKE, y que me encontrase el texto, tanto en mayúsculas como en minúsculas.  Pero realizando la consulta con like sin antes pasarlo a mayúsculas o a minúsculas, Oracle sólo encuentra el texto tal y como lo escribes. Por tanto, lo que se quiere es que si se escribe un texto que lo encuentre tanto si está escrito de una forma como de otra.

Solución:
Pasar a mayúsculas el campo y también la cadena que buscamos dentro del LIKE. De manera que la búsqueda se hará siempre en mayúsculas, y por lo tanto, encontrará el texto esté guardado en mayúsculas o minúsculas e independientemente de que quien busque a su vez lo teclee en minúsculas.

Ejemplo

accept cadena varchar2(150);

SELECT * from TABLA_EJ
WHERE UPPER(campo) LIKE UPPER('%&cadena%');

Donde campo es el campo texto donde vamos a buscar la cadena y &cadena la cadena de caracteres en concreto que queremos buscar.

Muy fácil pero hay que tenerlo en cuenta.

Va de extents …

En esta entrada de blog os pongo un par de consultas SQL que van perfectas para sacar el tamaño de los segmentos de ORACLE. Es ideal sobretodo para versiones anteriores a la 10G.

Tamaño de los segmentos por búsqueda concreta de segmento …

select initial_extent/1024/1024, next_extent/1024/1024, extents
from dba_segments
where segment_name like '%cadena_busqueda%'

Fragmentación de la Base de datos a nivel de extensiones … (Muestra los que tengan más de 5 extensiones).

REM    FRAGMENTACIÓN DE LA BASE DE DATOS.
REM     Obtiene el número de extensiones de los objetos de la base de datos
REM     y el número máximo a las que pueden llegar.

spool extents.lst

set    pagesize 66
clear  break
clear  columns
ttitle off

break on TABLESPACE skip 3 on TIPO skip 1

column EXTENTS format 999
column MAX_EXTENTS format 999
column NOMBRE format A21
column TIPO format A9
column TABLESPACE format A13
column PROPIETARIO format A13
column today new_value _date noprint

SELECT sysdate today FROM dual;

ttitle center 'Fragmentación de la Base de Datos' skip 1 -
center &_date  skip 1 -
right 'Pág.: ' format 999 sql.pno skip 2

REM Extrae los objetos de DB que tienen más de 5 extents.

SELECT tablespace_name TABLESPACE,
       segment_type TIPO,
       segment_name NOMBRE,
       owner PROPIETARIO,
       extents EXTENTS,
       max_extents MAX_EXTENTS
FROM sys.dba_segments
WHERE extents > 5
ORDER BY tablespace_name,segment_type,segment_name,owner;

spool off