viernes, 27 de febrero de 2009

Habilitando el I/O Asincrónico para Oracle 9iR2 en Red Hat 3/4

Habilitando el soporte asincrónico de I/O

El I/O asincrónico permite que Oracle pueda continuar procesando después de haber iniciado un llamado de I/O, aumentando así el rendimiento.

Red Hat, permite que Oracle solicite simultáneamente varias solicitudes de I/O en una sólo llamada del sistema. Esto reduce la carga de cambios de contexto y permite que el kernel sea optimizado en la actividad de disco.

Para habilitar el I/O en una base de datos Oracle, es necesario realizar un relink del motor de base de datos en Oracle9iR2.

Importante aclarar que la versión Oracle10gR2, ya trae el soporte de I/O habilitado, por tanto no es necesario realizar cambio alguno.


Recreación del kernel de Oracle9i R2 para habilitar el soporte I/O asincrónico.

Para realizar este proceso es necesario que la versión 9iR2, tenga aplicado cuando mínimo el patchset 9.2.0.4 ó superior.

Metalink Nota:279069.1.

Ejecute las siguientes instrucciones

Bajar la base de datos
SQL> shutdown
su - oracle
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk async_on
$ make -f ins_rdbms.mk ioracle

En el último punto, se va a crear un nuevo ejecutable para Oracle y el anterior va a ser renombrado a “$ORACLE_HOME/bin/oracleO”.

Si requiere revertir el proceso debe hacer lo siguiente:

Bajar la base de datos. 

SQL> shutdown
su - oracle
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk async_off

$ make -f ins_rdbms.mk ioracle

Habilitando el I/O asincrónico en Oracle 9iR2

Para habilitar el I/O asincrónico en Oracle, el parámetro disk_asynch_io necesita estar configurado al valor TRUE.

disk_asynch_io=true

En sqlplus:

alter system set disk_asynch_io=true scope=spfile;

El valor de facto en Oracle9i es TRUE.

Para permitir el I/O asincrónico en los sistemas de archivos se debe cambiar el valor de facto para el parámetro FILESYSTEMIO_OPTIONS.

El valor de facto es: NONE.

Las opciones disponibles para Oracle9iR2 son:

  • asynch: Habilita asincrónico I/O en los archivos del filesystem.
  • directio: Habilita I/O en forma directa en los archivos del filesystem.
  • setall: Habilita el I/O asincrónico y directo.
  • none: Des-habilita esta característica.

El valor recomendado para RHEL 3/4, es configurar filesystemio_options a "setall".



Creando una salida de formato html desde SQL*Plus 9i o superior


column MACHINE format a30
column IP_ADDRESS format a15
set linesize 200
SET markup HTML on
spool d:\tmp\test1.html;
select sid, serial#, username, process, machine, sys_context('userenv','ip_address') IP_ADDRESS
from v$session
where machine='MORFEO\T1_RVARGAS';
spool off;
SET markup HTML off



jueves, 26 de febrero de 2009

Desempacando archivos de instalación de software Oracle

Puede ser que hayas bajado software desde el sitio de OTN y no sabes como desempacarlo para instalarlo, sobre todo en plataformas UNIX ó Linux y el formato es de tipo "cpio.gz".

Si es así puedes hacer:

Opción 1.
  1. gunzip archivo.cpio.gz
  2. cpio -idmv < archivo.cpio
Opción 2.
  1. gunzip archivo.cpio.gz
  2. cat archivo.cpio | cpio -ivcd
Esto por lo general te va a crear un jerarquía de directorios bajo el directorio Disk1 a partir de la ubicación en donde estes corriendo el comando.

miércoles, 25 de febrero de 2009

Dónde estan los ejecutables de la instalación de Oracle XE

Si haz instalado la version gratis de Oracle XE 10g en algún servidor de sabor LINUX, los archivos ejecutables se encuentran ubicados en:
  • /usr/lib/oracle/xe/app/oracle/product/10.2.0

Obtener IP de la máquina conectada a la base de datos

SQL> column USER format a12
SQL> column IP_ADDRESS format a15
SQL> column HOST format a20
SQL> SELECT user, sys_context('userenv','ip_address') IP_ADDRESS, sys_context('userenv','host') HOST from dual;



martes, 24 de febrero de 2009

Administración de ARCHIVES para bases de datos Standby Manuales

En muchas ocasiones tenemos configurado una StandBy en modo manual y requerimos enviar los archive log de la base de producción a la base de datos de contingencia.
Esta es una manera práctica de como realizar ese proceso. Para ello necesitas agregar en el archivo /etc/hosts, la dirección IP y alias del servidor a donde quieres transferir los datos.
En este ejemplo utilizo el comando “rcp” de linux, pero pueden utilizar el utilitario de su preferencia, nada más hacen los ajustes en el script respectivo.

Como a la hora de realizar el respaldo, podría estarse llenando el último archive, vamos a generar una lista dinámica de los archives existentes en el directorio definido para la generación de los mismos y vamos a extraer los nombres de los archivos, tomando en cuenta para ello un ordenamiento basado en la hora y fecha de creación.
Así, si aplicamos el concepto del conteo de archivos ordenados por fecha y hora de creación menos uno, vamos a eliminar de la lista de archivos a transferir al último.
En el script “MORFEO” es el alias del servidor a donde vamos a copiar los archivos de origen.

