Curiosidades del Oráculo..

ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []

2010-04-07T07:24:00.000-07:00

PURPOSE:
This article discusses the internal error "ORA-600 [12235]", what
it means and possible actions. The information here is only applicable
to the versions listed and is provided only for guidance.

ERROR:
ORA-600 [12235] [a] [b] [c] [d] [e]

VERSIONS:
versions 7.0 to 9.2

DESCRIPTION:

This error shows up when Oracle detects an Oracle defunct process.

When an Oracle process starts up, it reads data from the SGA that defines
what type of process it should become.

If the process does not locate any valid customization data, it reports
ORA-600 [12235] and exits.

On a heavily loaded system, ORA-600 [12235] may be a symptom that the server
process was too slow in starting. That is, the process that initiated the
new server may timeout waiting for the new process to start and abandon the
new server request. In the new server process it is possible that the new
server reaches the code to customize its operation before the os request to
kill the process is actioned. The messages "ksbsrv: No startup
acknowledgement from forked process ..." and "Timed out trying to start
shared server ..." may be reported to trace and alert files.

FUNCTIONALITY:
USER/ORACLE INTERFACE LAYER

IMPACT:
NO IMPACT

SUGGESTIONS:

Ignore the error.

One of the most common reasons for this error to be reported is that
someone typed 'oracle' manually at the OS prompt.

If this error is reported regularly and appears not to be explained
by comments made in this note, contact Oracle Support Services.

Known Issues:

Bug# 3616023 P See Note 3616023.8
Tru64: "skgpspawn failed:category = 27143" during process / instance startup
Fixed: 10.2.0.1

Bug# 405159 See Note 405159.8
OERI:12235 possible starting any slave process
Fixed: 8.0.6.0

Comprobar el plan de ejecución con el que se ha compilado una sentencia sql que ya se ha ejecutado, obteniendo los datos de la SHARED POOL (v$sql).

2010-02-19T02:42:00.000-08:00

En V$SQL puedo ver las sentencias sql que ya se han ejecutado, de modo que puedo obtener el identificador (V$SQL.SQL_ID) de cualquier sentencia sql, para posteriormente consultar el plan de ejecución haciendo uso del paquete dbms_xplan (DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID)).

SQL> connect system/fabiancrea
Enter password:
Connected.

SQL> select deptno,count(*) from scott.emp group by deptno;
DEPTNO COUNT(*)
30 6
20 5
10 3

SQL> select sql_id from v$sql where sql_text='select deptno,count(*) from scott.emp group by deptno';
SQL_ID
cxwqkgckrcpd8

SQL> select * from table(dbms_xplan.display_cursor('cxwqkgckrcpd8'));

PLAN_TABLE_OUTPUT
SQL_ID cxwqkgckrcpd8, child number 0

select deptno,count(*) from scott.emp group by deptno

Plan hash value: 4067220884
----------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | HASH GROUP BY | | 3 | 9 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------
14
filas seleccionadas.

Instalar la utilidad STATSPACK. Crear snapshots y generar informe.

2010-02-19T02:27:00.000-08:00

Crear tablespace statspack_tsp con 100M en /u05/oradata/fabiancrea (crear dir.
/u05/oradata/fabiancrea).
Crear tablespace statspack_temp con 2M en /u05/oradata/fabiancrea .

Instalar statspack con $ORACLE_HOME/rdbms/admin/spcreate.sql, indicando los datos que se piden: clave de usuario perfstat, tablespace por defecto, y tablespace temporal por defecto.

$ mkdir /u05/oradata/fabiancrea
$ chmod Rg+w /u05/oradata/fabiancrea

SQL> create tablespace statpack_tsp datafile '/u05/oradata/fabiancrea /statpack_tsp01.dbf' size 100M
autoextend on next 10M maxsize 200M
extent management local autoallocate
segment space management auto;
Tablespace created.

SQL> create temporary tablespace statpack_temp
tempfile '/u05/oradata/fabiancrea/statpack_temp01.dbf' size 2M
autoextend on next 1M maxsize 10M;
Tablespace created.

SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
... Installing Required Packages
...
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
Enter value for perfstat_password: perfcursoXY
...
Choose the Default tablespace for the PERFSTAT user
...
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for performance data is not supported.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
SEGAUTO
PERMANENT
STATPACK_TSP PERMANENT
SYSAUX PERMANENT *
TSP4K PERMANENT
USERS PERMANENT
Pressing will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: STATPACK_TSP
...
Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.
...
Specify PERFSTAT user's temporary tablespace.
Enter value for temporary_tablespace: STATPACK_TEMP

© Juan Luis Serradilla Amarilla y Francisco Fernández Martínez 11
Administración Avanzada de Oracle10g
...
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Una vez instalado STATSACK, comprobamos si ha habido errores (en los ficheros *.lis):

SQL> !grep i
err *.lis
spcpkg.lis:No errors.
spcpkg.lis:No errors.
spcpkg.lis:SPCPKG complete. Please check spcpkg.lis for any errors.
spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:SPCUSR complete. Please check spcusr.lis for any errors.
Ahora podemos utilizar el paquete STATSPACK.
Primero compruebo si está activada la recolección de estadísticas:

SQL> show parameter timed_statistics
NAME TYPE VALUE
timed_statistics boolean TRUE
Ahora me conecto como PERFSTAT (o usuario con acceso al paquete STATSPACK) y genero el primer snapshot (baseline) con nivel 10 (i_snap_level). Después, espero al menos 5 minutos para generar otro snapshot, pues para sacar un informe necesito al menos dos:

SQL> connect perfstat
Enter password:
Connected.

SQL> exec STATSPACK.SNAP(i_snap_level=>10)
PL/SQL procedure successfully completed.
(... LANZAR ALGUNAS CONSULTAS, COMO LAS DOS SIGUIENTES ...)

SQL> SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
5924

SQL> SELECT COUNT(*) FROM DBA_EXTENTS;
COUNT(*)
2647
(... ESPERAR 5 MINUTOS DESDE QUE HICIMOS EL SNAPSHOT...)

SQL> exec STATSPACK.SNAP
PL/SQL procedure successfully completed.
Ahora ya podemos generar el informe:

SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
...
Enter value for begin_snap: 1
...
Enter value for end_snap: 2
...
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ Begin
Snap: 1 29Jan07
13:15:56 15 5.4
End Snap: 2 29Jan07
13:17:40 15 6.8
Elapsed: 1.73 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ Buffer
Cache: 28M Std Block Size: 2K
Shared Pool Size: 52M
Log Buffer: 2,819K

Load Profile Per Second Per Transaction
~~~~~~~~~~~~ Redo
size: 17,111.12 1,779,556.00
Logical reads: 352.43 36,653.00
Block changes: 64.20 6,677.00
Physical reads: 35.24 3,665.00
Physical writes: 31.09 3,233.00
User calls: 0.10 10.00
Parses: 13.85 1,440.00
Hard parses: 1.34 139.00
Sorts: 6.13 637.00
Logons: 0.00 0.00
Executes: 29.43 3,061.00
Transactions: 0.01
% Blocks changed per Read: 18.22 Recursive Call %: 99.98
Rollback per transaction %: 0.00 Rows per Sort: 12.03
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.95
Buffer Hit %: 89.99 Inmemory
Sort %: 100.00
Library Hit %: 83.66 Soft Parse %: 90.35
Execute to Parse %: 52.96 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 50.98 % NonParse
CPU: 61.48
Shared Pool Statistics Begin End
Memory
Usage %: 83.22 84.28

% SQL with executions>1: 72.27 87.15
% Memory for SQL w/exec>1: 66.21 78.80
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
dbfile sequential read 1,387 6 4 74.5
CPU time 1 16.4
db file scattered read 107 0 2 3.1
log file parallel write 9 0 24 2.6
log file switch completion 2 0 55 1.3
...
End of Report ( sp_1_2.lst )

Recompilacio n de objetos invalidos.

2010-02-13T17:17:00.000-08:00

Tenemos varios métodos para recompilar o ver objetos invalidos en Oracle, empecemos primero identificando los objetos invalidos, de la siguiente manera:

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

Ahora vayamos a la forma manual convencional;

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

o tambien con la DBMS_DDL:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

Ahora bien, vean este script que les tengo preparados, es un scripts que detecta objetos invalidos y crea un scripts para compilarlos.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/

Espero que les sirva todo esto.. exitos ..

Como diagnosticar un 0600 - [12700] [ ] [ ] [ ]

2009-12-21T07:40:00.000-08:00

Muchas veces una corrupcion de bloque puede venir asociada a un indice corrupto, y la ejecucion de un bloque corrupto puede dar un ORA 600[12700], el cual lo dice el alert de la base de datos y nos encontramos por ejemplo con este ejemplo (valga la redundancia)

Sat May 5 15:03:12 2007
ARC1: Beginning to archive log 3 thread 1 sequence 49943
Creating archive destination LOG_ARCHIVE_DEST_1: ‘/archive/oradata/ODSORA/1_49943.arc’
ARC1: Completed archiving log 3 thread 1 sequence 49943
Sat May 5 15:35:49 2007
Errors in file /oracle/dbs/admin/ODSORA/bdump/odsora_s003_27559.trc:
ORA-00600: internal error code, arguments: [12700], [90646], [71560692], [9], [0], [79], [], []
Sat May 5 15:40:25 2007
Errors in file /oracle/dbs/admin/ODSORA/bdump/odsora_s001_2441.trc:
ORA-00600: internal error code, arguments: [12700], [90646], [71560692], [9], [0], [79], [], []
Sat May 5 15:55:32 2007
Thread 1 advanced to log sequence 49945
Sat May 5 15:55:32 2007
Current log# 5 seq# 49945 mem# 0: /oracle/dbs/oradata/ODSORA/redo05a.log
Current log# 5 seq# 49945 mem# 1: /u20/oradata/ODSORA/redo05b.log
Sat May 5 15:55:32 2007
ARC0: Evaluating archive log 4 thread 1 sequence 49944
Sat May 5 15:55:32 2007
ARC0: Beginning to archive log 4 thread 1 sequence 49944

Para lo cual hacemos lo siguiente:

1) Creamos el siguiente procedure con el user “SYS”

CREATE OR REPLACE PROCEDURE oerr12700( a number , b number, c number) IS
un varchar2(99);tn varchar2(99); trowid varchar2(99);
ind_name varchar2(99); ind_col varchar2(99);
nfile number; nblock number; nrow number;
fname VARCHAR2(513) ;
dbs number ;
dbs_x varchar2(129);
x number;

BEGIN

x:= dbms_utility.get_parameter_value(’db_block_size’,dbs,dbs_x);

nfile:=dbms_utility.data_block_address_file(b);
select FILE_NAME into fname from dba_data_files
where RELATIVE_FNO = nfile ;

nblock:=dbms_utility.data_block_address_block(b);
select NAME,dba_users.username into tn,un from obj$,dba_users where dataobj#=a
and dba_users.user_id=obj$.owner# ;

trowid:= dbms_rowid.rowid_create(1,a,nfile,nblock,c);

