Resultado de un query de varias filas concatenadas en una

La idea de este post es acercarles una función que quizás en algún momento les sea de utilidad.

El propósito de la función es concatenar el resultado de un query que devolvió varias filas en solo 1,
la función es la siguiente:

CREATE OR REPLACE FUNCTION concatfilas(qry IN VARCHAR2) RETURN VARCHAR2 IS
filaFinal VARCHAR2(4000);
result VARCHAR2(4000);
cur sys_refcursor;
BEGIN OPEN cur FOR qry;
LOOP
FETCH cur INTO result;
EXIT WHEN cur%NOTFOUND;
IF filaFinal IS NULL THEN
filaFinal := resul;
ELSE
filaFinal := filaFinal ',' result;
END IF;
END LOOP;
RETURN filaFinal;
END;
/

Ejemplo

SQL> create table prueba (nombre varchar2(30));

Table created.

SQL> insert into prueba values(‘Luis’);

1 row created.

SQL> insert into prueba values(‘Bruno’);

1 row created.

SQL> insert into prueba values(‘Matias’);

1 row created.

SQL> insert into prueba values(‘Gonzalo’);

1 row created.

SQL> commit;

1 row created.

SQL> select concatfilas('SELECT nombre FROM prueba') as nombres
2 FROM dual;

NOMBRES
------------------------------------------------------------------
Luis, Bruno, Matias, Gonzalo

Espero les haya servido

Saludos!
Gondalf.

Perdida de un Control File

Lo que primero tenemos que tener en claro que la pérdida de un control file multiplexado inmediatamente realiza un abort a la instancia. Como nosotros tenemos multiplexado nuestro control file (de no tenerlo, hacerlo!), realizar una recuperación de la instancia no es nada complicado.

Para el siguiente ejemplo supongamos que tenemos 3 control files:
/u02/oradata/test/control01.ctl
/u03/oradata/test/control02.ctl
/u04/oradata/test/control03.ctl

Por un fallo en el /u04 perdemos el control03.ctl, por ende la instancia cae dada esta perdida.
Para poder hacer el recover de la instancia lo que hacemos es modificar el parámetro CONTROL_FILES dentro del SPFILE suprimiendo el control03.ctl.

Los pasos a seguir son:

- Levantamos la base en NOMOUNT, lo hacemos en este modo dado que en MOUNT la base ya realiza un chequeo de los control files.

SQL> startup nomount
ORACLE instance started.
Total System Global Area     188743680 bytes
Fixed Size     778036 bytes
Variable Size     162537676 bytes
Database Buffers     25165824 bytes
Redo Buffers     262144 bytes

- Con la instancia en NOMOUNT podemos modificar el paramentro CONTROL_FILE dentro del spfile:

SQL> alter system set control_files =
2 '/u02/oradata/test/control01.ctl',
3 '/u03/oradata/test/control02.ctl'
4 scope = spfile;
System altered.

- Bajamos la instancia

SQL> shutdown immediate

ORA-01507: database not mounted
ORACLE instance shut down.

- Levantamos la instancia con el nuevo parametro aplicado

SQL> startup
ORACLE instance started.
Total System Global Area       188743680 bytes
Fixed Size       778036 bytes
Variable Size       162537676 bytes
Database Buffers       25165824 bytes
Redo Buffers       262144 bytes
Database mounted.
Database opened.

- Por último lo que podemos hacer es checkear que el parametro haya quedado correcto para lo cual hacemos la siguiente consulta

SQL> select name, value
2 from v$spparameter
3 where name = 'control_files';

NAME            VALUE
--------------- --------------------------------------
control_files   /u02/oradata/test/control01.ctl
control_files   /u03/oradata/test/control02.ctl

Espero les haya servido

Saludos!
Gondalf

Renombrar un “Log Member”

Para poder renombrar un redo log member lo que necesitamos en primer lugar es el nuevo archivo de redo ya que el comando de SQL lo que hace únicamente es cambiar el puntero interno en el control file hacia el nuevo archivo de redo, es decir que el comando de SQL no modifica el archivo del sistema operativo.

Entonces, para poder renombrar el archivo de redo vamos a realizar los siguientes pasos:

1. Realizamos un shutdown de la base
SQL> shutdown immediate;

2. Copiamos o renombramos el archivo de redo utilizando comandos del sistema operativo

3. Montamos la base de datos

SQL> startup mount;

4. Realizamos el cambio en el puntero del archivo de control.

SQL> ALTER DATABASE RENAME FILE ‘old_redo_file’ TO              ‘new_redo_file’;

5. Abrimos la base de datos

SQL> ALTER DATABASE OPEN;

6. Por último realizamos un backup del archive de control ya que realizamos un cambio en el.

Espero les haya servido

Saludos!
Gondalf

Cambiando el orden de las columnas

