Oracle Text

alter user spe default tablespace INDEX_TEXT;

begin
CTX_DDL.CREATE_PREFERENCE ('PERFIL_DATASTORE', 'MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('PERFIL_DATASTORE', 'COLUMNS',
'ONP_DESCRIPCION,OFT_LOGROS');
end;
/
begin
CTX_DDL.CREATE_PREFERENCE ('LEXER_SINTILDES', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('LEXER_SINTILDES', 'BASE_LETTER', 'YES');
end;
/
begin
ctx_ddl.create_preference('DEMANDA_DATASTORE', 'multi_column_datastore');
ctx_ddl.set_attribute('DEMANDA_DATASTORE', 'columns',
'DEM_HABILIDADES,DEM_COMPETENCIAS,DEM_FUNCIONES_ALT');
END;
/

--CON UNA TABLA DE 1400299 REGISTROS Inicio 5:24 Fin 5:42
create index ONP_DESCRIPCION_TEXT_IDX on OFT_NOTAS_PERFIL(ONP_DESCRIPCION)
indextype is ctxsys.context
parameters('DATASTORE PERFIL_DATASTORE SYNC (ON COMMIT) LEXER LEXER_SINTILDES');



CREATE INDEX DEMANDA_TEXT_IDX ON demanda(dem_habilidades)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('DATASTORE DEMANDA_DATASTORE SYNC (ON COMMIT) LEXER LEXER_SINTILDES');

CREATE INDEX TITULO_NOMBRE_TEXT_IDX ON titulo(tit_nombre)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT) LEXER LEXER_SINTILDES');


CREATE INDEX INDICE_NOMBRE_TEXT_IDX ON indice(ind_nombre)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT) LEXER LEXER_SINTILDES')


--CON UNA TABLA DE 1445695 REGISTROS Inicio 5:44 Fin 5:47
CREATE INDEX OEX_FUNCIONES_TEXT_IDX ON OFT_EXPERIENCIA (OEX_FUNCIONES_ALT)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('SYNC (ON COMMIT) LEXER LEXER_SINTILDES');

alter user spe default tablespace USERS;


USO DE LOS INDICES EN PROCEDURE


CREATE OR REPLACE PROCEDURE "SPE"."TOKENIZER" ( iStart IN NUMBER, sPattern in VARCHAR2, sBuffer in VARCHAR2, sResult OUT VARCHAR2, iNextPos OUT NUMBER) IS
nPos1 number;
nPos2 number;
BEGIN
nPos1 := Instr (sBuffer ,sPattern ,iStart);
IF nPos1 = 0 then
sResult := NULL ;
ELSE
nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
IF nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
iNextPos := nPos2;
ELSE
sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
iNextPos := nPos2;
END IF;
END IF;
END tokenizer ;
/


CREATE OR REPLACE PACKAGE "BUSQUEDAS_PKG" IS

type lista is table of varchar2(12) index by binary_integer;

-- type lista_cno is varray(500) of varchar2(6);

palabras lista;
nro_palabras number;

type rec_oft is record(
oftId cz_oferta.oft_id%type,
porcentaje variable_cruce.vac_porcentaje%type);

type lis_oft is table of rec_oft index by binary_integer; -- lista para las ofertas que cumplen el perfil

function busquedaDemandanteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_ofertas;

function procesarBusquedaDemandante(ofertas lista_ofertas, variablesBusqueda reg_var) return lis_oft;

procedure qsort(arr in out lis_oft, lo in INTEGER, hi in INTEGER );

FUNCTION funcion_geo(par_depto cz_oferta.dpt_id_residencia%type,
par_municipio cz_oferta.mpo_id_residencia%type,
par_zona cz_oferta.zon_id_residencia%type,
par_barrio cz_oferta.bar_id_residencia%type,
oftId cz_oferta.oft_id%type) RETURN NUMBER;

FUNCTION funcion_etd(oftId oferta.oft_id%TYPE, titulo varchar2) RETURN NUMBER;

FUNCTION funcion_exp(oftId oferta.oft_id%TYPE, experiencia varchar2) RETURN NUMBER;

FUNCTION funcion_sue(oftId oferta.oft_id%TYPE, sueldo number) RETURN NUMBER;

FUNCTION funcion_pes(oftId oferta.oft_id%TYPE, programas_especiales varchar2) RETURN NUMBER;

function obtenerCaracteres(cadena varchar2) return varchar2;

function esNumero(variable_a_averiguar varchar2) return number;

function esLetra(variable_a_averiguar varchar2) return number;

function busquedaDemandanteSencilla(p_palabraClave varchar2) return lista_ofertas;

function normalizarCadena(p_palabraClave varchar2) return varchar2;

function obtenerCargoOferente(p_oferente number, p_palabraClave varchar2) return number;

END BUSQUEDAS_PKG;
/


CREATE OR REPLACE PACKAGE BODY BUSQUEDAS_PKG IS
lista_oft_busqueda cruce_pkg.lis_oft_sen;
lista_oft_id lista_ofertas := lista_ofertas(null);
ofertas_encontradas lista_ofertas;
ofertas_procesadas lis_oft;

function busquedaDemandanteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_ofertas is
v_max_reg number;
BEGIN
ofertas_encontradas := busquedaDemandanteSencilla(palabraClave);
if (ofertas_encontradas.count > 0) then
ofertas_procesadas := procesarBusquedaDemandante(ofertas_encontradas, variablesBusqueda);
if (ofertas_procesadas.count > 0) then
qsort(ofertas_procesadas, 1, ofertas_procesadas.count);
for i in ofertas_procesadas.first..ofertas_procesadas.last loop
lista_oft_id.extend;
lista_oft_id(lista_oft_id.last) := ofertas_procesadas(i).oftId;
end loop;
end if;
end if;
RETURN lista_oft_id;
END busquedaDemandanteEspecifica;

procedure qsort(arr in out lis_oft, lo in INTEGER, hi in INTEGER ) is
i INTEGER := lo;
j INTEGER := hi;
x NUMBER := arr((lo+hi)/2).porcentaje;
tmp rec_oft;
begin
LOOP
WHILE (arr(i).porcentaje < x) LOOP
i := i + 1;
END LOOP;
WHILE arr(j).porcentaje > x LOOP
j := j - 1;
END LOOP;
IF i <= j THEN
tmp := arr(i);
arr(i) := arr(j);
arr(j) := tmp;
i := i + 1;
j := j - 1;
END IF;
EXIT WHEN i > j;
END LOOP;
IF lo < j THEN
qsort(arr, lo, j);
END IF;
IF i < hi THEN
qsort(arr, i, hi);
END IF;
end qsort;

function procesarBusquedaDemandante(ofertas lista_ofertas, variablesBusqueda reg_var) return lis_oft is
par_ubicacion varchar2(5000);
par_depto number;
par_municipio number;
par_zona number;
par_barrio number;
par_estudio varchar2(100);
par_experiencia varchar2(100);
par_sueldo number;
par_pes varchar(100);
acumulado number(5,2):=0;
bandera boolean;
valor_tmp number(5,2);
cont_ubicacion number(1) := 1;
sepr varchar2(1);
sbuf clob;
sres clob;
pos number;
istart number;
strPalabra clob;
begin
for i in ofertas.first .. ofertas.last loop
bandera := TRUE;
acumulado := 0;
for b in 1 .. variablesBusqueda.count loop
if bandera = true then
CASE substr(variablesBusqueda(b),1,instr(variablesBusqueda(b),'|')-1)
WHEN 'GEO' THEN
par_ubicacion := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
sepr := ';';
sbuf := sepr || par_ubicacion;
istart := 1;
tokenizer (istart,sepr,sbuf,sres,pos);
strPalabra := upper(sres);
par_depto := to_number(strPalabra);

while (pos <> 0) loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
strPalabra := upper(sres);

