Script para ver los jobs en ORACLE

El siguiente script le permitirá ver los jobs programados en Oracle. Se basa en una consulta a la vista dba_jobs para lo cual debe tener privilegios de consulta

VERJOBS.SQL
column job format 99999
column schema format a15
column fail format 999
set pages 100
set line 200
select job, substr(SCHEMA_USER,1,12) schema,
       substr(TO_CHAR(NEXT_DATE,'DAY DD-MON-YY'),1,19) Next_Day, 
       NEXT_SEC, BROKEN b,
       FAILURES fail, substr(what,1,15) what, 
       decode(SUBSTR(INTERVAL,30,30),null,SUBSTR(INTERVAL,1,30),
                 SUBSTR(INTERVAL,30,30)) INTERVAL
from dba_jobs
ORDER BY NEXT_DATE, NEXT_SEC
/

El resultado es algo similar a:

SQL> @VERJOBS.SQL
JOB SCHEMA    NEXT_DAY            NEXT_SEC B FAIL WHAT               INTERVAL
--- --------- ------------------- -------- - ---- ------------------ --------------------
21  RUNNER    MONDAY    02-NOV-09 13:24:58 N    0 SCHEMA.SP_JOB_0001 sysdate + 1 / (24 * 60)

1 row selected.

ORA-28000: la cuenta está bloqueada

ORA 28000: THEN ACCOUNT IS LOCKED

SQL> alter user nombre_usuario account unlock;

Milisegundos en Oracle: timestamp y systimestamp

Para almacenar la fecha, hora incluidos los milisegundos en Oracle, realizar lo siguiente:


create table esquema.tabla
(FECHA_HORA timestamp(5) default systimestamp
,MENSAJE varchar2(4000)
);


Para consultar los milisegundos con un query puede realizar lo siguiente:


select to_char(FECHA_HORA,'yyyy/mm/dd hh24:mi:ss FF3')
from esquema.tabla;


el numero despues del FF indica la cantidad de digitos de las fraccciones de segundo

You cannot commit with autocommit set! JBoss

Estuve usando JBoss con una conexion oracle.jdbc.driver.OracleConnection; todo funcionaba bien. Pero finalmente termine por usar java.sql.Connection por un tema de WebService.

java.sql.Connection por defecto coloca la conexion como con.setAutoCommit(true). Y si aun por ello le haces con.commit() en el programa; se lanzara el mensaje "You cannot commit with autocommit set!".

Asi que usando java.sql.Connection, luego de la conexion debe hacer con.setAutoCommit(false) y los con.commit() van ha funcionar de nuevo.

Como realizo un bucle repetitivo en Oracle

Puede utilizar el WHILE, FOR o el LOOP

    limite := 1;
    WHILE limite <= 400
    LOOP
        suma := suma + limite;
        limite := limite + 1;
    END LOOP;


    FOR limite IN 1..400
    LOOP
        suma := suma + limite;
    END LOOP;
 
 
    limite := 1;
    LOOP
         suma := suma + limite;
         EXIT WHEN limite > 400;
    END LOOP;

 
    LOOP
       x := x + 1;
       IF x > 3 THEN
          EXIT;
       END IF;
    END LOOP;

Como instalar Oracle Express en Windows. Los primeros SQL en Oracle

Realice el download del Oracle Express desde: http://www.oracle.com/technology/software/index.html









Debe contar con una cuenta oracle, sino puede crearla en la opción signup now






INSTALAR ORACLE

Ejecute el archivo OracleXEUniv.exe que obtuvo de www.oracle.com








Cree un directorio donde instalara el Oracle y selecciónelo al instalar





Escoja un puerto como 8080, 8081, 8082, 8083.. que este libre.




Ingrese el password como manager para el administrador de Oracle.






Finalmente instale Oracle








En el firewall de Windows debe abrir el puerto 8082 elegido.









Revise que el Servicio Oracle este levantado como servicio de Windows








Conéctese a Oracle con la siguiente dirección.

http://127.0.0.1:8082/apex/



Elija la consola SQL





Cree una tabla en el esquema st232u

create user st232u identified by st232u default tablespace users quota unlimited on users;



CREATE TABLE st232u.pet (name VARCHAR(20), owner VARCHAR(20),

age NUMBER(5,2), sex CHAR(1), birth DATE, death DATE);

Para listar la metadata realize los siguientes querys.

select * from all_tables where owner='ST232U';
select * from dba_tables where owner='ST232U';




select OWNER, TABLE_NAME,

COLUMN_NAME, DATA_TYPE,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT

from all_tab_columns where owner='ST232U';

Construir el SQL para JAVA desde la descripcion de la tabla en ORACLE

Tiene una tabla en Oracle y desea crear SQL para colocarlo dentro de un programa JAVA; si hay muchas comas y comillas que colocar.

Si usted requiere algo como lo siguiente para colocarlo en su programa