dbms_output.put_line(’ ORA-600 [12700] [’||a||’],[’||b||’],[’||c||’]');
dbms_output.put_line(’————————————————–’);
dbms_output.put_line(’there is an index pointing to a row in ‘||un||’.'||tn);
dbms_output.put_line(’row is slot ‘||c||’ in file ‘||nfile||’ block ‘||nblock);
dbms_output.put_line(’one index entry is pointing to ROWID=”’|| trowid||””);
dbms_output.put_line(’————————————————–’);
dbms_output.put_line(’You may want to check the integrity of ‘||un||’.'||tn);
dbms_output.put_line(’executing :’);
dbms_output.put_line(’dbv file=’||fname||’
blocksize=’||dbs||’ start=’|| nblock||’ end=’||nblock);
dbms_output.put_line(’————————————————–’);

–
dbms_output.put_line(’IF dbv does not show any corruption, you can try to’);
dbms_output.put_line(’find the corrupted indexes using the queries proposed’);
dbms_output.put_line(’by the procedure oerr12700diag(’||a||’,'||b||’,'||c||’)');
dbms_output.put_line(’——————————————————-’);
END;
/

2) Lo ejecutamos:

SQL> set serveroutput on
SQL> execute oerr12700( 90646, 71560692, 9);

The output of the above procedure execution gives the following result

SQL> execute oerr12700( 90646, 71560692, 9);
ORA-600 [12700] [90646],[71560692],[9]
————————————————–
there is an index pointing to a row in PORTAL.PREMIUM_TRANSACTION_DATA
row is slot 9 in file 17 block 257524
one index entry is pointing to ROWID=’AAAWIWAARAAA+30AAJ’
————————————————–
You may want to check the integrity of PORTAL.PREMIUM_TRANSACTION_DATA
executing :
dbv file=/u03/oradata/portals.dbf
blocksize=8192 start=257524 end=257524
————————————————–
IF dbv does not show any corruption, you can try to
find the corrupted indexes using the queries proposed
by the procedure oerr12700diag(90646,71560692,9)
——————————————————-

PL/SQL procedure successfully completed.

Now, To verify whether data blocks is corrupted or corruption lies in index use the dbv utility as shown

dbv file=/u03/oradata/portals.dbf blocksize=8192 start=257524 end=257524;

If there is no data block corruption the output of the dbv utility will as shown:

bash-2.05$ dbv file=/u03/oradata/portals.dbf blocksize=8192 start=257524 end=257524;

DBVERIFY: Release 9.2.0.4.0 - Production on Mon May 7 13:23:42 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /u03/oradata/portals.dbf

DBVERIFY - Verification complete

Total Pages Examined : 1
Total Pages Processed (Data) : 1
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

The above confirms that data blocks are not corrupted and hence we to verify the index corruption as shown:

1) create the following procedure as SYS use

SQL> CREATE OR REPLACE PROCEDURE oerr12700diag( a number , b number, c number) IS
un varchar2(99);tn varchar2(99); trowid varchar2(99);
ind_name varchar2(99); ind_col varchar2(99);
nfile number; nblock number; nrow number;

cursor pindexes(towner varchar2, tname varchar2) is
select C.INDEX_NAME,COLUMN_NAME from dba_ind_columns C, dba_indexes I
where c.INDEX_NAME=i.INDEX_NAME
and I.INDEX_TYPE <> ‘DOMAIN’
and C.TABLE_OWNER=towner and C.TABLE_NAME=tname
and C.COLUMN_POSITION=1 ;

rpindexes pindexes%rowtype;

BEGIN
nfile:=dbms_utility.data_block_address_file(b);
nblock:=dbms_utility.data_block_address_block(b);
select NAME,dba_users.username into tn,un from obj$,dba_users where dataobj#=a
and dba_users.user_id=obj$.owner# ;

trowid:= dbms_rowid.rowid_create(1,a,nfile,nblock,c);

dbms_output.put_line(’————————————————–’);
dbms_output.put_line(’IF dbv did not show any corruption, you can try to’);
dbms_output.put_line(’find the corrupted indexes using following queries:’);
dbms_output.put_line(’——————————————————-’);
dbms_output.put_line(’If a query returns “no rows selected” index is sane’);
dbms_output.put_line(’If a query returns ‘||trowid||’ index is corrupted’);
dbms_output.put_line(’…………………………………………..’);

dbms_output.put_line(’.');
dbms_output.put_line(’To test ‘||un||’.'||tn||’ indexes ‘) ;
dbms_output.put_line(’.');
for rpindexes in pindexes(un,tn) loop
dbms_output.put_line(’.');
dbms_output.put_line(’To test INDEX ‘||rpindexes.INDEX_NAME||’ you run :’ );
dbms_output.put_line(’.');
dbms_output.put_line(’select rowid “‘||rpindexes.INDEX_NAME||’ corrupted!”
from ‘);
dbms_output.put_line(
‘(SELECT /*+ INDEX_FFS(’||tn||’,'||rpindexes.INDEX_NAME||’) */ ‘);
dbms_output.put_line(
rpindexes.COLUMN_NAME||’,rowid from ‘||
un||’.'||tn||’ where ‘||
rpindexes.COLUMN_NAME||’='||rpindexes.COLUMN_NAME||’) ‘ );
dbms_output.put_line( ‘where rowid=”’||trowid||”’;'||’ ‘);
end loop ;
END;
/

2) execute the procedure as note the output

SQL> set serveroutput on
SQL> execute oerr12700diag( 90646, 71560692, 9);

————————————————–
IF dbv did not show any corruption, you can try to
find the corrupted indexes using following queries:
——————————————————-
If a query returns “no rows selected” index is sane
If a query returns AAAWIWAARAAA+30AAJ index is corrupted
…………………………………………..
.
To test PORTAL.PREMIUM_TRANSACTION_DATA indexes
.
.
To test INDEX PREMIUM_TRANSACTION_DATA_PK you run :
.
select rowid “PREMIUM_TRANSACTION_DATA_PK corrupted!”
from
(SELECT /*+ INDEX_FFS(PREMIUM_TRANSACTION_DATA,PREMIUM_TRANSACTION_DATA_PK) */
CUST_ID,rowid from PORTAL.PREMIUM_TRANSACTION_DATA where CUST_ID=CUST_ID)
where rowid=’AAAWIWAARAAA+30AAJ’;

PL/SQL procedure successfully completed.

Now, we execute the following query

SQL> select rowid “PREMIUM_TRANSACTION_DATA_PK corrupted!” from
(SELECT /*+ INDEX_FFS(PREMIUM_TRANSACTION_DATA,PREMIUM_TRANSACTION_DATA_PK) */
CUST_ID,rowid from PORTAL.PREMIUM_TRANSACTION_DATA where CUST_ID=CUST_ID) where rowid=’AAAWIWAARAAA+30AAJ’;

The output to above query is

PREMIUM_TRANSACTION_DATA_PK corrupted!
——————————————————-
AAAWIWAARAAA+30AAJ

then it mean that index PREMIUM_TRANSACTION_DATA_PK is corrupted and hence we will have to drop and recreate the index PREMIUM_TRANSACTION_DATA_PK to avoid the error.

If there is any data block corruption them you have raise an SR with Oracle Support and follow appropriate steps.

Vistas Materializadas

2009-12-02T07:16:00.000-08:00

Vistas materializadas

En este tutorial vamos a ver lo que son las vistas materializadas (materialized views) y vamos a hacer un ejemplo de creación de una vista materializada y su posterior uso. Lo primero de todo es recordar lo que es una vista en un modelo de base de datos relacional.

Una vista es una consulta almacenada que representa un conjunto de tablas (posiblemente de diferentes esquemas) a la que le vamos a poner un nombre y vamos a tratarla como si fuese una tabla más de nuestro esquema, pero sin llegar a ser realmente una tabla. Algo que tiene que quedar claro es que una vista NO guarda datos, sino que solo almacena la consulta que nos va a ayudar a acceder a los datos. Pero, ¿porqué usar vistas? es muy sencillo, principalmente hay dos motivos. El primer motivo es de seguridad, a lo mejor no necesitamos que determinados usuarios acceden a toda la información de la base de datos y simplemente queremos formar un conjunto de datos específicos para dichos usuarios. El segundo motivo tiene que ver con la estructura de nuestro modelo de datos, ya que si es bastante complejo o con muchas tablas nos puede ser muy útil crear este tipo de vistas para organizar una cierta información de modo que nos sea mucho más cómodo acceder a ella mediante consultas mucho más sencillas.

Definición

A diferencia de las vistas "normales" una vista materializada almacena físicamente los datos resultantes de ejecutar la consulta definida en la vista. Este tipo de vistas materializadas realizan una carga inicial de los datos cuando se definen y posteriormente con una frecuencia establecida se actualizan los datos de la misma.Con la utilización de vistas materializadas logramos aumentar el rendimiento de las consultas SQL además de ser un método de optimización a nivel físico en modelos de datos muy complejos y/o con muchos datos.

Una vez definida una vista materializada uno de los problemas que nos encontramos es el de la actualización de los datos. Como se ha comentado antes, estas vistas contienen fisicamente los datos de las "tablas base", por lo que si cambian los datos de estas tablas no se reflejarán en la vista materializada. Para ello necesitamos establecer un mecanismo de resfresco automático en el que tendremos que definir el tipo y la forma de refresco.

La sentencia SQL que nos permite definir una vista materializada es esta:
view plainprint?

1. CREATE MATERIALIZED VIEW nombre_vista
2. [TABLESPACE nombre_ts]
3. [PARALELL (DEGREE n)]
4. [BUILD {INMEDIATE|DEFERRED}]
5. [REFRESH {FAST|COMPLETE|FORCE|NEVER}|{ON COMMIT|ON DEMAND|[START WITH fecha_inicio] NEXT intervalo}]
6. [{ENABLE|DISABLE} QUERY REWRITE]
7. AS SELECT ... FROM ... WHERE ...

CREATE MATERIALIZED VIEW nombre_vista [TABLESPACE nombre_ts] [PARALELL (DEGREE n)] [BUILD {INMEDIATE|DEFERRED}] [REFRESH {FAST|COMPLETE|FORCE|NEVER}|{ON COMMIT|ON DEMAND|[START WITH fecha_inicio] NEXT intervalo}] [{ENABLE|DISABLE} QUERY REWRITE] AS SELECT ... FROM ... WHERE ...

Con la palabra BUILD establecemos la forma de carga de datos en la vista. Con la opción INMEDIATE (opción por defecto) se cargarán los datos justo después de crear la vista, mientras que con la opción DEFERRED se definirá la vista cuando se ejecute la sentencia SQL sin cargar ningún dato, que se cargarán cuando se realize el primer refresco de la vista.

Con la palabra REFRESH definimos el método y la frecuencia de refresco de los datos.

La palabra QUERY REWRITE establece si queremos que el optimizador de nuestra base de datos pueda reescribir las consultas. El optimizador, sabiendo que ya existe una determinada vista materializada, puede modificar internamente nuestra consulta sobre una determinada tabla, de tal forma que se mejore el rendimiento de la consulta devolviendo los mismos datos que la consulta original.
Refresco

Como es entendible la política de refresco de cada vista repende altamente de nuestras necesidades y requerimientos sobre la frecuencia de actualización de los datos de las "tablas base".
Tipos de refresco

* COMPLETE : se borrarán todos los datos de la vista y se volverá a ejecutar la consulta definida en la vista por lo que se recargarán fisicamente los datos de las "tablas base".
* FAST : podemos decir que este tipo de refresco es una actualización incremental, es decir, solo se refrescarán aquellos datos que se hayan modificado desde el último refresco. Evidentemente este tipo de refresco es mucho más fast ;-) que el complete. Pero, ¿cómo sabe la base de datos que datos se han modificado desde el último refresco? lo sabe gracias a que previamente hemos tenido que crear unos determinados log de la vista (VIEW LOG) sobre cada una de las "tablas base" de la vista materializada.

view plainprint?
1. CREATE MATERIALIZED VIEW LOG ON tabla_base
2. WITH PRIMARY KEY
3. INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON tabla_base WITH PRIMARY KEY INCLUDING NEW VALUES;

Hay que decir que si usamos funciones sum, avg, max, min, etcétera, no vamos a poder usar este tipo de refresco.
*
FORCE : si se puede realizar el refresco tipo FAST se ejecuta, y sino se realiza el refresco COMPLETE. Es el valor por defecto del tipo de refresco.
* NEVER : nunca se realizará un refresco de la vista.

Formas de refresco

* Refresco manual : mediante el paquete de PL/SQL DBMS_MVIEW podemos forzar a realizar un refresco usando para ello la función REFRESH.

view plainprint?
1. DBMS_MVIEW.REFRESH ('nombre_vista');
2.
DBMS_MVIEW.REFRESH ('nombre_vista');

Con la función REFRESH_DEPENDENT se refrescarán todas las vistas materializadas que tengan algunas de sus "tablas base" en la lista de tablas pasada como parámetro de entrada.

view plainprint?
1. DBMS_MVIEW.REFRESH_DEPENDENT ('tabla1, tabla2, tabla3, ... , tablaN');
2.
DBMS_MVIEW.REFRESH_DEPENDENT ('tabla1, tabla2, tabla3, ... , tablaN');

Con la función REFRESH_ALL_MVIEWS se refrescarán todas las vistas materializadas de nuestra base de datos.
* Refresco automático : este refresco automático podemos hacerlo usando la palabra ON COMMIT, con la que se fuerza al refresco de la vista en el momento en el que se haga un commit sobre una de las "tablas base" de dicha vista. Otro tipo de refresco automático es el llamado refresco programado, en el cual podemos definir el momento exacto en el que queremos que se refresque nuestra vista. Para ello tenemos que definir la fecha del refresco en formate datetime y el intervalo de este.

Ejemplo práctico

Una vez visto todos los detalles teóricos vamos a hacer un pequeño ejemplo práctico para ver como definir vistas materializadas y analizar sus comportamientos.

Lo primero que vamos a hacer es crear una tabla (SQL) en nuestra base de datos (Oracle 9) y insertar algunos datos.

1. CREATE TABLE tabla
2. (campo1 int PRIMARY KEY,
3. campo2 int);
4.
5. insert into tabla values (1,2);
6. insert into tabla values (2,298);
7. insert into tabla values (3,223);
8. insert into tabla values (4,121);
9. insert into tabla values (5,34);
10. insert into tabla values (6,34);
11. insert into tabla values (7,78);
12. insert into tabla values (8,44);
13. insert into tabla values (9,34);
14. insert into tabla values (10,12);

CREATE TABLE tabla (campo1 int PRIMARY KEY, campo2 int); insert into tabla values (1,2); insert into tabla values (2,298); insert into tabla values (3,223); insert into tabla values (4,121); insert into tabla values (5,34); insert into tabla values (6,34); insert into tabla values (7,78); insert into tabla values (8,44); insert into tabla values (9,34); insert into tabla values (10,12);

A continuación creamos un VIEW LOG para la anterior tabla.
view plainprint?

1. CREATE MATERIALIZED VIEW LOG ON tabla
2. WITH PRIMARY KEY
3. INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON tabla WITH PRIMARY KEY INCLUDING NEW VALUES;

Despúes creamos nuestra vista materializada.

1. CREATE MATERIALIZED VIEW tabla_vm
2. BUILD IMMEDIATE
3. REFRESH FAST ON COMMIT
4. AS SELECT * FROM tabla;

CREATE MATERIALIZED VIEW tabla_vm BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM tabla;

Como se puede ver, la definición de la vista es absurda, ya que es la propia tabla, pero para nuestro ejemplo nos servirá ya que lo que se pretende es ver el comportamiento de la vista materializada creada.

En la anterior sentencia SQL creamos una vista materializada de nombre tabla_vm que se cargará inicialmente justo cuando se ejecute (BUILD IMMEDIATE), se refrescarán solo aquellas entradas que se hallan modificado en la tabla base (FAST) y lo harán cuando se ejecute la acción de commit (ON COMMIT).

Vamos a ver si realmente la teoría se corresponde con la práctica. Inicialmente tanto la tabla base como la vista materializada contienen estos valores:

Cuando ejecutamos una sentencia sobre la tabla base que actualize un determinado dato de ella (update tabla set campo2 = 123 where campo1 = 1) podemos ver que se ha actualizado en la tabla base pero no en la vista materializada ya que aun no se ha lanzado el refresco.

tabla base - tabla

vista materializada - tabla_vm

Para que la vista se refresque y se actualize solo con el dato modificado en la tabla base tenemos que hacer COMMIT.

Como se puede ver en la imagen de arriba ya tenemos los datos totalmente actualizados en nuestra vista materializada.

Este pequeño ejemplo no tiene valor para poder analizar la mejora en el rendimiento al usar este tipo de vistas pero nos ha servido (...o eso espero...) para que se vea como se puede crear una vista materializada y como es su funcionamiento.

Para borrar la vista materializada creada tenemos que ejecutar la siguiente sentencia SQL.

1. drop materialized view tabla_vm;

Migracion de 8i a 10GR1

2009-08-10T04:54:00.000-07:00

Via METALINK... completito

This is a step by step guide that explains how we can migrate a database to a new server and database release manually using a Cold backup.

How to do a cold backup of oracle database and restore it on the new server and then upgrade it.
Solution

1) Prerequisites
----------------

- The copy of the datafiles must be done with the database closed.

- Source Operating System and destination Operating System need to be the same

- Destination sever need to have the 10g installed with the same word size
as the 8.1.7.4. Both need to be 32 bit or both need to be 64 bit.

- If your database contains user tables with NCHAR columns, you must upgrade the
NCHAR columns before they can be used .
In the database documentation you have all the steps is detail:
Oracle® Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-02

2) Init.ora or spfile and controlfile
----------------------------------
- You need to copy the init.ora to the target host and locate it in
ORACLE_HOME\dbs on UNIX platforms and in ORACLE_HOME\database on Windows operating systems

- Copy the controlfile and the datafiles the redologs and all the archivelogs
generated, to the target host,

- You need to stop database with shutdown immediate or shutdown normal before copying the
files to target host,

3) Set the oracle environment on Target 10g host
-----------------------------------------------

C:\> set ORACLE_SID=
C:\> sqlplus "sys/ as sysdba"

- Check the init.ora parameters that reference location

control_files =
background_dump_dest =
core_dump_dest =
user_dump_dest =
log_archive_dest_1 =

- Make sure the COMPATIBLE initialization parameter is properly
set for the new Oracle Database 10g release.
compatible = 10.2.0.X

- We need to check which parameters need to be changed in the init.ora for the new 10g
release. We can use Pre-upgrade utility

* Copy the file utlu102i.sql from the ORACLE_HOME/rdbms/admin directory of the 10g
release to a directory on the old 8.1.7 server

* In the 8.1.7 environment, start SQL*Plus. Connect to the database instance as SYS
SQL> SPOOL info.log
SQL> @utlu102i.sql
SQL> SPOOL OFF

* Check the spool file and examine the output of the upgrade information tool, you
will see the minimum parameter setting for the 10g

4) Set up a password file for the target database
-----------------------------------------------
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<...>

The name and location of the password file on UNIX platforms, is ORACLE_HOME/dbs/orapw.
On Windows operating systems, the default password file is ORACLE_HOME\database\pwd.ora.

5) Start up the database and re mane files
-------------------------------------
Assuming that all the files are copied to target host and that controlfile has been located
at location specified by init.ora parameter control_files

- Start up the database in mount status

SQL> startup nomount pfile=\init.ora;

You can create at this point an spfile for the 10g instance, then you won't need to specify
the pfile location each startup.

SQL> create spfile from pfile=\init.ora;
SQL> shutdown immediate;
SQL> startup mount;

- If the datafile location is different on target server, we need to rename the files
Rename any of the datafiles to the new location, if necessary:

SQL>ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_HOST_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_HOST_LOCATION/AND_DATAFILE_NAME.DBF';

Rename the logfiles to the new location if necessary

SQL> alter database rename file '' to '';

6) Check that all the datafiles are in the right location:
-------------------------------------------------------
select file#, name from v$datafile;
Then shutdown the instance
sql>shutdown immediate;

7) Create SYSAUX tablespace
-------------------------
Once you have changed the datafile names you need to start up the instance
by issuing the following command:

SQL> STARTUP UPGRADE

And create sysaux tablespace

The following SQL statement would create a 500 MB SYSAUX tablespace for the
database:

SQL> CREATE TABLESPACE sysaux DATAFILE '/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

8) Run catupgrd.sql to upgrade the database
----------------------------------------

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