case cont_ubicacion
when 1 then par_municipio := to_number(strPalabra);
cont_ubicacion:=2;
when 2 then par_zona := to_number(strPalabra);
cont_ubicacion:=3;
when 3 then par_barrio := to_number(strPalabra);
end case;
end loop;
valor_tmp := funcion_geo(par_depto, par_municipio, par_zona, par_barrio, ofertas(i));
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
/*WHEN 'ETD' THEN
par_estudio :=substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_estudio is not null then
valor_tmp := funcion_etd(ofertas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;*/
WHEN 'EXP' THEN
par_experiencia := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
par_estudio :=substr(par_experiencia,1,instr(par_experiencia,';')-1);
if par_experiencia is not null then
valor_tmp := funcion_exp(ofertas(i), par_experiencia);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
--es el caso de cuando viene el parametro ETD pero no excluyente sino encuenta exp busca estudios
if par_estudio is not null then
valor_tmp := funcion_etd(ofertas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
end if;
end if;
WHEN 'PES' THEN
par_pes := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_pes is not null then
valor_tmp := funcion_pes(ofertas(i), par_pes);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
ELSE NULL;
END CASE;
end if;
end loop;
if acumulado > 0 and bandera = true then
ofertas_procesadas(ofertas_procesadas.count+1).oftid := ofertas(i);
ofertas_procesadas(ofertas_procesadas.count+1).porcentaje := acumulado;
end if;
end loop;
return ofertas_procesadas;
end procesarBusquedaDemandante;

FUNCTION funcion_geo(par_depto cz_oferta.dpt_id_residencia%type,
par_municipio cz_oferta.mpo_id_residencia%type,
par_zona cz_oferta.zon_id_residencia%type,
par_barrio cz_oferta.bar_id_residencia%type,
oftId cz_oferta.oft_id%type) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
o_dpt_id cz_demanda.dpt_id_residencia%type;
o_mpo_id cz_demanda.mpo_id_residencia%type;
o_zon_id cz_demanda.zon_id_residencia%type;
o_bar_id cz_demanda.bar_id_residencia%type;
BEGIN
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='GEO';
if (par_municipio is null) then
select dpt_id_residencia
into o_dpt_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto) then
porcentaje := var_porcentaje;
end if;
elsif (par_zona is null) then
select dpt_id_residencia, mpo_id_residencia
into o_dpt_id, o_mpo_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio) then
porcentaje := var_porcentaje;
end if;
elsif (par_barrio is null) then
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona) then
porcentaje := var_porcentaje;
end if;
else
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia, bar_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id, o_bar_id
from cz_oferta
where oft_id = oftId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona and o_bar_id = par_barrio) then
porcentaje := var_porcentaje;
end if;
end if;
RETURN porcentaje;
END funcion_geo;

