ORACLE: Archivos propios de una instancia.

select value from v$parameter where name = 'spfile';

select name from v$controlfile;

select member from v$logfile;

select name from v$archived_log where dest_id = 1;

select file_name from dba_temp_files;

select file_name from dba_data_files join dba_tablespaces using (tablespace_name) where contents = 'UNDO';

select file_name from dba_data_files join dba_tablespaces using (tablespace_name) where contents != 'UNDO';

ORACLE: DB Links

Cuando existen db_link creados sin db_domain y ahora la base tiene db_domain configurado, el db_link queda inaccesible. Para reconstruirlo con el db_domain, hay que eliminar temporalmente el db_domain desde el global_name.

• Select db_link from dba_db_link;
• enlace
• Select * from global_name;
• ORCL.WORLD
• Update sys.props$ set value$ = ‘ORCL’ where name = ‘GLOBAL_DB_NAME’;
• Commit;
• Select * from global_name;
• ORCL
• Drop public database link enlace;
• Alter database rename global_name to ORCL.WORLD;
• Select * from global_name;
• ORCL.WORLD
• Create public database link enlace …;
• Select db_link from dba_db_link;
• Enlace.world
• Si sólo hubiésemos hecho el alter database rename, no toma el cambio.

ORACLE: Migración de Vistas Materializadas

En dataware house es muy común la utilización de vistas materializadas.

En mi caso sucedió lo siguiente: por una migración de 9.2 a 11.2 de motor de base, se realizó una exportación/importación tradicional a nivel de esquemas. Estas herramientas incluyeron la metadata de las vistas materializadas. En el 9.2 tenía un db_domain configurado, mientras que la 11.2 estaba sin db_domain definido.

Para aquellas MV donde no tienen definido el tipo de refrescamiento (por defecto toma FORCE), o tienen explícitamente el tipo FORCE, la MV primero intenta hacer un FAST REFRESH y para ello se basa en el metadato almacenado en el campo dba_mviews.master_link, que como estamos hablando de una MV que viene migrada desde el 9.2 con db_domain, acarrea ese mismo valor. Como en la 11.2 quedaron los db_links con el dominio definido en NULL, al momento de hacer el refresh de la MV, esta intenta hacer un fast refresh utilizando el db_link informado en dba_mviews.master_link, en este caso no va a poder encontrar el mismo en la nueva base, y va a fallar por completo el refrescamiento. El problema de recrear por completo las MV, es principalmente el tiempo, por el volumen de datos que deben recuperar.

Una posible solución sería, eliminar la MV sin olvidar la cláusula PRESERVE TABLE. Esto hace que ante Oracle, la tabla contenedora de la vista, se vean como una tabla común y corriente ante el motor de la base. Luego, se volvería a contruír la MV con la cláusula ON PREBUILD TABLE, para que la tabla sea interpretada por el motor como una tabla contenedora de la nueva MV.

Las condiciones para poder efectuar satisfactoriamente esta maniobra, es que la MV sea exportada de un motor 9i en adelante, y que la nueva MV y la preexistente tabla tengan el mismo nombre y se encuentren en el mismo esquema.

TODO: debo probar qué pasa, si corrijo directamente la definición del db_link en el campo sys.snap$.mlink del diccionario de datos de la instancia, maniobra NO sugerida por metalink!

Languagesen>es GoogleDicCE

ninguno, imposible, prohibido, negativa, voto negativo, voto en contra, no

ORACLE: Undo TableSpace

Calculating the Space Requirements For Undo Retention

Given a specific UNDO_RETENTION parameter setting and some system statistics, the amount of undo space required to satisfy the undo retention requirement can be estimated using the following formula:

UndoSpace = UR * UPS + overhead

where:

• UndoSpace is the number of undo blocks
• UR is UNDO_RETENTION in seconds
• UPS is undo blocks for each second
• overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)

As an example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is 200 undo blocks for each second, with a 4K block size, the required undo space is computed as follows:

(2 * 3600 * 200 * 4K) = 5.8GBs.

Such computation can be performed by using information in the V$UNDOSTAT view. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view.

V$UNDOSTAT

V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. This view is available in both automatic undo management mode and manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle.

Column	Datatype	Description
BEGIN_TIME	DATE	Identifies the beginning of the time interval
END_TIME	DATE	Identifies the end of the time interval
UNDOBLKS	NUMBER	Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.
TXNCOUNT	NUMBER	Identifies the total number of transactions executed within the period
SSOLDERRCNT	NUMBER	Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.
NOSPACEERRCNT	NUMBER	Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.

Extraído de los manuales oficiales de Oracle 9i r2. También se puede obtener más información en la nota ID 460481.1 de metalink.

ORACLE: Error ORA-00313

Hoy amanecimos con un disco menos en el servidor, y la base de datos (un 9.2 en RAC) nos avisó que no podía acceder a un logfile de determinado thread y grupo.

Cómo los logfiles están cruzados en diferentes unidades de disco (justamente para prevenir estas fallas físicas de disco) tuve que eliminar los logfiles que figuraban como inválidos en la vista V$LOGFILE y a su vez reconstruirlos en otro FS, como se ejemplifica a continuación:

alter database drop logfile member '<ruta>/<logfile>';
alter database add logfile member '<ruta nueva>/<logfile>' reuse to group <grupo>

Cuidado, si algún drop falla, probablemente es porque ese logfile esté siendo utilizado por la base, en ese preciso momento, con lo cual podemos esperar o forzar el switch a otro redo, con alter system switch logfile; tal vez tengamos que ejecutar esta sentencia más de una vez, para que finalmente se pueda hacer el drop.

Si todo esto fué bien y consultamos la V$LOGFILE y nos figura alguno de los nuevos logfile como invalido, puede ser porque la instancia ya esté utilizando ese grupo de redo, con lo cual devuelta ejecutamos una o más veces el alter system switch logfile y con esto se debería solucionar, salvo que haya otro problema de fondo, por lo que no pueda ser utilizado el nuevo archivo, por ejemplo la nueva ubicación también tiene sectores inválidos.

ORACLE: Segmento temporal

Esta es una interesante consulta para echarle un vistazo al segmento temporal (en un 9.2.0)

SELECT ses.inst_id,
ses.sid,
ses.serial#,
ses.username,
ses.status,
ses.machine,
ses.program,
seg.blocks,
txt.piece,
txt.sql_text
FROM gv$session ses,
gv$tempseg_usage seg,
gv$sqltext txt
WHERE seg.blocks > 32
AND seg.sqladdr = txt.address
AND seg.sqlhash = txt.hash_value
AND seg.session_addr = ses.saddr
AND seg.session_num = ses.serial#
and seg.inst_id = ses.inst_id
and seg.inst_id = txt.inst_id
ORDER BY seg.blocks,
txt.piece;

Encontré este enlace que me pareció muy útil.

ORACLE: Bloqueante y bloqueado de un objeto de base de datos.

Basándome en el query que pasaron en este foro completé un poco más la información obtenida, agregándole la sentencia que está bloqueando, y la bloqueada.


SELECT LPAD (' ', DECODE (l.xidusn, 0, 8, 0))
|| l.oracle_username "User Name",
o.owner, o.object_name, o.object_type, l.locked_mode, st.sql_text
FROM v$locked_object l,
dba_objects o,
v$session s,
v$sqltext_with_newlines st
WHERE l.object_id = o.object_id
AND l.session_id = s.SID
AND ( ( l.xidusn = 0
AND st.address = s.sql_address
AND st.hash_value = s.sql_hash_value
)
OR ( l.xidusn <> 0
AND st.address = s.prev_sql_addr
AND st.hash_value = s.prev_hash_value
)
)
ORDER BY o.object_id, 1 DESC, st.piece;

Pro*C: dbms_output.put_line

No sé por qué nunca se me ocurrió esto, pero bueh.

