Muitas vezes precisamos obter a DDL de um objeto de banco de dados para algun tipo de verificação ou informação.
Para obtermos essas informações de forma rápida, sem a utilização de ferramentas gráficas, podemos utilizar alguns dos comandos que estamos descrevendo abaixo.
Para isso, vamos inicialmente, configurar as variáveis de ambiente para que a saída de nossas solicitações tenham uma formatação mais aceitável.
SQL> set lines 800
SQL> set pages 1000
SQL> set long 999999999
As duas primeiras ações abaixo, não são necessaŕias para obtermos os resultados que queremos, é apenas uma formatação de ambiente que pode ou não ser utilizada.
a) Adicionar um terminador SQL (; ou /) para cada sentença DDL gerada
SQL>exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
Procedimento PL/SQL concluído com sucesso.
b) Suprimir qualquer informação de atributos de armazenamento de segmentos
SQL>exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
Procedimento PL/SQL concluído com sucesso.
A seguir, alguns comandos para obtenção de DDL de objeto de banco de dados:
1) Gerar DDL para uma tabela
SQL> select dbms_metadata.get_ddl('TABLE','XPTO') "DDL TABLE" from dual;
DDL TABLE
--------------------------------------------------------------------------------
CREATE TABLE "OWNER"."XPTO"
("ID" NUMBER, "NOME" VARCHAR2(100), CONSTRAINT "PK_XPTO" PRIMARY KEY ("ID") ENABLE
);
2) Gerar DDL para uma view
SQL> select dbms_metadata.get_ddl('VIEW','VIEW_XPTO_01') "DDL VIEW" from dual;
DDL VIEW
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "OWNER"."VIEW_XPTO_01" ("NOME") AS SELECT NOME FROM XPTO;
3) Gerar DDL apenas para a chave estrangeira definida na tabela XPTO2
SQL> select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','XPTO2') "DDL FK" from dual;
DDL FK
--------------------------------------------------------------------------------
ALTER TABLE "OWNER"."XPTO2" ADD CONSTRAINT "FK_XPTO2_XPTO" FOREIGN KEY ("ID")
REFERENCES "OWNER"."XPTO" ("ID") ENABLE;
4) Gerar DDL para restrições do tipo (PK/UK/CHK) existentes definidas em XPTO
SQL> select dbms_metadata.get_dependent_ddl('CONSTRAINT','XPTO') "DDL PK/UK/CHK" from dual;
DDL PK/UK/CHK
--------------------------------------------------------------------------------
ALTER TABLE "OWNER"."XPTO" ADD CONSTRAINT "PK_XPTO" PRIMARY KEY ("ID") ENABLE;
5) Gerar DDL para qualquer trigger existente para a tabela XPTO
SQL>> select dbms_metadata.get_dependent_ddl('TRIGGER','XPTO') "DDL TRIGGER" from dual;
DDL TRIGGER
--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "OWNER"."TRG_XPTO"
BEFORE INSERT ON XPTO
FOR EACH ROW
BEGIN
:NEW.ID := DBMS_RANDOM.RANDOM;
END;
/
ALTER TRIGGER "OWNER"."TRG_XPTO" ENABLE;
6) Gerar DDL para qualquer índice existente para a tabela XPTO
SQL> select dbms_metadata.get_dependent_ddl('INDEX','XPTO') "DDL ÍNDICE" from dual;
DDL ÍNDICE
--------------------------------------------------------------------------------
CREATE INDEX "OWNER"."I_XPTO_INC" ON "OWNER"."XPTO" ("NOME");
CREATE UNIQUE INDEX "OWNER"."PK_XPTO" ON "OWNER"."XPTO" ("ID");
7) Gerar DDL para a view materializada
SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MVIEW_XPTO') "DDL MVIEW" from dual;
8) Exemplo para geração de comandos DDL's para todas as tabelas existentes
SQL> select dbms_metadata.get_ddl(object_type, object_name) ddl from user_objects where object_type = 'TABLE';
Obs.: É importante verificarmos se estamos conectados no banco de dados com o usuário (owner) do objeto que iremos extrair o ddl. Se estivermos conectados com outro usuário DBA ou como SYS, deveremos colocar o OWNER dos objetos nos comandos dbms_metadata.get_ddl para podermos obter corretamente as informações desejadas.
Abaixo segue um exemplo, onde vamos obter o DDL de uma view e estamos conectados como usuário SYS e esta view pertence ao usuário XPTO
SQL> show user
USER is "SYS"
SQL>
SQL> select dbms_metadata.get_ddl('VIEW','V_EXP_XPTO') "DDL VIEW" from dual;
ERROR:
ORA-31603: object "V_EXP_XPTO" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1
Conforme descrito anteriormente, a VIEW que estamos tentando obter o DDL, não pertence ao usuário que estamos conectado no banco de dados, neste caso o SYS.
Para conseguirmos obter o DDL desejado, precisamos informar o OWNER do objeto no comando dbms_metadata.get_ddl, conforme demonstrado abaixo:
SQL> select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OWNER') "DDL VIEW" from dual;
SQL> select dbms_metadata.get_ddl('VIEW','V_EXP_XPTO','XPTO') "DDL VIEW" from dual;
DDL VIEW
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE EDITIONABLE VIEW "XPTO"."V_EXP_XPTO" ("SAID
Obs.: A saída do DDL acima está suprimido. Para que a saída seja completa, temos que configurar o comando set long 999999999 antes da execução do comando dbms_metadata.
9) Obter o DDL de todos os objetos para um schema(Owner) específico
set long 999999999
set head off
set echo off
set pagesize 0
set verify off
set feedback off
select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
--Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
select
owner,
--Java object names may need to be converted with DBMS_JAVA.LONGNAME.
--That code is not included since many database don't have Java installed.
object_name,
decode(object_type,
'DATABASE LINK', 'DB_LINK',
'JOB', 'PROCOBJ',
'RULE SET', 'PROCOBJ',
'RULE', 'PROCOBJ',
'EVALUATION CONTEXT', 'PROCOBJ',
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE', 'TYPE_SPEC',
'TYPE BODY', 'TYPE_BODY',
'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',
'QUEUE', 'AQ_QUEUE',
'JAVA CLASS', 'JAVA_CLASS',
'JAVA TYPE', 'JAVA_TYPE',
'JAVA SOURCE', 'JAVA_SOURCE',
'JAVA RESOURCE', 'JAVA_RESOURCE',
object_type
) object_type
from dba_objects
where owner in ('SCHEMA_OWNER_DESEJADO')
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
--Ignore system-generated types that support collection processing.
and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
--Exclude nested tables, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
--Exlclude overflow segments, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;
10) Outros exemplos
SQL> select dbms_metadata.get_ddl('PROCEDURE','PR_TEMP_REU','ABCD') from dual;
SQL> select dbms_metadata.get_ddl('TABLE','PAUTA','DPTO') from dual;
SQL> select dbms_metadata.get_ddl('TRIGGER','TG_HREAX_01','VCTO') from dual;
SQL> select dbms_metadata.get_ddl('INDEX','ARTR_I_01','STSO') from dual;
Referências:
http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm
http://www.oracle.com/technetwork/pt/articles/sql/extraindo-comandos-ddl-2209683-ptb.html
Nenhum comentário:
Postar um comentário