Algunas consultas útiles de SQL para Oracle

Debido a mis actividades laborales, trabajo con frecuencia con la base de datos Oracle. Nunca falla que hay problemas con los rollback segments o bien hay que agregar datafiles o incluso hay que buscar qué proceso del sitema operativo se quedó colgado para eliminarlo y poder desmontar la base de datos, ver los tamaños de los índices o incluso algo tan simple como volcar la salida de na consulta a un archivo para su posterior revisión. He recopilado algunas de estas consultas y comandos que quizás les puedan ser de utilidad. Debo aclarar que no oy experto en Oracle por lo que estas consultas están sujetas a mejoras.

1. Volcado de la salida de una consulta hacia un archivo

set lines 100 pages 250
col SEGMENT_NAME format a25
col owner format a7
spool /tmp/datos.txt
select (columnaA"@@"columnaB) from miTabla;
spool off;

Primero modificamos el formato de la salida, despues indico que quiero que los datos se vayan hacia el archivo /tmp/datos.txt, ejecuto mi consulta agregando como separador de campos una doble arroba, para manipularlos facilmente y finalmente termino el proceso de volcado. Cabe mencionar que pueden mandar tantas consultas como necesiten.

2. Revisión del tamaño de los datafiles

select * from dba_data_files;

Mediante esta consulta podrán ver el tamaño que tienen disponible sus datafiles y darse una idea de si necesitan incrementarlos o crear alguno nuevo.

3. Revisión del tamaño de los tablespace_names

select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ;

Con esta consulta ppodrán ver el tamaño en bytes de sus tablespace names.

4. Revisión de todos los procesos del SO que actualmente acceden a la BdD

select ADDR, PID, SPID, USERNAME, SERIAL#, TERMINAL, PROGRAM, BACKGROUND, LATCHWAIT, LATCHSPIN from v$process;

La salida de esta consulta indica los procesos del sistema operativo que están accediendo a la base de datos. Con esto, se podrán dar cuenta qué proceso se quedó colgado, o quién está usando mucha memoria.

5. Revisión de un proceso particular del SO en la BdD

SELECT b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username, b.status, b.client_info,b.program,b.LOGON_TIME,d.sql_text,b.machine FROM v$session b, v$process c, v$sqlarea dWHERE b.paddr = c.addr and b.sql_hash_value=d.hash_value and b.sql_address=d.addressand c.sPID = &OSPID

Esta consulta lo ayudará a determinar específicamente si un proceso en particular del OS está acediendo a la base de datos.

6. Consulta de los índices asociados a una tabla en particular

select * from all_indexes where table_name = 'miTabla';

Mediante esta consulta podráns saber los nombres de los índices asociados a una tabla en particular.

7. Consulta de la columna asociada a un índice en particular.

select * from all_ind_columns where index_name = 'miIndice';

Devuelve el nombre de la columna que está indexada por un índice en particular.

8. Creación de un índice para una columna.

CREATE INDEX miIndice ON miTabla(columnaA) tablespace INDX;

9. Creación de un índice para varias columnas.

CREATE INDEX miIndice ON miTabla(columnaA,ColumnaB,..,ColumnaZ) tablespace INDX;

10. Eliminación de un índice.

DROP INDEX miBase.miIndice;

11. Alteración del tamaño de los maxextents para un índice.

ALTER INDEX miBase.miIndice STORAGE (maxextents unlimited); (Para hacerlo ilimitado)
ALTER INDEX miBase.miIndice STORAGE (maxextents 8192); (Para darle un tamaño de 8Mb)

12. Alteración del tamaño de los maxextents para una tabla.

ALTER TABLE miBase.miTabla STORAGE (maxextents unlimited); (Para hacerlo ilimitado)
ALTER TABLE miBase.miTabla STORAGE (maxextents 8192); (Para darle un tamaño de 8Mb)

13. Cálculo del tamaño del tablespace SYSTEM

select TOTAL_BLOCKS from dba_free_space_coalesced where tablespace_name = 'SYSTEM';

Muy útil para saber si se les anda llenando este espacio de tablas.

14. Alteración del tamaño de un datafile.

alter database datafile '/u02/oradata/miSID/system01.dbf' resize 5120M;

15. Agregación de un datafile.

alter tablespace system add datafile '/u02/oradata/miSID/system02.dbf' size 1024M;

Por el momento es lo que tengo, ojalá les sean de utilidad.

16. Revisión del estado de los rollback_segments

select segment_name,tablespace_name,status from dba_rollback_segs;

17. Creación de un Rollback segment

CREATE ROLLBACK SEGMENT rollbacksegment_name TABLESPACE SYSTEM storage (initial 10k next 10k minextents 2);

Y después hay que traerlo online.

alter rollback_segment rollbacksegment_name online;

18. Cambiar el tamaño de un rollback segment

ALTER ROLLBACK SEGMENT rollbacksegment_name STORAGE (MAXEXTENTS 12288);