Tags: Oracle

DBMS_METADATA – Uma ferramenta poderosa

Já passou por aquela situação de precisar recriar uma tabela ou um índice a partir de tabelas/índices de outro banco ?  Ou precisou replicar um usuário e todos os seus privilégios em outro local ?

O que se pergunta em momentos como esse é como fazer uma engenharia reversa dos objetos envolvidos de forma rápida e eficiente. É comum se usar as ferramentas de exportação e importação do Oracle (expdp/impdp) para resolver essa situação.

Porém, em muitos casos, em especial quando não se tem acesso direto ao servidor, isso pode ser penoso ou mesmo inviável.

Nesse caso a solução é usar a API disponibilizada pelo Oracle para fazer a engenharia reversa dos objetos do BD. Ela é disponibilizada através do package DBMS_METADATA. Dando uma primeira olhada nesse package, as opções são muitas. Há inclusive a possibilidade de se fazer a engenharia reversa de objetos junto com alguma transformação (remapping). Por exemplo, o script para criação de uma tabela pode ser gerado com outra tablespace de destino. Mas para a engenharia reversa simples, o “pulo do gato” é usar duas funções. Daremos a seguir alguns exemplos de uso dessas funções:

1) Para se obter scripts de criação de objetos do BD, usamos a function GET_DDL:

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;

Engenharia reversa de TABELA:

CREATE TABLE test
PCTFREE 0
TABLESPACE uwdata AS
SELECT table_name, tablespace_name
FROM user_tables;

SET LONG 10000

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

Engenharia reversa de VIEW:

CREATE OR REPLACE VIEW my_tables AS
select table_name, tablespace_name
FROM user_tables;

SELECT dbms_metadata.get_ddl(‘VIEW’, ‘MY_TABLES’)
FROM dual;

Engenharia reversa de FUNCTION:

CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS

BEGIN
RETURN user;
END whoami;
/

SELECT dbms_metadata.get_ddl(‘FUNCTION’, ‘WHOAMI’)
FROM dual;

Engenharia reversa de TABLESPACE:

SELECT dbms_metadata.get_ddl(‘TABLESPACE’, ‘UWDATA’)
FROM dual;

Os exemplos anteriores irão gerar os scripts de criação de objetos simples. A brincadeira passa a ficar mais interessante quando conseguimos, através de uma única query, gerar o script de criação de vários objetos. Por exemplo, se eu quiser gerar um script para criar todas as tabelas do usuário SDBANCO, posso fazê-lo através da sequência de comandos a seguir:

SET LONG 10000

SELECT dbms_metadata.get_ddl(‘TABLE’, table_name)
FROM dba_tables
WHERE owner = ‘SDBANCO’;

Essa mesma ideia aplicada no exemplo acima pode ser usada para qualquer tipo de objeto.

2) Para se obter scripts de concessão de privilégios, usamos a function GET_GRANTED_DDL:

A partir da function GET_GRANTED_DDL conseguimos recuperar os privilégios de qualquer natureza concedidos a um usuário ou ROLE, o que é muito útil nos trabalhos de cópia de determinado usuário ou sistema de um local para outro.

dbms_metadata.get_granted_ddl(
object_type  IN VARCHAR2,
grantee      IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT ‘COMPATIBLE’,
model        IN VARCHAR2 DEFAULT ‘ORACLE’,
transform    IN VARCHAR2 DEFAULT ‘DDL’,
object_count IN NUMBER   DEFAULT 10000)
RETURN CLOB;

Um exemplo de como obter os scripts necessários para a cópia de um usuário (nesse caso, o SDBANCO), com todos os seus privilégios, seria:

select dbms_metadata.get_ddl( ‘USER’, ‘SDBANCO’ ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( ‘SYSTEM_GRANT’, ‘SDBANCO’ ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( ‘OBJECT_GRANT’, ‘SDBANCO’ ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( ‘ROLE_GRANT’, ‘SDBANCO’ ) from dual;

Podemos notar que as 3 últimas queries do comando acima usam a função GET_GRANTED_DDL para recuperar os privilégios de sistema, de acesso a objetos e as roles concedidas ao usuário SDBANCO, respectivamente.

Aqui listamos alguns usos simples do package DBMS_METADATA. Em função da sua utilidade e da vasta gama de opções disponíveis, trata-se de uma ferramenta que merece ser avaliada e usada por todos aqueles que lidam com a criação de objetos em bases Oracle no seu trabalho diário.

Por MÁRCIO NUNES JARDIM

Postado em: 29 de dezembro de 2010

Confira outros artigos do nosso blog

REST não é JSON

21 de agosto de 2017

Bruno Sofiato

[Webinar] Profile de aplicações Java com Oracle Mission Control e Flight Recorder

24 de julho de 2017

Danival Calegari

Criando Mocks de serviços REST com SoapUI

27 de junho de 2017

Monise Costa

JavaScript 6: diferença entre var, let e const

09 de maio de 2017

Otávio Felipe do Prado

Deixe seu comentário