Como alguno de ustedes ya sabrá el proceso de cambiar el orden las columnas de una tabla en Oracle no se realiza con un simple ALTER TABLE.

Para poder llevar a cabo esta tarea a continuación vamos a ver una sucesión de pasos que nos van a ayudar a solucionar nuestra necesidad.

Cabe aclarar que si la tabla tiene indices asociados primero habría que relevarlos dado que los vamos a perder con este procedimiento.

SQL> create table EJEMPLO (col1 number, col2 number, col3 number);

Table created.

SQL> desc EJEMPLO;

Name          Null?     Type
----------    ------    ----------------
COL1                    NUMBER
COL2                    NUMBER
COL3                    NUMBER

SQL> create table AUXILIAR as select col3, col2, col1 from EJEMPLO;

Table created.

SQL> drop table EJEMPLO;

Table dropped.

SQL> alter table AUXILIAR rename to EJEMPLO;

Table altered.

SQL> desc EJEMPLO;

Name          Null?     Type
----------    ------    ----------------
COL3                    NUMBER
COL2                    NUMBER
COL1                    NUMBER

Espero les haya servido.

Saludos!
Gondalf

Agregar un disco a un diskgroup en ASM

La idea de este post es mostrarles un poco como podemos agregar espacio (un disco) a nuestro ASM, primero vamos
a crear el link necesario a nuestro raw device para que ASM pueda "verlo", para realizar esto hacemos lo siguiente:

1- Nos paramos en el directorio donde tenemos creados los links a los raw devices

TEST:/u00/oradata/asm# ls -latr

total 2
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009  asm_disk_01 -> /dev/rdisk/disk61
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009  asm_disk_02 -> /dev/rdisk/disk68
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009  asm_disk_03 -> /dev/rdisk/disk73
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009  asm_disk_04 -> /dev/rdisk/disk81
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009  asm_disk_05 -> /dev/rdisk/disk86
drwxrwxr-x   2 oracle     dba      1024 May 15  2009 .
drwxrwxr-x   4 oracle     dba        96 May  7 15:22 ..

2- Una vez que el sys admin nos indica que raw tenemos presentado vamos a proceder a darle los permisos necesarios
(en este ejemplo será el disk78)

TEST:/u00/oradata/asm# chown oracle:dba /dev/rdisk/disk78

3- Generamos el link necesario con el comando de UNIX ln -s

TEST:/u00/oradata/asm# ln -s /dev/rdisk/disk78 /u00/oradata/asm/asm_disk_06

4- Revisamos nuestro nuevo link

TEST:/u00/oradata/asm# ll

total 0
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_01 -> /dev/rdisk/disk61
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_02 -> /dev/rdisk/disk68
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_03 -> /dev/rdisk/disk73
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_04 -> /dev/rdisk/disk81
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_05 -> /dev/rdisk/disk86
lrwxr-xr-x   1 root         sys         17 May   7 16:12 asm_disk_06 -> /dev/rdisk/disk78

5- Como vemos el link lo creamos con un usuario y un grupo erróneo, por lo que debemos cambiarlo, para eso ejecutamos lo siguiente

TEST:/u00/oradata/asm# chown -h oracle:oinstall asm_disk_06

6- Volvemos a chequear

TEST:/u00/oradata/asm# ll

total 0
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_01 -> /dev/rdisk/disk61
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_02 -> /dev/rdisk/disk68
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_03 -> /dev/rdisk/disk73
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_04 -> /dev/rdisk/disk81
lrwxr-xr-x   1 oracle     oinstall   17 May 15  2009 asm_disk_05 -> /dev/rdisk/disk86
lrwxr-xr-x   1 oracle     oinstall   17 May  7 16:12 asm_disk_06 -> /dev/rdisk/disk78

7- Por otro lado chequemos que el raw presentado tenga los permisos necesario para que ASM lo pueda acceder correctamente

TEST:/u00/oradata/asm# ls -latr /dev/rdisk/disk78

crw-r-----   1 oracle     dba         23 0x000014 May 15  2009 /dev/rdisk/disk78

/*********************************************/

Una vez terminado el trabajo de generar el link para ASM estamos en condiciones de agregar el disco a nuestro diskgroup.
Quiero aclarar que voy a seguir los pasos que aconseja Oracle para agregar un nuevo disco a un diskgroup existente (Metalink ID 557348.1)

Paso 1 - Creamos un nuevo diskgroup para testear si ASM puede acceder al nuevo disco y asi no correr riesgos con nuestro diskgroup productivo
SQL> CREATE DISKGROUP TEST EXTERNAL REDUNDANCY DISK 'YOUR DISK';

Paso 2 - Checkeamos si el diskgroup esta creado y montado
SQL> SELECT STATE, NAME FROM V$ASM_DISKGROUP;

Paso 3 - Si tenemos varias instancias de ASM (hablamos de un RAC sobre ASM) debemos montar el diskgroup en las demas instancias de ASM
SQL> ALTER DISKGROUP TEST MOUNT;

