Recuperação de comandos de criação DDL para objetos Oracle

Muitas vezes nos vemos em situações que é necessária a reconstrução de objetos ou mesmo apenas sua estrutura para reutilizar em outro ambiente, etc.

Algumas ferramentas como o PL/SQL developer ou o Oracle SQL Developer tem funcionalidades fáceis para recuperação de DDL, porém há situações em que temos apenas o bom e velho SQL*Plus, e nele também é possivel recuperar os comandos de criação de objetos através da DBMS_METADATA.


DBMS_METADATA:
A partir da versão 9i do Oracle foi implementada uma package chamada DBMS_METADATA, utilizando seu método de get.ddl é possivel e muito simples recuperar os códigos de criação.
Vamos ao que interessa:

Primeiro vamos alterar o parâmetro SQLTERMINATOR que incluirá no final de cada DDL gerada um finalizador (; ou / que são os padrões para o fim de uma instrução SQL) para isto execute a procedure DBMS_METADATA.SET_TRANSFORM_PARAM() para alterar o parâmetro. Outro parâmetro necessário é o SEGMENTS_ATRIBUTES que devemos alterar para false, pois este parametro suprime  as infromações de armazenamento de segmentos.

SQL> -- Como exemplo, vou utilizar objetos já existentes do Schema SCOTT
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE from dba_objects where owner = 'SCOTT';

OWNER                          OBJECT_NAME             OBJECT_TYPE
-------------------------   --------------------------  -------------------
SCOTT                            PK_DEPT                        INDEX
SCOTT                            DEPT                               TABLE
SCOTT                            EMP                                TABLE
SCOTT                            PK_EMP                         INDEX
SCOTT                            BONUS                           TABLE
SCOTT                            SALGRADE                    TABLE

6 rows selected.

SQL> select TABLE_NAME, TABLESPACE_NAME from dba_tables where owner = 'SCOTT';

TABLE_NAME            TABLESPACE_NAME
------------------------  ------------------------------
DEPT                            USERS
EMP                             USERS
SALGRADE                 USERS
BONUS                        USERS

SQL> -- Iremos verificar a criação de um Indíce, uma table e a tablespace em si, mas esta package pode ser utilizada para outros objetos como Triggers, Views, etc

SQL> -- Configurando o ambiente:
SQL> SET pagesize 1000
SQL> SET linesize 1000
SQL> SET long 9999999
 
SQL> -- Incluindo o finalizador ao resultado (; ou /)
SQL> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,'SQLTERMINATOR',true);

PL/SQL procedure successfully completed.

SQL> -- Suprimindo qualquer informação de atributos de armazenamento de segmentos
SQL> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

SQL> -- Recuperando DDL da tabela EMP
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') "DDL TABLE" from dual;

DDL TABLE
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) ;



SQL> -- Repare que o comando é muito simples e necessita apenas do tipo, nome e se preciso do owner, abaixo DDL do indice da tabela EMP
SQL> select dbms_metadata.get_dependent_ddl('INDEX','EMP','SCOTT') "DDL ÍNDICE" from dual;

DDL INDICE
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  ;



SQL> -- Também é possivel gerar o codigo das constrainsts da tabela que deseja:
SQL> select dbms_metadata.get_dependent_ddl('CONSTRAINT','EMP','SCOTT') "DDL PK/UK/CHK" from dual;

DDL PK/UK/CHK
--------------------------------------------------------------------------------

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE
;



SQL> -- Desta mesmo forma utilizando o Dicionario de dados é possivel reconstruir uma tablespace ou todos os objetos de um tipo:
SQL> -- Tablespace
SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') DDL from dual;

DDL
--------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/banco/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/banco/users01.dbf' RESIZE 60293120;



SQL> -- Todas as tabelas do Schema SCOTT
SQL> select dbms_metadata.get_ddl(object_type, object_name, owner) ddl
from dba_objects
where owner = 'SCOTT'
and object_type = 'TABLE';

DDL
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."SALGRADE"
   (    "GRADE" NUMBER,
        "LOSAL" NUMBER,
        "HISAL" NUMBER
   ) ;



  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) ;



  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ENABLE
   ) ;



  CREATE TABLE "SCOTT"."BONUS"
   (    "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "SAL" NUMBER,
        "COMM" NUMBER
   ) ;


Seguindo está linha preceba que é possivel recuperar a DDL inclusive de objetos do Schema SYS.

Outra forma também que talvez você esteja familiarizado é através de import/export

 -- exportando as tabelas do schema SCOTT
C:\exp scott/tiger file=c:\scott grants=n statistics=none rows=n

Export: Release 10.2.0.1.0 - Production on Sex Jul 18 12:58:03 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8PC850  e no conjunto de
caracteres de AL16UTF16 NCHAR o servidor usa WE8MSWIN1252 conjunto de caracteres
(conversão de conjunto de caracteres possível)
OBS: dados (linhas) da tabela não serão exportados
OBS: concessões em tabelas/views/seqüências/funções não serão exportadas
. exportando objetos e ações procedurais anteriores ao esquema
. exportando os nomes da biblioteca de função externa para usuário SCOTT
. exportando sinônimos do tipo PÚBLICO
. exportando sinônimos do tipo privado
. exportando definições de tipos de objeto para usuário SCOTT
Sobre exportar objetos de SCOTT ...
. exportando vínculos de banco de dados
. exportando números de seqüência
. exportando definições de cluster
. sobre exportar tabelas de SCOTT ... via Caminho Convencional ...
. . exportando tabela             MVIEW_T1
. . exportando tabela                   T1
. . exportando tabela                   T2
. exportando sinônimos
. exportando views
. exportando procedimentos armazenados
. exportando operadores
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportando tipos de índices
. exportando índices funcionais, extensíveis e de bitmap
. exportando ações contabilizáveis
. exportando views materializadas
. exportando logs de snapshot
. exportando filas de serviço
. exportando filhos e grupos de renovação
. exportando dimensões
. exportando objetos e ações procedurais posteriores ao esquema
. exportando estatística
Exportação encerrada com sucesso, sem advertências.

-- Gerando os comandos DDL's para arquivo texto
C:\>imp scott/tiger file=c:\scott indexfile=c:\ddl.sql

Import: Release 10.2.0.1.0 - Production on Sex Jul 18 12:59:25 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Arquivo de exportação criado por EXPORT:V10.02.01 via caminho convencional
importação realizada nos conjuntos de caracteres WE8PC850 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres WE8MSWIN1252 (conversão
de charset possível)
. . saltando a tabela "MVIEW_T1"
. . saltando a tabela "T1"
. . saltando a tabela "T2"

Importação encerrada com sucesso, sem advertências.

-- Agora seu DDL estará no arquivo setado.

Bom, sem mais desculpas de dependencias de ferramentas gráficas ok? A maioria das atividades de bancos de dados são tão ou mais simples na forma de texta quanto isto. Aproveite para treinar um pouco e criar scripts que solicitem apenas owner e nome do objeto ou owner e tipo do objeto para a recuperação de objetos específicos de schemas.

Até a próxima.

Robert Santos