FUNCTION funcion_etd(oftId oferta.oft_id%TYPE, titulo varchar2) RETURN NUMBER IS
lista_definitiva lista_ofertas := lista_ofertas();
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
cursor c_titulos is select t.tit_id
from titulo t
where contains(tit_nombre,
'
'|| normalizarCadena(titulo) || '

transform((TOKENS, "{", "}", "AND"))
transform((TOKENS, "?{", "}", "AND"))


', 1) > 0;
--order by score(1) DESC;

TYPE cur_typ IS REF CURSOR;
c_estudios cur_typ;
v_titulos_qry varchar(32767) := '';
v_estudios_qry varchar(32767) := '';
encontro number;

begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='ETD';

for reg_titulos in c_titulos loop
v_titulos_qry := v_titulos_qry || reg_titulos.tit_id || ',';
end loop;
if (length(v_titulos_qry) > 0 ) then
v_titulos_qry := substr(v_titulos_qry,1,length(v_titulos_qry)-1) || ')';
v_titulos_qry := 'SELECT COUNT(9) FROM CZ_OFT_ETD_FORM WHERE OFT_ID = ' || oftId || ' AND TIT_ID IN (' || v_titulos_qry;
OPEN c_estudios FOR v_titulos_qry;
LOOP
FETCH c_estudios INTO encontro;
EXIT WHEN c_estudios%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_estudios;
end if;
RETURN porcentaje;
END funcion_etd;

FUNCTION funcion_exp(oftId oferta.oft_id%TYPE, experiencia varchar2) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
indice_exp varchar2(1000);
tiempo_exp number;
cursor c_experiencias is select i.ind_id
from indice i
where contains(ind_nombre,
'
'|| busquedas_pkg.normalizarCadena(indice_exp) || '

transform((TOKENS, "{", "}", "AND"))
transform((TOKENS, "?{", "}", "AND"))


', 1) > 0;
--order by score(1) DESC;

TYPE cur_typ IS REF CURSOR;
c_ofertas cur_typ;
v_experiencias_qry varchar(32767) := '';
encontro number;
begin
indice_exp := substr(experiencia, 1, instr(experiencia, ';')-1);
tiempo_exp := nvl(to_number(substr(experiencia, instr(experiencia, ';')+1, length(experiencia))),0);
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='IND';

for reg_experiencias in c_experiencias loop
v_experiencias_qry := v_experiencias_qry || reg_experiencias.ind_id || ',';
end loop;
if (length(v_experiencias_qry) > 0 ) then
v_experiencias_qry := substr(v_experiencias_qry,1,length(v_experiencias_qry)-1) || ')';
v_experiencias_qry := 'SELECT COUNT(9) FROM CZ_OFT_EXP_IND WHERE OFT_ID = ' || oftId || ' AND COE_MESES>= ' || tiempo_exp || ' AND IND_ID IN (' || v_experiencias_qry;
OPEN c_ofertas FOR v_experiencias_qry;
LOOP
FETCH c_ofertas INTO encontro;
EXIT WHEN c_ofertas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_ofertas;
end if;
RETURN porcentaje;
END funcion_exp;

FUNCTION funcion_sue(oftId oferta.oft_id%TYPE, sueldo number) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
sueldo_cumple number;
begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='SUE';
select count(9)
into sueldo_cumple
from oferta where oft_sueldo_aceptaria <= sueldo and oft_id=oftId;
if sueldo_cumple > 0 then
porcentaje := var_porcentaje;
end if;
RETURN porcentaje;
END funcion_sue;

FUNCTION funcion_pes(oftId oferta.oft_id%TYPE, programas_especiales varchar2) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
encontro number;
pes varchar2(32767);
v_pes_qry varchar(32767) := '';
TYPE cur_typ IS REF CURSOR;
c_ofertas cur_typ;
begin
pes := replace (programas_especiales, ';', ',');
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='PES';
v_pes_qry := 'SELECT COUNT(9) FROM CZ_OFTXPES WHERE OFT_ID = ' || oftId || ' AND PES_ID IN (' || pes || ')';

OPEN c_ofertas FOR v_pes_qry;
-- Fetch rows from result set one at a time:
LOOP
FETCH c_ofertas INTO encontro;
EXIT WHEN c_ofertas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_ofertas;
dbms_output.put_line(porcentaje);
RETURN porcentaje;
END funcion_pes;


function obtenerCaracteres(cadena varchar2) return varchar2 IS
retorno varchar2(32767);
len number(6);
c varchar2(1);
BEGIN
len := length(cadena);

for i in 1 .. len loop
c := substr(cadena,i,1);
if c = '+' or c = '_' or c = '-' or c = '%' then
retorno := concat(retorno,' ');
--elsif(c = ' ' || esNumero(c) = 1 || esLetra(c)) = 1 then
elsif c = ' ' or esNumero(c) = 1 or esLetra(c) = 1 then
retorno := concat(retorno,c);
end if;
end loop;
return retorno;
END obtenerCaracteres;

function esNumero(variable_a_averiguar varchar2) return number IS
esNumero NUMBER;
BEGIN
esNumero := TO_NUMBER(variable_a_averiguar);
esNumero :=1;
-- Sino es numerico entonces genera una exception
EXCEPTION
WHEN VALUE_ERROR THEN
esNumero := 0;
return esNumero;
end esNumero;

function esLetra(variable_a_averiguar varchar2) return number IS
esLetra NUMBER;
BEGIN
for i in 1..length(variable_a_averiguar) loop
if upper(substr(variable_a_averiguar,i,1))
IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','Ñ','O','P','Q','R','S','T','U','V','W','X','Y','Z') THEN
esLetra := 1;
else
esLetra := 0;
end if;
end loop;
return esLetra;
end esLetra;

function normalizarCadena(p_palabraClave varchar2) return varchar2 is
p_cadenaNormalizada varchar2(32767);
len number;
cad varchar2(1);

--
stbRetorno clob;
strPalabra clob;
incluirPrimeraVez number(1);
incluir number(1);
sepr varchar2(1);
sbuf clob;
sres clob;
pos number;
istart number;
palabrasNoIncluir lista;
BEGIN
len := length(p_palabraClave);
for i in 1 .. len loop
cad := substr(p_palabraClave,i,1);
if cad = '+' or cad = '_' or cad = '-' or cad = '%' then
p_cadenaNormalizada := concat(p_cadenaNormalizada,' ');
--elsif(c = ' ' || esNumero(c) = 1 || esLetra(c)) = 1 then
elsif cad = ' ' or esNumero(cad) = 1 or esLetra(cad) = 1 then
p_cadenaNormalizada := concat(p_cadenaNormalizada,cad);
end if;
end loop;

palabrasNoIncluir.delete;
palabrasNoIncluir(1) := 'DE';
palabrasNoIncluir(2) := 'Y';
palabrasNoIncluir(3) := '0';
palabrasNoIncluir(4) := chr(39);
palabrasNoIncluir(5) := ',';
palabrasNoIncluir(6) := 'PARA';
palabrasNoIncluir(7) := '-';

busquedas_pkg.nro_palabras := 1;
sepr := ' ';
sbuf := sepr || p_cadenaNormalizada;
istart := 1;
tokenizer (istart,sepr,sbuf,sres,pos);
strPalabra := upper(sres);
p_cadenaNormalizada := strPalabra;

while (pos <> 0) loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
strPalabra := upper(sres);

incluir := 1;
for i in palabrasNoIncluir.first..palabrasNoIncluir.last loop
if palabrasNoIncluir(i) = strPalabra then
incluir := 0;
end if;
end loop;
if incluir = 1 then
p_cadenaNormalizada := p_cadenaNormalizada || ' ' || strPalabra;
end if;
end loop;
return p_cadenaNormalizada;
end normalizarCadena;

function busquedaDemandanteSencilla(p_palabraClave varchar2) return lista_ofertas IS
lista_definitiva lista_ofertas := lista_ofertas();
v_max_reg number;
existe number;
cursor c_ofertas_per is select o.oft_id
from oft_notas_perfil o, cz_oferta cz
where contains(onp_descripcion,
'
'|| normalizarCadena(p_palabraClave) || '

transform((TOKENS, "{", "}", "AND"))


', 1) > 0
AND o.oft_id = cz.oft_id
AND rownum < v_max_reg;
--ORDER BY score(1) DESC;

cursor c_ofertas_exp is select score(1), o.oft_id
from oft_experiencia o, cz_oferta cz
WHERE contains(oex_funciones_alt,
'
' || normalizarCadena(p_palabraClave) || '

transform((TOKENS, "{", "}", "AND"))


', 1) > 0
AND o.oft_id = cz.oft_id
AND rownum < v_max_reg;
--ORDER BY score(1) DESC;
begin
select to_number(par_valor) into v_max_reg from spe.parametro where par_id=147;
for reg_oferta in c_ofertas_per loop
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_oferta.oft_id;
end loop;
if lista_definitiva.count < v_max_reg then
for reg_oferta in c_ofertas_exp loop
existe := 0;
for i in lista_definitiva.first .. lista_definitiva.last loop
if lista_definitiva(i)= reg_oferta.oft_id then
existe:=1;
end if;
end loop;
if existe = 0 and lista_definitiva.count < v_max_reg and lista_definitiva.last < lista_definitiva.limit then
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_oferta.oft_id;
end if;
end loop;
end if;
return lista_definitiva;
end busquedaDemandanteSencilla;

function obtenerCargoOferente(p_oferente number, p_palabraClave varchar2) return number is
p_indice oft_experiencia.ind_id%type := 0;
indice oft_experiencia.ind_id%type;
cursor c_indices is select i.ind_id
from indice i
where contains(ind_nombre,
'
'|| busquedas_pkg.normalizarCadena(p_palabraClave) || '

transform((TOKENS, "{", "}", "AND"))
transform((TOKENS, "?{", "}", "AND"))


', 1) > 0;
--order by score(1) DESC;

cursor c_indices_exp is select score(1), o.ind_id
from oft_experiencia o
WHERE contains(oex_funciones_alt,
'
' || normalizarCadena(p_palabraClave) || '

transform((TOKENS, "{", "}", "AND"))


', 1) > 0
AND o.oft_id = p_oferente
AND rownum = 1;
--ORDER BY score(1) DESC;

TYPE cur_typ IS REF CURSOR;
c_indice cur_typ;
v_indices_qry varchar(32767) := '';
begin
for reg_indices in c_indices loop
v_indices_qry := v_indices_qry || reg_indices.ind_id || ',';
end loop;
if (v_indices_qry <> null) then
v_indices_qry := substr(v_indices_qry,1,length(v_indices_qry)-1) || ')';
v_indices_qry := 'SELECT a.ind_id
FROM ( SELECT ind_id, coe_meses
FROM CZ_OFT_EXP_IND
WHERE OFT_ID = ' || p_oferente||
' AND IND_ID IN (' || v_indices_qry || ' ) a ' ||
'WHERE coe_meses = (SELECT MAX(coe_meses)
FROM ( SELECT ind_id, coe_meses
FROM CZ_OFT_EXP_IND
WHERE OFT_ID = ' || p_oferente||
' AND IND_ID IN (' || v_indices_qry || ' )))' ;

OPEN c_indice FOR v_indices_qry;
LOOP
FETCH c_indice INTO p_indice;
EXIT WHEN c_indice%NOTFOUND;
if p_indice <> null then
indice := p_indice;
end if;
END LOOP;
CLOSE c_indice;
end if;
if (indice is null) then
for reg_indice in c_indices_exp loop
indice := reg_indice.ind_id;
end loop;
end if;
if (indice is null) then
select ind_id
into indice
from cz_oft_exp_ind cz
where cz.oft_id= p_oferente
and coe_meses = (select max(coe_meses) from cz_oft_exp_ind where cz_oft_exp_ind.oft_ID = cz.oft_ID)
and rownum=1;
end if;
RETURN indice;
end obtenerCargoOferente;

END BUSQUEDAS_PKG;
/

CREATE OR REPLACE PACKAGE BUSQUEDAS_OFT_PKG AS

type lis_dem_sen is table of varchar2(12) index by binary_integer;

type rec_dem is record(
demId cz_demanda.dem_id%type,
porcentaje variable_cruce.vac_porcentaje%type);

type lis_dem is table of rec_dem index by binary_integer; -- lista para las ofertas que cumplen el perfil

function busquedaOferenteSencilla(p_palabraClave varchar2) return lista_demandas;

procedure qsort(arr in out lis_dem, lo in INTEGER, hi in INTEGER );

function busquedaOferenteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_demandas;

function procesarBusquedaOferente(demandas lista_demandas, variablesBusqueda reg_var) return lis_dem;

FUNCTION funcion_geo(par_depto cz_demanda.dpt_id_residencia%type,
par_municipio cz_demanda.mpo_id_residencia%type,
par_zona cz_demanda.zon_id_residencia%type,
par_barrio cz_demanda.bar_id_residencia%type,
demId cz_demanda.dem_id%type) RETURN NUMBER;

FUNCTION funcion_etd(demId demanda.dem_id%TYPE, titulo varchar2) RETURN NUMBER;

FUNCTION funcion_exp(demId demanda.dem_id%TYPE, experiencia varchar2) RETURN NUMBER;

FUNCTION funcion_sue(demId cz_demanda.dem_id%type, sueldo varchar2) RETURN NUMBER;

FUNCTION funcion_pes(demId cz_demanda.dem_id%type, programas varchar2) RETURN NUMBER;

END BUSQUEDAS_OFT_PKG;
/


CREATE OR REPLACE PACKAGE BODY BUSQUEDAS_OFT_PKG IS

lista_dem_busqueda lis_dem_sen;
lista_dem_id lista_demandas := lista_demandas(null);
demandas_encontradas lista_demandas;
demandas_procesadas lis_dem;

function busquedaOferenteSencilla(p_palabraClave varchar2) return lista_demandas IS
lista_definitiva lista_demandas := lista_demandas();
v_max_reg number;
existe number;

cursor c_demandas1 is select cz.dem_id
from indice i, cz_demanda cz
where i.ind_id = cz.ind_id and contains(i.ind_nombre,
'
'|| busquedas_pkg.normalizarCadena(p_palabraClave) || '

transform((TOKENS, "{", "}", "AND"))


', 1) > 0
AND rownum < v_max_reg;

cursor c_demandas2 is select cz.dem_id
from demanda d, cz_demanda cz
where contains(dem_habilidades,
'
'|| busquedas_pkg.normalizarCadena(p_palabraClave) || '

transform((TOKENS, "{", "}", "AND"))


', 1) > 0
AND d.dem_id = cz.dem_id
AND rownum < v_max_reg;
--ORDER BY score(1) DESC;

--transform((TOKENS, "?{", "}", "AND"))
begin
select to_number(par_valor) into v_max_reg from spe.parametro where par_id=147;
for reg_demanda in c_demandas1 loop
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_demanda.dem_id;
end loop;
--if lista_definitiva.count = 0 then
for reg_demanda2 in c_demandas2 loop
if lista_definitiva.count < v_max_reg then
lista_definitiva.extend;
lista_definitiva(lista_definitiva.last) := reg_demanda2.dem_id;
else
exit;
end if;
end loop;
dbms_output.put_line(lista_definitiva.last || ' ' || lista_definitiva.count);
return lista_definitiva;
end busquedaOferenteSencilla;


function busquedaOferenteEspecifica(variablesBusqueda reg_var, palabraClave varchar2) return lista_demandas is
v_max_reg number;
BEGIN
demandas_encontradas := busquedaOferenteSencilla(palabraClave);
if (demandas_encontradas.count > 0) then
demandas_procesadas := procesarBusquedaOferente(demandas_encontradas, variablesBusqueda);
if (demandas_procesadas.count > 0) then
qsort(demandas_procesadas, 1, demandas_procesadas.count);
for i in demandas_procesadas.first..demandas_procesadas.last loop
if length(demandas_procesadas(i).demId) > 0 then
lista_dem_id(lista_dem_id.last) := demandas_procesadas(i).demId;
lista_dem_id.extend;
end if;
end loop;
end if;
end if;
RETURN lista_dem_id;
END busquedaOferenteEspecifica;

function procesarBusquedaOferente(demandas lista_demandas, variablesBusqueda reg_var) return lis_dem is
par_ubicacion varchar2(5000);
par_depto number;
par_municipio number;
par_zona number;
par_barrio number;
par_estudio varchar2(100);
par_experiencia varchar2(100);
par_sector varchar2(100);
par_sueldo varchar2(100);
par_pes varchar(100);
acumulado number(5,2):=0;
bandera boolean;
valor_tmp number(5,2);
cont_ubicacion number(1):=1;
sepr varchar2(1);
sbuf clob;
sres clob;
pos number;
istart number;
strPalabra clob;
begin
for i in demandas.first .. demandas.last loop
bandera := TRUE;
acumulado := 0;
for b in 1 .. variablesBusqueda.count loop
if bandera = true then
CASE substr(variablesBusqueda(b),1,instr(variablesBusqueda(b),'|')-1)
WHEN 'GEO' THEN
par_ubicacion := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));

sepr := ';';
sbuf := sepr || par_ubicacion;
istart := 1;
tokenizer (istart,sepr,sbuf,sres,pos);
strPalabra := upper(sres);
par_depto := to_number(strPalabra);

while (pos <> 0) loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
strPalabra := upper(sres);
case cont_ubicacion
when 1 then par_municipio := to_number(strPalabra);
cont_ubicacion:=2;
when 2 then par_zona := to_number(strPalabra);
cont_ubicacion:=3;
when 3 then par_barrio := to_number(strPalabra);
end case;
end loop;
valor_tmp := funcion_geo(par_depto, par_municipio, par_zona, par_barrio, demandas(i));
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
/*WHEN 'ETD' THEN
par_estudio :=substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_estudio is not null then
valor_tmp := funcion_etd(demandas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;*/
WHEN 'EXP' THEN
par_experiencia := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
par_estudio :=substr(par_experiencia,1,instr(par_experiencia,';')-1);
if par_experiencia is not null then
valor_tmp := funcion_exp(demandas(i), par_experiencia);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
--es el caso de cuando viene el parametro ETD pero no excluyente sino encuenta exp busca estudios
if par_estudio is not null then
valor_tmp := funcion_etd(demandas(i), par_estudio);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
end if;
end if;
WHEN 'SEC' THEN
/* par_sector := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_sector is not null then
valor_tmp := funcion_sec(demandas(i), par_sector);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;*/
null;
WHEN 'SUE' THEN
par_sueldo := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_sueldo is not null then
valor_tmp := funcion_sue(demandas(i), par_sueldo);
if valor_tmp > 0 then
acumulado:= acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
WHEN 'PES' THEN
par_pes := substr(variablesBusqueda(b),instr(variablesBusqueda(b),'|')+1 , length(variablesBusqueda(b)));
if par_pes is not null then
valor_tmp := funcion_pes(demandas(i), par_pes);
if valor_tmp > 0 then
acumulado := acumulado + valor_tmp;
else
bandera := false;
exit;
end if;
end if;
ELSE NULL;
END CASE;
end if;
end loop;
if acumulado > 0 and bandera = true then
demandas_procesadas(demandas_procesadas.count+1).demid := demandas(i);
demandas_procesadas(demandas_procesadas.count+1).porcentaje := acumulado;
end if;
end loop;
return demandas_procesadas;
end procesarBusquedaOferente;

FUNCTION funcion_pes(demId cz_demanda.dem_id%type, programas varchar2) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
prog_especiales varchar2(5000);
TYPE cur_typ IS REF CURSOR;
c_demandas cur_typ;
v_pes_qry varchar(32767) := '';
encontro number;
BEGIN
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='PES';
prog_especiales:= replace(programas,';',',');
v_pes_qry := 'SELECT COUNT(9) FROM CZ_DEMXPES WHERE DEM_ID = ' || demId || ' AND PES_ID IN (' || prog_especiales || ')';

OPEN c_demandas FOR v_pes_qry;
LOOP
FETCH c_demandas INTO encontro;
EXIT WHEN c_demandas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_demandas;

RETURN porcentaje;
END funcion_pes;

FUNCTION funcion_geo(par_depto cz_demanda.dpt_id_residencia%type,
par_municipio cz_demanda.mpo_id_residencia%type,
par_zona cz_demanda.zon_id_residencia%type,
par_barrio cz_demanda.bar_id_residencia%type,
demId cz_demanda.dem_id%type) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
o_dpt_id cz_demanda.dpt_id_residencia%type;
o_mpo_id cz_demanda.mpo_id_residencia%type;
o_zon_id cz_demanda.zon_id_residencia%type;
o_bar_id cz_demanda.bar_id_residencia%type;
BEGIN
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='GEO';
if (par_municipio is null) then
select dpt_id_residencia
into o_dpt_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto) then
porcentaje := var_porcentaje;
end if;
elsif (par_zona is null) then
select dpt_id_residencia, mpo_id_residencia
into o_dpt_id, o_mpo_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio) then
porcentaje := var_porcentaje;
end if;
elsif (par_barrio is null) then
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona) then
porcentaje := var_porcentaje;
end if;
else
select dpt_id_residencia, mpo_id_residencia, zon_id_residencia, bar_id_residencia
into o_dpt_id, o_mpo_id, o_zon_id, o_bar_id
from cz_demanda
where dem_id = demId;
if (o_dpt_id = par_depto and o_mpo_id = par_municipio and o_zon_id = par_zona and o_bar_id = par_barrio) then
porcentaje := var_porcentaje;
end if;
end if;
RETURN porcentaje;
END funcion_geo;


FUNCTION funcion_sec(demId cz_demanda.dem_id%type, sector varchar2) RETURN NUMBER IS
porcentaje PLS_INTEGER:=0;
var_porcentaje number(5,2);
d_sec_id division_ciiu.dic_id%type;
BEGIN
/*select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='SEC';

select mpo_id_residencia into o_mpo_id
from cz_demanda
where dem_id = demId;
if par_municipio is not null then
if o_mpo_id = par_municipio then
porcentaje := var_porcentaje;
end if;
else
porcentaje := var_porcentaje;
end if;
RETURN porcentaje;*/
return null;
END funcion_sec;

FUNCTION funcion_sue(demId cz_demanda.dem_id%type, sueldo varchar2) RETURN NUMBER IS
salario_min varchar2(20);
salario_max varchar2(20);
porcentaje PLS_INTEGER:=0;
existe number;
var_porcentaje number(5,2);
begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='SUE';

salario_min := substr(sueldo, 1, instr(sueldo, ';')-1);
if (instr(salario_min,'+') > 0) then
select count(9)
into existe
from demanda d
where d.dem_id = demId and dem_sueldo_mes > salario_min;
else
salario_max := to_number(substr(sueldo, instr(sueldo, ';')+1, length(sueldo)));
select count(9)
into existe
from demanda d
where d.dem_id = demId and dem_sueldo_mes between salario_min and salario_max;
end if;

if (existe > 0) then
porcentaje:= var_porcentaje;
end if;
return porcentaje;
END funcion_sue;

FUNCTION funcion_etd(demId demanda.dem_id%TYPE, titulo varchar2) RETURN NUMBER IS
--lista_definitiva lista_demandas := lista_demandas();
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
cursor c_titulos is select t.tit_id
from titulo t
where contains(tit_nombre,
'
'|| busquedas_pkg.normalizarCadena(titulo) || '

transform((TOKENS, "{", "}", "AND"))
transform((TOKENS, "?{", "}", "AND"))


', 1) > 0;
--order by score(1) DESC;

TYPE cur_typ IS REF CURSOR;
c_estudios cur_typ;
v_titulos_qry varchar(32767) := '';
v_estudios_qry varchar(32767) := '';
encontro number;

begin
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='ETD';

for reg_titulos in c_titulos loop
v_titulos_qry := v_titulos_qry || reg_titulos.tit_id || ',';
end loop;
if (length(v_titulos_qry) > 0 ) then
v_titulos_qry := substr(v_titulos_qry,1,length(v_titulos_qry)-1) || ')';
v_titulos_qry := 'SELECT COUNT(9) FROM CZ_DEM_ETD_FORM WHERE DEM_ID = ' || demId || ' AND TIT_ID IN (' || v_titulos_qry;
OPEN c_estudios FOR v_titulos_qry;
LOOP
FETCH c_estudios INTO encontro;
EXIT WHEN c_estudios%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_estudios;
end if;
RETURN porcentaje;
END funcion_etd;

FUNCTION funcion_exp(demId demanda.dem_id%TYPE, experiencia varchar2) RETURN NUMBER IS
porcentaje number(5,2) := 0;
var_porcentaje number(5,2);
indice_exp varchar2(1000);
tiempo_exp number;
cursor c_experiencias is select i.ind_id
from indice i
where contains(ind_nombre,
'
'|| busquedas_pkg.normalizarCadena(indice_exp) || '

transform((TOKENS, "{", "}", "AND"))
transform((TOKENS, "?{", "}", "AND"))


', 1) > 0;
--order by score(1) DESC;

TYPE cur_typ IS REF CURSOR;
c_ofertas cur_typ;
v_experiencias_qry varchar(32767) := '';
encontro number;
begin
indice_exp := substr(experiencia, 1, instr(experiencia, ';')-1);
tiempo_exp := nvl(to_number(substr(experiencia, instr(experiencia, ';')+1, length(experiencia))),0);
select vac_porcentaje into var_porcentaje
from variable_cruce where vac_codigo='IND';

for reg_experiencias in c_experiencias loop
v_experiencias_qry := v_experiencias_qry || reg_experiencias.ind_id || ',';
end loop;
if (length(v_experiencias_qry) > 0) then
v_experiencias_qry := substr(v_experiencias_qry,1,length(v_experiencias_qry)-1) || ')';
v_experiencias_qry := 'SELECT COUNT(9) FROM CZ_DEMANDA WHERE DEM_ID = ' || demId || ' AND CDE_MESES_EXP <= ' || tiempo_exp || ' AND IND_ID IN (' || v_experiencias_qry;
OPEN c_ofertas FOR v_experiencias_qry;
LOOP
FETCH c_ofertas INTO encontro;
EXIT WHEN c_ofertas%NOTFOUND;
if encontro >0 then
porcentaje := var_porcentaje;
end if;
END LOOP;
CLOSE c_ofertas;
end if;

RETURN porcentaje;
END funcion_exp;

procedure qsort(arr in out lis_dem, lo in INTEGER, hi in INTEGER ) is
i INTEGER := lo;
j INTEGER := hi;
x NUMBER := arr((lo+hi)/2).porcentaje;
tmp rec_dem;
begin
LOOP
WHILE (arr(i).porcentaje < x) LOOP
i := i + 1;
END LOOP;
WHILE arr(j).porcentaje > x LOOP
j := j - 1;
END LOOP;
IF i <= j THEN
tmp := arr(i);
arr(i) := arr(j);
arr(j) := tmp;
i := i + 1;
j := j - 1;
END IF;
EXIT WHEN i > j;
END LOOP;
IF lo < j THEN
qsort(arr, lo, j);
END IF;
IF i < hi THEN
qsort(arr, i, hi);
END IF;
end qsort;

END BUSQUEDAS_OFT_PKG;
/

DBMS_SHARED_POOL package Objetos en Memoria

DBMS_SHARED_POOL

desc v$db_object_cache;

package provides access to the shared pool, which is the shared memory area where cursors and PL/SQL objects are stored. DBMS_SHARED_POOL enables you to display the sizes of objects in the shared pool, and mark them for keeping or unkeeping in order to reduce memory fragmentation.

The procedures provided here may be useful when loading large PL/SQL objects. When large PL/SQL objects are loaded, users response time is affected because of the large number of smaller objects that need to be aged out from the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects.

DBMS_SHARED_POOL is also useful for frequently executed triggers. You may want to keep compiled triggers on frequently used tables in the shared pool.

Additionally, DBMS_SHARED_POOL supports sequences. Sequence numbers are lost when a sequence is aged out of the shared pool. DBMS_SHARED_POOL is useful for keeping sequences in the shared pool and thus preventing the loss of sequence numbers.



KEEP Procedure
Keeps an object in the shared pool

SIZES Procedure
Shows objects in the shared pool that are larger than the specified size

UNKEEP Procedure
Unkeeps the named object


DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (
threshold_size NUMBER);
Parameters

Table 97-2 ABORTED_REQUEST_THRESHOLD Procedure Parameters

Parameter Description
threshold_size

Size, in bytes, of a request which does not try to free unpinned (not "unkeep-ed") memory within the shared pool. The range of threshold_size is 5000 to ~2 GB inclusive.



Usually, if a request cannot be satisfied on the free list, then the RDBMS tries to reclaim memory by freeing objects from the LRU list and checking periodically to see if the request can be fulfilled. After finishing this step, the RDBMS has performed a near equivalent of an 'ALTER SYSTEM FLUSH SHARED_POOL'.

Because this impacts all users on the system, this procedure "localizes" the impact to the process failing to find a piece of shared pool memory of size greater than thresh_hold size. This user gets the 'out of memory' error without attempting to search the LRU list.


KEEP Procedure
This procedure keeps an object in the shared pool. Once an object has been kept in the shared pool, it is not subject to aging out of the pool. This may be useful for frequently used large objects. When large objects are brought into the shared pool, several objects may need to be aged out to create a contiguous area large enough.

Syntax

DBMS_SHARED_POOL.KEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P');
Parameters


The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.

Currently, TABLE and VIEW objects may not be kept.

flag
(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.

Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.

Set to 'T' or 't' to specify that the input is the name of a type.

Set to 'R' or 'r' to specify that the input is the name of a trigger.

Set to 'Q' or 'q' to specify that the input is the name of a sequence.

In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.




For example:

DBMS_SHARED_POOL.KEEP('scott.hispackage')
This keeps package HISPACKAGE, owned by SCOTT. The names for PL/SQL objects follow SQL rules for naming objects (for example, delimited identifiers and multibyte names are allowed). A cursor can be kept by DBMS_SHARED_POOL.KEEP('0034CDFF, 20348871'). The complete hexadecimal address must be in the first 8 characters.


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

SIZES Procedure
This procedure shows objects in the shared_pool that are larger than the specified size. The name of the object is also given, which can be used as an argument to either the KEEP or UNKEEP calls.

Syntax

DBMS_SHARED_POOL.SIZES (
minsize NUMBER);
Parameters

Table 97-4 SIZES Procedure Parameters

Parameter Description
minsize
Size, in kilobytes, over which an object must be occupying in the shared pool, in order for it to be displayed.



Usage Notes

Issue the SQLDBA or SQLPLUS 'SET SERVEROUTPUT ON SIZE XXXXX' command prior to using this procedure so that the results are displayed.


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

UNKEEP Procedure
This procedure unkeeps the named object.

Syntax

DBMS_SHARED_POOL.UNKEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P');
Caution:

This procedure may not be supported in the future if automatic mechanisms are implemented to make this unnecessary.

Parameters


Parameter Description
name
Name of the object to unkeep. See description of the name object for the KEEP procedure.

flag
See description of the flag parameter for the KEEP procedure.

Exceptions

An exception is raised if the named object cannot be found.

Oracle Application Server 10G R3

omnctl status
opmnctl start
opmnctl startall
opmnctl stopall
opmnctl startproc ias-component=HTTP_Server

opmnctl status -app

Applications in Instance: as5.soaprod.us.oracle.com

application type: OC4J
------+-----------+---------+---------+----------------+----------+--------
pid | name | state | rtid | classification | routable | parent
------+-----------+---------+---------+----------------+----------+--------
11653 | system | started | g_rt_id | external | true |
11653 | default | started | g_rt_id | external | true | system
11653 | WSIL-App | started | g_rt_id | internal | true | default
11653 | ascontrol | started | g_rt_id | external | true | system
11653 | datatags | started | g_rt_id | internal | true | default
11653 | javasso | stopped | g_rt_id | internal | false |

./opmnctl status oc4j-group=default_group

Processes in Instance: as5.soaprod.us.oracle.com
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group | OC4J:home | 11653 | Alive

cat /etc/oratab

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/u01/app/oracle/infra:N
infra:/u01/app/oracle/infra:N
*:/u01/app/oracle/soa:N
soa:/u01/app/oracle/soa:N
*:/u01/app/oracle/as5:N

Para modificar el archivo con signo de admiracion antes

!clave

bajar la Consola de Enterprise Manager

/u01/app/oracle/as5/j2ee/home/config

vi system/jazn/data.xml

Para poder utilizar R2 para conectar con R3 debe tener en R2 Oracle AS Infrastrucutre Release 3 10.1.4

VI Sustituir y Reemplazar

Sustituir y Reemplazar

g/2010/s//2020/g

Laboratorio Oracle Redologs

LABORATORIO UNIX

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.group#
/

1 52428800 INACTIVE /u01/letodb/redo01.log
2 52428800 CURRENT /u01/letodb/redo02.log
3 52428800 ACTIVE /u01/letodb/redo03.log

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo01a.log ') SIZE 300M;

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo02a.log ') SIZE 300M;

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo03a.log ') SIZE 300M;


SQL> ALTER DATABASE DROP LOGFILE GROUP 1;



LABORATORIO WINDOWS

SQL> /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> ed
Escrito file afiedt.buf

1* select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.group#
SQL> /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

6 filas seleccionadas.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR en línea 1:
ORA-01624: log 1 necesario para recuperación de fallo de la instancia power (thread 1)
ORA-00312: log online 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG'


SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

Oracle Repair Table Block Detecting Corruption

Examples: Building a Repair Table or Orphan Key Table

The ADMIN_TABLE procedure is used to create, purge, or drop a repair table or an orphan key table.

A repair table provides information about the corruptions that were found by the CHECK_OBJECT procedure and how these will be addressed if the FIX_CORRUPT_BLOCKS procedure is run. Further, it is used to drive the execution of the FIX_CORRUPT_BLOCKS procedure.

An orphan key table is used when the DUMP_ORPHAN_KEYS procedure is executed and it discovers index entries that point to corrupt rows. The DUMP_ORPHAN_KEYS procedure populates the orphan key table by logging its activity and providing the index information in a usable manner.

Example: Creating a Repair Table
The following example creates a repair table for the users tablespace.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
For each repair or orphan key table, a view is also created that eliminates any rows that pertain to objects that no longer exist. The name of the view corresponds to the name of the repair or orphan key table and is prefixed by DBA_ (for example, DBA_REPAIR_TABLE or DBA_ORPHAN_KEY_TABLE).

The following query describes the repair table that was created for the users tablespace.

DESC REPAIR_TABLE

Name Null? Type
---------------------------- -------- --------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
Example: Creating an Orphan Key Table
This example illustrates the creation of an orphan key table for the users tablespace.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/
The orphan key table is described in the following query:

DESC ORPHAN_KEY_TABLE

Name Null? Type
---------------------------- -------- -----------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE

Example: Detecting Corruption
The CHECK_OBJECT procedure checks the specified object, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition name, or subpartition name when you want to check a portion of an object.

Validation consists of checking all blocks in the object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK_OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is skipped.

The following is an example of executing the CHECK_OBJECT procedure for the scott.dept table.

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
SQL*Plus outputs the following line, indicating one corruption:

number corrupt: 1
Querying the repair table produces information describing the corruption and suggesting a repair action.

SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
------------------------------------------------------------------------------
REPAIR_DESCRIPTION
------------------------------------------------------------------------------
DEPT 3 1 FALSE
kdbchk: row locked by non-existent transaction
table=0 slot=0
lockid=32 ktbbhitc=1
mark block software corrupt
The corrupted block has not yet been marked corrupt, so this is the time to extract any meaningful data. After the block is marked corrupt, the entire block must be skipped.

Example: Fixing Corrupt Blocks
Use the FIX_CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was generated by the CHECK_OBJECT procedure. Before changing a block, the block is checked to ensure that the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a timestamp.

This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME=> 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/
SQL*Plus outputs the following line:

num fix: 1
The following query confirms that the repair was done.

SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;

OBJECT_NAME BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
DEPT 3 TRUE
Example: Finding Index Entries Pointing to Corrupt Data Blocks
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows in corrupt data blocks. For each index entry, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.

This information can be useful for rebuilding lost rows in the table and for diagnostic purposes.

Note:

This should be run for every index associated with a table identified in the repair table.
In this example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index entries pointing to rows in the corrupt data block.

SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'PK_DEPT',
OBJECT_TYPE => dbms_repair.index_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
KEY_COUNT => num_orphans);
DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
END;
/
The following output indicates that there are three orphan keys:

orphan key count: 3
Index entries in the orphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same result set.

Example: Skipping Corrupt Blocks
The SKIP_CORRUPT_BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skipping applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.

The following example enables the skipping of software corrupt blocks for the scott.dept table:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
Querying scott's tables using the DBA_TABLES view shows that SKIP_CORRUPT is enabled for table scott.dept.

SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES
WHERE OWNER = 'SCOTT';

OWNER TABLE_NAME SKIP_COR
------------------------------ ------------------------------ --------
SCOTT ACCOUNT DISABLED
SCOTT BONUS DISABLED
SCOTT DEPT ENABLED
SCOTT DOCINDEX DISABLED
SCOTT EMP DISABLED
SCOTT RECEIPT DISABLED
SCOTT SALGRADE DISABLED
SCOTT SCOTT_EMP DISABLED
SCOTT SYS_IOT_OVER_12255 DISABLED
SCOTT WORK_AREA DISABLED

10 rows selected.

Rman Backup Tape Veritas

BACKUP FULL



connect catalog rman/password@cat10g;

connect target rman/password@letodb;

resync catalog;



run {

ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

ALLOCATE CHANNEL ch01

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

ALLOCATE CHANNEL ch02

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

ALLOCATE CHANNEL ch03

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

BACKUP DATABASE PLUS ARCHIVELOG FORMAT 'bk_u%u_s%s_p%p_t%t';

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

RELEASE CHANNEL ch03;

}



BACKUP INCREMENTAL LEVEL 0



connect catalog rman/password@cat10g;

connect target rman/password@letodb;

resync catalog;



run {

ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

ALLOCATE CHANNEL ch01

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

ALLOCATE CHANNEL ch02

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

ALLOCATE CHANNEL ch03

TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=SEN_CT1BOSUNBD-SPE_BD_letodb_F_DS,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Applicati

on-Backup)';

BACKUP

INCREMENTAL LEVEL=0

FORMAT 'bk_u%u_s%s_p%p_t%t'

DATABASE;

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

RELEASE CHANNEL ch03;

}



BACKUP FULL CLUSTER VERITAS



connect rcvcat rman/password@cat9i

connect target /



RUN {

ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64";

SEND 'NB_ORA_CLIENT=telbebdsdp1,NB_ORA_POLICY=BD_Online_ALTAMIRA_SDPTE01';

BACKUP

INCREMENTAL LEVEL=0

FORMAT 'db_%s_%p_%t'

TAG 'BD_SDPTE01_ALTAMIRA'

DATABASE;



RELEASE CHANNEL ch00;



# Backup Archived Logs

sql 'alter system archive log current';



ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC0;



ALLOCATE CHANNEL ch01

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC1;



ALLOCATE CHANNEL ch03

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC2;



ALLOCATE CHANNEL ch04

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC3;



BACKUP

FORMAT 'arch_%s_%p_%t'

ARCHIVELOG

ALL

DELETE INPUT;



RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

RELEASE CHANNEL ch03;





BACKUP ARCHIVELOG CLUSTER - VERITAS



connect rcvcat rman/password@cat9i

connect target /



RUN {

# Backup Archived Logs

sql 'alter system archive log current';



ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC0;



ALLOCATE CHANNEL ch01

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC1;



ALLOCATE CHANNEL ch03

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC2;



ALLOCATE CHANNEL ch04

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/password@RAC3;



BACKUP

FORMAT 'arch_%s_%p_%t'

ARCHIVELOG

ALL

DELETE INPUT;



RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

RELEASE CHANNEL ch03;



# Control file backup



ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64";

BACKUP

FORMAT 'ctrl_%s_%p_%t'

CURRENT CONTROLFILE;

RELEASE CHANNEL ch00;

}





BACKUP





connect catalog rman/rman@cat10g;

connect target rman/rman@letodb;

resync catalog;



run {

ALLOCATE CHANNEL ch00

TYPE 'SBT_TAPE' parms 'ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64,NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=CE_SEN_ct1bosunbd-spe_DB_letodb_FF_SD,NB_ORA_SERV

=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';

ALLOCATE CHANNEL ch01

TYPE 'SBT_TAPE' parms 'ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64,NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=CE_SEN_ct1bosunbd-spe_DB_letodb_FF_SD,NB_ORA_SERV

=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';

ALLOCATE CHANNEL ch02

TYPE 'SBT_TAPE' parms 'ENV=(SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64,NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=CE_SEN_ct1bosunbd-spe_DB_letodb_FF_SD,NB_ORA_SERV

=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';

# ALLOCATE CHANNEL ch03

# TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=CT1BOSUNBD-SPE1-bck,NB_ORA_POLICY=CE_SEN_ct1bosunbd-spe_DB_letodb_FF_SD,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backu

p)';

backup archivelog all delete input TAG 'BACKUP ARCHIVELOG CL LETODB';

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

RELEASE CHANNEL ch02;

#RELEASE CHANNEL ch03;

}

Oracle Full Text Indexing using Oracle Text

Full Text Indexing using Oracle Text

Oracle Text, previously know as interMedia Text and ConText, is an extensive full text indexing technology allowing you to efficiently query free text and produce document classification applications. In this article I'll only scratch the surface of this very complex feature.

CONTEXT Indexes
CTXCAT Indexes
CTXRULE Indexes
Index Maintenance
The examples in this article require access to the CTX_DDL package, which is granted as follows:

GRANT EXECUTE ON CTX_DDL TO ;

CONTEXT Indexes

The CONTEXT index type is used to index large amounts of text such as Word, PDF, XML, HTML or plain text documents. In this example we will store the data in a BLOB column, which allows us to store binary documents like Word and PDF as well as plain text. Using a CLOB is preferable if only plain text documents are being used.

First we build a sample schema to hold our data:

DROP TABLE my_docs;
DROP SEQUENCE my_docs_seq;
DROP PROCEDURE load_file_to_my_docs;

CREATE TABLE my_docs (
id NUMBER(10) NOT NULL,
name VARCHAR2(200) NOT NULL,
doc BLOB NOT NULL
)
/

ALTER TABLE my_docs ADD (
CONSTRAINT my_docs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_docs_seq;

CREATE OR REPLACE DIRECTORY documents AS 'C:\work';Next we load several files as follows:

CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN my_docs.name%TYPE) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
RETURN doc INTO v_blob;

v_bfile := BFILENAME('DOCUMENTS', p_file_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);

COMMIT;
END;
/

EXEC load_file_to_my_docs('FullTextIndexingUsingOracleText9i.doc');
EXEC load_file_to_my_docs('FullTextIndexingUsingOracleText9i.asp');
EXEC load_file_to_my_docs('XMLOverHTTP9i.asp');
EXEC load_file_to_my_docs('UNIXForDBAs.asp');
EXEC load_file_to_my_docs('emp_ws_access.sql');
EXEC load_file_to_my_docs('emp_ws_test.html');
EXEC load_file_to_my_docs('9ivsSS2000forPerformanceV22.pdf');Next we create a CONTEXT type index on the doc column and gather table statistics:

CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MY_DOCS', cascade=>TRUE);Finally we query table looking for documents with specific content:

SELECT SCORE(1) score, id, name
FROM my_docs
WHERE CONTAINS(doc, 'SQL Server', 1) > 0
ORDER BY SCORE(1) DESC;

SCORE ID NAME
---------- ---------- ------------------------------------------------
100 127 9ivsSS2000forPerformanceV22.pdf

1 row selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=58)
1 0 SORT (ORDER BY) (Cost=4 Card=2 Bytes=58)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MY_DOCS' (Cost=2 Card=2 Bytes=58)
3 2 DOMAIN INDEX OF 'MY_DOCS_DOC_IDX' (Cost=0)


SELECT SCORE(1) score, id, name
FROM my_docs
WHERE CONTAINS(doc, 'XML', 1) > 0
ORDER BY SCORE(1) DESC;

SCORE ID NAME
---------- ---------- ------------------------------------------------
74 123 XMLOverHTTP9i.asp
9 125 emp_ws_access.sql

2 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=58)
1 0 SORT (ORDER BY) (Cost=4 Card=2 Bytes=58)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MY_DOCS' (Cost=2 Card=2 Bytes=58)
3 2 DOMAIN INDEX OF 'MY_DOCS_DOC_IDX' (Cost=0)CTXCAT Indexes

The CTXCAT index type is best suited to smaller text fragments that must be indexed along with other relational data. In this example the data will be stored in a VARCHAR2 column.

First we create a schema to hold the data:

DROP TABLE my_items;
DROP SEQUENCE my_items_seq;
EXEC CTX_DDL.DROP_INDEX_SET('my_items_iset');

CREATE TABLE my_items (
id NUMBER(10) NOT NULL,
name VARCHAR2(200) NOT NULL,
description VARCHAR2(4000) NOT NULL,
price NUMBER(7,2) NOT NULL
)
/

ALTER TABLE my_items ADD (
CONSTRAINT my_items_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_items_seq;Next we populate the schema with some dummy data:

BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO my_items (id, name, description, price)
VALUES (my_items_seq.NEXTVAL, 'Bike: '||i, 'Bike Description ('||i||')', i);
END LOOP;

FOR i IN 1 .. 1000 LOOP
INSERT INTO my_items (id, name, description, price)
VALUES (my_items_seq.NEXTVAL, 'Car: '||i, 'Car Description ('||i||')', i);
END LOOP;

FOR i IN 1 .. 1000 LOOP
INSERT INTO my_items (id, name, description, price)
VALUES (my_items_seq.NEXTVAL, 'House: '||i, 'House Description ('||i||')', i);
END LOOP;

COMMIT;
END;

/Next we create a CTXCAT index on the DESCRIPTION and PRICE columns and gather table statistics. In order to create the index we must create an index-set with a sub-index for each column referenced by the CATSEARCH function:

EXEC CTX_DDL.CREATE_INDEX_SET('my_items_iset');
EXEC CTX_DDL.ADD_INDEX('my_items_iset','price');

CREATE INDEX my_items_name_idx ON my_items(description) INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('index set my_items_iset');

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MY_ITEMS', cascade=>TRUE);Finally we query table looking for items with a description that contains our specified words and an appropriate price:

SELECT id, price, name
FROM my_items
WHERE CATSEARCH(description, 'Bike', 'price BETWEEN 1 AND 5')> 0;

ID PRICE NAME
---------- ---------- ------------------------------------------------
1 1 Bike: 1
2 2 Bike: 2
3 3 Bike: 3
4 4 Bike: 4
5 5 Bike: 5

5 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=150 Bytes=6000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MY_ITEMS' (Cost=2 Card=150 Bytes=6000)
2 1 DOMAIN INDEX OF 'MY_ITEMS_NAME_IDX'


SELECT id, price, name
FROM my_items
WHERE CATSEARCH(description, 'Car', 'price BETWEEN 101 AND 105 ORDER BY price DESC')> 0;

ID PRICE NAME
---------- ---------- ------------------------------------------------
1105 105 Car: 105
1104 104 Car: 104
1103 103 Car: 103
1102 102 Car: 102
1101 101 Car: 101

5 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=150 Bytes=6000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MY_ITEMS' (Cost=2 Card=150 Bytes=6000)
2 1 DOMAIN INDEX OF 'MY_ITEMS_NAME_IDX'Every column used to restrict the selection or order the output in the CATSEARCH function should have a sub-index within the index-set. The CTXCAT index type is transactional so there is no need to synchronize the index.

CTXRULE Indexes
The CTXRULE index type can be used to build document classification applications.

First we must define our document categories and store them, along with a suitable query for the MATCHES function:

DROP TABLE my_doc_categories;
DROP TABLE my_categories;
DROP SEQUENCE my_categories_seq;
DROP TABLE my_docs;
DROP SEQUENCE my_docs_seq;

CREATE TABLE my_categories (
id NUMBER(10) NOT NULL,
category VARCHAR2(30) NOT NULL,
query VARCHAR2(2000) NOT NULL
)
/

ALTER TABLE my_categories ADD (
CONSTRAINT my_categories_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_categories_seq;

INSERT INTO my_categories VALUES(my_categories_seq.NEXTVAL, 'Oracle', 'ABOUT(Oracle)');
INSERT INTO my_categories VALUES(my_categories_seq.NEXTVAL, 'SQL Server', 'ABOUT(SQL Server)');
INSERT INTO my_categories VALUES(my_categories_seq.NEXTVAL, 'UNIX', 'ABOUT(UNIX)');Next we create a table to hold our documents:

CREATE TABLE my_docs (
id NUMBER(10) NOT NULL,
name VARCHAR2(200) NOT NULL,
doc CLOB NOT NULL
)
/

ALTER TABLE my_docs ADD (
CONSTRAINT my_docs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_docs_seq;Then we create an intersection table to resolve the many-to-many relationship between documents and categories:

CREATE TABLE my_doc_categories (
my_doc_id NUMBER(10) NOT NULL,
my_category_id NUMBER(10) NOT NULL
)
/

ALTER TABLE my_doc_categories ADD (
CONSTRAINT my_doc_categories_pk PRIMARY KEY (my_doc_id, my_category_id)
)
/Next we create a BEFORE INSERT trigger on the MY_DOCS table to automatically assign the documents to the relevant categories as they are being inserted. The MATCHES function is used to decide if the document matches any of our gategory queries. The resulting cursor is used to insert the matches into the intersect table:

CREATE OR REPLACE TRIGGER my_docs_trg
BEFORE INSERT ON my_docs
FOR EACH ROW
BEGIN
FOR c1 IN (SELECT id
FROM my_categories
WHERE MATCHES(query, :new.doc)>0)
LOOP
BEGIN
INSERT INTO my_doc_categories(my_doc_id, my_category_id)
VALUES (:new.id, c1.id);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/Next we create the CTXRULE index to support the trigger. For completeness we also create a CONTEXT index on the document itself, although this is not involved in the category assignment process:

CREATE INDEX my_categories_query_idx ON my_categories(query) INDEXTYPE IS CTXSYS.CTXRULE;
CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MY_CATEGORIES', cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MY_DOCS', cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'MY_DOC_CATEGORIES', cascade=>TRUE);Finally

we test the mechanism by inserting some rows and checking the classification:

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 'Oracle Document', 'This document constains the word Oracle!');

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 'SQL Server Document', 'This document constains the words SQL Server!');

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 'UNIX Document', 'This document constains the word UNIX!');

INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, 'Oracle UNIX Document', 'This document constains the word UNIX and the word Oracle!');


COLUMN name FORMAT A30;
SELECT a.name, b.category
FROM my_docs a,
my_categories b,
my_doc_categories c
WHERE c.my_doc_id = a.id
AND c.my_category_id = b.id;

NAME CATEGORY
------------------------------ ------------------------------
Oracle Document Oracle
SQL Server Document SQL Server
UNIX Document UNIX
Oracle UNIX Document UNIX
Oracle UNIX Document Oracle

5 rows selected.The output shows that the documents have been assigned to the correct categories. Note, the "Oracle UNIX Document" document has been assigned to both the "Oracle" and "UNIX" categories.

Index Maintenance
Not all Oracle Text indexes are automatically updated as records are added or deleted. To synchronize the index with the table you must call:

SQL> EXEC CTX_DDL.SYNC_INDEX('my_docs_doc_idx');Regular synchronizations of the index can be automated using the DBMS_JOB package. The following script is provided to make this task easier:

$ORACLE_HOME/ctx/sample/script/drjobdml.sqlIt can be called from SQL*Plus whilst logged on as the index owner as follows:

SQL> @drjobdml.sql index-name interval-mins
SQL> @drjobdml.sql my_docs_doc_idx 60Regular synchronization of text indexes can cause fragmentation which affects query performance. To correct this situation the index can be rebuilt or optimized. Index optimization can be performed in three basic modes (FAST, FULL or TOKEN). The FAST mode compacts fragmented rows but does not remove old data:

BEGIN
CTX_DDL.OPTIMIZE_INDEX('my_docs_doc_idx','FAST');
END;
/The FULL mode optimizes either the entire index or a portion of it, with old data removed:

BEGIN
CTX_DDL.OPTIMIZE_INDEX('my_docs_doc_idx','FULL');
END;
/The TOKEN mode perfoms a full optimization for a specific token:

BEGIN
CTX_DDL.OPTIMIZE_INDEX('my_docs_doc_idx','TOKEN', token=>'Oracle');
END;
/

Oracle Secrets Database

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 2 15:34:49 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SHOW PARAMETER audit syslog level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/POWER/ad
ump
audit_syslog_level string
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL> SELECT value FROM v$parameter WHERE name='audit syslog level';

no rows selected

SQL>

Yet, when executing CONNECT / AS SYSDBA, the facility and level logged in /var/adm/messages
on Solaris is “user.notice”:
Feb 21 11:45:52 dbserver Oracle Audit[27742]: [ID 441842 user.notice]
ACTION : 'CONNECT'
Feb 21 11:45:52 dbserver DATABASE USER: '/'
Feb 21 11:45:52 dbserver PRIVILEGE : SYSDBA
Feb 21 11:45:52 dbserver CLIENT USER: oracle
Feb 21 11:45:52 dbserver CLIENT TERMINAL: pts/3
Feb 21 11:45:52 dbserver STATUS: 0
If an SPFILE is used, the full setting is available by querying V$SPPARAMETER:

SQL> SELECT value FROM v$spparameter WHERE name='audit syslog level';
VALUE
-----------
user.notice

Auditing Non-Privileged Users
Of course, you may also direct audit records pertaining to non-privileged users to the system log by setting

AUDIT TRAIL=OS in addition to AUDIT SYSLOG LEVEL. Non-privileged users cannot delete audit trails logging their actions. The search for perpetrators with queries against auditing views, such as DBA AUDIT STATEMENT or DBA AUDIT OBJECT, is easier than searching the system log. For these reasons, keeping the audit trails of non-privileged users inside the database with

AUDIT TRAIL=DB is preferred. With the latter setting, audit trails are written to the table SYS.AUD$ and may be queried through the aforementioned data dictionary views. Setting AUDIT TRAIL=NONE switches off auditing of actions by non-privileged users.

SQL> AUDIT CONNECT BY appuser /* audit trail=os set */;
entries similar to the following are written to the syslog facility (example from Solaris):
Feb 21 11:41:14 dbserver Oracle Audit[27684]: [ID 930208 user.notice]
SESSIONID: "15" ENTRYID: "1" STATEMENT: "1" USERID: "APPUSER"
USERHOST: "dbserver" TERMINAL: "pts/3" ACTION: "100" RETURNCODE: "0"
COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle"
PRIV$USED: 5
Another entry is added to /var/adm/messages when a database session ends:
Feb 21 11:44:41 dbserver Oracle Audit[27684]: [ID 162490 user.notice]
SESSIONID: "15" ENTRYID: "1" ACTION: "101" RETURNCODE: "0"
LOGOFF$PREAD: "1" LOGOFF$LREAD: "17" LOGOFF$LWRITE: "0" LOGOFF$DEAD:
"0" SESSIONCPU: "2"
Note that additional data provided on the actions LOGON (100) and LOGOFF (101) conforms
to the columns of the view DBA AUDIT SESSION. Translation from action numbers to action
names is done via the view AUDIT ACTIONS as in this example:
SQL> SELECT action, name FROM audit actions WHERE action IN (100,101)
ACTION NAME
------ ------
100 LOGON
101 LOGOFF

When AUDIT SYSLOG LEVEL=AUTH.INFO, AUDIT SYS OPERATIONS=FALSE and AUDIT TRAIL=NONE,
CONNECT, STARTUP, and SHUTDOWN are logged via syslog. With these settings, an instance shutdown on Solaris writes entries similar to the following to /var/adm/messages:

Feb 21 14:40:01 dbserver Oracle Audit[29036]:[ID 63719 auth.info] ACTION:'SHUTDOWN'
Feb 21 14:40:01 dbserver DATABASE USER: '/'
Feb 21 14:40:01 dbserver PRIVILEGE : SYSDBA
Feb 21 14:40:01 dbserver CLIENT USER: oracle
Feb 21 14:40:01 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:40:01 dbserver STATUS: 0

When AUDIT SYSLOG LEVEL=AUTH.INFO, AUDIT SYS OPERATIONS=TRUE, and AUDIT TRAIL=NONE,
SQL and PL/SQL statements executed with SYSDBA or SYSOPER privileges are also logged via syslog. Dropping a user after connecting with / AS SYSDBA results in a syslog entry similar to the one shown here:

Feb 21 14:46:53 dbserver Oracle Audit[29170]: [ID 853627 auth.info]
ACTION : 'drop user appuser'
Feb 21 14:46:53 dbserver DATABASE USER: '/'
Feb 21 14:46:53 dbserver PRIVILEGE : SYSDBA
Feb 21 14:46:53 dbserver CLIENT USER: oracle
Feb 21 14:46:53 dbserver CLIENT TERMINAL: pts/3
Feb 21 14:46:53 dbserver STATUS: 0