Paso 4 - Chequeamos en todas las demás instancias de ASM si el diskgroup está montado
SQL> SELECT STATE, NAME FROM V$ASM_DISKGROUP;

Paso 5 - Desmontamos el diskgroup de todas las instancias menos de una, es de esta última de donde eliminamos la instancia de ASM
SQL> alter diskgroup test dismount; --(desde todas las instancias de ASM salvo de una).

SQL> DROP DISKGROUP TEST; --(desde la instancia ASM de donde no desmontamos el diskgroup).

Paso 6 - Agregamos el nuevo disco a nuestro diskgroup existente
SQL> ALTER DISKGROUP ADD DISK 'YOUR DISK';

Espero les haya servido.

Saludos!
Gondalf

Scripts sobre tamaños en general

•• Conocer Tamaño ocupado por una tabla concreta incluyendo los índices de la misma

SELECT sum(bytes)/1024/1024 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'));

•• Conocer Tamaño ocupado por una columna de una tabla

SELECT sum(vsize('NOMBRECOLUMNA'))/1024/1024 Mb FROM 'NOMBRETABLA';

•• Conocer Espacio ocupado por cada usuarios

SELECT owner, SUM(BYTES)/1024/1024 FROM dba_extents
GROUP BY owner;

•• Conocer Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...)

SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM dba_extents
GROUP BY SEGMENT_TYPE;

•• Conocer el tamaño ocupado por una tabla

SELECT SEGMENT_name, SUM(BYTES)/1024/1024 FROM dba_extents
WHERE segment_name = 'NOMBRETABLA'
GROUP BY segment_name;

•• 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 FROM dba_extents
GROUP BY segment_name
ORDER BY 2 desc;

•• Conocer el espacio ocupado por la base de datos

SELECT sum(bytes)/1024/1024 FROM dba_extents;

Espero les haya servido

Saludos!
Gondalf

Tablespace Temporal

Seguramente alguna vez necesiten recrear el tablespace temporal que tiene la base por default. Porque?
Una posible explicación podría ser que nuestro tablespace temporal está configurado en autoextent y por alguna razón aumentó considerablemente su tamaño y no podemos realizarle un RESIZE ya que nos devuelve el siguiente error:

SQL> alter database tempfile

2 '/u01/oradata/PRUEBA/temp01.dbf'

3 resize 100M;

alter database tempfile '/u01/oradata/PRUEBA/temporal01.dbf' resize 100M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Si esto ocurre debemos recurrir a otra estrategia. Tenemos 2 opciones las cuales vamos a tratar de explicar:

OPCION 1

Lo que vamos a hacer es borrar el tablespace y volverlo a crear, pero dado que no podemos borrar el tablespace temporal que la base tiene asignado por default debemos crear uno “provisorio”, creo que se va a entender mejor si vemos los pasos que necesitamos realizar:

SQL> create temporary tablespace temp tempfile

2 '/u01/oradata/PRUEBA/temp01.dbf'

3 size 100M;

Tablespace created.

SQL> alter database default temporary

2 tablespace temp1;

Database altered.

SQL> drop tablespace TEMPORAL including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMPORAL
2 tempfile '/u01/oradata/PRUEBA/temporal01.dbf' size 100M
3 autoextend on next 100M maxsize 32767M;

Tablespace created.

SQL> alter database default temporary tablespace temporal;

Database altered.

SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

OPCION 2

Existe otro método que podemos usar en Oracle 10g (lo podemos usar a partir de 9i) que nos permite borrar el tempfile directamente sin eliminar el tablespace, esto debemos hacerlo sin usuarios trabajando o trabajos batch ejecutándose que usen el temporal.
Para obtener el nombre del tempfile a borrar podemos hacer lo siguiente:

SQL> SELECT tablespace_name, file_name
2 FROM dba_temp_files

3 WHERE tablespace_name = 'TEMPORAL';

TABLESPACE_NAME    FILE_NAME                            
-----------------  --------------------------------
TEMPORAL           /u01/oradata/PRUEBA/temporal01.dbf

Teniendo el nombre podemos proceder a borrarlo, para eso ejecutamos lo siguiente:

SQL> alter database tempfile

2 '/u01/oradata/PRUEBA/temporal01.dbf'

3 drop including datafiles;

Database altered.

SQL> alter tablespace TEMPORAL add tempfile
2 '/u01/oradata/PRUEBA/temporal01.dbf' size 512m
3 autoextend on next 250m maxsize unlimited;

Tablespace altered.

SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_temp_files

3 WHERE tablespace_name = 'TEMPORAL';

TABLESPACE_NAME   FILE_NAME                   
----------------- --------------------------------
TEMPORAL          /u01/oradata/TESTDB/temporal01.dbf

Espero les haya servido

Saludos!
Gondalf