Construcción de scripts con ayuda del diccionario

Submitted by carlos on 7 June, 2007 - 23:12

Es bastante habitual si se trabaja con bases de datos que a menudo se tenga que realizar alguna tarea de creación o alteración de estructuras, análisis, recompilación, etc. sobre objetos de la base de datos. Para ello se suele crear un script con numerosas sentencias DDL, en las que la mayoría de las veces lo único que cambia es el nombre del objeto a tratar.
En estos casos puede ahorrarnos mucho trabajo la utilización del diccionario de la base de datos para construir estas sentencias dinámicamente.

Pondremos como ejemplo la creación de un nuevo campo para almacenar la fecha de creación de los registros en todas las tablas de un esquema de una base de datos ORACLE. Para ello utilizaríamos la siguiente sentencia:

SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME ||
' ADD FECHA_CREACION DATE DEFAULT SYSDATE;'
FROM ALL_TABLES
WHERE OWNER ='HR';

El resultado sería algo como esto:


ALTER TABLE HR.DEPARTMENTS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.EMPLOYEES ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.JOB_HISTORY ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.JOBS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.LOCATIONS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.REGIONS ADD FECHA_CREACION DATE DEFAULT SYSDATE;
ALTER TABLE HR.COUNTRIES ADD FECHA_CREACION DATE DEFAULT SYSDATE;

Ahora sólo restaría guardar estas sentencias en un script y
ejecutarlo, o lanzarlas directamente desde la aplicación que
utilicemos para interactuar con nuestra base de datos

Para el que tenga que (o prefiera) trabajar desde un terminal o linea
de comandos, la manera de hacer esto mismo con SQLPLUS sería la
siguiente:


SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL C:\campo_auditoria.sql
SQL> SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME ||
' ADD FECHA_CREACION DATE DEFAULT SYSDATE;'
FROM ALL_TABLES
WHERE OWNER ='HR';
SQL> SPOOL OFF;
SQL> SET FEEDBACK ON
SQL> SET HEADING ON

Y finalmente ejecutar el script generado, aunque es recomendable una revisión previa de las sentencias generadas

SQL> @C:\campo_auditoria.sql

 

Contenido relacionado

  • problema trigger

    Buenas tardes. Tengo un problema con una base de datos.

    Tengo estas 3 tablas:

  • Entrar en SQLPlus como dba sin introducir password

    Si tienes el usuario de sistema con el que se ha instalado la base de datos puedes entrar en SQL plus como usuario DBA y sin introducir ninguna contraseña de la siguiente manera:

    1. Entra en el sistema con este usuario.
    2. Desde la linea de comandos, entra en SQLplus poniendo:

    sqlplus "/as sysdba"

    Si has necesitado entrar así porque no recordabas la contraseña de algún usuario, ya puedes modificarla/s para poder utilizarlo/s después:

    SQL> alter user nombre_usuario identified by nuevo_password;

    Te puede pasar que haya más de una BD instalada en el servidor, por lo que tendrás que asegurarte de que las variables de entorno del usuario de Oracle están apuntando a la base de datos que te interesa.

    Para comprobar que has entrado en la base de datos correcta antes de tocar nada puedes ejecutar esta sentencia:

    SQL> select name from v$database;

  • Consulta de contraseñas de usuario de Oracle

    Por obvias razones de seguridad no se puede consultar en claro la contraseña de los usuarios de la base de datos, pero un usuario administrador como SYSTEM sí que tiene privilegios para consultar el password encriptado:

    SQL> select password from dba_users where username='SCOTT';
    PASSWORD
    -----------------------------
    F894844C34402B67

    La utilidad de esto está en que lo mismo que nos retorna la SELECT se puede utilizar en una sentencia de modificación de la contraseña;

    SQL> alter user scott identified by values 'F894844C34402B67';

    Con esto no hemos hecho nada, ya que le hemos asignado a SCOTT la misma contraseña que tenía (TIGER), pero si cambiamos la contraseña:

    SQL> alter user scott identified by nuevacontraseña;
    SQL> connect scott/nuevacontraseña
    Connected.

    Y ahora queremos volver a ponerle la contraseña inicial:

    SQL> connect system/dbapwd
    Connected.
    SQL> alter user scott identified by values 'F894844C34402B67';
    User altered.

    SQL> connect scott/tiger
    Connected.

  • Como obtener la lista de tablas con más movimiento(insert,update) en Oracle

    A fin de obtener una lista aproximada de las tablas con más movimientos de la base de datos podemos consultar el contenido de la tabla dba_tables y cruzarlo con el estado actual de cada tabla en la bbdd. Esto puede tener sentido cuando queremos confeccionar una lista de tablas a las que se debe actualizar estadísticas periódicamente o queremos controlar la cantidad de información que genera alguna aplicación en concreto. Los datos que obtenemos por cada tabla son siempre respecto al último analisis de la misma.

    La siguiente forma de hacerlo es un poco "rupestre" pero útil a la vez:

  • Mensaje de error ORA-02069. El Parámetro Global_names se debe definir en TRUE

    Porque al utilizar una function creada por nosotros muvale.dias:

    Si ejecutamos desde el entorno de producción, haciendo referencia a la tabla en producción, FUNCIONA

    insert into muvale.proceso
select * from muvale.proceso prc
where muvale.dias(prc.f_baja , prc.f_alta, sysdate, sysdate) > 0
and anproces = 2011 and nmproces = 1

    Si ejecutamos desde el entorno de producción, haciendo referencia a la tabla en DESARROLLO (@tasio), NO FUNCIONA?

    insert into muvale.proceso@tasio
select * from muvale.proceso prc
where muvale.dias(prc.f_baja , prc.f_alta, sysdate, sysdate) > 0
and anproces = 2011 and nmproces = 1

    Aparece el siguiente mensaje de error ORA-02069 El Parámetro Global_names se debe definir en TRUE para esta operación

    He visto que el error surge al utilizar la funcion muvale.dias. Aunque ocurre con cualquier function creado por mí. En cambio si se utilizan funciones propias de oracle, no hay problema

    Si quito esta función de la sentencia SQL si funciona

 

 

 

Gestion del Conocimiento    |    Business Intelligence y Analítica    |     Bases de Datos    |      ERP     |      CRM      |     Tendencias tecnológicas