copy_archives.sh
#!/bin/ksh
# Autor: Ronald Vargas Quesada
# Fecha: 2005 – 2009
# ronald.vargas.quesada@gmail.com
ARCH_DIR=MORFEO:/oracle/archive/MORFEO
COUNTER=`rsh MORFEO ls -la /oracle/archive/MORFEOR/*.arc wc -l`
MENOS=1
COUNT=`expr $COUNTER - $MENOS`
echo "Cantidad de archivos existentes en el directorio $ARCH_DIR files:$COUNTER"
echo "Archivos por copiar $COUNT"
if [ "$COUNT" > 1 ]; then
rsh MORFEO ls -lt /oracle/archive/MORFEO/*.arc tail -$COUNT awk '{print $9}' >copies_archive.lst
fi
/home/oracle/scripts/ejecutar_copia.sh

En este archivo se crea en forma dinámica el archivo de “comando.sh” que al final es quién copia los archivos al servidor de destino.

También definidos el comando “empacar.sh”, para dejar comprimidos los archivos hasta que sean reutilizados y así poder contar con mayor espacio en el servidor de destino.

ejecutar_copia.sh
# Autor: Ronald Vargas Quesada
# Fecha: 2005 – 2009
# ronald.vargas.quesada@gmail.com
FILES="/home/oracle/scripts/copies_archive.lst"
for lista in $FILES
do
cat $FILES awk '{ print "rcp MORFEO:" $1 " /oracle/archive/MORFEO" }' > /home/oracle/scripts/comando.sh
done
chmod 700 /home/oracle/scripts/comando.sh
/home/oracle/scripts/comando.sh
rm /home/oracle/scripts/comando.sh
cat $FILES awk '{ print "rsh morfeo gzip " $1}' > /home/oracle/scripts/empacar.sh
chmod 700 /home/oracle/scripts/empacar.sh
/home/oracle/scripts/empacar.sh
rm /home/oracle/scripts/empacar.sh

Analizando y estableciendo los índices que deben ser recreados


REM
REM rebuild_indx.sql
REM
REM Copyright (c) Oracle Software, 1998 - 2000
REM
REM Author : Jurgen Schelfhout
REM MODIFICIACION: Ronald Vargas Quesada 2006-2009
REM
REM This script will analyze all the indexes for a given schema
REM or for a subset of schema's. After this the dynamic view
REM index_stats is consulted to see if an index is a good
REM candidate for a rebuild or for a bitmap index.
REM
REM Database Version : 9iR1, 9iR2,10g
REM
REM =============================================================
prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt - deleted entries represent 20% or more of the current entries
prompt - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt - when distinctiveness is more than 99%
prompt
set linesize 1000
set serveroutput on
set verify off
declare
c_name INTEGER;
ignore INTEGER;
height index_stats.height%TYPE := 0;
lf_rows index_stats.lf_rows%TYPE := 0;
del_lf_rows index_stats.del_lf_rows%TYPE := 0;
distinct_keys index_stats.distinct_keys%TYPE := 0;
cursor c_indx is
select owner, table_name, index_name
from dba_indexes
where owner like upper('&schema');
begin
dbms_output.enable (1000000);
dbms_output.put_line ('Owner Index Name % Deleted Entries Blevel Distinctiveness');
dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------
---------------');
c_name := DBMS_SQL.OPEN_CURSOR;
for r_indx in c_indx loop
DBMS_SQL.PARSE(c_name,'analyze index ' r_indx.owner '.'
r_indx.index_name ' validate structure',DBMS_SQL.NATIVE);
ignore := DBMS_SQL.EXECUTE(c_name);
select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
into height, lf_rows, del_lf_rows, distinct_keys
from index_stats;
--
-- Index is considered as candidate for rebuild when :
-- - when deleted entries represent 20% or more of the current entries
-- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%
--
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
dbms_output.put_line (rpad(r_indx.owner,16,' ') rpad(r_indx.index_name,40,' ')
lpad(round((del_lf_rows/lf_rows)*100,3),17,' ')
lpad(height-1,7,' ') lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
end if;
end loop;
DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
set verify on

Menú aplicativo shell linux para tareas administrativas

El siguiente script-shell, puede ser utilizado para automatizar tareas a nivel de usuarios operadores en servidores LINUX ó Unix.

# Menu administrativo para labores de base de datos Oracle
# Diciembre 2009, Ronald Vargas, LINUX SHELL SCRIPT
# Version 2.0_2009
 
#VARIABLES DE AMBIENTE DE ORACLE
ORACLE_BASE=/oracle
ORACLE_HOME=$ORACLE_BASE/product/9.2.0
PATH=$ORACLE_HOME/bin:$PATH
 
opcion_no_disponible () {
echo "Opcion no disponible"
}
menu_miscelaneo() {
menu_misc=
until [ "$op" = "0" ]; do
clear
echo " "
echo "======================================================================"
echo -e "\033[1m MORFEO INTERNACIONAL S.A.- Fecha: $(date) \033[0m"
tput sgr0
echo "======================================================================"
echo -e '\E[47;31m'
echo " MENU APLICATIVO VENTAS "
echo -e '\E[37;44m'
echo " a - Salir del menu "
echo " b - Actualizar Tipo de Cambio "
echo " c - Actualizar Ofertas "
echo " d - Actualizar Existencias "
echo -e " f - Actualizar Traslados \033[0m"
tput sgr0
echo "======================================================================"
echo -n " Opcion: "
read op
echo " "
case $op in
a ) echo "Saliendo del Menu de Ventas"; break;;
a ) lsnrctl status; pausa;;
b ) lsnrctl status; pausa;;
c ) lsnrctl status; pausa;;
d ) lsnrctl status; pausa;;
esac
done
}
clear
pausa() {
echo "Presione ENTER para continuar"
read a
}
menu_principal() {
selection=
until [ "$selection" = "0" ]; do
clear
echo " "
echo "======================================================================"
echo -e "\033[1m MORFEO INTERNACIONAL S.A.- Fecha: $(date) \033[0m"
tput sgr0
echo "======================================================================"
echo -e '\E[47;31m'
echo " MENU APLICATIVO V1.0_2007 "
echo -e '\E[37;44m'
echo " 0 - Salir del menu "
echo " 1 - Ver_estado_LISTENER "
echo " 2 - Ver_Bases_de_Datos_Levantadas "
echo " 3 - Subir_LISTENER "
echo " 4 - Bajar_LISTENER "
echo " 5 - Bajar_BD "
echo " 6 - SuBir_BD "
echo " 7 - Generar_EXPORT "
echo " 8 - VER_espacio_disco "
echo " 9 - MONITOREAR_equipo "
echo "10 - VER_Crontab_Oracle "
echo -e " a - Miscelaneos \033[0m"
tput sgr0
echo "======================================================================"
echo -n " Opcion: "
read selection
echo " "
case $selection in
1 ) lsnrctl status; pausa;;
2 ) $HOME/scripts/Menu/db_monitor.sh; pausa;;
3 ) lsnrctl start; pausa;;
4 ) lsnrctl stop; pausa;;
5 ) $HOME/scripts/Menu/bajar_bd; pausa;;
6 ) $HOME/scripts/Menu/subir_bd; pausa;;
7 ) opcion_no_disponible; pausa;;
8 ) df -h; pausa;;
9 ) top; pausa;;
10 ) crontab -l; pausa;;
a ) menu_miscelaneo; pausa;;
* ) clear; echo "Derechos Ronald Vargas Q., 2009"; break ;;
esac
done
}
menu_principal


Presentación visual

lunes, 23 de febrero de 2009

Registro Histórico de la utilización de los tablespaces de la base de datos

Este procedimiento debe ser definido dentro del esquema del usuario SYSTEM.

CREATE OR REPLACE PROCEDURE SYSTEM.REGISTRA_CRECIMIENTO_BD AS
BEGIN
INSERT INTO CRECIMIENTO_BD
SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)", SYSDATE
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f, DUAL
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;
COMMIT;
END;
/
Definición del job que se ejecutará automáticamente para tomar la información.

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'SYSTEM.REGISTRA_CRECIMIENTO_BD;'
,next_date => to_date('24/02/2009 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' to_char(x));
COMMIT;
END;
/

A la hora de consultar la tabla de la base de datos, podríamos observar algo así:

Manejo de exports históricos de la base de datos

El dispositivo /media/KINGSTON es una llave maya colocada en el server, para mantener una copia de respaldo de emergencia, fuera de la que queda en el almacenamiento interno del servidor.

-------resp_full.sh cortar aquí ----------------------
mv /oradata/dmp/*.gz /oradata/dmp/historicos/
ORACLE_HOME=/opt/app/product/9.2
PATH=$PATH:$ORACLE_HOME/bin
FECHA=`date +%d%m%Y%k%m%H`
export FECHA ORACLE_HOME PATH
$ORACLE_HOME/bin/exp parfile=$HOME/scripts/exp.par
gzip /oradata/dmp/*.dmp
rm /media/KINGSTON/historico/*.gz
mv /media/KINGSTON/*.gz /media/KINGSTON/historico/
cp /oradata/dmp/*.gz /media/KINGSTON/eva_$FECHA.dmp.gz

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

Crontab -l

# Crontab de procesos del usuario ORACLE
# Ronald Vargas Q,
# 23 Febrero 2009
# Full Export de la base de datos
52 12 * * * su - oracle -c "/home/oracle/scripts/resp_full.sh"
52 22 * * * su - oracle -c "/home/oracle/scripts/resp_full.sh"
52 05 * * * su - oracle -c "/home/oracle/scripts/resp_full.sh"
00 * * * * /usr/sbin/ntpdate -u 172.20.1.12


[oracle@eva dmp]$ ls -la
total 130552
drwxr-xr-x 3 oracle oinstall 4096 Feb 23 12:56 .
drwxr-xr-x 6 oracle oinstall 4096 Nov 16 2006 ..
-rw-r--r-- 1 oracle oinstall 133511952 Feb 23 12:55 diario_eva_01_23022009120212.dmp.gz
drwxr-xr-x 2 oracle oinstall 28672 Feb 23 12:52 historicos

[oracle@eva dmp]$


Validación de Falso ó Verdadero de una manera inteligente

Set serveroutput on size 10000
SQL> 1 declare
variable boolean ;
v2 varchar2(2) :='S';
v4 varchar2(2);
v3 varchar2(10);
begin
v4 :='SI';
variable := variable
OR (v2='N')
OR (v4='NO');
IF variable = TRUE THEN v3:='TRUE'; ELSE v3:='FALSE'; END IF;
dbms_output.put_line(v3);
end;

SQL> /

FALSE
PL/SQL procedure successfully completed.

declare
variable boolean ;
v2 varchar2(2) :='N';
v4 varchar2(2);
v3 varchar2(10);
begin
v4 :='SI';
variable := variable
OR (v2='N')
OR (v4='NO');
IF variable = TRUE THEN v3:='TRUE'; ELSE v3:='FALSE'; END IF;
dbms_output.put_line(v3);
end;
/

TRUE

PL/SQL procedure successfully completed.

Obtener un número aleatorio en un rango de números

select round(dbms_random.value(1,1000000)) from dual
/

Obtener código fuente de triggers de un esquema del diccionario de la base de datos


set pagesize 200
set linesize 220
drop table temp_validacion;
create global temporary table temp_validacion( table_owner varchar2(30),
table_name varchar2(30), trigger_name varchar2(30), data varchar2(4000));
declare
cursor my_cursor is
select table_owner, table_name, trigger_name, trigger_body
from dba_triggers
where table_owner='owner';
V_OWNER VARCHAR2(30);
V_TABLE_NAME VARCHAR2(30);
V_COLUMN_NAME VARCHAR2(30);
my_var varchar2(32000);
begin
open my_cursor;
loop
fetch my_cursor into V_OWNER, V_TABLE_NAME, V_COLUMN_NAME,my_var;
exit when my_cursor%notfound;
my_var := substr(my_var,1,4000);
insert into temp_validacion values (V_OWNER, V_TABLE_NAME, V_COLUMN_NAME, my_var);
end loop;
close my_cursor;
end;
/

Leer valores de campos tipo LONG en una tabla


create table valores (
Tabla varchar2(80),
campo varchar2(80),
valor_de_facto varchar2(2000))
/
declare
cursor my_cursor is
select table_name, column_name, data_default
from all_tab_columns
where owner='owner' and data_default is not null;
my_var varchar2(2000);
tabla varchar2(80);
columna varchar2(80);
begin
open my_cursor;
loop
fetch my_cursor into tabla, columna, my_var;
exit when my_cursor%notfound;
my_var := substr(my_var,1,2000);
insert into valores values (tabla, columna, my_var);
end loop;
close my_cursor;
end;
/

Información general sobre una tabla de un esquema específico

------------------------------------Cortar aqui --------------------------------------

SET ECHO OFF
accept table_name prompt "Ingrese el nombre de la tabla :"
accept owner_name prompt "Ingrese el dueño del esquema para la tabla :"
set linesize 220
set heading on
set verify on
set newpage 0
ttitle 'Table Description - Space Definition'
spool tfstbdsc.lst
btitle off
column nline newline
set pagesize 80
set heading off
set embedded off
set verify off
accept report_comment char prompt 'Enter a comment to identify system: '
select 'Date - 'to_char(sysdate,'Day Ddth Month YYYY HH24:MI:SS'),
'At - ''&&report_comment' nline,
'Username - 'USER nline
from sys.dual
/
prompt
set embedded on
set heading on
set verify off
column ts format a30
column ta format a30
column clu format a30
column pcf format 99999999999990
column pcu format 99999999999990
column int format 99,999,999,990
column mat format 99,999,999,990
column inx format 99,999,999,990
column nxt format 99,999,999,990
column mix format 99,999,999,990
column max format 99,999,999,990
column pci format 99999999999990
column num format 99,999,999,990
column blo format 99,999,999,990
column emp format 99,999,999,990
column avg format 99,999,999,990
column cha format 99,999,999,990
column rln format 99,999,999,990
column hdg format a30 newline
set heading off
select 'Table Name' hdg, TABLE_NAME ta,
'Tablespace Name' hdg, TABLESPACE_NAME ts,
'Cluster Name' hdg, CLUSTER_NAME clu,
'% Free' hdg, PCT_FREE pcf,
'% Used' hdg, PCT_USED pcu,
'Ini Trans' hdg, INI_TRANS int,
'Max Trans' hdg, MAX_TRANS mat,
'Initial Extent (K)' hdg, INITIAL_EXTENT/1024 inx,
'Next Extent (K)' hdg, NEXT_EXTENT/1024 nxt,
'Min Extents' hdg, MIN_EXTENTS mix,
'Max Extents' hdg, MAX_EXTENTS max,
'% Increase' hdg, PCT_INCREASE pci,
'Number of Rows' hdg, NUM_ROWS num,
'Number of Blocks' hdg, BLOCKS blo,
'Number of Empty Blocks' hdg, EMPTY_BLOCKS emp,
'Average Space' hdg, AVG_SPACE avg,
'Chain Count' hdg, CHAIN_CNT cha,
'Average Row Length' hdg, AVG_ROW_LEN rln
from all_tables
where TABLE_NAME=UPPER('&&owner_name.&&table_name')
/
set heading on
set embedded off
column Default_Value format a25
column cn format a30 heading 'Column Name'
column fo format a15 heading 'Type'
column nu format a8 heading 'Null'
column nds format 99,999,999 heading 'No Distinct'
column dfl format 9999 heading 'Dflt Len'
column dfv format a40 heading 'Default Value'
ttitle 'Table Description - Column Definition'
select COLUMN_NAME cn,
DATA_TYPE
decode(DATA_TYPE,
'NUMBER',
'('to_char(DATA_PRECISION)
decode(DATA_SCALE,0,'',','to_char(DATA_SCALE))')',
'VARCHAR2',
'('to_char(DATA_LENGTH)')',
'DATE','',
'Error') fo,
decode(NULLABLE,'Y','','NOT NULL') nu,
NUM_DISTINCT nds,
DEFAULT_LENGTH dfl,
DATA_DEFAULT dfv
FROM all_tab_columns
where TABLE_NAME=UPPER('&&owner_name.&&table_name')
order by COLUMN_ID
/
ttitle 'Table Constraints'
set heading on
set verify off
column cn format a30 heading 'Primary Key'
column cln format a45 heading 'Table.Column Name'
column ct format a7 heading 'Type'
column st format a7 heading 'Status'
column ro format a30 heading 'Ref OwnerConstraint Name'
column se format a70 heading 'Criteria ' newline
break on cn on st
set embedded on
prompt Primary Key
prompt
select cns.CONSTRAINT_NAME cn,
cns.TABLE_NAME'.'cls.COLUMN_NAME cln,
initcap(cns.STATUS) st
from all_constraints cns,
all_cons_columns cls
where cns.table_name=upper('&&owner_name.&&table_name')
and cns.owner=user
and cns.CONSTRAINT_TYPE='P'
and cns.constraint_name=cls.constraint_name
order by cls.position
/
prompt Unique Key
prompt
column cn format a30 heading 'Unique Key'
select cns.CONSTRAINT_NAME cn,
cns.TABLE_NAME'.'cls.COLUMN_NAME cln,
initcap(cns.STATUS) st
from all_constraints cns,
all_cons_columns cls
where cns.table_name=upper('&&owner_name.&&table_name')
and cns.owner=user
and cns.CONSTRAINT_TYPE='U'
and cns.constraint_name=cls.constraint_name
order by cls.position
/
column cln format a38 heading 'Foreign Key' newline
column clfn format a38 heading 'Parent Key'
break on cn on st skip 1
prompt Foreign Keys
prompt
select cns.CONSTRAINT_NAME cn,
initcap(STATUS) st,
cls.TABLE_NAME'.'cls.COLUMN_NAME cln,
clf.OWNER'.'clf.TABLE_NAME'.'clf.COLUMN_NAME clfn
from all_constraints cns,
all_cons_columns clf ,
all_cons_columns cls
where cns.table_name=upper('&&owner_name.&&table_name')
and cns.owner=user
and cns.CONSTRAINT_TYPE='R'
and cns.constraint_name=cls.constraint_name
and clf.CONSTRAINT_NAME = cns.R_CONSTRAINT_NAME
and clf.OWNER = cns.OWNER
and clf.POSITION = clf.POSITION
order by cns.CONSTRAINT_NAME, cls.position
/
prompt Check Constraints
prompt
column se format a75 heading 'Criteria'
set arraysize 1
set long 32000
select CONSTRAINT_NAME cn,
initcap(STATUS) st,
SEARCH_CONDITION se
from all_constraints
where table_name=upper('&&owner_name.&&table_name')
and owner=user
and CONSTRAINT_TYPE='C'
/
prompt View Constraints
select CONSTRAINT_NAME cn,
initcap(STATUS) st,
SEARCH_CONDITION se
from all_constraints
where table_name=upper('&&owner_name.&&table_name')
and owner=user
and CONSTRAINT_TYPE='V'
/
spool off
btitle off
ttitle off
clear breaks
clear columns
clear computes
set verify on
set long 80

Borrado eficiente de muchos registros en una tabla

CREATE PROCEDURE DeleteMasivo( NombreTabla IN VARCHAR2,
CondicionWhere IN VARCHAR2 DEFAULT NULL,
RegistrosCommit IN NUMBER DEFAULT 1000 ) IS
idSelect INTEGER;
idDelete INTEGER;
execSelect INTEGER;
execDelete INTEGER;
SQLCursor VARCHAR2(2000);
ROWIDDelete ROWID;
BEGIN
--
-- Se prepara un cursor dinánico para seleccionar los ROWIDs
-- de los registros a borrar.
--
SQLCursor := 'SELECT rowid FROM ' NombreTabla ' ' CondicionWhere;
idSelect := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(idSelect, SQLCursor, DBMS_SQL.v7);
DBMS_SQL.DEFINE_COLUMN_ROWID(idSelect, 1, ROWIDDelete);
execSelect := DBMS_SQL.EXECUTE(idSelect);
--
-- Se prepara el cursor para borrar los registros
--
idDelete := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(idDelete, 'DELETE FROM ' NombreTabla
' WHERE ROWID = :rowid_delete', DBMS_SQL.v7);
LOOP
IF DBMS_SQL.FETCH_ROWS(idSelect) = 0 THEN
--
-- Obtiene los ROWIDs de las filas a borrar
--
DBMS_SQL.COLUMN_VALUE(idSelect, 1, ROWIDDelete);
DBMS_SQL.BIND_VARIABLE(idSelect, 'rowid_delete', ROWIDDelete);
--
-- Ejecuta el delete para las filas obtenidas
---
execDelete := DBMS_SQL.EXECUTE(idDelete);
--
-- Hace un commit cada n filas.
-- n se especifica en el parámetro "RegistrosCommit"
--
IF MOD(DBMS_SQL.LAST_ROW_COUNT, RegistrosCommit) = 0 THEN
COMMIT;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
--
-- Hace commit otra vez, para validar el ultimo grupo de registros
--
COMMIT;
DBMS_SQL.CLOSE_CURSOR(idSelect);
DBMS_SQL.CLOSE_CURSOR(idDelete);
END DeleteMasivo;

Sitios de interés

Grupo de Usuarios de Oracle Colombia
www.asuoc.org.co

Oracle Technology Network
otn.oracle.com

Manuales en línea de Productos Oracle
http://tahiti.oracle.com/

Sitio de scripts y soporte
http://www.dbasupport.com/

Oracle Faq
http://www.oraclefaq.net/

Blog para Coordinación Grupo de Usuarios Oracle para Costa Rica
http://ugoraclecr.blogspot.com/

Servidores Virtuales en Internet - Recomendando --
http://eyesos.org/
Afinamiento de Tablas con Excesivos Borrados
Utilizando Oracle 10G
Ronald Fco. Vargas Quesada


Tip: Manejo de la “Marca de Agua” en tablas con muchos borrados en 10G.

REFERENCIAS:

Metalink Notes:
242736.1 10G New Features, On Segment Advisor 17/12/2004
242090.1 10G New Features, Segment Shrink
115586.1 How to deallocate unused space from table, index or cluster.
Bug 3516451.8 13/01/2005
Oracle Database 10G: New Features, Robert G. Freeman, OSBORNE
Capítulo 2, páginas 37 y 38, Shrinking and Compacting Segments Online.

En la mayoría de los casos, encontramos tablas que son utilizadas dentro de procesos de cierre de mes ó de pases a históricos, en las cuáles se efectúan borrados parciales de los registros de la tabla.


Cuando en un proceso se borran registros de forma parcial, la “Marca de Agua” no es reseteada, por tanto, los nuevos registros son insertados en bloques que se encuentran posteriores a esta y no en los bloques de los segmentos que fueron liberados con la acción del borrado.
Al pasar el tiempo, los procesos que involucran tablas con estas características especiales de insercciones y borrados, empiezan a experimentar problemas de rendimiento.


La mayoría de las veces, procedemos a recrear la tabla con nuevos parámetros de almacenamiento, para resetear la “Marca de Agua”, ó bien optamos por exportar e importar el objeto afectado.



Afortunamente, este proceso ahora, puede ser realizado de una manera más ágil, utilizando la nueva opción de “SHRINK” en el comando “ALTER TABLE”, que permite compactar los segmentos de un objeto y ajustar la “Marca de Agua” al mismo tiempo, eso si, aplicando ciertas restricciones.



CONECTÁNDONOS A LA BASE DE DATOS


Para probar esto, utilizaremos una base de datos 10G R1, versión 10.1.0.2.0 en un LINUX Red
Hat 3.0 AS.



Logee en la instancia con el usuario “hr” y el password “hr”. Recuerde que en 10G, la mayoría de
usuarios creados durante el proceso de instalación de la base de datos, excepto “SYSTEM y SYS”,quedan bloqueados de facto. Así que antes de hacer la prueba, desbloquee el usuario.





SQL> connect hr/hr
Connected.
SQL> desc employees
Name Null? Type
--------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)



CREACIÓN DE OBJETOS DE PRUEBA


Basándonos en la tabla “employee”, vamos a crear la tabla “test”, para simular el efecto de borrado de los registros.


SQL> create table test as select * from employees;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
107



Ahora insertemos algunos registros adicionales.


SQL> ed
1 declare
2 begin
3 for i in 1..10000 loop
4 insert into test select * from employees;
5 end loop;
6* end;


SQL> /
PL/SQL procedure successfully completed.


SQL> select count(*) from test;
COUNT(*)
----------
1070107


Determinando el espacio ocupado por el objeto
Ahora determinemos cuál es el espacio ocupado por la tabla “TEST” en la base de datos, utilizando el siguiente script:



set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space('HR','TEST','TABLE',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('OBJECT_NAME = TEST');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = 'TOTAL_BLOCKS);
dbms_output.put_line('TOTAL_BYTES = 'TOTAL_BYTES);
dbms_output.put_line('UNUSED_BLOCKS = 'UNUSED_BLOCKS);
dbms_output.put_line('UNUSED BYTES = 'UNUSED_BYTES);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = 'LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = 'LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = 'LAST_USED_BLOCK);
end;
/
Al ejecutar el script en la base de datos, obtenemos la siguiente información:
SQL> set serveroutput on
SQL> set linesize 120
SQL> /



OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768



PL/SQL procedure successfully completed.





BORRANDO REGISTROS EN LA TABLA DE PRUEBA

Si realizamos un borrado de aquellos registros cuyo "rownum <> "

SQL> delete from test
2 where rownum <> commit;
Commit complete.

SQL> select count(*) from test;
COUNT(*)
----------
220108
SQL> /
OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768


PL/SQL procedure successfully completed.


Como se puede observar la cantidad de bloques ocupados por la tabla sigue siendo igual, a la cantidad de bloques que teníamos previo al borrado. Insertemos nuevos registros en la tabla.

SQL> declare
2 begin
3 for i in 1..10 loop
4 insert into test select * from employees;
5 end loop;
6 end;
7 /


PL/SQL procedure successfully completed.

SQL> select count(*) from test;
COUNT(*)
----------
221178


Al verificar los bloques utilizados por la tabla “TEST”, posterior a la nueva insercción, encontramos que la cantidad de bloques ocupados sigue siendo la misma. Volvamos a repetir el proceso de inserción, pero esta vez, vamos a insertar más cantidad de registros.

OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768
PL/SQL procedure successfully completed.



Esta vez, vamos a repetir el ciclo de inserción en 1000 veces, para insertar 100000 registros
apróximadamente.


SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into test select * from employees;
5 end loop;
6 end;
7 /


PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 11264
TOTAL_BYTES = 92274688
UNUSED_BLOCKS = 256
UNUSED BYTES = 2097152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2184
LAST_USED_BLOCK = 768
PL/SQL procedure successfully completed.
SQL> select count(*) from test;
COUNT(*)
----------
328178


Como se observa el objeto no creció, pero ahora tenemos 328178 en 92MB ocupados por la tabla y según nuestros cálculos, los 328178 registros, deberían ocupar apróximadamente una tercera parte de este espacio.


APLICANDO LA NUEVA CARACTERÍSTICA DE “SHRINK”.


Primero que todo, debemos habilitar la posibilidad de mover registros entre bloques del objeto,al igual que lo hacemos, cuando queremos reparar registros encadenados en una tabla, con la siguiente instrucción:


SQL> alter table test enable row movement;
Table altered.


Ahora procedamos a ejecutar el “SHRINK” de la tabla.

SQL> alter table test shrink space cascade;
Table altered.

Al ejecutar nuevamente el script para obtener los bloques utilizados por el tabla, obtenemos:

OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 3160
TOTAL_BYTES = 25886720
UNUSED_BLOCKS = 0
UNUSED BYTES = 0
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 6280
LAST_USED_BLOCK = 88


PL/SQL procedure successfully completed.


Ahora podemos observar cómo el tamaño de bloques que componen el objeto a disminuído de
11264 a 3160, y por supuesto su tamaño también a cambiado de 92MB a 26MB.



Optimización Basada en Costo ( CBO )
Ahora veamos esto a nivel de rendimiento. Borremos la tabla y volvamos a crearla con un índice sobre los campos “employee_id, first_name y last_name”.



SQL> create index PK_test_IDX01 on test(employee_id, first_name, last_name);
Index created.
SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into test select * from employees;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.


Procedemos a fijar los nuevos registros en la tabla de pruebas.

SQL> SQL> commit;
Commit complete.


Verificamos la cantidad de registros existentes en la tabla “TEST”.


SQL> select count(*) from test;
COUNT(*)
----------
1070107


Obtengamos el plan de ejecución de la siguiente instrucción sobre la tabla “TEST” utilizando el índice que hemos creado previamente.


SQL> explain plan for
2 select employee_id, first_name, last_name, job_id, salary from test
3 where employee_id=100;


Ahora desplegamos el contenido del “Explain Plan”.


SQL> select * from table ( dbms_xplan.display);


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2321975210
---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 10347 596K 71 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 10347 596K 71 (0) 00:00:01
* 2 INDEX RANGE SCAN PK_TEST_IDX01 10347 70 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
18 rows selected.


SQL> delete from test
2 where rownum <>

SQL> commit;


Commit complete.
SQL> explain plan for
2 select employee_id, first_name, last_name, job_id, salary from test
3 where employee_id=100;
Explained.


SQL> select * from table ( dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2321975210
-------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------
0 SELECT STATEMENT 2056 118K 62 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 2056 118K 62 (0) 00:00:01
* 2 INDEX RANGE SCAN PK_TEST_IDX01 2056 61 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note

-----
- dynamic sampling used for this statement
18 rows selected.
SQL> alter table test enable row movement;
Table altered.
SQL> alter table test shrink space cascade;
Table altered.
SQL> explain plan for
2 select employee_id, first_name, last_name, job_id, salary from test
3 where employee_id=100;
Explained.
SQL> select * from table ( dbms_xplan.display );

PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 2321975210
---------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 2056 118K 15 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID TEST 2056 118K 15 (0) 00:00:01
* 2 INDEX RANGE SCAN PK_TEST_IDX01 2056 14 (0) 00:00:01
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)

Note
-----
- dynamic sampling used for this statement
18 rows selected.
OBJECT_NAME = PK_TEST_IDX01
-----------------------------------
TOTAL_BLOCKS = 976
TOTAL_BYTES = 7995392
UNUSED_BLOCKS = 6
UNUSED BYTES = 49152
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 2696
LAST_USED_BLOCK = 74
PL/SQL procedure successfully completed.


CONSIDERACIONES
Dentro de las consideraciones que se debe tener, es que este procedimiento no puede ser utilizado en los siguientes casos:


  • • Tablas en Clusters
    • Cualquier tabla con un campo LONG
    • Tablas con Vistas Materializadas con la opción on-commit.
    • Cualquier tabla con una Vista Materializada basada en ROWID.
    • Un índice tipo LOB
    • IOT
    • Segmentos LOB compartidos
    • Segmentos temporales y de UNDO.
panicov9.sql
Con este script sql, puedes crear toda la estructura necesaria para recrear una base de datos en algún otro servidor ó simplemente, extraer datos de ubicación de datafiles, seguridad y usuarios.

REM ------------------------------------------------------------------------
REM Autor: Mark Gurry
REM ------
REM
REM Modificado por: Ronald Vargas Quesada, Consultor DBA
REM ---------------
REM
REM Nombre del script: panico.sql
REM ------------------
REM
REM Función: Genera un 'script' con todas las instrucciones necesarias
REM -------- para recrear una base de datos.
REM ----------------------------------------------------------------------
REM Crea el ambiente apropiado para correr
REM ----------------------------------------------------------------------
REM ----------------------------------------------------------------------
REM Crea cascaron de la base de datos a recrear
REM ----------------------------------------------------------------------
set feedback off;
set linesize 0
set pages 0
spool /oracle/files/estructura_bd.sql
prompt set linesize 200
Rem ----------------------------------------------------------------------
Rem Create todos los Tablespaces
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de tablespaces ----------*' from dual;
prompt
select 'create tablespace ' T.tablespace_name ' logging' chr(10)
'datafile ''' F.file_name ''' size ' to_char(F.bytes/1048576)
'M' chr(10)
'AUTOEXTEND ON MAXSIZE UNLIMITED'chr(10)
'EXTENT MANAGEMENT LOCAL SEGMENT'chr(10) 'space MANAGEMENT AUTO;'
from
sys.dba_data_files F,
sys.dba_tablespaces T
where
T.tablespace_name = F.tablespace_name
and T.contents !='UNDO' and
T.contents !='TEMPORARY'
and F.file_id = ( select min(file_id)
from sys.dba_data_files
where tablespace_name = T.tablespace_name );
Rem ----------------------------------------------------------------------
Rem Create Tablespaces UNDO
Rem ----------------------------------------------------------------------
select 'REM *---------- Creacion UNDO Tablespaces ----------*' from dual;
select 'create UNDO tablespace ' T.tablespace_name chr(10)
'datafile ''' F.file_name ''' size ' to_char(F.bytes/1048576)
'M' chr(10)
'AUTOEXTEND ON MAXSIZE UNLIMITED;'
from
sys.dba_data_files F,
sys.dba_tablespaces T
where
T.tablespace_name = F.tablespace_name
and T.contents ='UNDO'
and F.file_id = ( select min(file_id)
from sys.dba_data_files
where tablespace_name = T.tablespace_name );
Rem ----------------------------------------------------------------------
Rem Create todos los Datafiles de los Tablespaces
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de Datafiles ---------* ' from dual;
prompt
select 'alter tablespace ' T.tablespace_name chr(10)
'add datafile ''' F.file_name ''' size '
to_char(F.bytes/1048576) 'M;'
from
sys.dba_data_files F,
sys.dba_tablespaces T
where
T.tablespace_name = F.tablespace_name
and F.file_id != ( select min(file_id)
from sys.dba_data_files
where tablespace_name = T.tablespace_name );
Rem ----------------------------------------------------------------------
Rem Create todos los Roles del sistema
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de Roles ----------*' from dual;
prompt
select 'create role ' role
decode(password_required,'NO',' not identified;',
' identified externally;')
from sys.dba_roles;
Rem ----------------------------------------------------------------------
Rem Create todos los profiles
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de profiles ----------*' from dual;
prompt
select distinct 'create profile ' profile ' limit ' ';'
from sys.dba_profiles;
select 'alter role ' profile ' limit '
resource_name ' ' limit ';'
from sys.dba_profiles
where limit != 'DEFAULT'
and ( profile != 'DEFAULT'
or limit != 'UNLIMITED' );
Rem ----------------------------------------------------------------------
Rem Create las conecciones de los usuarios
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de conecciones ----------*' from dual;
prompt
select 'create USER ' username
' identified by XXXXX ' chr(10)
' default tablespace ' default_tablespace
' temporary tablespace ' temporary_tablespace chr(10)
' quota unlimited on ' default_tablespace ' '
' quota unlimited on ' temporary_tablespace ';'
from sys.dba_users
where username not in ('SYSTEM','SYS','_NEXT_USER','PUBLIC');
Rem ----------------------------------------------------------------------
Rem Asigna el password a los usuarios
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Asignacion de passwords ----------*' from dual;
prompt
select 'alter USER ' username ' identified by values '''
password ''';'
from sys.dba_users
where username not in ('SYSTEM','SYS','_NEXT_USER','PUBLIC')
and password != 'EXTERNAL';
Rem ----------------------------------------------------------------------
Rem Create quotas en tablespaces
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion de quotas ----------*' from dual;
prompt
select 'alter USER ' username ' quota '
decode(max_bytes,-1,'unlimited',to_char(max_bytes/1024) ' K')
' on tablespace ' tablespace_name ';'
from sys.dba_ts_quotas;
Rem ----------------------------------------------------------------------
Rem Otorga privilegios del sistema
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Privilegios del sistema ----------*' from dual;
prompt
select 'grant ' S.name ' to ' U.username ';'
from system_privilege_map S,
sys.sysauth$ P,
sys.dba_users U
where U.user_id = P.grantee#
and P.privilege# = S.privilege
and P.privilege# <> 0
start
with grantee# in (1, U.user_id )
and privilege# > 0 );
Rem ----------------------------------------------------------------------
Rem Creacion sinonimos publicos
Rem ----------------------------------------------------------------------
set linesize 200
prompt
select 'REM *---------- Creacion sinonimos publicos ----------*' from dual;
prompt
select 'create public synonym ' synonym_name ' for '
decode(table_owner,'','',table_owner'.') table_name
decode(db_link,'','','@'db_link) ';'
from sys.dba_synonyms
where owner = 'PUBLIC'
and table_owner != 'SYS';
set linesize 120
Rem ----------------------------------------------------------------------
Rem Creacion de los database links
Rem ----------------------------------------------------------------------
prompt
select 'REM *---------- Creacion los database links ----------*' from dual;
prompt
select 'create public database link ' db_link chr(10)
'connect to ' username ' identified by XXXXXX using '''
host ''';'
from sys.dba_db_links;
spool off;
exit

Creación de ambiente de Pruebas y Producción Ejecución de scripts-shell desde Procedimiento o Paquete de base de datos
Oracle 9i R2 9.2.0.7
Linux Red Hat Enterprise Server 4 y 5


Objetivo:
Ejecutar desde un procedimiento o paquete de base de datos Oracle, scripts de S.O.

Pre-requisitos de implementación:
Los paquetes de Java de la base de datos, deben estar en estado “VÁLIDO” y para esta prueba e implementación se tiene la base de datos parchada con el patch set 6. ( 9.2.0.7 ). En esencia debería funcionar con un patch set superior

SQL> column comp_name format a60
SQL> select comp_name, status, version from dba_registry;
COMP_NAME STATUS VERSION
------------------------------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
Oracle Workspace Manager VALID 9.2.0.1.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0
Oracle interMedia VALID 9.2.0.7.0
Spatial VALID 9.2.0.7.0
Oracle Text VALID 9.2.0.7.0
Oracle XML Database VALID 9.2.0.7.0




Debemos contar con acceso al sistema operativo con el usuario root ó el dueño de los scripts.
Cuenta con privilegios de DBA en la base de datos y de ser necesario, cuenta y password del esquema en donde se implementará el llamado del paquete.
Es requisito un directorio en la base de datos apuntando al directorio físico en el servidor. Este debe ser creado por el usuario “SYS” de la BD. El directorio físico debe existir antes de crear el directorio.

DROP DIRECTORY DIRECTORIO;
CREATE OR REPLACE DIRECTORY
DIRECTORIO AS
'/opt/oracle/files';
GRANT READ, WRITE ON DIRECTORY SYS.DIRECTORIO TO esquema WITH GRANT OPTION;
GRANT READ, WRITE ON DIRECTORY SYS.DIRECTORIO TO PUBLIC;

Creación ambiente de prueba:


Vamos a crear un ambiente rápido de pruebas, para verificar el funcionamiento de los paquetes. Con la cuenta DBA, procedemos a otorgar los permisos de ejecución a los paquetes de la base de datos sobre los directorios de trabajo del sistema operativo, así como definimos el usuario de base de datos a utilizar dichos permisos.
Con el siguiente bloque, vamos a definir los permisos de ejecución, lectura y escritura sobre todos los archivos que se encuentren en el directorio /opt/oracle/scripts , /opt/oracle/files, /opt/oracle/files/historico, /opt/oracle/files/bitacora, al esquema.


begin
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/scripts/*',
'execute');
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'/opt/oracle/scripts/*',
'readFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/scripts/*',
'read,write');
-- commit;
end;
/
begin
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/historico/*',
'execute');
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'/opt/oracle/files/historico/*',
'readFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/historico/*',
'read,write');
-- commit;
end;
/
begin
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/bitacora/*',
'execute');
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'/opt/oracle/files/bitacora/*',
'readFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/bitacora/*',
'read,write');
-- commit;
end;
/


Una vez creados los permisos, procedemos a crear el paquete que utilizaremos para el llamado de los scripts desde la base de datos.



create or replace and compile java source named "Util"
as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/


Luego vamos a crear la función con la cuál haremos el llamado de los scripts:


create or replace function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/


Vamos a crear el procedimiento que ejecutará la llamada a la función anterior.


create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/


Vamos a crear un pequeño script, para realizar la prueba de ejecución:


[oracle@test scripts]$ more escribe.sh
#!/bin/sh
echo "Hola como estas, esto se escribe desde la base de datos" > /opt/oracle/scripts/texto.txt
[oracle@test scripts]$
[oracle@test scripts]$ ls -la escri* pasar*
-rwxr-xr-x 1 oracle oinstall 105 Feb 4 08:25 escribe.sh
-rwx------ 1 oracle oinstall 865 Feb 4 08:27 pasar_archivo.sh
[oracle@test scripts]$


Este script, deberá crear un archivo en la ruta especificada con el parámetro del comando “echo” incluido en el mismo.
Luego simplemente llamamos a al procedimiento desde la base de datos, logeados con el usuario del esquema al cuál le dimos permisos.



SQL> execute rc('/opt/oracle/scripts/escribe.sh');
PL/SQL procedure successfully completed.
[oracle@test scripts]$ more texto.txt
Hola como estas, esto se escribe desde la base de datos
[oracle@test scripts]$


Ahora vamos a realizar las pruebas con un script más complejo, que se encargue de llamar a un procedimiento de la base de datos, se conecte a un servidor ftp y traslade el contenido del procedimiento ejecutado, dejando un historial de los archivos creados y escribiendo a una bitácora.


[oracle@test scripts]$ more pasar_archivo.sh
#!/bin/sh
# Proceso para envio de archivos

# Hecho 03 de Febrero de 2009
# Ronald Vargas
# Archivo de autologin /opt/oracle/.netrc
# Generacion de datos llamando a procedimiento almacenado de la BD

echo 'Hora de inicio '>>/opt/oracle/files/bitacora/bitacora_proceso.txt
date >>/opt/oracle/files/bitacora/bitacora_proceso.txt
#-------------------------------------
sqlplus usuario/$PASS <>/dev/null
echo "Proceso de Traslado de archivos concluido"
echo 'Hora de Finalizacion '>>/opt/oracle/files/bitacora/bitacora_proceso.txt
date >>/opt/oracle/files/bitacora/bitacora_proceso.txt
[oracle@test scripts]$


El archivo .netrc, contiene el usuario utilizado para conectarse haciendo autologin en el servidor FTP.


[oracle@test ~]$ more .netrc
machine nombre_servidor login $USER password $PASS
[oracle@test ~]$


Nota:
Las variables $USER y $PASS, deben ser sustituidas por los valores verdaderos a utilizar.

En el procedimiento debe incluirse la llamada al script de traslado de archivos, de la siguiente manera:


# ----Llamado al procedimiento de traslado de archivos al servidor
execute rc('/opt/oracle/scripts/pasar_archivo.sh');

Otras Aplicaciones:

Podemos utilizar el mismo procedimiento para dar permisos de ejecución a comandos del sistema operativo, como en el ejemplo a continuación:


SQL> begin
2 dbms_java.grant_permission
3 ('esquema',
4 'java.io.FilePermission',
5 '/usr/bin/who',
6 'execute');
7 end;
8 /


PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec dbms_java.set_output(1000000);
SQL> execute rc('/usr/bin/who');


root :0 Sep 18 15:39
root pts/1 Sep 18 15:41 (:0.0)
root pts/2 Feb 3 17:59 (t1_rvargas.red.co.cr)
PL/SQL procedure successfully completed.