Paquete DBMS_METADATA

Con este paquete (DBMS_METADATA) podemos obtener el código DLL (sentencia de creación) de cualquier objeto de BBDD, para ello utilizaremos la función GET_DDL, cuya sintaxis es la siguiente:

1
2
3
4
5
6
7
8

DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT ‘COMPATIBLE’,
model IN VARCHAR2 DEFAULT ‘ORACLE’,
transform IN VARCHAR2 DEFAULT ‘DDL’)
RETURN CLOB; 

Los parámetros que utiliza son:

1 .Tipo de objeto (tabla, index, vista…)

2. Nombre del objeto

3. El esquema al que pertenece ( No es necesario)

4. Versión de servidor para la cual se extraerá el código. Establece un filtro que limita la extracción del metadata para objetos superiores la versión indicada, que nunca puede ser menos de la 9.2.0.

5. Model: Por ahora sólo ORACLE

6. Con este parámetro podemos indicar si el metadata lo devolverá en DDL o en XML

1

SELECT dbms_metadata.get_ddl(’TABLE’, ‘PRUEBA’, ‘ORACLE’) FROM dual;

Devolvería:

1
2
3
4
5
6
7
8
9
10
11

  CREATE TABLE “ORACLE”.”PRUEBA”
 
   (    “NOMBRE” VARCHAR2(1)
 
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 
  TABLESPACE “ORACLE4″

Podemos extraer el esquema entero o unas tablas específicas:

1
2
3
4
5
6

spool tablas.lst 
 
SELECT DBMS_METADATA.<strong>GET_DDL</strong>('TABLE',u.table_name)
   FROM USER_TABLES u  WHERE TABLE_NAME LIKE 'BP%';
 
spool off 

Incluso podemos recuperar todo el esquema de la BBDD, con una sencilla sentencia:

SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER)
FROM USER_OBJECTS

Antes de realizar esto podemos cambiar algunos parámetros de metadata con el procedimiento SET_TRANSFORM_PARAM.

–       Para evitar que el output salga formateado con líneas en blanco y sangrías, en todos los objetos:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘PRETTY’, false);

–       Si queremos generar los atributos del segmento (storage attribute, tablespace…), storage y tablespace para las tablas y los índices de los objetos definidos. Por defecto son todos TRUE:

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘TABLESPACE’, true);

–       Si se quiere evitar que las constrains (non-referential y referential) se incluyan en las sentencias DDL de las tablas. Por defecto es true :

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS’, false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘REF_CONSTRAINTS’, false);

–       Si queremos que las constrains y los índices formen parte del create table, pondremos este parámetro como false, si preferimos tenerlos a parte dentro de un ALTER TABLE hay que ponerlo a TRUE.

dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS_AS_ALTER’, false);

Para más información sobre estas opciones, aquí encontré una tabla que lo explica bastante bien.