TEXT                                                       SQL COLUMN_ID
-------------------------------------------------------- ----- ---------
" ,CUSTOMER_ID                  = CUSTOMER_ID         "+     1         1
" ,ACCOUNT_NUM                  = ACCOUNT_NUM         "+     1         2
" ,LNAME                        = LNAME               "+     1         3
" ,FNAME                        = FNAME               "+     1         4
" ,CUSTOMER_ID                   "+                          2         1
" ,ACCOUNT_NUM                   "+                          2         2
" ,LNAME                         "+                          2         3
" ,FNAME                         "+                          2         4

SQL> desc FOODMART.CUSTOMER
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 CUSTOMER_ID                               NOT NULL NUMBER
 ACCOUNT_NUM                               NOT NULL NUMBER(15)
 LNAME                                     NOT NULL VARCHAR2(30)
 FNAME                                     NOT NULL VARCHAR2(30)

Entonces utilize el siguiente script en ORACLE para obtenerlo. Solo debera indicar el esquema y la tabla. Y listo, a hacer COPY+PASTE.

-- TABS.SQL
set echo off
rem ***********************************************
rem  Columnas
rem ***********************************************
set verify off
set linesize 320
set pagesize 2000
set numwidth 5
clear buffer
accept owner_tabla prompt 'Ingrese esquema.tabla : '

select '" ,'||rpad(COLUMN_NAME,28,' ')||' = '
            ||rpad(COLUMN_NAME,28,' ')||'"+'  TEXT, 
              1 SQL, COLUMN_ID
from all_tab_cols
where OWNER||'.'||TABLE_NAME  = trim(upper('&owner_tabla'))
union all
select '" ,'||rpad(COLUMN_NAME,28,' ')||'  "+', 
              2, COLUMN_ID
from all_tab_cols
where OWNER||'.'||TABLE_NAME  = trim(upper('&owner_tabla')) 
order by SQL, COLUMN_ID
/
clear breaks
set numwidth 9
ttitle off
set verify on
set serverout on size 200000
set pagesize 2000
set linesize 80

Utilizar NVL2 en vez de DECODE con valores nulos NULL en Oracle

Para procesar valores nulos usamos el NVL, DECODE o NVL2

Si el primer argumento es nulo devolver el segundo argumento.

select nvl(null,'alfa') from dual;
----
alfa
1 row selected.

Si el primer argumento no es nulo devolver el mismo primer argumento.

select nvl('data','alfa') from dual;
----
data
1 row selected.


Si el primer argumento es igual segundo devolver el tercer argumento.

select decode(null,null,'alfa','beta') from dual;
----
alfa
1 row selected.

Si el primer argumento no es igual segundo devolver el cuarto argumento.

select decode('data',null,'alfa','beta') from dual;
----
beta
1 row selected.


Si el primer argumento es nulo devolver el tercer argumento.

select nvl2(null,'alfa','beta') from dual;
----
beta
1 row selected.

Si el primer argumento no es nulo devolver el segundo argumento.

select nvl2('data','alfa','beta') from dual;
----
alfa
1 row selected.

Como indico el TABLESPACE al crear una tabla

Para crear una tabla y su clave primaria, indique el TABLESPACE con la siguiente plantilla.

CREATE TABLE ESQUEMA.MI_TABLA (
   MT_CLAVE           NUMBER(3)    NOT NULL,
   MT_DESCRIPCION     VARCHAR2(50) NULL      
)  TABLESPACE TBS_ESQUEMA_DATA;

ALTER TABLE ESQUEMA.MI_TABLA
   ADD CONSTRAINT PK_MI_TABLA
   PRIMARY KEY (MT_CLAVE) 
   USING INDEX TABLESPACE TBS_ESQUEMA_IDX;

Deseo convertir un numero es una letra

Deseo convertir un numero es una letra

1 -> A
2 -> B
3 -> C
4 -> D

En Oracle, utiliza estos querys para trasladar un numero en una letra

select chr(1+96) FROM dual;
select chr(2+96) FROM dual;
select chr(3+96) FROM dual;
select chr(4+96) FROM dual;

select chr(1+64) FROM dual;
select chr(2+64) FROM dual;
select chr(3+64) FROM dual;
select chr(4+64) FROM dual;


Como generar números al azar en Oracle

Generar numeros aleatorios (al azar, random) entre 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;
611

Generar un numero aleatorio de 12 digitos.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
175055628780

Generar en mayusculas o minusculas 20 caracteres aleatorios

SQL> select dbms_random.string('U', 20) str from dual;
VUOQOSTLHCKIPIADIZTD
SQL> select dbms_random.string('L', 20) str from dual;
xpoovuspmehvcptdtzcz

Generar 20 caracteres imprimibles

