Obtener Metadatos de Oracle10g
September 4th, 2008Dentro de una actividad del trabajo, tuve la necesidad de obtener los metadatos de una base de datos (es una tarea muy común: incluso se puede utilizar una interfaz gráfica como SQL Developer o Toad), sin embargo, en esta ocasión necesitaba automatizar el proceso dentro de un procedimiento almacenado dentro de la base de datos.
Para el caso de los procedimientos almacenados se puede utilizar dba_source, pero no funciona para las tablas, y para el resto de los objetos se pueden obtener con vistas del diccionario de datos, o con dbms_metadata.get_ddl.
Ahora bien, dbms_metadata.get_ddl obtiene el DDL de cualquier objeto, sin embargo, lo retorna con su información de almacenamiento (PCTFREE, Tablespace, Segments, etc.) por lo que podría no ser un buen acercamiento si nadamás interesa el metadato (asi como para migrarlo a otra base de datos).
DBMS_METADATA es un paquete muy completo que nos permite facilitar la tarea de obtener los metadatos de cualquier objeto, y tiene la ventaja de que podemos manipular ese metadato para facilitar nuestras tareas.
Primero, utilizamos el método open del paquete para indicarle que vamos a hacer una petición, esta retorna un ID o Handler que utilizaremos en el resto de la rutina:
v_meta_handler := dbms_metadata.open('TABLE');
v_meta_handler es una variable de tipo number que almacena el id de trabajo para el paquete, esto nos permite utilizar el paquete para obtener otros metadatos en otras funciones, incluso trabajando en paralelo; ya que los id’s son independientes.
Ahora, debemos indicarle al paquete “que queremos y como lo queremos” primero, filtramos los objetos que queremos:
dbms_metadata.set_filter(v_meta_handler, 'SCHEMA', 'SYSTEM');
dbms_metadata.set_filter(v_meta_handler, 'NAME', 'EXAMPLE');
Después le indicamos Como lo queremos mediante los métodos: add_transform, set_remap_param y set_transform_param:
v_transform1 := dbms_metadata.add_transform(v_meta_handler,'MODIFY');
dbms_metadata.set_remap_param(v_transform1,'REMAP_SCHEMA','SYSTEM',NULL);
v_transform2 := dbms_metadata.add_transform(v_meta_handler, 'DDL'); dbms_metadata.set_transform_param(v_transform2, 'SQLTERMINATOR', false); dbms_metadata.set_transform_param(v_transform2,'SEGMENT_ATTRIBUTES',FALSE);
Para cada transformación se requiere especificar un ID de transformación, mas que nada se utiliza para no mezclar los tipos entre la misma operación, y entre operaciones. Primero le decimos que nos agregue una transformación de tipo “MODIFY”, ya que vamos a cambiar el DDL, después le decimos que “remapeamos” el esquema de ‘SYSTEM’ a NULL, esto para que no nos agregue el propietario del objeto (si queremos podemos cambiarlo por cualquier otro, dbms_metadata no valida que exista, solo lo cambia.
La segunda transformación le especifica que lo vamos a trabajar como DDL, anteriormente el paquete lo mantenia en una estructura interna de XML, por lo que se le puede hacer cualquier clase de cambios con un coste mínimo de renidimiento, pero debido a que nos interesa la instrucción, ahora le especificamos que la debe preparar como DDL.
Le especificamos que queremos el caracter de fin de instrucción SQL (”;” o “/” según sea el caso), y le especificamos que quite los atributos de segmento (almacenamiento, ubicación, fragmentación, etc.)
y ya por último, recuperamos la información:
v_ddls := dbms_metadata.fetch_ddl(v_meta_handler);
if (v_ddls is not null) then
v_ddl := v_ddls(1);
dbms_output.put_line(v_ddl.ddltext);
end if;
v_ddls es una variable de tipo sys.ku$_ddls, la cual a su ves es una tabla del tipo sys.ku$_ddl, la cual es un tipo definido por el usuario con la estructura:
SQL> desc sys.ku$_ddl; Name Null? Type ----------------------------------------- -------- -------------------- DDLTEXT CLOB PARSEDITEMS SYS.KU$_PARSED_ITEMS
El que nos interesa en este momento es DDLTEXT, sin embargo, podemos utilizar PARSEDITEMS para procesar la instrucción y obtener datos granulados de la instrucción DDL (asombroso, ¿no?), esto nos permite incluso alterar cualquier aspecto de la instrucción DDL sin necesidad de escribir programas muy complejos.
El código completo, junto con su ejecución es el siguiente:
SQL> declare
2 v_meta_handler number;
3 v_transform1 number;
4 v_transform2 number;
5
6 v_ddls sys.ku$_ddls;
7 v_ddl sys.ku$_ddl;
8 begin
9 v_meta_handler := dbms_metadata.open('TABLE');
10 dbms_metadata.set_filter(v_meta_handler, 'SCHEMA', 'SYSTEM');
11 dbms_metadata.set_filter(v_meta_handler, 'NAME', 'EXAMPLE');
12
13
14 v_transform1 := dbms_metadata.add_transform(v_meta_handler, 'MODIFY');
15 dbms_metadata.set_remap_param(v_transform1,'REMAP_SCHEMA','SYSTEM',NULL);
16
17 v_transform2 := dbms_metadata.add_transform(v_meta_handler, 'DDL');
18 dbms_metadata.set_transform_param(v_transform2, 'SQLTERMINATOR', false);
19 dbms_metadata.set_transform_param(v_transform2,'SEGMENT_ATTRIBUTES',FALSE);
20
21 v_ddls := dbms_metadata.fetch_ddl(v_meta_handler);
22 if (v_ddls is not null) then
23 v_ddl := v_ddls(1);
24 dbms_output.put_line(v_ddl.ddltext);
25 end if;
26 dbms_metadata.close(v_meta_handler);
27 end;
28 /
CREATE TABLE "EXAMPLE"
( "FLD1" NUMBER
)
PL/SQL procedure successfully completed.
sin cosas raras ni mas!, así se puede obtener el DDL de cualquier objeto dentro de una base de datos de ORACLE, para mayor referencia, la página oficial de DBMS_METADATA para Oracle10g es: http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_metada.htm#ARPLS026