9) Run utlu102s.sql to display the results of the upgrade:
-------------------------------------------------------
SQL> @utlu102s.sql
SQL> SPOOL OFF;

Verify the upgrade.log file to check for any errors during the upgrade processes

10) Bounce Database
---------------
Shut down and restart the instance to reinitialize the system parameters for normal operation.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11) Create UNDO tablespace to use AUTOMATIC UNDO MANAGEMENT
-------------------------------------------------------

SQL> create undo tablespace UNDOTS1
datafile '/undots101.dbf' size 500M;
SQL> alter system set UNDO_MANAGEMENT=AUTO scope=spfile;
SQL> alter system set UNDO_TABLESPACE=UNDOTS1 scope=spfile;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP

12) Run olstrig.sql
----------------
Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.
SQL> @olstrig.sql

13) Recompile packages
------------------
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

14) Check for invalid objects
-------------------------
Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

ORA-1652 unable to extend ....

2009-06-25T04:57:00.001-07:00

Este es un error comun que nos pasa a menudo.. es cuando queremos o mejor dico necesitas mas espacio temporal para alocar un tablespace, ahora bien si esta tablespace es de alocamiento temporal esta todo bien pero cuando tenemos este error para un tablespace NO temporal estamos en problemas y para esto tenemos que setear un event que se puede hacer en el init o via alter system, de la siguiente forma..

EVENT="1652 TRACE NAME ERRORSTACK LEVEL 3"

Este event lo debemos poner en el INIT de la base y reiniciarla, pero suele ocurriri en el gran porcentaje de la veces que la base no se puede bajar por el estado productivo de esta, entonces hacemos lo siguiente:

alter system set events '1652 trace name errorstack level 3'

Pero bien, al hacer esto no se genera el trace por lo que vamos a necesitar el uso del dbms_system.set_ev

Ahroa para averiguar bien cual fue el segmento que ocasiono el problemas hacemos el siguiente query...

select * from dba_segments where next_extent > 38400 * db_block_size

Y de esta forma vemos el segmento que ocasiono el problema...

select segment_name,segment_type from dba_segments where next_extent >= (38400 * 8192)

SEGMENT_NAME SEGMENT_TYPE
-------------------------------------------

SYS_IL0000011225C00004$$ LOBINDEX
SYS_IL0000010344C00005$$ LOBINDEX
SYS_LOB0000011225C00004$$ LOBSEGMENT
SYS_LOB0000010344C00005$$ LOBSEGMENT

Ahora en si no podemos hacer nada para solucionar dicho tema mas que agrandar dicho tablespace.

Espero que les sea de ayuda..
Exitos

Creación de código fuente para "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY" y "JAVA SOURCE"

2009-06-08T03:09:00.000-07:00

Ya pase para la creacion de un indice, ahora va para los demas objetos en base a un owner o type.
El siguiente ejemplo es para extraer un package body...

___________________________________________________

select 'CREATE OR REPLACE ' from dual
/
select text
from dba_source
where owner = '&1' AND NAME = '&2' AND Type = 'PACKAGE BODY'
/
--
spool off

espero que les sea de buen uso..

Extraer Codigo para recrear un indice..

2009-06-08T00:54:00.000-07:00

Agrego un codigo para extraer codigo fuente para la recreacion de un indice desde "CERO", espero que sea util.