SQL> select dbms_random.string('P', 20) str from dual;
*Yw>IKzsj\uI8K[IQPag

Crear un usuario en ORACLE

Para crear un usuario/esquema en ORACLE para alojar tablas, use el siguiente script:

create user USUARIO identified by PASSWORD default NOMBRE_TABLESPACE
grant connect to USUARIO;
alter user USUARIO identified by NUEVO_PASSWORD
alter user USUARIO quota unlimited on NOMBRE_TABLESPACE;


Base de Datos definida en un Dataguard Físico

Pregunta:
Tenía una base de datos definida en un dataguard físico, ahora ya no existe la
base de datos primaria así que la bd en standby ya no esta recibiendo las
transacciones de la bd primaria. Mi pregunta es cómo puedo abrir la base de
datos que estaba en standby para que pueda estar disponible a los usuarios.
Queremos hacer unas pruebas con esta base de datos que estuvo inaccesible
mientras funcionaba el dataguard.

Respuesta:
también tenemos una BD en Dataguard, y tuvimos el mismo problema, prueba
los siguientes pasos:
Levantar Standby
SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

Failover.

SQL> alter database commit to switchover to primary with session shutdown;

Da error: ORA-16139: media recovery required

Según nota: 219825.1 Hay que hacer un Failover Forzado
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Falla: ORA-16140: standby online logs have not been recovered
Si pasa esto, según nota:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE SKIP STANDBY LOGFILE;

Una vez finalizado

SQL> shutdown immediate;

SQL> startup;

Transacciones dudosas

Pregunta:
Hace un tiempo en una base de datos tenía un link mediante el cual realizaba modificaciones en una base de datos remota, hubo algunos inconvenientes y quedaron algunas transacciones marcadas como dudosas, en su momento realicé un commit force, hasta aca todo bien, aparentemente.
Resulta que, el link ya no existe mas, pero en el log quedo dando un error por cada una de esas transacciones pendientes.
ERROR, tran=7.28.375837, session#=1, ose=0:
ORA-02019: connection description for remote database not found

En PENDING_TRANS$, PENDING_SESSIONS$ y PENDING_SUB_SESSIONS$ tengo 19 registros en cada tabla. En la columna STATUS figura una C, y en la descripcion del estado dice FORCED COMMIT.

La pregunta es ¿Como se puede forzar el estado de estas transacciones para que no siga intentando conectarse a un link que ya no existe mas?
¿hay alguna forma?

Respuesta:

Hace tiempo teníamos 10 BDs en replica, cada una estaba ubicada en distintos
centros y a veces, por problemas de conexión, se nos quedaban pendientes
transacciones, y como bien dices, forzábamos el commit o rollback, según.

Había casos en los que las transacciones seguían apareciendo, y existe una
forma de eliminar estas transacciones, para ello hay un procedimiento en
Metalink (Note:1012842.102) que resumiendo, los pasos son:

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 prepared

SQL> rollback force '37.16.108'; ==>For example

Rollback complete.

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 forced rollback

SQL>Commit;

SQL>alter session set "_smu_debug_mode" = 4;
Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('37.16.108'); ==>For example

Script para ver los constraints de una tabla

Con el siguiente script en el Oracle SQL+ podrá listar los constraints de una tabla.

CONST.SQL
set echo off
set verify off
set linesize 320
set pagesize 2000
set numwidth 5
col constraint_type format a15
col column_name     format a40
col position format 999
break on constraint_name
clear buffer
accept ot prompt 'Ingrese SCHEMA.TABLE_NAME : '
ttitle left 'Constraints de la tabla '&ot
select a.constraint_name , 
       decode(a.constraint_type, 'C','Check', 'P','Primary', 
             'R','Foreign', 'U','Unique', 'Others') constraint_type, 
       b.column_name, b.position Pos 
from dba_constraints a, 
     dba_cons_columns b
where a.owner = b.owner
and   a.table_name = b.table_name
and   a.constraint_name = b.constraint_name
and   a.owner||'.'||a.table_name = trim(upper('&ot'))
order by 2,1
/
clear breaks
set numwidth 9
ttitle off
set verify on
set serverout on size 200000
set pagesize 2000
set linesize 80

Y luego muestra lo siguiente:

SQL> @CONST.SQL
Ingrese SCHEMA.TABLE_NAME : CONTAB.MONEY

Constraints de tabla CONTAB.MONEY
CONSTRAINT_NAME                CONSTRAINT_TYPE COLUMN_NAME                              POS
------------------------------ --------------- ---------------------------------------- ---
SYS_C00122003                  Check           MONEY_NAME
PK_JUEGOS                      Primary         MONEY_ID                                  1

2 rows selected.

Oracle SQL+ sin ventana de Login. Script para logearse

En Propiedades del acceso directo correspondiente al sqlplusw.exe, cambia el destino a algo similar a lo siguiente: C:\oracle\product\10.2.0\client_1\bin\sqlplusw.exe /nolog

El nolog asegura que no se presentara la ventana de login.

Y para logearte crea un script llamado MILOGIN.SQL, asi en el prompt del sqlplusw.exe o sqlplus.exe ingrese mediante


SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 2 11:27:17 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> MILOGIN.SQL


Lo mejor es que tenga un script para cada servidor, instancia.


MILOGIN.SQL
connect MIUSUARIO/PASSWORD@TNSNAMESDB
set heading off
set feedback off
column prom new_value prom
select user||'-'||global_name||'>' prom
from global_name
/
set sqlprompt '&prom '
set heading on
set feedback on
set time on
set timing on


Asi luego de conectarse aparecera:

SQL> @MILOGIN.SQL
Connected.

MIUSUARIO-DBNAME>
11:29:32 MIUSUARIO-DBNAME>