Nosotros estamos acostumbrados a sacar mensajes desde un PL/SQL siempre y cuando usemos el sqlplus:

$ sqlplus ***/***
SQL> set serveroutput on; <=== para que se vean los mensajes que mostremos con el dbms_output
SQL> begin
2 dbms_output.enable(null); <=== para habilitar un buffer sin límites
3 dbms_output.put_line('Hola Quique'); <=== "muestra el texto por pantalla" (nótese las comillas)
4 end;
5 / hola PL/SQL procedure successfully completed.
SQL>
Ahora, la incognita errónea que siempre nos preguntamos o al menos yo lo hice es, cómo implementamos la instrucción "set serveroutput on" desde Pro*C, si este es un comando de sqlplus? Y ete aquí que lo que está mal formulada es la pregunta o el concepto del put_line. put_line no muestra un texto, sino que pone un texto dentro del buffer habilitado con enable, entonces ahora pensando que tenemos un almacen de textos en memoria, lo que nos queda es recuperarlo de ese almacen, y si alguna vez se te dá por leer los manuales de PL/SQL, vas a ver que el package dbms_output tiene varias rutinas, entre ellas el bendito get_line(), quien se encarga de obtener ese texto del buffer! Entonces ahora sí estoy en condiciones de utilizar el dbms_output desde Pro*C! Acá va el ejemplo:

exec sql begin declare section;
varchar vcTexto[40000];
short indTexto;
int iStat;
short indStat;
exec sql end declare section;
...
memset(&vcTexto, (int)NULL, sizeof(vcTexto));
exec sql execute
begin
dbms_output.enable(null);
dbms_output.put_line('Hola mundo desde PL/SQL');
dbms_output.get_line(:vcTexto:indTexto, :iStat:indStat);
end;
end-exec;
if(indTexto==0) printf("%s\n", vcTexto.arr);

Otro ejemplo (en este recupero el texto en otro bloque, pero como la sesion es la misma, el buffer prevalece y por ende su contenido):

exec sql begin declare section;
varchar vcTexto[40000];
short indTexto;
int iStat;
short indStat;
exec sql end declare section;
...
exec sql execute
begin
dbms_output.enable(null);
dbms_output.put_line('Hola mundo desde PL/SQL');
end-exec;
memset(&vcTexto, (int)NULL, sizeof(vcTexto));
exec sql execute
begin
dbms_output.get_line(:vcTexto:indTexto, :iStat:indStat);
end;
end-exec;
if(indTexto==0) printf("%s\n", vcTexto.arr);

Tercer y último ejemplo:
Dentro de una rutina almacenada, lo lleno de mensajes en todas las posibles salidas de error/exception.

SQL> create or replace procedure Quique is
2 begin
3 FOR i IN 1..100 LOOP
4 dbms_output.put_line(RPAD('*',1000,'*'));
5 END LOOP;
6 end;
7 /
Procedure created.
SQL>

e invoco la rutina desde el Pro*C, de esta forma:

exec sql begin declare section;
varchar vcTexto[40000];
short indTexto;
int iStat;
short indStat;
exec sql end declare section;
...
memset(&vcTexto, (int)NULL, sizeof(vcTexto));
exec sql execute
begin
dbms_output.enable(null);
Quique;
end;
end-exec;

while(iStat==0) {
memset(&vcTexto, (int)NULL, sizeof(vcTexto));
exec sql execute
begin
dbms_output.get_line(:vcTexto:indTexto, :iStat:indStat);
end;
end-exec;
if(indStat<0) istat = 1;
if(indTexto==0)
printf("%s\n", vcTexto.arr);
}

Cuando el buffer quede vacío, iStat contendrá el valor uno.
Cómo dice el manual, hay que declarar una variable huesped de tipo varchar no inferior a 32767, de lo contrario emitirá el error ora-6502.
Ojo! Si entre lecturas del buffer con get_line, se invoca un put_line, éste vacía el buffer con lo cual se pierde el resto de las lecturas previas al put_line.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#BABGBACJ