______________________________________
set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool ind_&&SCHEMA_NAME
set termout off
col y noprint
col x noprint
col z noprint
select 'rem **** Create Index DDL for '||chr(10)||
'rem **** '||username||''''||'s tables'||chr(10)||chr(10)
from dba_users
where username = upper ('&&1')
/
select table_name z,
index_name y,
-1 x,
'create ' || rtrim(decode(uniqueness,'UNIQUE','UNIQUE',null))
|| ' index ' ||
rtrim(index_name)
from dba_indexes
where table_owner = upper('&&1')
union
select table_name z,
index_name y,
0 x,
'on ' ||
rtrim(table_name) ||
'('
from dba_indexes
where table_owner = upper('&&1')
union
select table_name z,
index_name y,
column_position x,
rtrim(decode(column_position,1,null,','))||
rtrim(column_name)
from dba_ind_columns
where table_owner = upper('&&1')
union
select table_name z,
index_name y,
999999 x,
')' || chr(10)
||'unrecoverable ' || chr(10)
||'STORAGE(' || chr(10)
||'INITIAL ' || initial_extent || chr(10)
||'NEXT ' || next_extent || chr(10)
||'MINEXTENTS ' || '1' || chr(10)
||'MAXEXTENTS ' || max_extents || chr(10)
||'PCTINCREASE '|| '0' ||')' || chr(10)
||'INITRANS ' || ini_trans || chr(10)
||'MAXTRANS ' || max_trans || chr(10)
||'PCTFREE ' || '0' || chr(10)
||'TABLESPACE ' || tablespace_name ||chr(10)
||'PARALLEL (DEGREE ' || DEGREE || ') ' || chr(10)
||'/'||chr(10)||chr(10)
from dba_indexes
where table_owner = upper('&&1')
order by 1,2,3;

Exitos, espero les sirva..

Uso de los Parametros..

2009-06-04T00:32:00.000-07:00

Amigos dba's.. va un listado de todos los parametros que se utilizan en el INI de la base segun version, es bueno a la hora de aplicarlo dado que hay veces que no sabemos o se nos olvida la forma de escribirlo.

Esta bueno espero que les sirva.

Green = New in 10g

        Red   = Valid in 9i but dropped (or hidden) in 10g

        Bold  = Static Parameter - change in pfile/SPfile

        Normal= Dynamic Parameter - change in pfile/SPfile/ALTER SYSTEM/SESSION

   PARAMETER                       DESCRIPTION

  ------------------------------  ----------------------------------------

   ACTIVE_INSTANCE_COUNT  = int   Active instances in the cluster

   AQ_TM_PROCESSES = int          Number of AQ Time Managers to start

   ARCHIVE_LAG_TARGET = int       Max no. seconds of redos the standby could lose

   asm_diskgroups = string        Disk groups to mount automatically

   asm_diskstring = string        Disk set locations for discovery

   asm_power_limit = int          Number of processes for disk rebalancing

   AUDIT_FILE_DEST = 'directory'  Directory in which auditing files are to reside

   AUDIT_SYS_OPERATIONS = {TRUE|FALSE}

   AUDIT_TRAIL = {NONE | FALSE | DB | TRUE | OS} Enable system auditing 9i

   AUDIT_TRAIL = {NONE | DB | DB_EXTENDED| OS} Enable system auditing 10g

   BACKGROUND_CORE_DUMP = {PARTIAL | FULL}

   BACKGROUND_DUMP_DEST = 'path or directory'

   BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED

   BITMAP_MERGE_AREA_SIZE = int   Memory for BITMAP MERGE

   BLANK_TRIMMING  = {TRUE|FALSE}

   CIRCUITS = int

   CLUSTER_DATABASE = {TRUE|FALSE}    If TRUE startup in cluster database mode

   CLUSTER_DATABASE_INSTANCES = int

   CLUSTER_INTERCONNECTS  = ipaddr [:ipaddr...] Interconnects for RAC use

   COMMIT_POINT_STRENGTH = int

   COMPATIBLE = release_number     [CHAR: 9.2.0.0.0]

   CONSTRAINT[S] = { IMMEDIATE | DEFERRED | DEFAULT }

   CONTROL_FILE_RECORD_KEEP_TIME = int   Time in Days

   CONTROL_FILES =filename [,filename [...] ]

   CORE_DUMP_DEST = 'text'

   CPU_COUNT = int

   CREATE_BITMAP_AREA_SIZE = int

   CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name' } [NOOVERRIDE]

   CURSOR_SHARING = {SIMILAR | EXACT | FORCE}

   CURSOR_SPACE_FOR_TIME = {TRUE|FALSE}

   CURRENT_SCHEMA = schema        Change the current schema of the session

   DB_2k_cache_size = int bytes         Size of cache for 2K buffers

   DB_4k_cache_size = int bytes         Size of cache for 4K buffers

   DB_8k_cache_size = int bytes         Size of cache for 8K buffers

   DB_16k_cache_size = int bytes        Size of cache for 16K buffers

   DB_32k_cache_size = int bytes        Size of cache for 32K buffers

   DB_BLOCK_BUFFERS = int Deprecated in favour of DB_CACHE_ SIZE

   DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED

   DB_BLOCK_CHECKSUM = {TRUE | FALSE}

   DB_BLOCK_SIZE = int  [bytes] Do not alter after db creation

   DB_CACHE_ADVICE = {ON | READY | OFF}

   DB_CACHE_SIZE = int [bytes]

   DB_CREATE_FILE_DEST = directory

   DB_CREATE_ONLINE_LOG_DEST_n = directory  (where n = 1-5)

                             Default locn for Oracle-managed control files and online redo logs.

   DB_DOMAIN = domain_name   Directory part of global database name

   DB_FILE_MULTIBLOCK_READ_COUNT = int

   DB_FILE_NAME_CONVERT = [(]'dbfile1' , 'dbfile2'...[)]

                          Datafile name convert patterns and strings

                          for standby/clone db [old string, new string]

   DB_FILES = int

   db_flashback_retention_target = int   Max Flashback Database log retention (minutes)

   DB_KEEP_CACHE_SIZE  = int [bytes]

   DB_NAME = database_name

   db_recovery_file_dest = string    Default database recovery file location

   db_recovery_file_dest_size = int  Database recovery files size limit

   DB_RECYCLE_CACHE_SIZE = int [bytes]

   db_unique_name = string           Database Unique Name

   DB_WRITER_PROCESSES = int         Number of background database writer

                                     processes to start

   DBLINK_ENCRYPT_LOGIN = {TRUE|FALSE}  Enforce password encryption for distributed login

   DBWR_IO_SLAVES = int

   DDL_WAIT_FOR_LOCKS = {TRUE|FALSE}  Disable NOWAIT DML lock acquisitions

   DG_BROKER_CONFIG_FILEn = filename  (where n = 1 or 2)

   DG_BROKER_START = {TRUE|FALSE}

   DISK_ASYNCH_IO = {TRUE|FALSE}

   DISPATCHERS = 'dispatch_clause' (see SQL ref manual for detail)(MTS_Dispatchers in Ora 8/9)

   DISTRIBUTED_LOCK_TIMEOUT = int

   DML_LOCKS = int            One for each table modified in a transaction

   DRS_START = {TRUE|FALSE}   Start DG Broker monitor (DMON process)

   ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}

   ENQUEUE_RESOURCES = int    Resources for enqueues

   EVENT = debug_string       Debug event control

   FAL_CLIENT = string        Fetch archive log Client

   FAL_SERVER = string        Fetch archive log Server

   FAST_START_IO_TARGET = int     Upper bound on recovery reads(Deprecated)

   FAST_START_MTTR_TARGET = int

   FAST_START_PARALLEL_ROLLBACK = {FALSE | LOW | HIGH}

                                  Max number of parallel recovery slaves

   FILE_MAPPING = {TRUE|FALSE}

   FILEIO_NETWORK_ADAPTERS = char      Network Adapters for File I/O

   FILESYSTEMIO_OPTIONS = {none | setall | directIO | asynch}

   FIXED_DATE = {'YYYY_MM_DD_HH24_MI-SS' | 'date in default format'}

                                  Fix SYSDATE value for debugging

   GC_FILES_TO_LOCKS = '{file_list=lock_count[!blocks][EACH][:...]}'

                                  RAC/OPS - lock granularity number of

                                  global cache locks per file (DFS)

   GCS_SERVER_PROCESSES = int     Number of background gcs server processes to start

   GLOBAL_CONTEXT_POOL_SIZE = {1 MB | int MB}

   GLOBAL_NAMES = {TRUE | FALSE}  Enforce that database links have same

                                  name as remote database

   HASH_AREA_SIZE = int        Size of in-memory hash work area (Shared Server)

   HASH_JOIN_ENABLED = {TRUE|FALSE}

   HI_SHARED_MEMORY_ADDRESS = int    SGA starting address (high order 32-bits

                                     on 64-bit platforms)

   HS_AUTOREGISTER = {TRUE | FALSE}  Enable automatic server DD updates in HS

                                     agent self-registration

   IFILE = parameter_file_name       Include file in init.ora

   INSTANCE = int                    Connect to a different RAC instance

   INSTANCE_GROUPS = group_name [,group_name ... ]

   INSTANCE_NAME = instance_id

   INSTANCE_NUMBER = int

   INSTANCE_TYPE = {RDBMS|ASM}    Type of instance to be executed

                                  RDBMS or Automated Storage Management

   ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED}

   JAVA_MAX_SESSIONSPACE_SIZE = int [bytes]

   JAVA_POOL_SIZE = int [bytes]

   JAVA_SOFT_SESSIONSPACE_LIMIT = int

   JOB_QUEUE_PROCESSES = int

   LARGE_POOL_SIZE = int [bytes]

   LICENSE_MAX_SESSIONS = int    Maximum number of non-system user sessions

                                 (concurrent licensing)

   LICENSE_MAX_USERS = int       Maximum number of named users that can be created

                                 (named user licensing)

   LICENSE_SESSIONS_WARNING = int Warning level for number of non-system

                                  user sessions

   LOCAL_LISTENER = network_name  Define which listeners instances register with

   LOCK_NAME_SPACE = namespace    Used for generating lock names for standby/primary database

                                  assign each a unique name space

   LOCK_SGA = {TRUE | FALSE}

   LOG_ARCHIVE_CONFIG = [SEND|NOSEND] [RECEIVE|NORECEIVE] [ DG_CONFIG]

   LOG_ARCHIVE_DEST = string

   LOG_ARCHIVE_DEST_n = {null_string |

      {LOCATION=local_pathname | SERVICE=tnsnames_service}

          [MANDATORY | OPTIONAL] [REOPEN[=integer]]}

   LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}   (n = 1-10)

   LOG_ARCHIVE_DUPLEX_DEST = string

   LOG_ARCHIVE_FORMAT = string    [CHAR: "MyApp%S.ARC"]

   LOG_ARCHIVE_LOCAL_FIRST = {TRUE|FALSE}    Establish EXPEDITE attribute default value

   LOG_ARCHIVE_MAX_PROCESSES = int

   LOG_ARCHIVE_MIN_SUCCEED_DEST = int   Minimum number of archive destinations

                                        that must succeed

   LOG_ARCHIVE_START = {TRUE | FALSE}

   LOG_ARCHIVE_TRACE = int            Tracing level for Archive logs

   LOG_BUFFER = int bytes             Redo circular buffer size

   LOG_CHECKPOINT_INTERVAL = int      Checkpoint threshold, # redo blocks

   LOG_CHECKPOINT_TIMEOUT = int       Checkpoint threshold, maximum time interval between

                                      checkpoints in seconds

   LOG_CHECKPOINTS_TO_ALERT = {TRUE|FALSE}    Log checkpoint begin/end to alert file

   LOG_FILE_NAME_CONVERT = ['old string','new string']

                                    Convert patterns/strings for standby/clone db

   LOG_PARALLELISM = int           Number of log buffer strands

   LOGMNR_MAX_PERSISTENT_SESSIONS = int  Maximum no of threads to mine

   MAX_DISPATCHERS = int         Max number of dispatchers

   MAX_DUMP_FILE_SIZE = {size bytes|UNLIMITED} [DEFERRED]

   MAX_ENABLED_ROLES = int       Max number of roles a user can have enabled

   MAX_ROLLBACK_SEGMENTS = int   Max number of rollback segments in SGA cache

   MAX_SHARED_SERVERS = int      Max number of shared servers]

   mts_circuits = int        Max number of circuits (10g see CIRCUITS)

   mts_dispatchers           Specifications of dispatchers (10g see DISPATCHERS)

   MTS_LISTENER_ADDRESS      Address(es) of network listener [CHAR]

   mts_max_dispatchers       Max number of dispatchers (10g see MAX_DISPATCHERS)

   mts_max_servers           Max number of shared servers (10g see MAX_SHARED_SERVERS)

   MTS_MULTIPLE_LISTENERS = {TRUE|FALSE}   Are multiple listeners enabled?

   MTS_SERVERS = int         Number of shared servers to start up [NUMBER]

   mts_service = string      Service supported by dispatchers [CHAR]

   mts_sessions = int        max number of shared server sessions [NUMBER]

   nls_calendar ='string'      NLS calendar system name (Default=GREGORIAN)

   nls_comp = {BINARY | ANSI}  NLS comparison, Enterprise Edition

   nls_currency ='string'      NLS local currency symbol

   nls_date_format ='format'   NLS Oracle date format

   nls_date_language =language NLS date language name (Default=AMERICAN)]

   nls_dual_currency = currency_symbol

   nls_iso_currency = territory    Override the default set by NLS_TERRITORY

   nls_language = language         NLS language name (session default)

   nls_length_semantics = {BYTE|CHAR}}  Default when creating new columns

   nls_nchar_conv_excp = {TRUE|FALSE}  Raise an exception instead of

                                       allowing an implicit conversion

   nls_numeric_characters ="decimal_character group_separator"

   nls_sort = {BINARY |linguistic_def}     Case-sensitive or insensitive sort

                                  linguistic_def may be BINARY, BINARY_CI, BINARY_AI,

                                  GERMAN, GERMAN_CI, etc

   nls_territory = territory          Territory name (country settings)

   nls_time_format =time_format       Time format

   nls_time_tz_format = time_format   Time with timezone format

   nls_timestamp_format = time_format     Timestamp format

   nls_timestamp_tz_format = time_format  Timestamp with timezone format

   O7_DICTIONARY_ACCESSIBILITY = {TRUE | FALSE}   Allow Dictionary Access (as in Ora V7 )

   OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED   Space for application objects Max

   OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED       Space for application objects Min

   OLAP_PAGE_POOL_SIZE =int bytes

   OPEN_CURSORS = int              Max # cursors per session

   OPEN_LINKS = int                Max # open links per Session

   OPEN_LINKS_PER_INSTANCE = int   Max # open links per instance

   OPTIMIZER_DYNAMIC_SAMPLING = int

   OPTIMIZER_FEATURES_ENABLE = {8.0.0|8.0.3|8.0.4|8.0.5|8.0.6|8.0.7|8.1.0|8.1.3|8.1.4|8.1.5|8.1.6|8.1.7|9.0.0|9.0.1|9.2.0}

                               Configure qry optimiser based on an Oracle release No.

   OPTIMIZER_INDEX_CACHING = int     Percent to cache (favour nested loop joins & IN-list)

   OPTIMIZER_INDEX_COST_ADJ = int    Adjust the cost of index vs FTS

   OPTIMIZER_MAX_PERMUTATIONS = int  Max join permutations per qry block

   OPTIMIZER_MODE = [RULE | CHOOSE | FIRST_ROWS | ALL_ROWS]

   oracle_trace_collection_name =collection   Name for use by Oracle TRACE

   oracle_trace_collection_path =path         Path to .cdf & .dat files (ORACLE_HOME/otrace/admin/cdf)

   oracle_trace_collection_size =int bytes    Max trace file size

   oracle_trace_enable = {TRUE|FALSE}         Enable Oracle Trace

   oracle_trace_facility_name ={ORACLED | ORACLEE | ORACLESM | ORACLEC}    TRACE event set

   oracle_trace_facility_path =path           TRACE definition files:  ORACLE_HOME/otrace/admin/fdf/

   OS_AUTHENT_PREFIX = prefix                 Prefix for auto-logon accounts [string]

   OS_ROLES = {TRUE|FALSE}                    Retrieve roles from the operating system

   PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE}  Tune degree of parallelism

   PARALLEL_AUTOMATIC_TUNING = {TRUE|FALSE}       Automatic tuning

   PARALLEL_EXECUTION_MESSAGE_SIZE = int bytes    Message buffer size

   PARALLEL_INSTANCE_GROUP = 'group'              RAC: Limit instances used

   PARALLEL_MAX_SERVERS = int

   PARALLEL_MIN_PERCENT = int                Min percent of threads required for

                                             parallel query

   PARALLEL_MIN_SERVERS = int

   PARALLEL_SERVER = [TRUE | FALSE]          Startup in parallel server mode

   PARALLEL_SERVER_instances = int           No. of instances (used for sizing SGA)

   PARALLEL_THREADS_PER_CPU = int

   PARTITION_VIEW_ENABLED = {TRUE|FALSE}     Deprecated (use partition TABLES)

   PGA_AGGREGATE_TARGET = int bytes          Automatically size the SQL working area

   plsql_code_type ={INTERPRETED | NATIVE}   Code-type

   PLSQL_COMPILER_FLAGS = { [DEBUG | NON_DEBUG] [INTERPRETED | NATIVE] }

   plsql_debug ={TRUE | FALSE}

   plsql_native_c_compiler

   plsql_native_library_dir = ['Path_to_directory']

   plsql_native_library_subdir_count = int

   plsql_native_linker =path             Path to linker

   plsql_native_make_file_name =path     Pathname of make file

   plsql_native_make_utility =path       Pathname of make utility

   plsql_optimize_level                  Optimize level

   PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED]

   plsql_warnings =string                Compiler warnings settings

                                  See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS

   PRE_PAGE_SGA = {TRUE|FALSE}           Pre-page sga for process

   PROCESSES = int                       User processes

   QUERY_REWRITE_ENABLED = {FORCE | TRUE | FALSE} [DEFERRED | NOOVERRIDE]

   QUERY_REWRITE_INTEGRITY = {ENFORCED | TRUSTED | STALE_TOLERATED}

   RDBMS_SERVER_DN = Distinguished Name

   READ_ONLY_OPEN_DELAYED = {TRUE | FALSE}   Delay opening read_only files until first access

   RECOVERY_PARALLELISM = int    Server processes to use for parallel recovery

   REMOTE_ARCHIVE_ENABLE = [RECEIVE[,SEND] | FALSE | TRUE]

                                 Enable or disable sending archived redo logs to/from remote destinations

   REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}

                                 Remote-procedure-call dependencies mode

   REMOTE_LISTENER =network_name

   REMOTE_LOGIN_PASSWORDFILE ={NONE | SHARED | EXCLUSIVE}    Use a password file

   REMOTE_OS_AUTHENT = {TRUE | FALSE}    Allow non-secure remote clients to use

                                         auto-logon accounts

   REMOTE_OS_ROLES = {TRUE | FALSE}      Allow non-secure remote clients to use os roles

   REPLICATION_DEPENDENCY_TRACKING = {TRUE | FALSE}

   RESOURCE_LIMIT = {TRUE | FALSE}       Master switch for resource limit

   RESOURCE_MANAGER_PLAN = plan_name     Turn on Resource Manager plan

   resumable_timeout =seconds            Set resumable_timeout

   ROLLBACK_SEGMENTS = (rbs1 [, rbs2] ... )

   ROW_LOCKING = [ALWAYS | DEFAULT | INTENT]

   SERIAL_REUSE =    {DISABLE | SELECT | DML | PLSQL | ALL}   Cursor memmory management

   SERVICE_NAMES = db_service_name [,db_service_name [,...] ]

   SESSION_CACHED_CURSORS = int          Session cursors to cache

   SESSION_MAX_OPEN_FILES = int          Max no. of BFiles (LOB) each session can open

   SESSIONS = int                        Max no. of user and system sessions

   SGA_MAX_SIZE =int bytes               Initial SGA size

   sga_target = int bytes                 Target size of SGA

   SHADOW_CORE_DUMP = {PARTIAL | FULL | NONE}  Include SGA in core file

   SHARED_MEMORY_ADDRESS = int                 SGA starting address (platform specific)

   SHARED_POOL_RESERVED_SIZE = int bytes       Reserved area of shared pool

   SHARED_POOL_SIZE = int         Size of shared pool

   SHARED_SERVERS = int           Number of shared servers to start up (MTS)

   SHARED_SERVER_SESSIONS = int   Max number of shared server sessions

   SKIP_UNUSABLE_INDEXES  = {TRUE | FALSE}

   smtp_out_server = server_clause               utl_smtp server and port configuration parameter

   SORT_AREA_RETAINED_SIZE =int bytes [DEFERRED]   UGA Memory to retain (Shared Server)

   SORT_AREA_SIZE = int bytes [DEFERRED]           In-memory sort work area (Shared Server)

   SORT_MULTIBLOCK_READ_COUNT     Obsolete in 9i

   SPFILE =spfile_name            Parameter file

   sp_name =name                  Service Provider Name

   SQL92_SECURITY  = {TRUE | FALSE}     Require select privilege for update/delete

   SQL_TRACE = {TRUE | FALSE}           Enable SQL trace

   sqltune_category =category           Qualifier for applying hintsets

   SQL_VERSION =version                 Sql language version, for compatibility

   STANDBY_ARCHIVE_DEST = 'filespec'    Standby database archivelog destination

   STANDBY_FILE_MANAGEMENT = {MANUAL | AUTO}

                                        Automate file mmanagement on standby DB

   STAR_TRANSFORMATION_ENABLED = {TEMP_DISABLE | TRUE | FALSE}

   STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}        Collect Statistics

   streams_pool_size = int bytes        Size of the streams pool

   TAPE_ASYNCH_IO = {TRUE | FALSE}      Allow I/O requests to tape devices at the same time as CPU processing

   THREAD =int                          Redo thread to use (RAC)

   TIMED_OS_STATISTICS = int            Gather OS statistics every x seconds

   TIMED_STATISTICS = {TRUE | FALSE}    Collect time statistics

   TIME_ZONE =  '[+ | -] hh:mm'| LOCAL | DBTIMEZONE | 'time_zone_region'

   TRACE_ENABLED = {TRUE | FALSE}       Trace execution path (Internal use only-Oracle support services)

   TRACEFILE_IDENTIFIER = "traceid"     Trace file custom identifier

   TRANSACTION_AUDITING = {TRUE | FALSE} [DEFERRED]

   TRANSACTIONS = int                    Max. number of concurrent active transactions

   TRANSACTIONS_PER_ROLLBACK_SEGMENT = int

   UNDO_MANAGEMENT = {MANUAL | AUTO}    Undo space management mode (Manual=rollback segs)

   UNDO_RETENTION = int                 Undo retention in second

   UNDO_SUPPRESS_ERRORS  = {TRUE |FALSE}     Suppress RBU errors in SMU mode

   UNDO_TABLESPACE =undoname            Select an undo tablespace

   USE_INDIRECT_DATA_BUFFERS = {TRUE|FALSE}  Configure SGA Memory cache for >4Gb RAM

   USE_PRIVATE_OUTLINES = {TRUE |FALSE |category_name }

   USE_STORED_OUTLINES = { TRUE |FALSE |category_name} [NOOVERRIDE]

   USER_DUMP_DEST = 'directory_name'    User process dump directory

   UTL_FILE_DIR                         Utl_file accessible directories list

                                   UTL_FILE_DIR ='Path1', 'Path2'..

or

                                   UTL_FILE_DIR ='Path1'  # Must be

                                   UTL_FILE_DIR ='Path2'  # consecutive entries

   WORKAREA_SIZE_POLICY = {AUTO | MANUAL}    Policy used to size SQL working areas

ORA - 0600 - CONCEPTOS y EXPERIENCIA

2009-05-29T02:42:00.000-07:00

El núcleo del Oracle plantea un ORA-00600 error inesperado cuando una condición inesperada se cumple en su funcionamiento normal. Un ORA-00600 error puede no ser necesariamente un error todo el tiempo y podría ser causado por diversas razones. Pueden incluir la falta de recursos de sistema operativo, los fallos de hardware y otras muchas cuestiones que dependen del sistema conductor. En muchos casos, el error ORA-00600 indica que la corrupción existente en la base de datos Oracle o puede ser una indicación de los acontecimientos que condujeron a los datos o base de datos a la corrupcion. Nosotros los administradores de oracle debemos tomar muy en serio los 0600 y reportarlos de inmediato a Oracle. Veamos ahora como deducir o darnos cuenta del tipo de 0600.

ORA-00600: Cómo identificar que han tenido un ORA-00600

Cuando un error ORA-00600 se genera, al prinicio de la solicitud puede o no puede ver este error. Por lo tanto, el dba debe aplicar una alerta a la página de registro de seguimiento de la primera aparición de un ORA-00600. Entonces cuando se genera un error, parece como ejemplo a continuación y se muestra con una lista de argumentos. El error crea una entrada en el alert de la base en BDUMP y crea también un trace en el directorio udump. Estos 2 archivos son muy críticos para ser cargado en la solicitud de soporte al contactar metalink.

El primer argumento identifica la zona en el oracle de código de error fue capturado y ofrece la mejor indicación para identificar el problema. El primer argumento puede ser un número o una cadena de caracteres. El resto de los argumentos son los valores que dependen de diversas variables internas incluyendo información etc.

ORA-00600: la solución de problemas de la experiencia del DBA

Metalink proporciona información detallada sobre la solución de problemas de errores. Oracle logra un documento actualizado sobre ORA-00600 errores generados con cada versión de Oracle en servidores internos de su base de conocimientos.

También contiene Metalink, la Nota 153788,1 - Solucionar un error ORA-00600 ORA utilizando el argumento-00600 Herramienta de búsqueda, esta herramienta tiene en primer argumento y la pila de información como insumos para proporcionar orientación sobre la identificación de las posibles cuestiones relacionadas con el error.

Cómo resolver con un ORA-00600

ORA-00600 debe considerar un grave error y el dba debería abrir un TIE con oracle y seguir trabajando en la solución de la cuestión. La documentacion de Metalink que usted puede apuntar algunas soluciones, pero se debe consultar con Oracle antes de aplicar cualquier parche al respecto.

Espero que les sirva.

ORA -0600 [ksnpost : ksnigb]

2009-05-29T01:50:00.000-07:00

Error que se debe al limite de inodos del sistema operativo
Se tiene que modificar el valor de nofiles a 8192

se hace de la siguiente manera:
1.- Verificamos los valores actuales.

> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 1024
vmemory(kbytes) unlimited

2.- luego cambiamos el valor rlim_fdmax en el /etc/system al número indicado (1024).

3.- para que haga efecto se debe hacer un reinicio del equipo.

Aportado por Claudia Franco (dba Oracle Senior)
Espero que les sirva.

ORA - 0600 [ttcgcshnd-1] [a]

2009-05-29T01:00:00.000-07:00

Bueno aca nos encontramos con dos posibles soluciones...

1.- Este fucking error se da cuando se utiliza un cliente Jdbc anterior a la 9i para hablar con un 9i o post-9i, se verá una excepción ORA-600 en casi todo lo que haces. por ejemplo: ORA-600 [ttcgcshnd-1]. se debe a que no se esta usando un cliente exactamente igual al de la base de datos.
Oracle comprueba que el conjunto de caracteres locales id es válido como parte del proceso para hacer las conversiones de datos de caracteres entre los distintos conjuntos de caracteres. Este error se plantea cuando el conjunto de caracteres de identificación se encuentra no se considere válido.

ORA-600 [ttcgcshnd-1] [a]: El argumento [a] es local el conjunto de caracteres de identificación

Solución:
set event = "10841 trace name context forever" en el archivo init.ora. Para esto se deberia crear un BTP para incorporar esta linea en el init de la base.

2.- como segundo punto y quizas mas para tener en cuenta es lo que dice una nota de metalink:
This Problem is caused due to Bug in oracle and happens when accessing Oracle 9.0.1 Database with JDBC 8.1.7.0.0.
This bug is caused by the mismatch between the 9i server and the 8.1.7 Jdbc-Thin clients. OERI:ttcgcshnd-1 using JDBC thin pre-9i to 9i database

You are using class12.jar of JDBC 8.1.7.0.0.

Solution: This is fixed in the JDBC Thin Driver bundled in Database patchset 8.1.7.2 or higher version.

So use Oracle provided class12.jar
This might be from JDBC thin driver 8.1.7.2 .
Which is under \oracle\ora92\jdbc\lib\class12.jar.

Espero que les sea de utilidad..
exitos..

Fragmentacion de Tablespaces.

2009-05-22T01:46:00.000-07:00

Este script muestra los extents su ubicación dentro de los tablespace y a su vez permite ver la identificación de la fragmentación de los mismos. Aca se los dejo.. EXITOS !!!

-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF

DECLARE
CURSOR c_extents IS
SELECT owner,
segment_name,
block_id AS start_block,
block_id + blocks - 1 AS end_block
FROM dba_extents
WHERE tablespace_name = Upper('&1')
ORDER BY block_id;

v_last_block_id NUMBER := 0;
BEGIN
FOR cur_rec IN c_extents LOOP
IF cur_rec.start_block > v_last_block_id + 1 THEN
DBMS_OUTPUT.PUT_LINE('*** GAP ***');
END IF;
v_last_block_id := cur_rec.end_block;
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
END LOOP;
END;
/

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18

Top 10 de los queries que estan en memoria.

2009-05-22T01:37:00.001-07:00

Este query muestra los 10 queries mas pesados en memoria.... esta piola porque podemos evaluarlo para ver ocupacion de SGA que nos eta matando al equipo o en su defecto la propia base.

aca se los dejo..
EXITOS !!!

-- -----------------------------------------------------------------------------------
-- Author : Fabian Crea - Cristian Mendoza
-- Last Modified: 15/12/2007
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &&1;

SET PAGESIZE 14

Performance - Query Tuning

2008-12-11T06:00:00.000-08:00

Veremos aca algo de Tunning, el "QUE", "COMO", y "PARA QUE".

Create a plan table
@?/rdbms/admin/utlxplan.sql
___________________________________________________

Autotrace

To switch it on:

column plan_plus_exp format a100

set autotrace on explain   # Displays the execution plan only.
set autotrace traceonly explain # dont run the query
set autotrace on  # Shows the execution plan as well as statistics of the statement.
set autotrace on statistics  # Displays the statistics only.
set autotrace traceonly      # Displays the execution plan and the statistics

________________________________________________________

Find a query's hash

Put something unique in the like clause

select hash_value, sql_text
from v$sqlarea
where sql_text like '%TIMINGLINKS%FOLDERREF%'

______________________________________________________________________

Grab the sql associated with a hash

select sql_text from v$sqlarea where hash_value = '&hash' /

________________________________________________________

Look at a query's stats in the sql area

select executions
, cpu_time
, disk_reads
, buffer_gets
, rows_processed
, buffer_gets / executions
from v$sqlarea
where hash_value = '&hash'

______________________________________________________________
/

Cuanto pesa una BASE DE DATOS.

2008-12-11T05:57:00.000-08:00

Este query nos muestro cuanto pesa una base de datos, saca un calculo en base a los data files asignados y los data files free en espacio.
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
/

Clonar un User

2008-11-13T00:14:00.000-08:00

Aca un script muy importante.. siempre nos piden ... creame un usuario igual a tal otro.. y tenemos que dar muchas vueltas... aca todo en un solo paso.. espero les sea util...

set lines 999 pages 999

set verify off

set feedback off

set heading off

select username

from dba_users

order by username

undefine user

accept userid prompt 'Enter user to clone: '

accept newuser prompt 'Enter new username: '

accept passwd prompt 'Enter new password: '

select username , created

from dba_users

where lower(username) = lower('&newuser')

accept poo prompt 'Continue? (ctrl-c to exit)'

spool /tmp/user_clone_tmp.sql

select 'create user ' || '&newuser' || ' identified by ' || '&passwd' || ' default tablespace ' || default_tablespace || ' temporary tablespace ' || temporary_tablespace || ';' "user"

from dba_users

where username = '&userid'

select 'alter user &newuser quota '|| decode(max_bytes, -1, 'unlimited' , ceil(max_bytes / 1024 / 1024) || 'M') || ' on ' || tablespace_name || ';'

from dba_ts_quotas

where username = '&&userid'

select 'grant ' ||granted_role || ' to &newuser' || decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"

from dba_role_privs

where grantee = '&&userid'

select 'grant ' || privilege || ' to &newuser' || decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"

from dba_sys_privs

where grantee = '&&userid'

spool off

undefine user

set verify on

set feedback on

set heading on

@/tmp/user_clone_tmp.sql

!rm /tmp/user_clone_tmp.sql

Como Calcular que tan GRANDE es la base de datos.

2008-11-11T07:18:00.000-08:00

Este script te lo lo dice ...

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
/

Las STATPACKS

2008-11-11T05:20:00.000-08:00

Quien alguna vez no habra protestado para instalar las statpack en oracle, Este documento lo aporto una amiga de Madrid, Alicia Lopez Serrano (DBA Oracle)

1. pasos necesarios para la automatización de la toma de datos de una base de datos

En las base de datos que estén en Oracle8i deberemos activar la temporización de las estadísticas, para ello ejecutaremos en la base de datos con un usuario con permisos de DBA la sentencia:

Alter system set timed_statistics=true;

En Oracle9i esto no sería necesario puesto que todas las bases de datos son creadas por defecto con este parámetro a TRUE.

A continuación vemos los pasos necesarios para la utilización del paquete dbms_statspack que se encarga de tomar las imágenes de las estadísticas internas de funcionamiento de la base de datos.

1.- Creamos un tablespace de 100 Mbytes para el usuario PERFSTAT, propietario de los objetos donde se almacenarán las estadísticas de funcionamiento de la base de datos.

create tablespace perfstat datafile '/database/x08/perfstat_x0801.dbf' size 100M extent management local autoallocate;

Esta sentencia deberemos particuralizarla para los puntos de montaje de cada máquina en particular, en cuanto al nombre del fichero se refiere.

2.- Lanzamos como el usuario propietario de la base de datos, internal para Oracle8i y sys para Oracle9i, el script $ORACLE_HOME/rdbms/admin/spcreate. Nos pedirá el tablespace por defecto para el usuario que será PERFSTAT y el tablespace temporal, que será TEMPORARY_DATA. En Oracle9i además pide la clave del usuario PERFSTAT, que recomendamos que se ponga igual que el nombre.

En caso de que en este paso nos encontremos con alñgún problema deberemos ejecutar el script $ORACLE_HOME/rdbsm/admin/spdrop para tomar todos los objetos relacionados con el usuario creado.

3.- Creamos el job con el script proporcionado por Oracle, $ORACLE_HOME/rdbms/admin/spauto.sql, junto con las modificaciones necesarias nuestras como indicamos a continuación en esta nota, y que tomará una imagen de la base de datos cada hora. Para ello deberemos conectarnos a la base de datos con el usuario PERFSTAT y la clave que le hayamos puesto en su creación en el paso 1. A continuación tecleamos los siguiente:

spool spauto.lis

-- Programa la toma de una imagen cada hora.

variable v_num_job number;

variable v_num_ins number;

begin

select instance_number into :v_num_ins from v$instance;

dbms_job.submit(:v_num_job,

'statspack.snap(i_snap_level=>10, i_modify_parameter=>''true'');',

trunc(sysdate+1/24,'HH'),

'trunc(sysdate+1/24,''HH'')',

TRUE,

:v_num_ins);

commit;

end;

prompt

prompt Número de job para la recolección automática de estadíticas para la instancia.

prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

prompt Este número será necesario cuando se modifique o borre el job

prompt El job:

print :v_num_job

prompt

prompt Procesos de encolados de jobs.

prompt ~~~~~~~~~~~~~~~~~~~~~~

prompt Debajo el actual valor del parámetro the job_queue_processes en el init.ora

prompt El valor de este parámetro debe ser mayor que 0 para que funcione la

prompt recolección de estadísticas automáticamente.

show parameter job_queue_processes

prompt

prompt Próxima ejecución planificada

prompt ~~~~~~~~~~~~~~~~~~~~~

prompt La próxima ejecución planificada del job es:

Select

job, next_date, next_sec

from

user_jobs

where

job = :jobno;

spool off;

La modificación con respecto al que viene por defecto con la instalación de Oracle es el nivel con el cual se recogen las estadísticas elevándola de 5 a 10, lo cual permite recoger las estadísticas de los latchs, bloqueos internos de las estructuras internas de la base de datos, de los procesos hijos, que en caso de problemas con el rendimiento tendríamos mucha más información para afrontar el problema.

Una vez creado el job será necesarios extraer los informes, para lo cual deberemos ejecutar el script $ORACLE_HOME/rdbms/admin/spreport y darle los identificadores de lasa imágines inicial y final, así como el nombre del informe, como podemos ver a continuación:

13:38:27 SQL/PERFSTAT/x08>@c:\des_817\rdbms\admin\spreport

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

933643006 X08 1 x08

Completed Snapshots

Snap Snap

Instance DB Name Id Snap Started Level Comment

------------ ------------ ----- ----------------- ----- ----------------------

x08 X08 1 27 Dic 2004 11:09 5

2 27 Dic 2004 11:45 10

3 27 Dic 2004 12:07 10

4 27 Dic 2004 12:38 10

5 27 Dic 2004 13:00 10

6 27 Dic 2004 14:00 10

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 2

Begin Snapshot Id specified: 2

Enter value for end_snap: 3

End Snapshot Id specified: 3

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_2_3. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: informe_prueba

A partir de aquí nos mostraría toda la información que extrae y que podremos encontrar en el fichero informe_prueba.lst.

Esto es todo amigos...

Espero haberlos ayudado..

Tuning full

2008-11-11T05:13:00.000-08:00

Bueno aca va algo de tuning. Este es un Script que chequea toda la base de datos y da tips de que tocar y que no tocar en la base de datos.

## by Norberto Fabian Crea

## Agosto de 2000

## para Version 8i - 9i y 10g

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

SELECT *

FROM v$database;

PROMPT

DECLARE

v_value NUMBER;

FUNCTION Format(p_value IN NUMBER)

RETURN VARCHAR2 IS

BEGIN

RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || ' ';

END;

BEGIN

-- --------------------------

-- Dictionary Cache Hit Ratio

-- --------------------------

SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100

INTO v_value

FROM v$rowcache;

DBMS_Output.Put('Dictionary Cache Hit Ratio : ' || Format(v_value));

IF v_value <>

DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');

ELSE

DBMS_Output.Put_Line('Value Acceptable.');

END IF;

-- -----------------------

-- Library Cache Hit Ratio

-- -----------------------

SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100

INTO v_value

FROM v$librarycache;

DBMS_Output.Put('Library Cache Hit Ratio : ' || Format(v_value));

IF v_value <>

DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');

ELSE

DBMS_Output.Put_Line('Value Acceptable.');

END IF;

-- -------------------------------

-- DB Block Buffer Cache Hit Ratio

-- -------------------------------

SELECT (1 - (phys.value / (db.value + cons.value))) * 100

INTO v_value

FROM v$sysstat phys,

v$sysstat db,

v$sysstat cons

WHERE phys.name = 'physical reads'

AND db.name = 'db block gets'

AND cons.name = 'consistent gets';

DBMS_Output.Put('DB Block Buffer Cache Hit Ratio : ' || Format(v_value));

IF v_value <>

DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');

ELSE

DBMS_Output.Put_Line('Value Acceptable.');

END IF;

-- ---------------

-- Latch Hit Ratio

-- ---------------

SELECT (1 - (Sum(misses) / Sum(gets))) * 100

INTO v_value

FROM v$latch;

DBMS_Output.Put('Latch Hit Ratio : ' || Format(v_value));

IF v_value <>

DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');

ELSE

DBMS_Output.Put_Line('Value acceptable.');

END IF;

-- -----------------------

-- Disk Sort Ratio

-- -----------------------

SELECT (disk.value/mem.value) * 100

INTO v_value

FROM v$sysstat disk,

v$sysstat mem

WHERE disk.name = 'sorts (disk)'

AND mem.name = 'sorts (memory)';

DBMS_Output.Put('Disk Sort Ratio : ' || Format(v_value));

IF v_value > 5 THEN

DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');

ELSE

DBMS_Output.Put_Line('Value Acceptable.');

END IF;

-- ----------------------

-- Rollback Segment Waits

-- ----------------------

SELECT (Sum(waits) / Sum(gets)) * 100

INTO v_value

FROM v$rollstat;

DBMS_Output.Put('Rollback Segment Waits : ' || Format(v_value));

IF v_value > 5 THEN

DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');

ELSE

DBMS_Output.Put_Line('Value acceptable.');

END IF;

-- -------------------

-- Dispatcher Workload

-- -------------------

SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)

INTO v_value

FROM v$dispatcher;

DBMS_Output.Put('Dispatcher Workload : ' || Format(v_value));

IF v_value > 50 THEN

DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');

ELSE

DBMS_Output.Put_Line('Value acceptable.');

END IF;

END;

PROMPT

SET FEEDBACK ON

#..............................

Trabajo con Latches

2008-10-29T06:54:00.000-07:00

What Are Latches?

Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the

SGA). In simple terms, latches prevent two processes from simultaneously updating — and

possibly corrupting — the same area of the SGA.

Oracle sessions need to update or read from the SGA for almost all database operations. For

instance:

• When a session reads a block from disk, it must modify a free block in the buffer

cache and adjust the buffer cache LRU chain1.

• When a session reads a block from the SGA, it will modify the LRU chain.

• When a new SQL statement is parsed, it will be added to the library cache within the

SGA.

• As modifications are made to blocks, entries are placed in the redo buffer.

• The database writer periodically writes buffers from the cache to disk (and must

update their status from “dirty” to “clean”).

• The redo log writer writes entries from the redo buffer to the redo logs.

Latches prevent any of these operations from colliding and possibly corrupting the SGA.

How Latches Work

Because the duration of operations against memory is very small (typically in the order of

nanoseconds) and the frequency of latch requests very high, the latching mechanism needs to

be very lightweight. On most systems, a single machine instruction called “test and set” is

used to see if the latch is taken (by looking at a specific memory address) and if not, acquire it

(by changing the value in the memory address).

If the latch is already in use, Oracle can assume that it will not be in use for long, so rather

than go into a passive wait (e.g., relinquish the CPU and go to sleep) Oracle will retry the

operation a number of times before giving up and going to passive wait. This algorithm is

called acquiring a spinlock and the number of “spins” before sleeping is controlled by the

Oracle initialization parameter “_spin_count”.

The first time the session fails to acquire the latch by spinning, it will attempt to awaken after

10 milliseconds. Subsequent waits will increase in duration and in extreme circumstances

may exceed one second. In a system suffering from intense contention for latches, these waits

will have a severe impact on response time and throughput.

1 The LRU (Least Recently Used) chain records how often individual blocks have been accessed. If a

block is not accessed it moves towards the LRU end of the list and eventually is flushed from the cache.

Causes of contention for specific latches

The latches that most frequently affect performance are those protecting the buffer cache,

areas of the shared pool and the redo buffer.

• Library cache latches: These latches protect the library cache in which sharable

SQL is stored. In a well defined application there should be little or no contention for

these latches, but in an application that uses literals instead of bind variables (for

instance “WHERE surname=’HARRISON’” rather that “WHERE

surname=:surname,” library cache contention is common.

• Redo copy/redo allocation latches: These latches protect the redo log buffer,

which buffers entries made to the redo log. Recent improvements (from Oracle 7.3

onwards) have reduced the frequency and severity of contention for these latches.

• Shared pool latches: These latches are held when allocations or de-allocations of

memory occur in the shared pool. Prior to Oracle 8.1.7, the most common cause of

shared pool latch contention was an overly large shared pool and/or failure to make

use of the reserved area of the shared pool2.

• Cache buffers chain latches: These latches are held when sessions read or write to

buffers in the buffer cache. In Oracle8i, there are typically a very large number of

these latches each of which protects only a handful of blocks. Contention on these

latches is typically caused by concurrent access to a very “hot” block and the most

common type of such a hot block is an index root or branch block (since any index

based query must access the root block).

Measuring Latch Contention

Ratio-based Techniques

Conventional wisdom in the mid-90s was to focus on the latch “miss” rate to determine the

degree of latch contention.

We see that for each latch, the number of gets (requests for the latch), misses (number of

times the first request fails) and sleeps (number of times a session failed to obtain a latch by

spinning) are recorded. In the past, queries such as the following were often used to determine

latch health:

SQL> select name, gets, misses, misses*100/gets misspct from v$latch where gets>0;

NAME GETS MISSES MISSPCT

---------------------------------------- ----------- ----------- -------

latch wait list 32 1 3.13

process allocation 28 0 .00

session allocation 1,223,068 84 .01

session switching 8,009 0 .00

process group creation 40 0 .00

session idle bit 2,426,940 1 .00

shared java pool 1,188 0 .00

event group latch 28 0 .00

messages 2,128,851 461 .02

enqueues 3,168,279 7 .00

enqueue hash chains 1,747,312 2 .00

channel handle pool latch 40 0 .00

channel operations parent latch 158 3 1.90

message pool operations parent latch 3 0 .00

file number translation table 946 0 .00

mostly latch-free SCN 1 0 .00

cache buffers lru chain 343,827 0 .00

active checkpoint queue latch 174,293 0 .00

checkpoint queue latch 1,459,929 26 .00

cache buffers chains 13,851,179 16,254 .12

cache buffer handles 61 0 .00

This approach was flawed on a number of levels:

• It is actually the number of sleeps that most accurately influences the impact of the

latch contention on response time.

• A high miss rate is expected for certain latches.

• A latch with a high miss rate (or sleep rate) that is not frequently accessed is probably

not impacting performance.

• Even if a latch is experiencing a high sleep rate, we can’t determine the impact on

performance without taking into account waits for other resources. So if sessions are

waiting 90% for IO, 8% for CPU and 2% for latch, expending effort on halving the

latch sleep wait only provides a 1% improvement in response time – probably not

noticeable.

In the above example the “latch wait list” latch has the highest miss rate. However, this is

totally irrelevant since it was only requested 26 times, while the “cache buffer chains” latch

appears to have only a moderate miss rate, but has been requested almost three million times

and — as we shall see — is the latch most affecting performance.

Wait interface-based techniques

A better approach to estimating the impact of latch contention is to consider the relative

amount of time being spent waiting for latches. The following query gives us some indication

of this:

SELECT event, time_waited,

round(time_waited*100/ SUM (time_waited) OVER(),2) wait_pct

FROM (SELECT event, time_waited

FROM v$system_event

WHERE event NOT IN

('Null event',

'client message',

'rdbms ipc reply',

'smon timer',

'rdbms ipc message',

'PX Idle Wait',

'PL/SQL lock timer',

'file open',

'pmon timer',

'WMON goes to sleep',

'virtual circuit status',

'dispatcher timer',

'SQL*Net message from client',

'parallel query dequeue wait',

'pipe get'

) UNION

(SELECT NAME, VALUE

FROM v$sysstat

WHERE NAME LIKE 'CPU used when call started'))

ORDER BY 2 DESC

EVENT TIME_WAITED WAIT_PCT

------------------------------ ----------- ----------

latch free 40144 31.67

CPU used when call started 30341 23.94

control file sequential read 12341 9.74

direct path read 11933 9.41

control file parallel write 6487 5.12

file identify 5666 4.47

log file sync 3492 2.75

log file parallel write 3213 2.53

instance state change 3064 2.42

log file switch completion 3049 2.41

db file sequential read 2290 1.81

Now we can look at the sleeps in v$latch to determine which latches are likely to be

contributing most to this problem:

SQL> select name, gets, sleeps,

sleeps*100/sum(sleeps) over() sleep_pct, sleeps*100/gets sleep_rate

from v$latch where gets>0

order by sleeps desc;

NAME GETS SLEEPS SLEEP_PCT SLEEP_RATE

------------------------------ ----------- ------------ --------- ----------

cache buffers chains 13,863,552 38,071 99.48 .2746

session allocation 1,223,982 110 .29 .0090

checkpoint queue latch 1,461,039 39 .10 .0027

library cache 9,239,751 22 .06 .0002

shared pool 869,652 16 .04 .0018

messages 2,130,515 6 .02 .0003

redo writing 1,330,987 6 .02 .0005

latch wait list 33 0 .00 .0000

session switching 8,014 0 .00 .0000

session idle bit 2,428,851 0 .00 .0000

enqueues 3,171,018 0 .00 .0000

channel handle pool latch 40 0 .00 .0000

message pool operations parent 3 0 .00 .0000

latch

mostly latch-free SCN 1 0 .00 .0000

Now we are in a position to make some reasonable conclusions:

• Latch sleeps contribute to about 30% of database response time (very excessive),

AND

• It’s the cache buffers chains latches that contributes to the vast majority of these

waits.

Note that if we had used the conventional “ratio based” analysis outlined in the previous

section we would have discounted cache buffers chains latches as a problem because the miss

rate was “only” 0.15%.

Tuning the Application to Avoid Latch Contention

There are some things we can do within our application design that can reduce contention for

latches.

Using Bind Variables

As noted earlier, failure to use bind variables within an application is the major cause of

library cache latch contention. All Oracle applications should make use of bind variables

whenever possible.

However, all is not lost if you are unable to modify your application code. From 8.1.6

onwards you can use the “CURSOR_SHARING” parameter to cause Oracle to modify SQL

on the fly to use bind variables. A setting of FORCE causes all literals to be converted to bind

variables. A setting of SIMILAR causes statements to be rewritten only if it would not cause

the statements execution plan today (which can happen if there are histogram statistics

defined on a column referenced in the WHERE clause).

Avoiding Hot Blocks

Cache buffers chains latch contention is one of the most intractable types of latch contention.

There are a couple of things you can do at the application level to reduce the severity of this

type of contention.

Firstly, identify the blocks that are “hot.” Metalink note 163424.1, “How to Identify a Hot

Block Within The Database” describes how to do this. Having identified the identity of the

hot block, you will most likely find that it is an index root or branch block. If this is the case,

there are two application design changes that may help.

1) Consider partitioning the table and using local indexes. This might allow you to

spread the heat amongst multiple indexes (you will probably want to use a hash

partition to ensure an even spread of load amongst the partitions).

2) Consider converting the table to a hash cluster keyed on the columns of the index.

This allows the index to be bypassed completely and may also result in some other

performance improvements.

Manejo de Firewall con Oracle (esta bueno)

2008-03-26T07:10:00.000-07:00

Extraido de Metalink, esta muy bien explicado.

Doc ID: Note:125021.1
Subject: Oracle Connectivity with Firewalls
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 21-NOV-2000
Last Revision Date: 24-MAY-2001

PURPOSE
-------

This will explain the Oracle connection process with firewalls
and port redirection.

SCOPE & APPLICATION
-------------------

This document is intended to explain Oracle connectivity to all
non networking users. The document will explain how the connection
works, how the port is redirected, how the connection is blocked, and
how to correct the problem.

Oracle Connection and Firewalls
-------------------------------

When the Oracle client makes a sqlplus connection to the database
(sqlplus userid/password@alias), it will compare the alias name you
supplied in the sqlplus line and look for a match in the tnsnames.ora
file or names server. Once it obtains the address for the database
server, a connetion attempt is made to the server from the client.
The listener is contacted on the database server and port redirection
can take place depending on the platform, configuration of the init.ora
file and/or the Oracle product being used. The OS will obtain a free port
from the OS and send back to the client via the listener the new port
assignment. The client will then try to connect to the database on a
new port.

A remote Oracle client making a connection to an Oracle database can
fail if there is a firewall installed between the client and the server
if there is port redirection. The firewall will block the port when
the Oracle client connects to the database and can fail with the client
receiving Oracle error ora-12203 or ora-12535. The Client connection
failure is due to port redirection from the Windows operating system.
Port redirection requires the Client to connect to the database using
a different port than originally configured in the configuration ora files.
Oracle MTS on Unix platforms, (without specifying the address with the
ports in the init ora file), Oracle SSL, and NT platforms will cause port
redirection.

A level 16 client trace file can verify if the problem is a firewall
issue. In the sqlnet.ora file on the client add the followng lines:

trace_level_client = 16
trace_file_client = client
trace_directory_client = < a valid directory and path > ie: c:\temp

Save the changes to the sqlnet.ora file and try connecting with sqlplus
to force the error. This will create the trace file. Here are several
excerts from a level 16-trace file of what to look for.

The initial packets sent to the listener on port 1521 in trace file.

niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=server1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=v815.world)
(CID=(PROGRAM=D:\V815\BIN\SQLPLUSW.EXE)(HOST=server1)(USER=system))))
nladget: entry
nladget: exit
nscall: entry
nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1521

The received packet from the listener telling the client to use 1729 port.

nscon: recving a packet
nsprecv: entry
nsbal: entry
nsbgetfl: entry
nsbgetfl: normal exit
nsmal: entry
nsmal: 44 bytes at 0xb892d0
nsmal: normal exit
nsbal: normal exit
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 232 had bytes read=64
nttrd: exit
nsprecv: 64 bytes from transport
nsprecv: tlen=64, plen=64, type=5
nsprecv: packet dump
nsprecv:00 40 00 00 05 00 00 00 |.@......|
nsprecv:00 36 28 41 44 44 52 45 |.6(ADDRE|
nsprecv:53 53 3D 28 50 52 4F 54 |SS=(PROT|
nsprecv:4F 43 4F 4C 3D 74 63 70 |OCOL=tcp|
nsprecv:29 28 48 4F 53 54 3D 31 |)(HOST=1|
nsprecv:33 38 2E 32 2E 32 31 33 |38.2.213|
nsprecv:2E 36 31 29 28 50 4F 52 |.61)(POR|
nsprecv:54 3D 31 37 32 39 29 29 |T=1729))| <- port change
nsprecv: normal exit
nscon: got NSPTRD packet
nscon: got 54 bytes connect data
nscon: exit (0)

The client resolving the connection to port 1729.

nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1729
nttbnd2addr: using host IP address: 138.2.213.61
nttbnd2addr: exit
nsc2addr: normal exit

Edit the trace file and you can see the send packets sent from the client
on port 1521 (or your port if different) to the listener. There will be
receive packets packets returned from the server to the client reflecting
a new port assignment. Then the client will send packets again from the
client only this time to a different port. The connection will then fail
at this point in the trace file.

The port that is assigned to the client is randomly chosen by the
operating system and can't be modified. It can be any free port available
that the server determines is not is use by any other software or hardware.

Once it is determined that the problem is the firewall causing the
connection to fail, the next step is to select a solution to resolve
the issue

Being that Oracle is working correctly, and the firewall is working
correctly, there are several solutions to correct the problem to
allow the clients to connect to the database.

Solution: Firewall Vendor
The first solution is to contact the firewall vendor and see if they have
an upgrade to allow for oracle connectivity with OS port redirection. If
the firewall software can be upgraded, it is the best solution to follow.

Solution: Connection Manager
The second solution is used for Oracle net8 versions and above, and
requires setting up connection manager (cman) to allow the clients to
connect through a firewall (note:2077721.6 explains cman in more
detail with configuration examples). Connection Manager is an executable
that can be run from the bin directory, which allows clients to connect
when a firewall is in place between the client and the server.
Connection Manager is similar to a listener. It reads a cman.ora file,
which contains an address that Connection Manager listens on for incoming
connections, usually default ports of 1610 or 1630. Connection Manager
starts similar to the listener and will enter a listening state. The
Oracle client needs to be running net8 or above, and will need to have
the following entered into the tnsnames.ora file.

cmantest =
(description =
(address_list =
(address = <- first address is to the cman
(protocol=tcp)
(host=hostname or ip of cman)
(port=1610)
)
(address= <- second address is to listener
(protocol=tcp)
(host=hostname or ip of listener)
(port=1521)
)
)
(connect_data = (sid = sidname))
(source_route = yes) <-This tells the client that it is
) using cman and it must take the
first two addresses listed.

When the client contacts the connection manager, cman will look in
cache for the second address the client brought with it. The second
address will point to the host machine where the listener is running.
Cman will then use that address to direct the client to the listener
and then the connection to the database will be made.

There are many documents on metalink, which will explain connection
manager and configuration in more detail. This note is to explain the
connection process with firewalls, and not to go into detail with
connection manager.

Solution: Use_Shared_Socket
A third solution for NT servers is to add the use_shared_socket = true
into the registry (see 124140.1). This will allow the OS to share port
1521 and clients will then stay on 1521 when connecting to the database
and will not be port redirected. A downfall of this option is all connetions
will stay on the listener port and if the listener is stopped or
restarted all the connections will be severed from the database.
----------------------------------------------------------------------
By default on Windows NT and Windows 2000 Port Redirection is used, but it can
be changed to Port Sharing with a registry key or environment variable.

In Windows NT port sharing can be activated by setting

USE_SHARED_SOCKET = TRUE

in the system environment (Control Panel, SYSTEM environment).

In Windows 2000 this is done also by the SYSTEM button in the control panel,
but environment variables are specified and found by clicking the "
Advanced" button.

The parameter can also be set within the WINNT registry under

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE (Releases 8.0) or
\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME<#> (Release 8i )

Both methods require a shutdown/startup of the NT server

See following document for more details:

How to enable USE_SHARED_SOCKET on WINNT and
Windows 2000
-----------------------------------------------------------------------

Unix
Unix Platforms can have problems connecting to a database through a
firewall if they have implemented Multi Threaded Server (MTS). MTS
dispatchers will redirect connection ports like NT Platform does as
mentioned above.

Solution: Setting MTS ports
A work around for this is to specify the port in the mts parameters of
the init.ora file (note:1016349.102). This will allow the dispatcher
to use the port specified and will not select a port a random. Then make
sure the port is open on the firewall. The following example will show
the ports set to 2450 and 3125. Please set these parameters according to
your individual systems. This solution will also work with NT.

Example
mts_dispatchers="(address=(protocol=tcp)(host=hostname)
(port=2450))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)(host=hostname)
(port=3125))(dispatchers=1)"

SSL
Using SSL will cause Port redirection. The work around is to select
and set the ports using MTS in the init.ora, or by setting the Ports
with Connection Manager in the cman.ora file.

RELATED DOCUMENTS
-----------------
note:1016349.102
note:66382.1
note:124140.1

Encontrar un String en la SQL AREA

2008-03-25T05:01:00.000-07:00

Este script lo aporto Cristian Mendoza, cooequiper en IBM. Con este string, ingresas la cadena que querés buscar en la sql; o sea, los sql ejecutados por alguien. Con un par de joins más se puede ver si alguna sesión apunta a determinado texto sql.

select SQL_TEXT
FROM v$sql
WHERE INSTR(UPPER(SQL_TEXT),'NOLOGGING')>0
or INSTR(UPPER(SQL_TEXT),'UNRECOVERABLE')>0;

Manejo de DEADLOCK

2008-03-25T04:24:00.000-07:00

Existen muchos casos con este error, y es como que debemos siempre buscar la parte logica de decirle al cliente que la aplicacion esta hecha de la forma correcta.

Estuve investigando un poco e hice un ejemplo, espero que sea de utilidad, como entender un deadlock. Traducido significa, "abrazo mortal" es cuando 2 o más usuarios están esperando algún dato que está siendo loqueado por alguna sesión. Si ésto sucede, los usuarios involucrados en el Deadlock deben esperar y no pueden continuar con el procesamiento. Cuando Oracle detecta que se produjo un Deadlock, lo que hace es cortar la ejecución del procedimiento y mostrar el siguiente mensaje de error: ORA-00060: deadlock detected while waiting for resource. Tengamos en cuenta que cuando se produce éste error, Oracle genera un archivo de trace en el directorio UDUMP con información acerca del error, a titulo informativo, por lo general este trace nos dice que query produjo el error y quien loqueo a quien... es aca donde le podemos decir al cliente que la arregle y vea donde esta el problema en la aplicacion, claro que esto despues de varios deadlock en la misma consulta... generalmente éste problema se produce por un mal diseño de la aplicación.

Arme un ejemplo facil y dio resultado..

SQL_9iR2> CREATE TABLE sesion_1 AS
2 SELECT level id, 'nom_'||level nombre
3 FROM dual
4 CONNECT BY level <= 10 ;

Table created.

SQL_9iR2> CREATE TABLE sesion_2 AS
2 SELECT level id, 'nom_'||level nombre
3 FROM dual
4 CONNECT BY level <= 10 ;

Table created.

En la SESION 1 loqueo un registro de la tabla SESION_1 correspondiente al ID 1.

SQL_9iR2> UPDATE sesion_1
2 SET nombre = 'nom_'||id*2
3 WHERE id = 1 ;

1 row updated.

En la SESION 2 loqueo un registro de la tabla SESION_2 correspondiente al ID 1.

SQL_9iR2> UPDATE sesion_2
2 SET id = id+10
3 WHERE id = 1 ;

1 row updated.

En la SESION 1 modifico un registro de la tabla SESION_2 correspondiente al ID 1. Vemos que esta sesión se 'colgó' debido al loqueo y no nos devuelve el control. Todavía no se produjo el Deadlock... sólo se produjo un loqueo.

SQL_9iR2> UPDATE sesion_2
2 SET id = id+10
3 WHERE id = 1 ;

En la SESION 2 modifico un registro de la tabla SESION_1 correspondiente al ID 1.
Vemos que esta sesión se 'colgó' debido al loqueo y no nos devuelve el control.

SQL_9iR2> UPDATE sesion_1
2 SET nombre = 'nom_'||id*2
3 WHERE id = 1 ;

Esto va a producir un Deadlock y luego de unos segundos aparece un mensaje de error en la SESION 1:

SQL_9iR2> UPDATE sesion_2
2 SET id = id+10
3 WHERE id = 1 ;
UPDATE sesion_2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

La SESION 2 sigue colgada esperando que la SESION 1 termine la transacción que comenzó. Entonces en la SESION 1 ejecutamos...

SQL_9iR2> rollback ;

Rollback complete.

En la SESION 2 se libera automáticamente el loqueo...

SQL_9iR2> UPDATE sesion_1
2 SET nombre = 'nom_'||id*2
3 WHERE id = 1 ;

1 row updated.