SQL MAGAZINE 145 HGYUJMK

Prévia do material em texto

Sumário
Conteúdo sobre Novidades
25 – Oracle 12c: Conheça a cláusula pattern matching 
[ Tércio Costa ]
14 – Trabalhando com o PostgreSQL: implantação do banco de dados 
[ Bruno de Oliveira Machado ]
Conteúdo sobre Novidades
06 – SQL Server 2016: Conheça as novidades 
[ Felipe de Assis ]
Artigo no estilo Mentoring
32 – Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump 
[ Marcelo Thomaz Cicala ]
145ª Edição - 2017 - ISSN 1677918-5 - Impresso no Brasil
Editor
Rodrigo Oliveira Spínola (rodrigo.devmedia@gmail.com)
Subeditor
Eduardo Oliveira Spínola
Consultora Técnica
Anny Caroline (annycarolinegnr@gmail.com)
Jornalista Responsável
Kaline Dolabella - JP24185
Capa e Diagramação
Romulo Araujo
Fale com o Editor! 
É muito importante para a equipe saber o que você está achando da 
revista: que tipo de artigo você gostaria de ler, que artigo você mais 
gostou e qual artigo você menos gostou. Fique a vontade para entrar 
em contato com os editores e dar a sua sugestão!
Se você estiver interessado em publicar um artigo na revista ou no site 
SQL Magazine, entre em contato com os editores, informando o título e 
mini-resumo do tema que você gostaria de publicar:
Rodrigo Oliveira Spínola - Editor da Revista
rodrigo.devmedia@gmail.com
RODRIGO OLIVEIRA SPÍNOLA
Editor Chefe da SQL Magazine, Mobile 
e Engenharia de Software Magazine. 
Professor da Faculdade Ruy Barbosa, 
uma instituição parte do Grupo DeVry. 
Doutor e Mestre em Engenharia de 
Software pela COPPE/UFRJ.
Assine agora e tenha acesso a 
todo o conteúdo da DevMedia:
www.devmedia.com.br/mvp
Atendimento ao leitor
A DevMedia possui uma Central de Atendimento on-line, onde você 
pode tirar suas dúvidas sobre serviços, enviar críticas e sugestões e 
falar com um de nossos atendentes. Através da nossa central também 
é possível alterar dados cadastrais, consultar o status de assinaturas 
e conferir a data de envio de suas revistas. Acesse www.devmedia
.com.br/central, ou se preferir entre em contato conosco através do 
telefone 21 3382-5038. 
Publicidade
publicidade@devmedia.com.br – 21 3382-5038 
Anúncios – Anunciando nas publicações e nos sites do Grupo DevMedia, 
você divulga sua marca ou produto para mais de 100 mil desenvolvedores 
de todo o Brasil, em mais de 200 cidades. Solicite nossos Media Kits, com 
detalhes sobre preços e formatos de anúncios.
EXPEDIENTE
Guia HTML 5Guia HTML 5
DEVMEDIA
http://www.devmedia.com.br/guias/guia-html/3
Um verdadeiro manual de referência
com tudo que você precisa sobre HTML!
6 SQL Magazine • Edição 145
Este artigo é destinado a profissionais que lidam com bancos de dados 
SQL Server diretamente e/ou tomam decisões técnicas relacionadas 
à implementação de soluções usando esta plataforma de gerencia-
mento de dados. O conteúdo abordará as novidades disponibilizadas 
na versão 2016 do produto com o intuito de expor suas melhorias e 
seus novos recursos.
Fique por dentro
SQL Server 2016: 
Conheça as novidades
Veja neste artigo uma análise sobre as 
funcionalidades apresentadas para a nova versão
As duas últimas versões do SQL Server (2012 e 2014) trouxeram algumas funcionalidades que mudaram bastante o cenário de implantação 
do produto, principalmente quando são consideradas 
características como performance e alta disponibi-
lidade. Nesse contexto, recursos como Always On 
Availability Groups, In-Memory Optimized Tables/
Procedures e Columnstore Indexes se tornaram, desde 
seus respectivos lançamentos, essenciais para garantir 
a ambientes críticos de bancos de dados os mais altos 
níveis de performance, disponibilidade e capacidade 
de gerenciamento. 
Dando sequência à evolução do produto, o time res-
ponsável pelo desenvolvimento da plataforma trabalhou 
para melhorar ainda mais os mecanismos já implemen-
tados e, claro, adicionar novas funcionalidades para que 
o SQL Server possa atender eficientemente diversos ce-
nários e casos de uso onde performance, confiabilidade 
e segurança são necessários junto ao gerenciamento de 
dados, não importando sua volumetria. 
Temporal Tables, Query Store e Stretch Databases são 
apenas três das novas funcionalidades presentes no 
SQL Server 2016. Além dessas, pode-se mencionar as 
melhorias feitas junto ao recurso In-Memory Optimized 
Tables/Procedures, cuja lista de pré-requisitos para 
implantação foi drasticamente reduzida, e também os 
ajustes nos Columnstore Indexes, que serão compatíveis 
com índices tradicionais do tipo Row Store a partir da 
versão 2016, limitação que bloqueava a adoção desse 
recurso em alguns cenários.
Outra novidade que se pode notar na versão 2016 do 
SQL Server é a abordagem de integração entre On-Premi-
ses e Cloud Computing. Essa ideologia, que já vem sendo 
aplicada há alguns anos pela Microsoft, tornou-se ainda 
mais forte, trazendo ao principal software de gerencia-
mento de dados novas funcionalidades que permitirão 
melhor aproveitamento dos recursos disponibilizados 
na plataforma de nuvem Windows Azure. 
Devido à amplitude do assunto em relação a cada uma das fun-
cionalidades aqui apresentadas, o objetivo desse artigo não será 
adentrar nos detalhes técnicos acerca de como as novas funciona-
lidades irão se comportar quando utilizadas, mas sim apresentar 
uma visão geral do que cada uma possibilitará, trazendo ao leitor 
um esboço expressivo da nova versão do SQL Server.
Stretch Databases
O primeiro recurso a ser apresentado neste artigo é denominado 
Stretch Databases. Seu objetivo é permitir o armazenamento de 
tabelas específicas no Azure SQL Database, fazendo com que 
objetos do mesmo banco de dados fiquem distribuídos entre o 
ambiente tradicional e a plataforma de nuvem. 
Basicamente, com a funcionalidade Stretch Databases será 
possível selecionar uma ou mais tabelas de um banco de dados 
armazenado localmente e indicar ao SGBD que esses objetos 
devem ser mantidos agora no Windows Azure, removendo-os do 
ambiente On-Premises e mantendo apenas uma referência lógica 
entre os dois locais.
Para as aplicações, essa divisão lógica do banco de dados é 
completamente transparente, visto que o próprio SQL Server é 
responsável por enviar e recuperar os registros do Azure SQL 
Database quando houver manipulações e/ou consultas sobre as 
tabelas armazenadas em nuvem.
Na camada de banco de dados, o funcionamento será relativamente 
simples: as tabelas selecionadas para usarem esse recurso ainda 
existirão no servidor local e ainda serão capazes de receber 
comandos DML normalmente, mas todos os registros serão 
Edição 145 • SQL Magazine 7 
assincronamente transferidos para o Azure SQL Database quando 
inseridos no objeto, diferente do que acontecerá quando comandos 
UPDATE, DELETE e SELECT forem executados, pois nesses casos 
os dados serão movimentados “em tempo real” para atender as 
requisições de forma transparente às aplicações, agregando à 
solicitação apenas o tempo necessário para a transferência na rede. 
A transferência dos dados entre os ambientes On-Premises e 
Cloud Computing acontece totalmente em background, através de 
tarefas internas do SQL Server que são as principais responsáveis 
por direcionar os registros para o Azure SQL Database e/ou solicitá-
los novamente quando uma query faz requisições de leitura. 
A Figura 1 ilustra a arquitetura básica do recurso Stretch 
Databases.
Figura 1. Arquitetura básica da funcionalidade Stretch Databases
O principal benefício dessa funcionalidade é a possibilidade 
de usar os recursos locais com maior eficiência, pois será 
significativamente mais fácil remover dados históricos e/ou 
raramente acessados do mesmo servidor onde a porção de dados 
mais ativa e importante ao negócio está localizada, permitindo 
assim melhor utilização dos discos, por exemplo.
Dados históricos costumamfazer com que os backups dos bancos 
de dados sejam maiores e, consequentemente, a janela de execução 
aumente drasticamente ao longo da vida útil da solução, além de 
prejudicarem a performance da seleção e manipulação de dados 
por parte das aplicações, pois na maioria das vezes, apenas uma 
pequena porcentagem dos registros armazenados em uma tabela 
é constantemente utilizada. 
Outro exemplo de dados acessados ocasionalmente são 
tabelas de auditoria, cuja funcionalidade geralmente é rastrear 
e armazenar ações de usuários ou aplicações para uma possível 
consulta depois. Normalmente, os dados de histórico mantidos 
por questões de auditoria são destinados a ajudar caso haja algum 
tipo de investigação a respeito de uma falha ou erro, ou mesmo 
para serem entregues a órgãos do governo conforme possíveis 
regulamentações existentes para aquele segmento de negócio. 
Os dois cenários mencionados anteriormente são apenas 
alguns dos exemplos que poderão ser beneficiados com a Stretch 
Databases, pois ambos lidam com uma porção dos dados que 
é acessada apenas ocasionalmente. Desse modo, armazená-los 
em um servidor de alta performance, com discos extremamente 
rápidos e que trazem consigo um custo elevado talvez não seja o 
melhor dos cenários quando o assunto é rentabilidade.
Outra vantagem ganha destaque quando consideramos questões 
financeiras, visto que o custo de armazenamento em nuvem é 
significativamente inferior ao valor exigido para a aquisição e 
manutenção de equipamentos de alta performance. É importante 
destacar que o custo, em ambientes tradicionais, não se refere 
apenas à aquisição de equipamentos, mas também à manutenção, 
contratação ou treinamento de pessoal qualificado para 
administrar, infraestrutura para instalação, entre outras coisas. 
Além disso, a implantação desse recurso é bastante simples. 
Basta possuir em mãos uma conta do Windows Azure, habilitar 
o banco de dados para Stretch e selecionar as tabelas desejadas, 
que precisam atender uma pequena lista de critérios, como não 
possuir constraints unique ou default. Depois disso, basta deixar 
que o próprio SQL Server provisione o ambiente em nuvem, caso 
ele não exista, e faça os demais ajustes. 
A principal desvantagem é a latência de rede existente entre 
os ambientes On-Premises e Cloud Computing, fator que deve 
ser considerado quando performance ainda é importante para 
consultas e manipulações dos dados armazenados no Azure SQL 
Database. Devido ao armazenamento das tabelas selecionadas 
ser realizado remotamente, em um Datacenter da Microsoft, 
consultas sobre os dados desses objetos tendem a necessitar um 
tempo superior ao que seria necessário caso estivessem localmente 
no servidor. 
Devido a essa desvantagem, é importante avaliar cuidadosamente 
quais tabelas indicar para o armazenamento remoto. Não é 
recomendado, por exemplo, aplicar esse recurso sobre uma tabela 
que seja crítica a uma aplicação LOB (Linha de Negócio, em inglês), 
pois cada transação efetuada precisará do tempo normalmente 
exigido e também do tempo agregado pela latência de rede, e esse 
aumento no tempo de execução pode não ser tolerável.
Query Store
O objetivo desse novo recurso é prover ao administrador de 
banco de dados a capacidade de analisar planos de execução 
retroativamente, assim possibilitando, por exemplo, a comparação 
de como uma determinada rotina tem sido executada pelo SQL 
Server ao longo de um determinado período, o que facilita a 
identificação de alguns tipos de problemas de performance.
Basicamente, o Query Store armazena planos de execução 
gerados pelo Query Optimizer e os disponibiliza para consulta 
através de uma nova interface gráfica adicionada ao Management 
Studio. Para isso, ele captura os planos de execução depois que o 
processo é executado e armazena no mesmo banco de dados onde 
a query foi disparada.
Assim como o recurso Stretch Databases, o Query Store é 
invisível às aplicações e pode ser considerado uma ferramenta de 
apoio a profissionais responsáveis pela identificação e resolução 
de problemas de performance mais voltados a rotinas e comandos 
T-SQL. 
SQL Server 2016: Conheça as novidades
8 SQL Magazine • Edição 145
A configuração do Query Store é muito 
simples. É necessário apenas habilitar 
a funcionalidade no banco de dados 
desejado e ajustar algumas configurações 
como, por exemplo, a quantidade de 
espaço em disco que será destinado ao 
armazenamento de planos de execução e 
o período de retenção dos planos dentro 
do banco de dados. 
Uma vez habilitado, o SQL Server 
passa a inserir uma cópia dos planos 
de execução na base de dados do Query 
Store e disponibilizá-los para consulta 
conforme a periodicidade configurada 
inicialmente. Esse ajuste e os mencionados 
anteriormente podem ser visualizados 
na janela de configuração do recurso, 
apresentada na Figura 2.
Depois de coletados e disponibilizados 
para consulta, os planos de execução 
podem ser acessados através de alguns 
relatórios presentes no SQL Server 
Management Studio, sob a aba Query 
Store, dentro do diretório do próprio banco 
de dados em questão. Por padrão, existem 
quatro relatórios, cada um permitindo 
que os dados sejam visualizados sob uma 
perspectiva diferente. 
A Figura 3 apresenta um dos relatórios 
disponíveis, onde é possível conferir, 
por exemplo, as 25 queries que mais 
consumiram recursos na última hora (canto 
superior esquerdo), além de permitir a 
visualização do plano de execução (canto 
inferior) e também de como diferentes 
planos de execução (caso exista mais de um) 
da mesma procedure têm se comportado no 
ambiente no mesmo período mencionado 
anteriormente (canto superior direito). 
Os relatór ios apresentados pelo 
Query Store ainda possibilitam que 
o administrador de banco de dados 
selecione um plano de execução que 
possui melhor desempenho dentre aquelas 
diferentes execuções de uma determinada 
procedure e solicitar ao SQL Server que 
fixe essa estratégia como caminho padrão 
que o produto deve utilizar para todas as 
chamadas da rotina. 
Essa ferramenta facilitará, ainda, a 
aplicação de outro recurso já existente 
nas versões anteriores da plataforma e 
conhecido como Plan Guides, que pode ser 
definido justamente como a fixação de um 
único plano de execução para um código 
T-SQL em específico, técnica que remove 
a autonomia do produto em definir a 
melhor estratégia durante a execução 
propriamente dita do processo e transfere 
ao administrador de banco de dados essa 
responsabilidade. 
Temporal Tables
As tabelas temporais são um novo tipo de 
objeto no SQL Server 2016 cujo objetivo é 
rastrear e armazenar as modificações feitas 
sobre os registros de uma determinada 
Figura 2. Configuração do recurso Query Store
Figura 3. Relatório de visualização do Query Store
Edição 145 • SQL Magazine 9 
tabela, possibilitando que os dados sejam consultados como se a 
query tivesse sido disparada em outro ponto específico no tempo, 
como uma semana atrás.
É válido destacar que esse recurso, diferentemente do SQL Server 
Audit, por exemplo, não tem como foco a identificação de quem 
fez uma ou outra modificação nos dados, mas sim o que mudou 
propriamente dito nos registros. O foco é totalmente direcionado 
ao histórico dos dados, não considerando questões de auditoria 
de segurança e/ou assuntos correlacionados. 
Seu funcionamento é bem simples: a tabela temporal é criada e 
referenciada junto à tabela que deverá ser rastreada. Depois disso, 
qualquer operação que altere ou exclua dados do objeto principal 
provocará uma inserção na tabela temporal, onde serão mantidas 
as versões anteriores dos registros que foram modificados, 
mantendo assim um histórico de todas as versões geradas para 
cada registro depois de sua implementação. 
Além disso, toda a implementação,assim como o funcionamento, 
é invisível às aplicações e usuários, não exigindo nenhuma 
modificação em códigos-fonte ou rotinas presentes em qualquer 
uma das camadas, mesmo nos bancos de dados. Isso acontece 
porque a aplicação desse recurso no banco de dados não exige 
modificações significativas nos objetos tradicionais, onde os dados 
são normalmente manipulados.
Basicamente, ao criar uma tabela temporal, comandos de 
manipulação de dados como UPDATEs e DELETEs (deve-se 
também considerar o MERGE, pois, na prática, ele atualiza ou 
exclui registros) efetuados sobre a tabela principal provocam um 
comando adicional, responsável por registrar a versão anterior 
do registro na tabela temporal, que acumulará todas as versões já 
existentes e não mais válidas dos dados que sofreram alterações 
ou exclusões.
Comandos INSERT não geram operação alguma na tabela 
temporal, visto que não criam uma versão anterior para ser 
considerada como histórico, mas sim incluem um novo registro no 
banco de dados. Já em relação aos comandos SELECT, a Microsoft 
adicionou quatro novos comandos para permitir a seleção dos 
dados históricos como se a query tivesse sido executada em 
outro horário/dia. Caso nenhuma dessas novas cláusulas seja 
usada, uma consulta convencional é realizada pelo SQL Server, 
retornando apenas os dados da tabela tradicional. A Figura 4 
ilustra o conceito aplicado nas tabelas temporais. 
Armazenar dados históricos da forma que as Temporal 
Tables provisionarão era, até então, uma tarefa árdua para 
o administrador, pois exigia a aplicação de outros recursos, 
complexos de implementar e, principalmente, de administrar, ou 
até mesmo dependiam de aquisição de ferramentas de terceiros, 
adaptações no banco de dados ou nas aplicações, o que tornava a 
necessidade de se manter esses dados um enorme desafio técnico. 
O principal ponto de atenção em relação a esse recurso é o volume 
de dados que pode ser gerado na tabela temporal depois de um 
período com essa funcionalidade habilitada. Como cada comando 
UPDATE e DELETE gera um novo registro no histórico, deve-se 
atentar ao tamanho do objeto por questões de administração e 
performance, pois uma tabela temporal associada a um objeto 
que sofra uma considerável quantidade de alterações pode gerar 
um volume de versões extremamente grande. 
Deve-se ressaltar que o volume de dados armazenados em 
uma tabela temporal não trará impactos na performance e no 
funcionamento convencional do ambiente em relação a operações 
de leitura na tabela principal, visto que nada muda para as 
tabelas onde os dados de produção estão armazenados. Contudo, 
operações que geram alteração de dados, como UPDATE e 
DELETE, causarão um comando adicional de INSERT no servidor, 
justamente para manter a versão histórica do registro que foi 
modificado.
Figura 4. Arquitetura básica das Temporal Tables
Adicionalmente, tarefas administrativas como backup, 
manutenção de índices, transferências de dados feitas por recursos 
como Always On Availability Groups tendem a considerar esses 
registros em suas operações, aumentando assim a quantidade de 
dados a serem processados por cada uma delas.
Para mitigar as dificuldades mencionadas, estratégias como 
particionamento de dados, combinadas com operações de 
Switch Partition, por exemplo, podem ser aplicadas e usadas 
constantemente para movimentar os dados históricos para tabelas 
separadas e/ou outros filegroups do banco de dados.
FOR JSON
O SQL Server 2016 traz agora suporte à cláusula FOR JSON, o que 
faz a nova versão do produto ser capaz de retornar dados nesse 
formato, facilitando a integração de dados entre a plataforma de 
banco de dados e as aplicações que consomem e transferem dados 
usando o padrão JSON (BOX 1).
É uma linguagem de comunicação similar ao XML que tem sido largamente adotada por web 
services para responder a requisições através da internet. É baseada em JavaScript, porém possui 
suas particularidades adaptadas justamente para possibilitar transferências de dados através da 
internet com menor tráfego de dados possível. 
BOX 1. JSON 
Basicamente, será possível consultar os dados usando a 
linguagem T-SQL e formatar os resultados em JSON, tornando 
viável o consumo e transmissão direto da informação sem 
necessidade de uma nova conversão por parte da aplicação, através 
de web services. 
SQL Server 2016: Conheça as novidades
10 SQL Magazine • Edição 145
No enta nto, a i nda não haverá a 
possibilidade de armazenar os dados no 
formato JSON, nem haverá uma grande 
variedade de comandos específicos para 
manipular dados nesse modelo, como é 
possível fazer quando se trabalha com 
dados armazenados dentro do SQL Server 
em XML.
Os comandos já divulgados pela 
Microsoft incluem conversões automáticas 
de dados tabulares para o formato JSON, 
como pode ser visto na Figura 5, além da 
possibilidade de se realizar o inverso, ou 
seja, ler dados provindos desse modelo e 
manipulá-los como registros tabulares, 
como é comumente feito no SQL Server. 
Live Query Statistics
Mais uma novidade presente no SQL 
Server 2016 será a possibilidade de 
visualizar um plano de execução em 
tempo real. Na prática, esse recurso 
permitirá que um administrador de 
banco de dados acompanhe como o SQL 
Server está conduzindo uma determinada 
query durante seu processamento em 
tempo real. 
Nas versões anteriores, haviam duas 
possibilidades quando o assunto era 
visualizar um plano de execução de 
uma query: estimado e real. O primeiro 
deles é baseado em uma estimativa 
do SQL Server de como seria a melhor 
forma de se atender à requisição feita 
pelo usuário sem mesmo processá-la, 
enquanto o segundo corresponde ao plano 
de execução efetivamente utilizado para 
concluir aquela solicitação. 
Naturalmente, o plano de execução 
estimado é mais rápido para ser obtido, 
visto que não há necessidade de o 
SQL Server processar a consulta. Em 
contrapartida, o plano estimado pode 
não ser fidedigno à estratégia que seria 
escolhida pelo produto em uma situação 
onde a query fosse realmente processada. 
O plano de execução real, por sua vez, 
é totalmente confiável em relação ao 
que foi escolhido pelo SQL Server no 
processamento da query. Adicionalmente, 
retorna informações complementares 
que podem ser obtidas apenas quando 
a consulta é processada de fato. Sua 
desvantagem é a necessidade de aguardar 
a conclusão da consulta, o que muitas 
vezes demanda muito tempo ou em alguns 
ambientes não é possível devido a i números 
fatores ou situações adversas.
É na questão tempo exigido para se 
obter o plano de execução real que esse 
novo recurso se encaixa perfeitamente, 
pois será possível analisar as etapas 
que o SQL Server está conduzindo para 
entregar os dados solicitados antes mesmo 
do processo ser concluído, inclusive 
acompanhando como o volume de dados 
está sendo processado por cada um dos 
operadores. 
A Figura 6 ilustra o resultado obtido 
através do uso desta nova funcionalidade: 
um plano de execução “vivo”, ainda em 
processamento, onde o responsável pela 
análise pode visualizar não apenas a 
estratégia de execução do SQL Server, 
mas também o percentual de conclusão de 
cada operador, entre outras informações 
adicionais.
Backup to Azure
Não é segredo que o assunto backup seja 
uma das principais responsabilidades de 
qualquer profissional cuja posição inclua 
cuidar de um ambiente de banco de 
dados. Essa questão é tão importante que 
são inúmeros os cuidados que devem ser 
tomados com os arquivos resultantes desse 
processo. Uma vez que serão requisitados, 
na maioria das vezes, em momentos 
críticos, de grande tensão e preocupação 
de todos, principalmente das pessoas 
responsáveis pelo negócio da empresa, 
como coordenadores, diretores, presidente, 
etc., é imensurável a importânciade tais 
arquivos.
Backups são normalmente gerados em 
disco e depois copiados para um dispositivo 
de armazenamento a longo prazo, como 
uma fita. Em alguns ambientes, são feitos 
diretamente para a fita ou mesmo para 
um servidor munido de um software 
gerenciador de backups, que automatiza 
boa parte do trabalho, como o módulo 
Data Protection Manager do System Center, 
conjunto de soluções de administração de 
ambientes corporativos da Microsoft. 
Armazenar arquivos de backup fora 
de um ambiente On-Premises, onde Figura 6. Visualização do plano de execução em tempo real
Figura 5. Suporte nativo a JSON
Edição 145 • SQL Magazine 11 
são necessários equipamentos adequados, sala cofre, proteção, 
segurança de acesso, entre outros requisitos, não é um conceito 
totalmente novo, visto que alguns fornecedores de soluções 
voltadas para Cloud Computing disponibilizam serviços 
dedicados ao armazenamento de dados críticos. Para tal, 
oferecem todos os requisitos mencionados anteriormente com 
um custo inferior ao normalmente investido pelas empresas no 
provisionamento dessa infraestrutura.
Uma das novas funcionalidades do SQL Server 2016 endereça 
justamente esse novo cenário: a realização de backups e restores 
de forma integrada ao armazenamento de dados em nuvem. Essa 
nova capacidade introduzirá maior facilidade em questões como 
segurança dos backups, garantia de disponibilidade e até mesmo 
maior flexibilidade em projetos de migração.
A partir da versão 2016 é possível realizar backups e armazenar 
os arquivos diretamente em um espaço de armazenamento do 
Windows Azure, assim como restaurar bancos de dados usando 
a mesma premissa. Isso facilita consideravelmente atividades de 
recuperação de desastres, pois os arquivos de backup estarão 
salvos e acessíveis independentemente do tipo de problema que 
venha a ocorrer, mesmo que um dos datacenters da Microsoft 
fique indisponível, pois cada arquivo é replicado para mais duas 
localizações visando garantir total proteção dos dados. 
Outra atividade que se beneficiará desse novo recurso é a 
migração de bancos de dados entre dois ambientes On-Premises 
que estejam conectados por uma rede de baixa performance, mas 
possuam uma conexão mais rápida com a internet, por exemplo. 
Nesse cenário, será possível realizar o backup diretamente para o 
ambiente Cloud Computing e depois restaurar o banco de dados 
para o segundo servidor usando o mesmo arquivo, provindo do 
Windows Azure.
Outro cenário onde esse recurso trará benefícios será a migração 
entre um ambiente On-Premises e outro hospedado na plataforma 
Windows Azure, visto que o mesmo processo apresentado no 
cenário anterior poderá ser aplicado.
Para efeitos de comparação, essa estratégia de cópia e migração 
de bancos de dados através de backups possui uma pequena 
diferença em relação às versões anteriores, onde é necessário 
realizar os backups localmente ou em um diretório de rede, e 
depois fazer o upgrade para o Windows Azure. Com o SQL Server 
2016, o tempo poderá ser poupado, visto que apenas uma tarefa, 
a execução do backup propriamente dita, será necessária para 
atingir o mesmo objetivo.
Always Encrypted 
Pensando na segurança dos dados, a Microsoft introduziu 
no SQL Server 2016 uma funcionalidade que permitirá que 
aplicações se comuniquem com a plataforma de banco de dados 
e estabeleçam uma transmissão de dados entre eles onde apenas 
informações criptografadas poderão ser enviadas e recebidas. 
Essa funcionalidade permitirá que a aplicação manipule os 
dados em formato legível e no momento do envio deles para o 
banco de dados, providencie sua criptografia, transferindo-os 
na rede já protegidos por algoritmos avançados de segurança. 
O banco de dados, por sua vez, receberá os dados criptografados 
e os armazenará da mesma forma, ilegíveis. Por fim, quando a 
aplicação solicitar ao SQL Server que responda a uma requisição 
de dados, a plataforma de banco de dados enviará os dados 
ainda criptografados para a camada de apresentação, que será 
responsável por exibi-los em seu formato tradicional. Essa 
arquitetura pode ser conferida na Figura 7. 
Figura 7. Arquitetura básica de funcionamento do Always Encrypted
O objetivo principal desse recurso é evitar que dados 
sejam acessados, roubados e interpretados por pessoas mal-
intencionadas que consigam acesso à rede de dados da empresa. 
Caso haja uma violação de segurança e o atacante consiga capturar 
qualquer informação que esteja sendo transferida entre a aplicação 
e o banco de dados, ele não conseguirá compreendê-los, visto 
que somente a aplicação, através de sua camada de acesso ADO, 
pode remover a criptografia e disponibilizar os dados no formato 
“clear text”. 
Dynamic Data Masking
Essa nova funcionalidade do SQL Server 2016 tem a capacidade de 
proteger dados armazenados em colunas específicas mascarando 
os valores para que usuários sem o privilégio necessário não 
tenham acesso a determinadas informações mesmo que ainda 
possam executar comandos sobre aquele objeto, tudo isso de 
forma totalmente transparente para as aplicações.
O Dynamic Data Masking pode ser aplicado tanto em uma nova 
tabela quanto sobre uma já existente, através de novas cláusulas 
T-SQL adicionadas ao produto para configurar como os dados 
serão mascarados. Cada coluna recebe sua própria configuração, 
ou seja, uma tabela com 10 colunas pode ter algumas com essa 
funcionalidade e outras sem.
Embora a conf iguração seja feita sobre cada coluna 
individualmente, para visualizar os dados legivelmente é 
necessário possuir a permissão UNMASK sobre aquela tabela. 
SQL Server 2016: Conheça as novidades
12 SQL Magazine • Edição 145
Todos os usuários que não possuírem esse direito, ao selecionar 
os dados do objeto, receberão os valores das colunas onde há o 
Dynamic Data Masking configurado como valores mascarados, 
conforme a especificação feita na implantação da funcionalidade. 
É importante destacar que os dados permanecem armazenados 
sem máscara na tabela e nos índices existentes, sendo mascarados 
apenas no momento da entrega do result set ao usuário solicitante 
que não possuir a permissão necessária. 
A Figura 8 ilustra a arquitetura geral dessa funcionalidade, 
onde é possível visualizar a diferença entre os resultados obtidos 
para dois diferentes grupos de usuários. O primeiro deles com 
permissão de SELECT sobre o objeto, mas sem direito a visualizar 
o valor contido naqueles campos, e o segundo, com direito total 
a consultar e ver os dados de toda a tabela. 
Figura 8. Arquitetura da funcionalidade Dynamic Data Masking
Row-Level Security 
Pode-se definir como objetivo desse recurso a capacidade de 
conceder permissões de segurança aos usuários conforme o 
resultado de uma função, que atuará como critério de validação 
para liberar ou não consultas e operações de manipulação de 
dados a quem estiver tentando alguma dessas operações. Tudo 
isso dinamicamente, sem que aplicações ou os próprios usuários 
notem alguma diferença nas queries executadas por eles. 
Na prática, essa funcionalidade foi projetada para permitir 
aos administradores de banco de dados a implementação de 
segurança baseada em predicados personalizados, que podem 
inclusive ser dinâmicos conforme a lógica inserida dentro da 
função responsável por essa classificação, em uma arquitetura 
bem similar a outro recurso: o Resource Governor. 
Sua arquitetura é constituída por dois objetos: uma função de 
classificação e uma política de segurança (denominada security 
policy na arquitetura oficial da funcionalidade), objeto novo 
disponibilizado no SQL Server 2016 para associar uma função a 
uma ou mais tabelas, podendo inclusive ser considerada como 
um container de associações.
A função de qualificação é responsável por receber um ou 
mais parâmetros, provindos da tabelaassociada na política de 
segurança, e avaliar, a cada comando SELECT efetuado, quais 
serão os dados que aquele usuário poderá visualizar. Com base 
nisso, o próprio SQL Server adicionará o filtro equivalente nos 
operadores do plano de execução e assim retornar apenas os 
registros classificados como acessíveis para o devido usuário.
A Figura 9 apresenta a arquitetura básica da funcionalidade 
Row-Level Security simulando um cenário de vendas onde 
dois grupos de colaboradores (gerentes e diretores) possuem 
diferentes permissões em relação a quais dados podem ou não 
serem visualizados. 
Figura 9. Arquitetura básica da funcionalidade Row-Level Security
No exemplo em questão, os diretores possuem permissão total 
de visualização. Assim, quando disparam uma consulta à tabela 
de vendas, receberão como resultado todos os dados existentes 
naquela tabela. Por outro lado, os gerentes, ao executarem a mesma 
consulta, poderão ver apenas as vendas relativas ao território 
correspondente à sua unidade. 
A lógica por trás da classificação de quais dados um usuário 
específico pode visualizar fica dentro da função, que é 
desenvolvida pelo profissional responsável pelo ambiente e 
associada a uma tabela através do container Security Policy. Dentro 
desse objeto as possibilidades são praticamente incontáveis. Um 
exemplo é a possibilidade de determinar a permissão baseada 
em diversos critérios, como o ID do departamento onde aquele 
colaborador está alocado, restringindo-o, nesse caso, a visualizar 
apenas os dados do setor em questão. 
Cada uma das novidades do SQL Server 2016, tanto as novas 
funcionalidades quanto as melhorias feitas pela Microsoft junto 
a alguns dos recursos já existentes, reforçam o comprometimento 
da empresa em tornar o produto melhor, atendendo a cenários 
com a maior robustez e eficiência possíveis, independente da 
criticidade e volumetria de dados a ser processada.
Além de todas as funcionalidades apresentadas neste artigo, 
vale destacar que diversos recursos já existentes no produto 
desde versões anteriores receberam melhorias, algumas delas 
significativas, como no caso do In-Memory Optimized Tables 
Edição 145 • SQL Magazine 13 
and Procedures, mencionado no começo deste artigo, além 
de outras alterações que foram aplicadas na versão 2016 que 
superficialmente passam aos olhos, mas em cenários específicos 
podem fazer total diferença.
Os investimentos feitos pela Microsoft na plataforma de 
dados baseada em SQL Server (tanto On-Premises quanto 
Cloud Computing, através do Azure SQL Database) colocaram 
recentemente o produto como líder no quesito segurança, por 
exemplo, entre todos os fornecedores de sistemas gerenciadores 
de bancos de dados segundo o Gartner, instituto responsável por 
diversos testes e benchmarks, entre outras coisas relacionadas à 
tecnologia da informação. 
Essa marca apenas comprova a evolução que o SQL Server vem 
tendo em suas versões mais recentes. Novas funcionalidades, 
novos conceitos, inúmeras melhorias e novas possibilidades 
Felipe de Assis
felipe.assis@outlook.com / http://felipedba.wordpress.com
É especialista SQL Server na DataEX, empresa Gold Partner 
Microsoft especializada na prestação de serviços para a plata-
forma de dados Microsoft. É certificado desde a versão 2005 do produto 
e atualmente é MCSE Data Platform nas versões 2012/2014. Colabora 
eventualmente com artigos para a revista SQL Magazine e é um dos palestrantes do SQL 
Server PASS no Brasil, tendo participado do último SQL Saturday, realizado no mês de 
novembro de 2015 em Brasília, onde falou sobre SQL Server 2016: as Temporal Tables. 
Autor
destacaram o produto como uma solução corporativa de dados 
completa e muito abrangente, capaz de ser implementada nos 
mais diferentes cenários onde a gestão de dados é primordial.
14 SQL Magazine • Edição 145
São inúmeras as situações em que o administrador de dados ou sys- 
admin não dispõe de métricas eficazes para obter a melhor implemen-
tação e performance do banco de dados. Algumas vezes isso decorre 
até da falta de informação sobre as potencialidades oferecidas pelo 
sistema. Para os que optaram pelo PostgreSQL em sua organização, 
apresentamos nesse artigo diversos aspectos técnicos, do hardware ao 
software, da instalação ao tuning, do sistema gerenciador de banco de 
dados ao sistema operacional, das formas de instalação à configuração 
do cluster, visando auxiliar na tarefa de implantação para que o leitor 
obtenha o melhor resultado em sua instalação.
Fique por dentro
Trabalhando com o 
PostgreSQL: 
implementação do
banco de dados
Domine os aspectos de uma implementação em 
ambiente de produção
A produção e interpretação de dados relacionados às atividades humanas sempre fomentou a evolução da tecnologia que atualmente cons-
titui como um dos pilares da civilização moderna. Na 
década de 70, no séc. XX, não era diferente. Os setores 
comercial e industrial da época já manifestavam o in-
teresse em obter acesso mais rápido aos seus registros 
em decorrência do armazenamento convencional de 
arquivos, bem como reduzir a redundância e inconsis-
tência dos dados até que fosse possível compartilhá-los, 
garantindo certo nível de segurança. Apostando nesse 
mercado, a IBM (International Business Machines) iniciou 
suas pesquisas determinada a oferecer uma ferramenta 
que atendesse à nova demanda. Durante as pesquisas, 
alguns modelos de bancos de dados foram explorados, 
dentre eles, o hierárquico, de rede e relacional. Nesse 
contexto, o modelo de dados deve ser entendido como 
o conjunto de ferramentas conceituais para a descrição 
de dados, seus relacionamentos, semântica e restrições 
que mantenham a consistência. Esses modelos se sub-
dividem em três grupos: lógicos baseados em objetos, 
lógicos baseados em registros e modelo físicos de dados.
Retornando à pesquisa da IBM, ela batizou o resultado 
do projeto inicial com o nome System R, visionário na 
implementação de sistemas de bancos de dados rela-
cionais e na implementação da SQL (Structured Query 
Language), embora não fosse integralmente fiel ao pro-
posto por Codd em suas regras para o modelo relacional. 
Na época seus autores divulgaram generosamente os detalhes de 
seu objeto de estudos à comunidade por meio de artigos, o que 
encorajou dois pesquisadores de Berkley, os cientistas Michael 
Stonebraker e Eugene Wong, a desenvolverem o próprio projeto 
de banco de dados relacional. Ambos já haviam arrecadado fun-
dos com o projeto INGRES (Interective Graphics Retrieval System) 
- um banco de dados geográfico que fora desenvolvido para um 
grupo econômico, implementado por um grupo de estudantes e 
financiado por órgãos de pesquisa do governo norte-americano - o 
que garantiria subsídio ao início do projeto. Do projeto "INGRES" 
derivaram outros sistemas de bancos de dados, tais como SQL 
Server, Sybase e o próprio PostgreSQL.
O projeto Postgres foi iniciado em 1986 na Universidade de 
Berkley, e sua proposta era suprir as dificuldades impostas pelo 
Edição 145 • SQL Magazine 15 
INGRES, como a manipulação de tipos definidos pelo usuário 
(hoje conhecidos como objetos) pelo sistema relacional e a de gran-
de volume de armazenamento. Através dessa lógica, o nome Post-
greSQL veio da aglutinação de "post-INGRES" (após o INGRES).
Em 1994 o PostQUEL, que era a linguagem nativa para interpre-
tação de consultas implementada pelo Postgres, foi substituída 
pelo interpretador SQL. Esse foi um marco importante na história 
do projeto, que após isso passou a ser chamado PostgreSQL.
PostgreSQL
O PostgreSQL utiliza o modelo lógico baseado em registros atra-
vés do modelo relacional. Adicionalmente, o projeto atual permite 
manipulações diretas análogas ao conceito da orientação a objetos, 
como herança, polimorfismo e o uso de objetos.É distribuído 
sob a licença BSD (Berkley Software Distribution), que caracteriza 
o software como pertencendo ao domínio público e, portanto, 
livremente personalizável, exigindo somente o reconhecimento 
autoral para a distribuição do binário, versão original ou alterada. 
Essa licença é muito permissível e viabiliza o funcionamento de 
softwares em diferentes formatos de licença trabalhando juntos. 
Não se pode dizer que a licença BSD é necessariamente melhor 
que a licença "concorrente", GPL (Gnu General Public License). 
A questão é que a primeira é menos restritiva. Note que tanto a 
licença BSD quanto o Postgres tiveram origem no mesmo local, a 
Universidade de Berkley.
A norma ACID, acrônimo para os conceitos de Atomicidade, 
Consistência, Isolamento e Durabilidade, que representam os 
critérios básicos para transações e recuperações a falhas, é inte-
gralmente implementada pelo PostgreSQL. Essas propriedades 
asseguram aspectos essenciais para a qualidade das operações, 
tais como controle de integridade referencial e de concorrência de 
multi-versão, ou MVCC (Multiversion Concurrency Control), além 
de ser premissa do modelo relacional.
Existem muitas implementações maduras no PostgreSQL, tais 
como replicação síncrona e assíncrona, segurança SSL(Secure So-
cket Layer) e criptografia nativos, utilização de clusters de dados, 
operação com multithreads, multiplataforma, suporte a 24 idiomas, 
backups PITR (Point-In-Time Recovery), áreas de arma-
zenamento (tablespaces), pontos de salvamento (save-
poins), subconsultas e visões atualizáveis, controle 
de locking, suporte à definição de funções em PL/
PgSQL, Perl, Python, Ruby, dentre outras lingua-
gens de programação, foreign data-wrappers, alerta 
e failover automatizado para ambientes de missão 
crítica, além de inúmeras características vantajosas. 
Existe também o interesse crescente da comunidade 
em apoiar o desenvolvimento do software através 
de contribuições e patrocínios, como é o caso da 
Hewlett-Packard, VMware, EnterpriseDB, Fujitsu, 
Google, Skype e RedHat, para citar algumas. Por 
isso, o PostgreSQL é frequentemente apontado como 
ótima opção em projetos que demandem estabi-
lidade e facilidade de uso conjugada a boa curva 
de aprendizado, visto que ostenta a reputação de ser o sistema 
gerenciador de banco de dados de código livre mais sofisticado 
disponível. Como dito anteriormente, o PostgreSQL é um projeto 
comunitário, open source, coordenado pelo PostgreSQL Global De-
velopment Group, e que não limita o escopo de suas capacidades, 
recursos e funcionalidades aos usuários, ou seja, está totalmente 
à disposição para qualquer um, a qualquer momento.
Um aspecto que corrobora o alinhamento às demandas mais 
atuais, como a disponibilidade de serviços em nuvem e a escala-
bilidade vertical, é o fato de que o PostgreSQL não é somente re-
lacional: é multi-paradigma, agregando também funcionalidades 
No-SQL (Not Only SQL). Aqui temos um bom exemplo de como 
o uso de extensões pode ampliar a gama de possibilidades do 
sistema. O PGXN (PostgreSQL Extension Network) é um sistema de 
distribuição central para bibliotecas open source de extensão para 
o PostgreSQL. De forma similar, também existe o pgFoundry, que 
reúne diversos projetos relativos ao PostgreSQL.
A maioria das linguagens de programação modernas proveem 
interface nativa para conexões com o PostgreSQL. Como alterna-
tiva em caso de ausência, há disponibilidade de acesso através 
da interface ODBC (Open Database Connectivity), que permitiriam 
acesso por produtos da Microsoft como Access e Excel. Aplicações 
desenvolvidas em Java contam com o suporte oferecido pelo driver 
JDBC (Java Database Connectivity). Já para o PHP, teríamos o PDO 
(PHP Data Objects) como opção. O acesso aos dados ocorre de vá-
rias formas, podendo ser através de linha de comando (psql), pelo 
código SQL embutido na aplicação/framework, por cliente gráfico 
(pgAdmin III) ou via navegador web (phpPgAdmin), por exemplo.
Arquitetura e versionamento
Outro ponto forte do projeto é a sua arquitetura cliente-servidor 
(ver Figura 1), fazendo com que o acesso aos dados seja distribuído 
a clientes que não os acessam diretamente mas via processo no 
servidor (postmaster), mesmo que o cliente esteja acessando na mes-
ma máquina onde o serviço está instalado. A seguir, uma forma 
simplificada de como esse acesso se dá, através de TCP/IP (Transfer 
Control Protocol/Internet Protocol) ou Internet, via tunelamento.
Figura 1. Arquitetura cliente-servidor implementada no PostgreSQL
Trabalhando com o PostgreSQL: implementação do banco de dados
16 SQL Magazine • Edição 145
A Tabela 1 indica algumas das limitações para as capacidades 
de armazenamento.
Antes de iniciarmos nossas considerações sobre a instalação 
do PostgreSQL, explicaremos o controle de versão praticado em 
suas distribuições. Suas atualizações encontram-se disponíveis 
no site oficial do PostgreSQL, e o número da versão já nos indica, 
de imediato, o que podemos encontrar. Esse número deve ser 
decomposto em duas partes: a primeira delas nos dará a versão 
majoritária e a segunda, a versão minoritária. A versão majoritária 
representa um marco evolutivo do sistema, composto por novas 
funcionalidades e revisões e é identificada pelos dois primeiros 
números da versão. Já a versão minoritária se refere a atualizações 
de segurança e correções de bugs, sinalizada pelo último número 
da versão. O clico de vida da versão majoritária é de cerca de cinco 
anos, com versões lançadas a cada ano, enquanto isso, disponibili-
zações de atualizações de versões minoritárias são lançadas quase 
que mensalmente. A inclusão de novas funcionalidades costuma 
ser amplamente discutida em listas internacionais, como a wiki 
do Roadmap, debatidas e aprovadas durante a PgCon, evento que 
ocorre no Canadá anualmente.
Por exemplo, à época da publicação desse artigo, a versão estável 
mais atual disponível era a 9.5.3. Isso significa que tínhamos à dis-
posição todas as funcionalidades da versão 9.5 e também estávamos 
à terceira revisão de segurança e correções de erros para a série.
Atualizações minoritárias do cluster nunca mudam o formato 
de armazenamento interno e são sempre compatíveis. É possível 
apenas substituir os executáveis enquanto o sistema estiver pa-
rado e reiniciar o cluster após isso. Por exemplo, a versão 8.42 é 
compatível com a 8.4, 8.4.1 e 8.4.6. As atualizações minoritárias 
não alteram o diretório de dados e são simples assim. Contu-
do, atualizações majoritárias sujeitam o PGDATA, diretório de 
dados anteriormente citado, a alterações. Isso torna o processo 
mais sensível. O método tradicional para esse procedimento é a 
realização do dump, utilizando o pg_dump ou pg_dumpall a partir 
da versão mais recente, a fim de compatibilizar os recursos, na 
versão anterior, com consequente restore na versão mais atual. No 
entanto, essas não são as únicas opções seguras de atualização
A partir da série 8.4.x foi possível migrar um cluster da série 
8.3.x para a mais recente, de forma direta, sem a necessidade de 
dump-restore, através do aplicativo pg_migrator, que mais tarde 
teve seu nome alterado para pg_upgrade e atualmente encontra-
-se estável. Sua performance é notadamente mais rápida que a 
forma tradicional, além de evitar que espaço adicional em disco 
seja necessário para a migração. 
Também é possível usar certos métodos de replicação, como 
Slony, para criar um servidor de espera (stand by) com a versão 
atualizada do PostgreSQL. Isso é possível porque Slony suporta 
replicação entre diferentes versões majoritárias do PostgreSQL. A 
espera pode estar no mesmo computador ou em um computador 
diferente. Uma vez que tenha sincronizado com o servidor master 
(executando a versão mais antiga do PostgreSQL), você pode alter-
nar mestres e encerrar a instância de banco de dadosmais antigo.
 De forma geral, os itens atualizados são os relativos à adminis-
tração, SQL, API da biblioteca, catálogos do sistema e o servidor 
API (Application Programming Interface) da linguagem C.
É altamente aconselhável que sejam consultadas as notas de 
lançamento da versão antes de proceder as atualizações. Mesmo 
que o processo seja crítico, é melhor realizá-lo do que expor seus 
dados a vulnerabilidades já conhecidas.
Caso o fornecedor do seu sistema operacional não forneça 
atualizações de segurança da série utilizada por você, considere 
utilizar os pacotes de atualização disponíveis no site oficial do 
PostgreSQL. No entanto, se não existir pacote disponível de qual-
quer forma, considere compilar o PostgreSQL diretamente no seu 
ambiente com as suas preferências e otimizações.
Recursos de hardware
Até esse ponto já abordamos vários aspectos que justificariam 
a escolha do PostgreSQL como sistema gerenciador de bancos de 
dados como opção. Agora iremos abordar questões relacionadas 
ao hardware. Esse é um aspecto central que pode conduzir ao su-
cesso ou fracasso da implementação. Prioritariamente, devemos 
conhecer os discos e controladoras, memória RAM (Random Access 
Memory), CPU (Central Processing Unit) e rede. Nos adiantando um 
pouco mais, inclusive a opção pelo sistema de arquivos é deter-
minante. Torna-se obrigatório avaliar informações técnicas tais 
como estatísticas, relatórios de benchmarks e consultar a opinião 
de pessoas cuja experiência poderia contribuir. Alta performance 
em bancos de dados só é obtida com domínio sobre o hardware.
Hard Disk
Por ser o recurso mais consumido pelo SGBD, iniciaremos nossa 
discussão pelos discos e controladoras. Ao escolher um HD (Hard 
Disk), você certamente se atenta à sua capacidade de armazena-
mento, à sua interface e, provavelmente, ao tamanho do cache, 
afinal, essas são as informações que acompanham a descrição do 
produto. Mas há outros parâmetros ligados ao desempenho do 
dispositivo que também devem ser observados. Os mais conhe-
cidos são: seek time, latency time e access time.
O seek time, em tradução livre, tempo de busca, normalmente 
indica o tempo que a cabeça de leitura e gravação leva para se 
deslocar até uma trilha do disco ou mesmo de uma trilha a ou-
tra. Quanto menor esse tempo, melhor o desempenho, é claro. 
Esse parâmetro pode ter algumas diferenciações, sendo que sua 
divulgação varia de fabricante para fabricante. Latency time é a 
Tamanho máximo de um banco Ilimitado
Tamanho máximo de uma tabela 32 TB
Tamanho máximo de uma linha 1,6 TB
Tamanho máximo de um campo 1 GB
Tamanho máximo de linhas por tabela Ilimitado
Tamanho máximo de colunas por tabela
De 250 a 1.600, dependendo dos 
tipos de dados utilizados
Tamanho máximo de índices por tabela Ilimitado
Tabela 1. Capacidade de armazenamento
Edição 145 • SQL Magazine 17 
medida que indica o tempo necessário para que a cabeça de lei-
tura e gravação se posicione no setor do disco que deve ser lido 
ou mesmo gravado. Esse parâmetro sofre influência do tempo 
de rotação dos discos (atualmente de 5.400, 7.200 e 10.000 RPM / 
Rotações Por Minuto) e também é informado em milissegundos. 
O access time, corresponde a um cálculo que combina os parâme-
tros de latency time e seek time. Em termos práticos, o access time 
indica o tempo necessário para se obter uma informação do HD. 
Novamente, quanto menor esse tempo, melhor.
RAID
Além de meramente armazenar dados, soluções de armazena-
mento têm que prover acesso à informação de maneira eficiente, 
em tempo hábil e, dependendo do caso, oferecendo algum tipo 
de proteção contra falhas. É nesse ponto que os sistemas RAID 
(Redundant Array of Independent Disks) apresentam sua utilidade: 
segurança, capacidade e desempenho. Dessa forma, um arranjo 
ou sistema RAID é a aglomeração física de HDs representados 
por uma unidade lógica de armazenamento. Podemos entender 
a unidade lógica como o dispositivo que o sistema operacional 
identifica como apto ao armazenamento secundário, independen-
temente da quantidade de dispositivos em uso e da tecnologia do 
disco. Os benefícios são muitos. Para mencionar alguns, temos:
• Em caso de falha de um dos discos, os dados existentes nele 
não serão perdidos, pois podem ser replicados em outra unidade 
(redundância);
• É possível elevar o volume de armazenamento a qualquer mo-
mento com a adição de mais HD;
• O acesso à informação pode se tornar mais rápido, pois os dados 
são distribuídos em todos os discos;
• Dependendo do caso, há maior tolerância a falhas, pois o sistema 
não é paralisado se uma unidade parar de funcionar;
• Um sistema RAID pode ser mais barato que um dispositivo de 
armazenamento mais sofisticado e, ao mesmo tempo, oferecer 
praticamente os mesmos resultados.
Múltiplos discos RAID separados por tabelas e índices muito 
acessados são importantes. Outro ponto importante é optar por 
controladoras RAID que disponham de baterias. Isso viabiliza o 
uso de seu cache de forma mais eficiente e segura. Há disponi-
bilidade de diferentes tipos de arranjos que visam atender com 
flexibilidade as necessidades dos administradores de dados e 
sysadmins em RAID. Não serão citadas aqui as opções porque 
fogem ao escopo do artigo.
Memória RAM
No caso da análise dos requisitos para a aquisição de memória 
temos um cenário mais enxuto: quanto mais, melhor. Porém, como 
sempre há uma reserva, tem-se que devemos dar preferência às 
que possuem correção de erro com registradores, ou ECC (Error 
Correction Check). A principal característica desse tipo de memória 
em relação às comuns é a presença de bits adicionais. Contudo, 
vale ressaltar que a correção de erro não ocorre semente na me-
mória RAM: em discos rígidos, mídias digitais, CD, DVD, etc. A 
estratégia é simples: “o que foi escrito foi exatamente o que foi 
gravado?”. Caso sim, ok; caso contrário, uma nova tentativa de 
resposta positiva é feita. Em caso de sucessivos erros de integri-
dade da informação, o setor defeituoso é marcado como badblock 
e isolado no sistema de arquivos para que outras tentativas de IO 
(input-output) não ocorram ali. Em tempo, caso esteja trabalhando 
em um sistema OLTP (On-Line Transaction Processing), saiba que a 
demanda por memória principal não será tão grande quanto em 
um sistema OLAP (On-Line Analytical Processing), onde a produção 
de relatórios, estatísticas e transformações podem exigir mais que 
em um ambiente de transações rápidas, que é o caso do primeiro.
CPU
A CPU é o componente do hardware que, em última instância, 
realiza as tarefas solicitadas pelos programas. Existe um proces-
so denominado escalonamento, ou scheduling, responsável por 
verificar qual processo é mais prioritário e possui menor tempo 
de realização, possibilitando a execução de processos concor-
rentes e viáveis. O escalonamento com múltiplos processadores 
não é linear. Nesse caso, poucos processadores rápidos são mais 
eficientes que muitos processadores lentos. Contudo, o banco de 
dados tira proveito de ambientes multiprocessados através da 
implementação de processadores de 64 bits e clock elevado. Para o 
PostgreSQL, a visibilidade entre processos é importante, portanto, 
quanto mais núcleos no mesmo local, melhor será a comunicação 
entre eles. Por isso, processadores com múltiplos núcleos são 
muito bem recebidos.
Memória compartilhada e semáforos
Apesar de admitir que o PostgreSQL busca atender a uma vasta 
gama de necessidades, partiremos do princípio de que o leitor 
visa usá-lo em ambientes de grande porte. Nesse caso, é razoável 
imaginar que, a princípio, o software esbarre em alguns limites 
do sistema operacional. Semáforos - utilizados como método de 
sincronização entre processos - e memória compartilhada - área de 
memória em que um contexto processual pode trabalhar - frequen-
temente necessitamde ajustes. Alguns outros pontos como limite 
de processos por usuário, quantidade de arquivos abertos simul-
taneamente por processo e a quantidade de memória disponível 
para esse podem afetar o funcionamento do PostgreSQL, já que 
ele utiliza a memória compartilhada e semáforos para realizar a 
comunicação inter processos baseando-se no modelo System V IPC 
(lê-se "System Five") porque a execução de um processo pressupõe 
por parte do sistema operacional, entre outras coisas, a criação de 
um contexto de execução próprio que, de certa forma, abstrai o 
processo dos componentes reais do sistema. Devido a essa virtu-
alização dos recursos, o processo não tem conhecimento acerca 
dos outros processos e, como tal, não consegue trocar informação.
O cache do PostgreSQL é conhecido como shared buffer e fica 
armazenado em segmentos de memória compartilhada pelos 
processos do servidor, ou back-ends. Uma forma de visualizá-los 
é utilizando a ferramenta ipcs. 
Trabalhando com o PostgreSQL: implementação do banco de dados
18 SQL Magazine • Edição 145
Os limites de memória compartilhada ajustáveis ocorrem através 
dos parâmetros de kernel, com o Linux:
• SHMMNI: representando o número máximo de segmentos de 
memória compartilhada no sistema operacional;
• SHMMAX: tamanho máximo de um segmento de memória 
compartilhada. Dependendo da instalação do sistema operacional, 
esse valor pode vir baixo. A configuração para esse valor deve 
estar alinhada ao que for definido em alguns outros parâmetros 
do PostgreSQL, como shared_buffers, max_connections, autova-
cuum_max_workers, max_prepared_transactions, max_locks_per_tran-
sactions e wal_buffers;
• SHMALL: significa o tamanho total de memória compartilhada 
pelo sistema operacional. É importante disponibilizar espaço o 
suficiente para que toda a memória compartilhada e mais os back-
-ends caibam nesse parâmetro.
Como mencionado anteriormente, os semáforos são responsáveis 
pela sincronização inter processos e mostram-se particularmente 
úteis quando o número de conexões se eleva demasiadamente. 
A seguir, temos alguns parâmetros relacionados a eles:
• SEMMNI: número máximo de conjuntos de semáforos. A re-
gra para o PostgreSQL é que seja, no mínimo, (max_connections + 
autovacuum_max_workers) / 16;
• SEMMSL: número máximo de semáforos por conjunto. 
É considerada uma boa prática reservar pelo menos 17 semáforos 
por conjunto;
• SEMMNS: número máximo de semáforos no sistema 
operacional, que deve ser SEMMNI * SEMMSL;
• SEMOPM: número máximo de operações por semáforo quando 
da chamada do sistema semop().
Journaling
Existe também outro aspecto que foge ao escopo das confi-
gurações nativas do PostgreSQL e diz respeito aos sistemas de 
arquivos do sistema operacional, que é a estrutura lógica uti-
lizada pelos sistemas operacionais para organização do arma-
zenamento. A relevância desse tema nesse contexto se mostra 
ao considerarmos que estamos lidando com volumes cada vez 
maiores de dados cuja complexidade de manutenção aumenta 
proporcionalmente, sendo que a escolha de um file system em 
detrimento de outro, mais adequado, poderia comprometer o 
desempenho e a confiabilidade do cluster. Há consenso entre 
especialistas em bancos de dados que o suporte a journaling é 
uma vantagem, o que justifica a sua popularidade e que, de 
forma geral, lida melhor com prevenção a perdas de dados e a 
recuperação em caso de falha. Journal, também conhecida como 
"registros de log", de onde deriva o termo journaling, é uma 
pequena área reservada no sistema de arquivos que armazena 
metadados sobre arquivos a fim de oferecer uma recuperação 
rápida e eficiente. No entanto, há um ponto a considerar: a ge-
ração de overhead, já que, nesse cenário, o processo de gravação 
do arquivo ocorre da seguinte forma:
• Registro de blocos de metadados;
• Efetuar a escrita no bloco;
• Registrar metadados do bloco.
Contudo, em caso de falha entre as etapas, podem ocorrer incon-
sistência das informações onde existiriam blocos marcados como 
utilizados, porém, vazios. Da mesma forma, blocos de metadados 
preenchidos, sem referência a qualquer dado. Isso não inviabiliza a 
recuperação, todavia, a torna mais lenta. Quanto ao overhead, pode 
ser considerável, visto que pode gravar o mesmo dado duas vezes. 
Por esse motivo, existem modos de operação de journaling. São eles:
• Journal: persiste todas as alterações. É o mais lento dos três 
modos, mas é o que possui maior capacidade de evitar perda de 
dados;
• Ordered: grava somente mudanças em arquivos de metadados, 
bem como as atualizações no arquivo de dados antes de fazer as 
mudanças associadas ao sistema de arquivos. É o modo padrão nos 
sistemas de arquivos ext3 (abordado posteriormente). Os modos 
journal e ordered são os mais indicados para o armazenamento de 
dados brutos;
• Write-back: semelhante ao ordered, mas utiliza o processo de 
escrita do sistema de arquivos em uso para gravação. É o mais 
rápido journaling ext3 com o menor overhead, o que o torna forte 
candidato a ser utilizado de forma segura com pg_xlog porque 
resíduos de armazenamento podem ser facilmente removidas pelo 
mecanismo WAL (Write-Ahead Logging). A seguir listamos alguns 
dos sistemas de arquivos que possuem o journaling:
- EXT3: desenvolvido pela Red Hat, é basicamente a extensão 
do EXT2 com a adição do journaling. E isso é uma vantagem 
porque o torna compatível com seu antecessor. Utiliza endereço 
de 32 bits e blocos de até 8 kB. O tamanho máximo dos arqui-
vos e da partição são determinados pelo tamanho dos blocos 
durante a formatação. Na Tabela 2, vemos a representação 
dessa distribuição.
Por padrão, o tamanho do bloco é definido automaticamente 
mas também pode ter seu valor configurado manualmente 
usando o parâmetro “-b” do comando mkfs.ext3 (usado para for-
matar as partições EXT3 no Linux), como em “mkfs.ext3 -b 2048 
/dev/hda1” (cria blocos de 2 KB) ou “mkfs.ext3 -b 4096 /dev/hda1” 
(para blocos de 4 KB). A implementação do recurso no EXT3 
é feita na camada “Journaling Block DeviceI” com o propósito 
de alcançar qualquer tipo de dispositivo com sua tecnologia.
O EXT3 dá suporte a “write barries” com algumas limitações, 
por exemplo, não funcionar em RAID de software e despejar o 
cache inteiro da controladora e não somente o arquivo.
Tamanho do bloco Tamanho máximo da 
partição
Tamanho máximo dos arquivos
1 kB 2 TB 16 GB
2 KB 8 TB 256 GB
4 KB 16 TB 2 TB
8 KB 32 TB 2 TB
Tabela 2. Capacidade de armazenamento no EXT3
Edição 145 • SQL Magazine 19 
A utilização do EXT3 é ampla em distribuições menos re-
centes e o sistema de arquivos é reconhecido como estável, 
contudo, a tendência é que o EXT3 seja lentamente substi-
tuído pelo EXT4.
- EXT4: tornou-se o padrão atual por ser aceito como a evo-
lução natural do EXT3. Implementa corretamente a questão 
dos “write barriers”, reserva de blocos contíguos para alocação 
e oferece outras melhorias para reduzir fragmentação de ar-
quivos. Possui desfragmentação on-line – que é o equivalente 
a desfragmentar os arquivos enquanto estão sendo alocados, 
verificação de integridade do journal (journal checksums) e 
compatibilidade com as versões da família EXTFS (ext2, ext3 
e ext4). Para usar o ext4 em uma nova partição, basta que 
você formate ela com o comando mkfs.ext4. Por exemplo, 
vamos considerar que a partição /dev/hda6 vai ser formatada 
para usar o novo file-system através do comando mkfs.ext4 /
dev/hda6. Para usar o ext4 em uma partição que já existe e 
que foi formatada previamente com o ext3, basta que ela seja 
montada usando o ext4dev no tipo do file-system conforme o 
comando mount -t ext4dev /dev/hda1 /mnt/hda1. Na Tabela 3 
verificamos algumas limitações de armazenamento do EXT4.
Tamanho do bloco
Tamanho máximo da 
partição
Tamanho máximo dos 
arquivos
4 kB 1 EB 16 TB
Tabela 3.Capacidade de armazenamento no EXT4
- XFS: sistema de arquivos 64 bits compatível com os de 
32 bits, de alto desempenho e geralmente escalável. Desenvol-
vido inicialmente para sistema operacional IRIX mas como é 
open source, logo foi migrado para o mundo GNU/Linux. Tam-
bém possui desfragmentação on-line. Um recurso interessante 
para esse sistema de arquivos é o redimensionamento on-line, 
realizado pelo utilitário “xfs_growfs”. Contudo, o redimen-
sionamento é no único sentido de aumentar o volume, nunca 
diminuí-lo, sendo apropriado para lidar com grandes volumes 
de dados. É pouco popular no Linux, porém é admitido como 
o mais adequado para bancos de dados, pois apresenta uma 
melhor implementação do journaling e de write barriers, envian-
do somente o necessário para o cache. Na Tabela 4 verificamos 
algumas limitações de armazenamento do XFS. Especialistas 
o apontam como o mais recomendado para PostgreSQL. Esse 
sistema é divido em três partes:
a) Seção de dados: contém informações sobre ponteiros para 
extensões ou ponteiros para listas de extensões;
b) Seção de log;
c) Seção de tempo real.
Tamanho do bloco Tamanho máximo da partição
Tamanho máximo dos 
arquivos
512 b a 64 kB 8 EB, em 32 b
8 EB, em 64b
16 TB, em 32 b
Tabela 4. Capacidade de armazenamento no XFS
Tuning do pg_xlog
O pg_xlog é o local onde se armazenam os logs de transação do 
PostgreSQL. Em um banco com caráter fortemente transacional, 
é uma otimização obrigatória separá-lo desse contexto, uma vez 
que nesse ponto a carga de entrada e saída é intensa e provocada 
pelas seguidas sincronizações do fsync. Para isso, basta separar 
o pg_xlog em outro dispositivo e referenciá-lo através de um link 
simbólico para o ponto de montagem. Podemos obter um resul-
tado equivalente se utilizarmos a opção –X no initd no momento 
da criação do cluster. 
Dê preferência por alocar o diretório do pg_xlog em armazena-
mento não-volátil, porque a perda de dados nesse arquivo pode 
impedir a inicialização do cluster. Sendo otimista, no mínimo as 
informações das últimas transações. Portanto, não é exagero su-
gerir que se utilize de armazenamento confiável em um esquema 
de redundância para esse diretório.
Pool de conexões: o pgBouncer
Ainda falando sobre performance, é oportuno discutir sobre o 
pooler de conexões. Esse conceito está relacionado à capacidade 
de disponibilizar um cache de conexões de bancos de dados que 
possam ser reutilizadas. Esse recurso tem o objetivo de garantir o 
desempenho das aplicações uma vez que abrir e fechar conexões, 
notadamente em sistemas distribuídos, é um processo custoso. 
Mas se todas as conexões do depósito de conexões estiverem em 
uso, o sistema gerenciador de banco de dados deve ser capaz 
de fornecer uma nova. Como o PostgreSQL é multiprocesso e 
cada processo representa um back-end, se executarmos múltiplos 
processos no SGBD teremos a mesma quantidade no sistema 
operacional. O problema disso é que, via de regra, processos, 
além de exigirem mais trabalho do escalonador, costumam des-
perdiçar recursos. 
Por isso, um grande número - superior a 200 - de conexões di-
retas deve ser evitado. O número máximo de conexões simultâ-
neas que o PostgreSQL pode suportar é definido pelo parâmetro 
max_connections. A memória compartilhada para cada conexão 
é de aproximadamente 400 bytes. Ainda há um parâmetro que 
permite acesso reservado a administradores para o caso do pool 
estar em sua capacidade máxima e o mesmo precisar acessar, é 
o superuser_reserved_connections. Como alternativa às aplicações 
que não gerenciam de forma independente o pool de conexões, 
existem dois gerenciadores externos: pgPool2 e pgBouncer, que 
atendem muito bem a ambientes em que existem muitas sessões 
simultâneas que executam tarefas breves. O acesso via gerenciador 
externo deve ser transparente e diminuir o impacto do custo de 
abertura e fechamento de sessões. Esse gerenciador de conexões 
não requer muitos recursos de hardware. Um servidor virtual 
com 4 cores, 8 GB de memória e 30 GB de disco, é suficiente para 
a implementação do pgBouncer. Contudo, o throughput de rede 
deve ser monitorado pois esse é o gargalo. OpgBouncer funciona 
em três modos de operação:
• Session: considerado o mais sutil entre as opções. Sua caracte-
rística é fornecer uma conexão do banco sob demanda durante 
Trabalhando com o PostgreSQL: implementação do banco de dados
20 SQL Magazine • Edição 145
toda a permanência do usuário. Após isso, a conexão é devolvida 
ao pooler. Cabe mencionar que ele não traz ganhos significativos;
• Transaction: nesse modo, a solicitação só é atendida durante 
uma transação. Quando o gerenciador percebe o fim da transação, 
a sessão é encerrada e a conexão é devolvida. Esse é sugerido como 
modo de operação para o PostgreSQL;
• Statement: oposto ao modo session. Aqui, a conexão é atribuída 
a cada comando, isoladamente, o que inviabiliza uma transação 
com múltiplas declarações, o que o torna pouco prático. Como os 
outros, a conexão retorna ao pooler no final.
É possível configurar o pgBouncer através do arquivo “pg-
bouncer.ini”, geralmente alocado em /etc/pgbouncer/pgbouncer.ini. 
O leitor perceberá que ele é divido nas seções database e pgbouncer. 
O elemento básico é um parâmetro configurado no formato chave-
-valor. Podemos observar na Tabela 5 as opções de configuração 
para a seção databases.
dbname Nome do banco de dados do servidor
dbhost Endereço IP do servidor
port Porta TCP onde a requisição será atendida
user Usuário do servidor
password Senha de autenticação para o usuário
pool_size
Tamanho máximo do pool para esse banco de dados. 
O padrão é o default_pool_size
connect_query
Consulta executada entre o estabelecimento da conexão e 
o início da sessão
client_encoding Codificação de caracteres utilizada
datestyle Informa o estilo da data a ser utilizada
timezone Informa a zona horária a ser utilizada
Tabela 5. Parâmetros de configuração de conexão do pgBouncer
Já na seção pg_bouncer, temos à disposição os parâmetros gerais 
para ajustes apresentados na Tabela 6.
Temos igualmente à disposição opções administrativas e de visu-
alização de logs que auxiliam na implementação do gerenciador de 
pooler e no monitoramento de eventos. Na Tabela 7 identificamos 
os mais relevantes.
Enquanto gerenciador do pool de conexões, um sistema deve 
prover um bom monitoramento das filas de acesso entre outras 
verificações. O pgBouncer dispõe de alguns parâmetros que aten-
dem a essa demanda que são explicados brevemente na Tabela 8.
Monitoramento com pgBouncer
Acessos pelo console do pgBouncer se relacionam ao controle e 
gerenciamento de operações. O monitoramento é feito através 
do comando show, que exibe estatísticas sobre o uso do pool. 
Vemos as opções disponíveis para argumentos no comando 
show na Listagem 1. Os colchetes representam o conjunto de 
opções, enquanto o caractere pipe indica a exclusividade entre 
elas. Ou seja, dentre as possibilidades, apenas uma pode ocorrer 
em certo momento.
listen_addr Endereço TCP/IP onde o servidor atenderá as solicitações.
listen_port Porta TCP/IP onde o servidor está atendendo.
unix_socket_dir Diretório do soquete de domínio.
log_file Caminho do arquivo de log.
pidfile Caminho do arquivo que informa o PID, ou id do processo.
auth_type
Tipo de autenticação suportada. Pode ser: trust, any, plain, 
crypt ou md5.
pool_mode
Define quando uma conexão pode ficar disponível a ou-
tros acessos. São eles: session, transaction e statement.
max_client_conn
Número máximo de conexões cliente aceitas. Tecnica-
mente, esse valor deve corresponder a max_client_conn + 
(max_pool_size * num_databases * num_clientes).
default_pool_size
Define quantas conexões são permitidas por usuário ou 
banco.
reserve_pool_size
Se refere à quantidade de conexõesadicionais são permiti-
das a usuário ou banco.
reserve_pool_ti-
meout
Limite de tempo para o atendimento a uma solicitação. 
Caso o cliente não seja atendido, o pgBouncer reserva 
uma conexão para ele.
server_round_ro-
bin
Visto que as conexões são reutilizadas e solicitadas a partir 
de uma pilha, significa que poucas conexões reais são utili-
zadas e que serão distribuídas de forma mais uniforme.
Tabela 6. Parâmetros de configurações gerais do pgBouncer.
syslog
Habilita o recurso na plataforma Windows, onde é 
utilizado.
syslog_facility
Para esse parâmetro, “facilidade” se refere ao tipo de 
programa que está enviando as informações para o 
syslog.
log_connections Registra as conexões realizadas com sucesso.
log_disconnections Registra eventos de conexões desfeitas e suas causas.
log_pooler_errors
Registra as saídas de erro que o pgBouncer envia para 
os clientes.
admin_users
Parâmetro administrativo que identifica os usuários 
que têm permissão para se autenticar no console do 
gerenciador de pooler e efetuar comandos.
stats_users
Outra opção administrativa que permite a identificação 
dos usuários que podem obter acesso ao pgBouncer 
através do console mas que só podem realizar coman-
dos read-only.
Tabela 7. Parâmetros de monitoramento do pgBouncer
server_login_retry
Tempo de espera necessário para que uma nova 
tentativa de login seja realizada em caso de falha 
anterior.
query_timeut
Consultas que atingem esse limite sem obter o resul-
tado de seu processamento são canceladas.
client_idle_timeout
Se a conexão do cliente estiver ociosa mais do que o 
tempo informado por esse parâmetro, ela será fecha-
da. O valor mínimo para esse parâmetro deve ser o 
tempo total de conexão.
client_login_timetout
Se o cliente obter a conexão mas não estabelecer uma 
sessão dentro desse limite, terá seu acesso cancelado.
autodb_idle_timeout
Tempo de espera antes de solicitar o fechamento de 
certa conexão utilizada por um pool genérico.
Tabela 8. Parâmetros de gerenciamento de pool do pgBouncer
Edição 145 • SQL Magazine 21 
Já o controle pode ser realizado através das opções:
• Pause: o pgBouncer utiliza a estratégia de tentar desconectar 
todos os servidores após a finalização normal de as consultas;
• Suspend: todos os buffers são escritos e o pgBouncer para de 
atender a solicitações. Assim como o pause, aguarda que todas as 
consultas tenham terminado normalmente;
• Resume: retorno às rotinas após a execução de um comando 
pause ou suspend;
• Shutdown: termina o processo do pgBouncer;
• Reload: faz a recarga do arquivo de configuração do pgBouncer.
Instalação do PostgreSQL
Existem diferentes formas de instalar o PostgreSQL. Agora, 
passaremos da teoria à prática. O primeiro ponto de interrogação 
que nos vem à mente é: “qual pacote usar? ”. Para tornar mais 
explícita nossa abordagem e dar espaço à análise, os elencamos 
conforme segue:
• Fornecido pelo fabricante do sistema operacional;
• Disponível em postgresql.org;
• Compilado a partir do código-fonte.
Dito isso, vamos começar pelo primeiro. Geralmente essa é a 
forma mais rápida e fácil de instalação pois o PostgreSQL já vem 
com todas as flags desejadas pelo fabricante e integrando-se ao seu 
sistema de diretórios. Não é necessariamente vantajoso porque 
dificulta a personalização das flags e por ter caráter de versio-
namento com foco nas versões minoritárias – o que geralmente 
é definido pelo fabricante –, deixando de fora as atualizações 
majoritárias. Mas a possibilidade de um suporte personalizado 
que suplante essas dificuldades para o usuário passa a ser uma 
vantagem.
Mais uma possibilidade é visitar o site oficial do PostgreSQL 
e escolher e realizar o download do pacote mais apropriado para 
seu sistema operacional. Os pacotes disponíveis são mantidos 
pela comunidade de desenvolvimento do projeto e não ne-
cessariamente abrangerão todos os sistemas operacionais ou 
todos os forks e versões dos mesmos. Para usuários do Windows 
e Mac OS X, essa é a única via. Os pacotes são compilados com 
as configurações de flags acordadas pela comunidade. Para as 
distribuições GNU/Linux mais recentes baseadas em Debian e 
Red Hat, respectivamente, estão disponíveis os repositórios para 
apt e yum. Os repositórios também contam com todas as versões 
disponibilizadas do PostgreSQL, além dos módulos contrib e 
ferramentas que já citamos aqui, como pgAdminIII e Slony. Por-
tanto, utilize as instruções do seu gerenciador de pacotes em 
função da sua escolha:
• No Debian: como superusuário, apt-get install postgresql;
• No CentOS: como superusuário, yum install postgresql-server;
• No Windows e Mac: utilize o instalador próprio.
Por fim, resta a compilação do código-fonte que é uma técnica 
que apresenta maior grau de dificuldade e requer certa familiari-
dade com desenvolvimento. É necessário ter instalado o compila-
dor da linguagem C e o código-fonte das bibliotecas necessárias: 
zlib e libreadline. Certamente essa é a opção que permite maior 
liberdade de configuração e, por isso, possibilita a instalação de 
qualquer versão disponível do PostgreSQL.
Para encerrar a discussão, você é que precisa entender a neces-
sidade do seu negócio e optar. Contudo, é importante considerar 
alguns pontos que podem guiar o leitor nessa decisão. Dê pre-
ferência ao pacote disponibilidade pelo fabricante, caso esteja 
disponível e atenda às suas necessidades. Caso seu fornecedor não 
ofereça o pacote, busque-o na comunidade. Contudo, se ambos os 
cenários não lhe agradam, siga pelo caminho da compilação. Mas 
para isso, esteja consciente de que os itens a seguir se esgotaram, 
na seguinte ordem:
1) O sistema operacional não possui os pacotes;
2) O pacote oferecido pelo sistema não está na versão que você 
precisa;
3) Não há oferta pelo pacote que você precisa no site oficial;
4) Você precisa alterar flags de compilação;
5) Você precisa alterar o código-fonte.
Instalação a partir de repositórios no Debian
A partir de agora iniciaremos os processos de instalação do 
PostgreSQL através de repositórios, que são locais, remotos ou 
não, de onde as dependências das instalações e atualizações 
podem buscar automaticamente arquivos de forma a facilitar a 
instalação e tornar o processo mais confiável. Essa é uma conve-
niência pois, dessa forma, podemos incrementar nosso sistema 
operacional de acordo com nossa vontade. As informações sobre 
os pacotes ficam armazenadas em listas que devem ser sincroni-
zadas periodicamente.
Partimos do princípio de que temos em mãos uma instalação 
GNU/Linux cuja lista de repositórios está desatualizada. Em um 
terminal, como root, editamos o arquivo sources.list. Considerando 
a Listagem 2 como referência, verificamos a instalação utilizando 
o apt-get na primeira linha. Na linha 02 temos a adição do reposi-
tório para a instalação do PostgreSQL. Ele contém as referências 
de onde serão baixados os pacotes. Adicionamos as linhas 03 e 04 
e salvamos a alteração. Essas inclusões dizem respeito à importa-
ção da chave de autenticação do repositório e permitem instalar 
os pacotes. Após isso, no terminal, executamos as linhas de 05 a 
07. Com isso, temos as dependências mapeadas para iniciarmos 
a instalação do PostgreSQL.
Listagem 1. Parâmetros para a cláusula show.
SHOW { STATS|
 SERVERS|
 CLIENTS|
 POOLS|
 LISTS|
 USERS|
 DATABASES |
 FDS|
CONFIG
 }
Trabalhando com o PostgreSQL: implementação do banco de dados
22 SQL Magazine • Edição 145
A sequência de comandos representada na Listagem 3 inicia o 
processo de instalação do PostgreSQL em sua linha 01 e, após isso, 
verificamos a localização dos arquivos da instalação no diretório 
referidopela linha 02. 
Instalação a partir de repositórios no CentOS
De forma análoga ao que foi realizado para instalação no Debian, 
teremos que configurar o repositório para proceder a instalação 
do PostgreSQL. A interpretação que fazemos da Listagem 4 é que, 
a princípio, é muito semelhando ao que foi exibido na listagem 
anterior. Mas há diferenças: o cluster não é inicializado automa-
ticamente no CentOS como é no Ubuntu. Essa característica é 
mostrada na linha 04 da Listagem 4. Além disso, os arquivos de 
dados e de configuração são separados em diretórios distintos no 
Ubuntu. Já no CentOS essa separação não existe.
Acompanhando os comandos, vemos que na linha 01 a lista 
de pacotes do repositório informado é atualizada. Após isso, o 
comando da linha 02 sugere que listemos todas as ocorrências 
de pacotes relacionados ao postgres no repositório que acabamos 
de atualizar. Ao leitor que está executando esses comandos no 
terminal, será exibida uma lista com o resultado solicitado pela 
linha anterior. A instalação é iniciada através do comando da 
linha 03. A penúltima linha habilita o serviço do PostgreSQL no 
CentOS e a última o inicializa. Essas duas últimas configurações 
são particulares desse sistema operacional.
Instalação a partir da compilação de código-fonte
Iniciaremos agora a demonstração de como instalar o Postgre- 
SQL a partir de binários. Essa situação não é rara e muitas vezes 
temos que lançar mão desse recurso para dispor de um ambiente 
que atenda às nossas expectativas. O código apresentado na 
Listagem 5 mostra a sequência dos comandos para a instalação 
no Debian para a versão 9.4.2 do PostgreSQL. Com a finalidade 
de abstrair o processo e torná-lo mais convidativo, não vamos 
alterar flags durante o processo. Contudo, o momento seria esse, 
se fosse o caso. 
Da interpretação e execução do código da Listagem 5, depreen-
demos que se trata de um processo relativamente simples e sem 
muitos percalços, mas que deve ser feito com atenção. A linha 
01 mostra a necessidade de, previamente, fazer o download de 
pacotes de desenvolvimento que dão início ao processo. A linha 
seguinte realiza o download do código-fonte diretamente do repo-
sitório para que seja utilizado. Em seguida, na linha 03, o pacote 
é descompactado para o diretório informado nela. A utilidade 
desse diretório será informada posteriormente. O comando da 
linha 04 se refere à navegação, não necessariamente obrigatória, 
mas que facilita o entendimento, até a pasta onde está o arquivo 
recém descompactado. Lá podemos visualizar alguns arquivos, 
o que é visto pela linha 07.
A presença do arquivo “configure” é o que nos interessa nesse 
momento já que ele é o responsável por proceder a verificação de 
todos os requisitos de instalação do PostgreSQL. Essa verificação 
é iniciada pelo comando da linha 06. O makefile contém todos os 
blocos de configuração para que o PostgreSQL seja instalado. Ve-
remos isso acontecer ao executar o comando indicado na linha 07 
da listagem. Nesse momento já temos o software instalado, agora 
daremos início à configuração do ambiente. Começaremos pelos 
binários de instalação, que por padrão após a compilação são 
alocados em /usr/local/pgsql/bin. O arquivo profile deve ser alterado 
para que seja possível a todos os usuários a invocação dos biná-
rios pelo terminal. Nesse caso, basta alterarmos a configuração 
do path – dentro desse arquivo – e adicionarmos o endereço dos 
binários da nova instalação, que é usr/localpgsql/bin. Atualizamos 
a leitura do arquivo conforme visto na linha 09 para que as alte-
rações sejam efetivadas.
Nessa forma de instalação o usuário postgres não é automatica-
mente criado. Por isso temos que criar a infraestrutura para ele. 
Como é sabido, ele deve existir no contexto do sistema operacional. 
Listagem 4. Instalação do PostgreSQL no Red Hat.
01 # yum localinstall
 http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1 
 .noarch.rpm
02 # yum list postgres*
03 # yum install postgresql94-server
04 # /usr/pgsql-9.4/bin/postgresql94-setup initdb
05 # systemctl enable postgresql-9.4
06 # systemctl start postgresql-9.4
Listagem 2. Atualização da lista de repositório local no Debian.
01 # vim /etc/apt/sources.list
02 ....
03 deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdgmain
04 # wget https://www.postgresql.org/media/keys/ACCC4CF8.asc
05 # apt-key add ACCC4CF8.asc
06 # apt-get update
07 # apt-get upgrade
Listagem 3. Instalação do PostgreSQL no Debian.
01 # apt-get install postgresql-9.4
02 # ls -l /usr/lib/postgresql/9.4/bin
03 # whereis pg_dump
04 # whereis pg_dumpall
05 # whereis psql
Listagem 5. Instalação do PostgreSQL a partir dos pacotes binários.
01 # apt-get install build-essential libreadline-dev libghc-zlib-dev
02 # wget ftp://ftp.postgresql.org/pub/source/v9.4.2/postgresql/9.4.2.tar.gz
03 # tar zxvf postgresql-9.4.2.tar.gz –C /usr/src
04 # cd /usr/src/postgresql-9.4.2/
05 # ls
06 # ./configure
07 # make world && make install –world
08 # vim /etc/profile
09 # source /etc/profile
10 # mkdir –p /var/lib/postgresql –s /bin/bash postgres
11 # chown postgres. /var/lib/postgresql/ -R
12 # chmod 700 /var/lib/postgresql
13 # initdb –D /var/lib/postgresql/9.4/main
Edição 145 • SQL Magazine 23 
Para isso, criamos uma pasta e sua estrutura de subdiretórios, 
indicando seu local e o bash com o nome postgres, conforme exibido 
na linha 10. A linha 12 lhe dá permissões pertinentes sobre ela. 
Por fim, a última linha da listagem inicia o cluster do banco. Após 
isso, o sistema gerenciador de banco de dados open source está 
devidamente configurado. O leitor já pode realizar uma conexão 
básica com o servidor, conforme mostrado na Listagem 6.
O código mostrado realiza uma conexão bem simples com o ban-
co de dados. A linha 01 faz o login com o usuário postgres. A linha 
02 invoca o aplicativo, enquanto a linha 03 faz o detalhamento 
de todas as tabelas do banco. A linha 04 encerra o aplicativo e a 
última faz o logoff. Uma sintaxe bem prática para o acesso via psql 
é "psql – U usuario –h host banco". Algumas considerações sobre 
essa sintaxe são:
• Omitir o usuário faz o programa assumir o nome do usuário 
do sistema operacional;
• Não informar o host sugere ao programa que o endereço é 
localhost;
• Por último, omitir a informação do banco leva o psql admitir 
que a conexão deve ser feita com um banco de mesmo nome que 
o usuário.
Inicialização do cluster do PostgreSQL
Chegamos a um ponto fundamental no nosso processo de 
implementação do sistema gerenciador de banco de dados. 
Conforme explicado anteriormente, o hardware é um dos recur-
sos que podem potencializar o serviço ou mesmo se tornar um 
gargalo. Mas esse é um problema que a computação contornou 
através do recurso de cluster, que possibilita ao PostgreSQL a 
expansão de sua capacidade de processamento para múltiplos 
servidores físicos, interligados e sincronizados, transferindo o 
gerenciamento de funções de alta disponibilidade, tolerância a 
falhas e balanceamento de carga do hardware para o software, uti-
lizando recursos abertos e interoperáveis e soluções baseadas 
em software livre que permitem a implementação de sistemas 
de cluster e grid sem ter que arcar com o ônus de licenciamento 
de software.
O PostgreSQL permite clusters na mesma máquina física, 
desde que tenham configurações e bancos de dados distintos. 
Inclusive, mais de um cluster pode estar em execução simul-
taneamente desde que em portas TCP diferentes. Fisicamente, 
pode ser definido na variável de ambiente $PGDATA. Caso a 
variável exista e esteja configurada corretamente, não é neces-
sário utilizar o parâmetro –D no utilitário initdb, tampouco nos 
outros utilitários que utilizam a pasta do cluster. 
Listagem 6. Conexão de teste com o servidor.
01 # su - postgres
02 $ psql
03 =#\dt
04 =# \q
05 $ exit
É necessário que o diretório de armazenamento seja de 
propriedade de quem irá gerenciá-lo, normalmente o postgres. 
A Tabela 9 apresenta os principais arquivos que ficarão dis-
postos no cluster.
PG_VERSION Arquivo Registra a versão majoritária do PostgreSQL.
base Subdiretório
Dispõe de um subdiretório para cada banco 
de dados em uso.
global Subdiretório Armazena o catálogo do PostgreSQL.
pg_clog Subdiretório
Contém dados sobre a finalização das 
transações.
pg_multixact Subdiretório
Registra dados sobre o estado multitran-
sacional. Usado para prover níveis de 
isolamento.
pg_stat_tmp Subdiretório
Armazena arquivos temporários do subsis-
tema de estatísticas.
pg_subtrans Subdiretório Dados do estado de subtransações.
pg_tblspc Subdiretório
Contém links simbólicos para as tablespa-
ces.
pg_twophase Subdiretório
Contém arquivos de estados para prepared 
statements.
pg_xlog Subdiretório
Armazena os logs de transação, conhecidos 
como WAL, já citados.
postmaster.opts Arquivo
Disponibiliza as opções de prompt da últi-
ma inicialização do cluster.
postmaster.pid Arquivo
Contém o PID e o ID do segmento de shared 
buffer.
postgresql.conf Arquivo
Contém as configurações gerais do Post-
greSQL.
pg_hba.conf Arquivo
Responsável por definir as permissões por 
usuário, banco de dados, máscara de rede, 
tipo de autenticação ou a combinação entre 
esses fatores.
pg_ident.conf Arquivo
Modelo “chave-valor” que define a associa-
ção entre usuários de sistemas e bancos de 
dados.
Tabela 9. Estrutura do cluster PostgreSQL
O pg_ctl é um utilitário para iniciar, parar ou reiniciar o servidor 
PostgreSQL ou mostrar o status de um servidor ativo. Embora 
o servidor possa ser iniciado manualmente, o pg_ctl encapsula 
tarefas como redirecionar a saída do log, desacoplar do terminal 
e do grupo de processos de forma adequada, além de fornecer 
opções convenientes para uma parada controlada. O comando 
demonstrado na primeira linha da Listagem 7 é a sintaxe para 
a inicialização do cluster. Onde há "pasta", entenda-se o caminho 
de diretórios - que pode ser variável - onde se localiza o cluster. 
As demais linhas seguem a mesma lógica e são autoexplicativas. 
Listagem 7. Utilização do aplicativo pg_ctl para tarefas no cluster.
# pg_ctl –D /pasta/cluster start
# pg_ctl –D /pasta/cluster stop
# pg_ctl –D /pasta/cluster restart
# pg_ctl –D /pasta/cluster reload
# pg_ctl –D /pasta/cluster status
Trabalhando com o PostgreSQL: implementação do banco de dados
24 SQL Magazine • Edição 145
Ainda sobre cluster, temos mais uma explicação sobre pg_stat_
tmp. Como a escrita é intensa nesse diretório e seu conteúdo não 
necessariamente precisa ser persistente, aconselha-se colocá-lo 
em RAM disk. Mas fique atento por que ao fazer isso, em caso de 
reinício do cluster, as estatísticas de uso do banco serão perdidas. 
O banco de dados e o usuário postgres
Podemos dizer nessa etapa que esse é o primeiro contato que 
o usuário terá com o PostgreSQL após concluir todas as etapas 
de instalação. O banco criado automaticamente é o que contém 
as informações do catálogo do sistema. Esse banco, chamado 
postgres, cria um usuário homônimo e não pode ser removido se 
não exist irem outras opções para conexão no cluster. Esse é o pri-
meiro banco a ser criado no cluster já apto a estabelecer conexões. 
Além desse, são criados outros dois: o template0 e o template1. 
A estrutura utilizada como “molde” para o banco postgres é de-
finida pelo template1. Esse template, ou modelo, é o padrão para 
a criação dos bancos de dados posteriores. Ele pode ser ajustado 
e essas alterações se refletirão nos objetos futuros. Já o template0 
é imutável, sequer a ponto de removê-lo. Ele é conhecido como 
"modelo mínimo". Por "mínimo" entenda-se "mínimo funcional", 
visto que pode ser utilizado como modelo em caso de falha de 
todos os objetos e do template1.
Sobre o usuário postgres, ele deve existir no sistema operacional. 
Geralmente o próprio processo de instalação se encarrega dessa 
instanciação. Esse será inicialmente o superuser do banco, sendo 
que esse objeto não pode ser removido pois é através dele que o 
cluster pode ser recuperado em caso de crash.
 Já dispomos de todo o ambiente devidamente instalado, configu-
rado e ajustado. Nos resta fazer um teste simples de conexão para 
verificar o mínimo sobre o que acabamos de montar. A ferramenta 
mais comum para essa tarefa é a psql. A Listagem 6 demonstrou 
de forma bem simples como uma conexão pode ser realizada.
Bruno de Oliveira Machado
bruno.execute@hotmail.com
Tecnólogo em Análise e Desenvolvimento de Sistemas, espe-
cialista em Tecnologias para Desenvolvimento Web, trabalha 
no Núcleo de Tecnologia da Informação da Universidade Estadual Vale 
do Acaraú como Administrador de Dados utilizando PostgreSQL. Tem se 
dedicado a conciliar suas atividades entre a administração e otimização dos servidores de 
banco de dados, suporte à equipe de desenvolvimento de software e à implementação 
de soluções em Business Intelligence com Pentaho, bem como à atividade de tutor do 
curso de Análise e Desenvolvimento de Sistemas da Unopar.
Autor
Links:
Site oficial do PostgreSQL
http://www.postgresql.org
Repositório de extensões para o PostgreSQL
http://pgxn.org
Sistemas de arquivos NTFS, FAT16, FAT32, EXT2 e EXT3
http://www.diegomacedo.com.br/sistemas-de-arquivos-ntfs-fat16-fat32-ext2-e-ext3/
Referência oficial sobre o utilitário pg_ctl na versão 9.4 do PostgreSQL
https://www.postgresql.org/docs/current/static/app-pg-ctl.html
Projetos relacionados ao PostgreSQL.
http://pgfoundry.org
Referência oficial sobre atualização do cluster
https://www.postgresql.org/docs/9.2/static/upgrading.html
Sistemas de arquivos com Journaling
http://www.ppgia.pucpr.br/~laplima/ensino/so_common/materia/05_arquivos.html
Edição 145 • SQL Magazine 25 
Este artigo é útil quando queremos pesquisar e encontrar padrões 
nas linhas que estão armazenadas no nosso banco de dados. Iremos 
aprender a utilizar a nova cláusula introduzida na versão 12c do Oracle, 
a MATCH_RECOGNIZE. Com ela poderemos criar e identificar esses 
padrões. Com essa nova sintaxe, podemos fazer o mesmo trabalho de 
antes, mas de uma maneira muito mais simples, ganhando em perfor-
mance, escalabilidade e manutenção. Vamos aprender, com a ajuda de 
exemplos, o que são esses padrões, como defini-los e encontrá-los nas 
linhas correspondentes, e o que podemos ganhar com isso.
Fique por dentro
Oracle 12c: Conheça a 
cláusula pattern matching
Aprenda a utilizar a cláusula MATCH_RECOGNIZE 
para identificar padrões através das linhas 
armazenadas no banco
Estamos vivendo em uma época em que a in-formação está sendo cada vez mais valorizada e necessária. Podemos constatar isso com o 
aumento de ofertas de emprego na área de BI, onde 
são analisados os dados da empresa para apoiar as 
atividades de tomada de decisão. Encontrar padrões no 
consumo dos clientes, nas ofertas de certo produto etc., 
está cada vez mais comum e quase que mandatório em 
uma empresa que deseja sobreviver nesse mundo cada 
vez mais competitivo.
Encontrar esses padrões já era possível em versões 
anteriores a 12c do Oracle, mas essas soluções eram 
difíceis de se escrever, de entender e muitas vezes inefi-
cientes ao executar, consumindo recursos consideráveis 
no processo. Com a ajuda das novas cláusulas e sintaxe 
do 12c, o mesmo processo ficou agora nativo ao SQL e 
podemos, assim, alcançar resultados melhores de forma 
bem mais eficiente.
Podem existir diferentes tipos de padrões, tudo depen-
de daquilo que estamos procurando. Alguns exemplos 
seriam: padrão de preços, em que diferentes épocas do 
ano o preço sobe e desce de acordo com o mercado, ou 
apenas o número bruto de vendas, quantidade de visitasem um site, comportamento em aplicações financeiras 
para detecção de fraude, etc. Neste artigo iremos abordar 
como definir esses padrões, agora com a sintaxe nativa 
do SQL, e como pesquisar por isso nas linhas que estão 
armazenadas no banco de dados. Iremos aprender a 
utilizar a nova cláusula introduzida na versão 12c do 
Oracle, MATCH_RECOGNIZE, que irá nos ajudar a 
identificar padrões através de uma sequência de linhas 
armazenadas no banco. Com essa nova sintaxe, podemos 
fazer o mesmo trabalho de antes, mas de uma maneira 
muito mais simples, ganhando em performance, esca-
labilidade e manutenção. Vamos aprender o que são 
esses padrões, como defini-los e encontrá-los nas linhas 
correspondentes, e o que podemos ganhar com isso. 
DEFINE
Entre todas as novas cláusulas da sintaxe do Oracle relacionadas 
a Pattern Matching, a primeira que vamos falar é a DEFINE. Com 
essa cláusula, que é obrigatória, podemos definir variáveis de pa-
drão primárias que serão então utilizadas para criar o padrão final 
que será utilizado para realizar a busca nas linhas. A pergunta 
que devemos fazer é: que características definem o meu padrão?
Vamos utilizar um exemplo clássico, que é o histórico de preços 
de um produto, algo bem comum em sites que buscam o melhor 
preço para um determinado produto, guardando assim o seu 
histórico para saber se o preço realmente está bom ou não. Vamos 
analisar a Figura 1.
Olhando rapidamente para esse gráfico, podemos encontrar 
o preço do produto em um determinado dia. Mas podemos ver 
mais nesse gráfico do que apenas isso. Podemos encontrar alguns 
padrões, como as variações de preços em forma de "V", onde o 
preço começa a baixar em um determinado momento e depois 
começa a subir até certo ponto. Ou até mesmo um "W" em que esse 
mesmo processo ocorre duas vezes. Antes de definir esse padrão, 
precisamos das variáveis que serão utilizadas nesse padrão, que 
nada mais é que uma condição que deverá ser cumprida para que 
uma linha seja mapeada para essa variável.
É isso o que a cláusula DEFINE faz. Vamos ver agora como definir 
essas variáveis utilizando o exemplo do gráfico. 
Oracle 12c: Conheça a cláusula pattern matching 
26 SQL Magazine • Edição 145
Quais são as características do padrão em "V" que está compre-
endido entre os dias 5 e 10? O dia 5 é o ponto de partida, onde o 
preço estava alto e a próxima mudança de preço é quando possi-
velmente existiu uma promoção, no dia 6. Essa foi caracterizada 
por uma decida no preço, a primeira perna do nosso "v". A parti 
do dia 7 até o dia 10, o preço começa a subir até atingir o preço 
mais alto, antes de começar a cair novamente. Então devemos ter 
três variáveis, o início, a descida e a subida. A cláusula ficaria 
como nos mostra o código da Listagem 1.
Definimos então duas variáveis de padrão. A primeira identificada 
com SUB, faz a comparação com a linha anterior, PREV, para saber 
se tem um preço maior ou não. Se tiver, essa linha será mapeada 
como SUB. A variável DES é exatamente o contrário, comparando 
se tem um valor menor que a linha anterior, assim essa linha será 
mapeada como DES. Agora faltou uma variável para o início do 
padrão. Acontece que nem toda variável precisa de uma definição, 
significando que qualquer linha pode então ser mapeada para esse 
padrão, que é o caso do que veremos mais adiante.
PATTERN
Aqui definimos qual é o padrão que será pesquisado em todas 
as linhas. Nessa cláusula, utilizamos as variáveis definidas em 
DEFINE para criar esse padrão. Descrevemos de forma simples, 
Figura 1. Gráfico de preços de um determinado produto em um período de tempo
Listagem 1. Criando um padrão com duas variáveis.
DEFINE
 SUB AS SUB.preco > PREV(SUB.preco),
 DES AS DES.PRECO < PREV(DES.preco)
a sequência em que as linhas serão mapeadas entre as variáveis 
e a quantidade de linhas. Podemos utilizar expressões regulares 
para tornar a nossa busca ainda mais poderosa. Veja um exemplo 
na linha a seguir:
PATTERN (INI, DES+, SUB+)
Nesse exemplo utilizamos uma variável INI, que não foi definida 
na Listagem 1, ou seja, qualquer linha pode ser mapeada como 
INI, o nosso ponto de partida. Logo após temos uma a variável 
DESC, que significa que a próxima linha deverá ter um preço 
menor que a linha mapeada como INI. O símbolo de "+" significa 
que pelo menos uma linha deverá ser mapeada ou mais como essa 
variável. E logo após temos a última variável, SUB, que também 
tem que ter pelo menos uma ou mais linhas mapeadas, indicando 
um aumento no preço. Formando assim um padrão de "V".
Para que esse padrão seja encontrado, um conjunto de linhas 
contínuas deverão ser mapeadas conforme a sequência defini-
da em PATTERN e todas as condições em DEFINE deverão ser 
verdadeiras. A definição desse padrão poderá ser algo bem mais 
avançada do que esse exemplo. Para isso, é necessário ter um bom 
conhecimento sobre expressões regulares. 
MEASURES
Aqui definimos as medidas, que serão apresentadas em forma 
de uma lista de colunas como resultado final para cada combi-
nação de padrão encontrada. Aqui podemos usar funções junto 
com as variáveis de padrão, além de operadores de navegação, 
como o PREV que já vimos anteriormente. Vejamos um exemplo 
na Listagem 2.
Edição 145 • SQL Magazine 27 
Nessa listagem definimos qual será o retorno da nossa query em 
formato de colunas ao encontrar uma combinação de padrão. As 
duas primeiras colunas, definidas nas linhas 01 e 02, estão referen-
ciando a variável INI, que não tem definição, ou seja, poderá ser 
qualquer linha (como o início do nosso padrão). Irá então exibir 
tanto o preço inicial como a data inicial do nosso ponto de partida 
do padrão. Logo após isso, nas linhas 03 a 06 temos a referência 
às variáveis DES e SUB, que foram definidas na Listagem 1, que 
é uma linha que tem um preço menor que a sua anterior (DES) e 
uma linha com um preço maior que a anterior (SUB). Também irá 
exibir a data e o preço da linha. Note o uso da função de navegação 
LAST. Com o uso dessa função, garantimos que o valor retornado 
será o último para cada padrão, tanto o de descida quanto o de 
subida, sendo então o menor e o maior preço, respectivamente.
AFTER MATCH SKIP
Essa cláusula se refere a determinar o ponto para retomar a 
busca por um padrão após ter encontrado uma correspondência. 
Ou seja, a partir de qual linha poderá ser contado como início 
para uma próxima combinação de padrão. Temos algumas opções 
disponíveis, que são:
• AFTER MATCH SKIP TO NEXT ROW: será determinado como 
ponto de partida a linha após a primeira linha do padrão atual 
encontrado;
• AFTER MATCH SKIP PAST LAST ROW: será determinado 
como ponto de partida a linha após a última linha do padrão 
atual encontrado. Esse é o valor default;
• AFTER MATCH SKIP TO FIRST variavel_padrao: será deter-
minado como ponto de partida a primeira linha mapeada para 
uma determinada variável de padrão;
• AFTER MATCH SKIP TO [LAST] variavel_padrao: será deter-
minado como ponto de partida a última linha mapeada para uma 
determinada variável de padrão.
Deve-se ter alguns cuidados ao escolher o ponto de retorno para 
a busca de uma nova combinação de padrão, pois poderá não ser 
encontrado nada ou até mesmo ficar em um estado de loop infini-
to, gerando assim exceções. Um exemplo seria retomar para uma 
variável padrão, mas se no DEFINE essa variável for opcional e 
não tiver nenhuma linha mapeada para ela (não existindo assim 
um ponto de retomada), será gerada uma exceção em tempo de 
execução. Outro exemplo seria utilizar o ponto de retorno para 
uma determinada variável sendo que ela também foi o início da 
combinação de padrão atual, formando assim um loop infinito, 
gerando também uma exceção em tempo de execução.
Algumas outras cláusulas importantes
Temos algumas outras cláusulas importantes a serem mencio-
nadas antesde termos um exemplo prático. Veja alguma delas:
• (ONE ROW | ALL ROWS) PER MATCH: aqui escolhemos se 
para cada variável de padrão encontrada, serão exibidas todas as 
linhas mapeadas ou apenas uma como um resumo;
• PARTITION BY coluna: dividimos as linhas em grupos de 
acordo com os valores em comum na coluna especificada. Algo 
similar ao GROUP BY;
• ORDER BY: ordena as linhas, com os seus grupos, para serem 
localizados os padrões. 
Essas últimas duas cláusulas são já bem conhecidas por quem 
usa funções analíticas. Temos também algumas funções impor-
tantes para mencionar, que são:
• CLASSIFIER(): retorna qual foi a variável de padrão na qual 
a linha foi mapeada;
• MATCH_NUMBER(): atribui um número em sequência para 
cada padrão encontrado, retornando assim em qual padrão, da 
sequência, aquela linha pertence.
Como os dados são processados
Após ter conhecido as cláusulas e funções mais importantes, 
vamos ver como é o processamento de uma query com a cláusula 
MATCH_RECOGNIZE em alguns passos simples:
1. A tabela será particionada em grupos de acordo com a cláusula 
PARTITION BY, onde cada grupo tem o mesmo valor em uma 
determinada coluna;
2. Cada partição será ordenada pelo ORDER BY;
3. Se inicia então em cada partição a busca pelo padrão definido 
em PATTERN;
4. A busca se inicia na primeira linha e as seguintes para encon-
trar uma combinação como definida em PATTERN. Se não for 
encontrado nada, a busca irá se iniciar na linha seguinte e assim 
por diante. Caso seja encontrada uma combinação positiva, são 
calculadas as expressões presentes na cláusula MEASURES;
5. São retornadas as quantidades de linhas de acordo com a 
cláusula ONE ROW PER MATCH ou ALL ROWS PER MATCH;
6. E para finalizar, após uma combinação de padrão, a cláusula 
AFTER MATCH SKIP irá informar aonde irá se retomar o processo 
de pesquisa por mais uma combinação de padrão.
Criando o ambiente de testes
Para iniciar a demonstração do uso do MATCH_RECOGNIZE, 
devemos criar uma tabela onde irão ficar os registros de testes, 
que nada mais são que um histórico de preços de determinados 
produtos. Após isso, serão inseridas algumas linhas para popular 
a tabela e realizar uma query. É nessa tabela que iremos fazer a 
busca pelos padrões. Na estrutura dessa tabela temos o ID, pro-
duto, preço e data da venda. Confira a Listagem 3.
Vamos analisar um pouco o conteúdo da tabela teste_pattern, que 
possui apenas quatro colunas. Além do ID da venda, temos o pro-
duto, que são apenas dois, JAVA e ORACLEDB. Nas próximas duas 
Listagem 2. Criando measures.
01 MEASURES INI.data AS data_inicio,
02 INI.preco AS preco_inical,
03 LAST(DES.data) AS data_menor_preco,
04 LAST(DES.preco) AS menor_preco,
05 LAST(SUB.data) AS data_menor_preco,
06 LAST(SUB.preco) AS menor_preco
Oracle 12c: Conheça a cláusula pattern matching 
28 SQL Magazine • Edição 145
colunas temos a data da venda e o preço que o produto foi vendido. 
Nas instruções em sequência, temos uma série de INSERTs, que 
cobre um período de 18 dias e tem uma variação de preço dos dois 
produtos já mencionados. Apenas com essas informações, pode-
mos criar e pesquisar por padrões. Iremos utilizar tudo o que foi 
visto anteriormente na explicação de cada uma das cláusulas mais 
comuns. Veja um primeiro exemplo na Listagem 4.
Listagem 3. Criando o ambiente de testes.
CREATE TABLE teste_pattern (
 pattern_id NUMBER,
 produto VARCHAR2(20),
 data_venda DATE,
 preco NUMBER
);
/
INSERT INTO teste_pattern VALUES(1, ‘JAVA’, sysdate, 20);
INSERT INTO teste_pattern VALUES(2, ‘ORACLEDB’, sysdate, 200);
INSERT INTO teste_pattern VALUES(3, ‘ORACLEDB’, sysdate+1, 190);
INSERT INTO teste_pattern VALUES(4, ‘ORACLEDB’, sysdate+2, 185);
INSERT INTO teste_pattern VALUES(5, ‘ORACLEDB’, sysdate+3, 190);
INSERT INTO teste_pattern VALUES(6, ‘ORACLEDB’, sysdate+4, 210);
INSERT INTO teste_pattern VALUES(7, ‘JAVA’, sysdate+5, 25);
INSERT INTO teste_pattern VALUES(8, ‘JAVA’, sysdate+6, 15);
INSERT INTO teste_pattern VALUES(9, ‘JAVA’, sysdate+7, 10);
INSERT INTO teste_pattern VALUES(10, ‘JAVA’, sysdate+8, 25);
INSERT INTO teste_pattern VALUES(11, ‘ORACLEDB’, sysdate+9, 210);
INSERT INTO teste_pattern VALUES(12, ‘ORACLEDB’, sysdate+10, 150);
INSERT INTO teste_pattern VALUES(13, ‘JAVA’, sysdate+11, 30);
INSERT INTO teste_pattern VALUES(14, ‘ORACLEDB’, sysdate+12, 180);
INSERT INTO teste_pattern VALUES(15, ‘ORACLEDB’, sysdate+13, 300);
INSERT INTO teste_pattern VALUES(16, ‘JAVA’, sysdate+14, 35);
INSERT INTO teste_pattern VALUES(17, ‘JAVA’, sysdate+15, 25);
INSERT INTO teste_pattern VALUES(18, ‘JAVA’, sysdate+16, 30);
INSERT INTO teste_pattern VALUES(19, ‘ORACLEDB’, sysdate+17, 250);
INSERT INTO teste_pattern VALUES(20, ‘ORACLEDB’, sysdate+18, 350);
Listagem 4. MATCH_RECOGNIZE.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 PARTITION BY produto
04 ORDER BY data_venda
05 MEASURES INI.data_venda AS data_inicio,
06 LAST(DES.data_venda) AS data_menor_preco,
07 LAST(DES.preco) AS menor_preco,
08 LAST(SUB.data_venda) AS data_maior_preco,
09 LAST(SUB.preco) AS maior_preco
10 ONE ROW PER MATCH
11 AFTER MATCH SKIP TO LAST SUB
12 PATTERN (INI DES+ SUB+)
13 DEFINE
14 DES AS DES.preco < PREV(DES.preco),
15 SUB AS SUB.preco > PREV(SUB.preco)
16 ) MR
17 ORDER BY MR.produto, MR.data_inicio;
18 /
Na linha 2 foi utilizada a cláusula MATCH_RECOGNIZE, 
que possibilita criar e pesquisar por padrões. Logo nas linhas 
3 e 4, foi definido que a partição será feita com base nos valo-
res da coluna produto, que no caso será duas partições, e que 
cada partição será ordenada com base na coluna data_venda. As 
variáveis de padrão foram definidas nas linhas 14 e 15 e utili-
zadas para criar o padrão na linha 12. Na linha 10 foi definido 
que para cada padrão encontrado só será exibida uma linha e 
não todas as linhas que foram mapeadas para as variáveis de 
padrão. Já na linha 11 indicamos onde recomeçar pela busca 
de um novo padrão após já ter encontrado um, no caso foi 
escolhida a última linha mapeada como SUB, ou seja, a última 
linha de um padrão poderá ser o início de outro padrão. E, para 
finalizar, nas linhas 5 até 9 foram definidas as medidas que 
serão apresentadas como resultado final em forma de colunas. 
Veja esse resultado na Listagem 5.
Aqui podemos verificar o resultado da nossa primeira query. 
Além das colunas que definimos na cláusula MEASURES, foi 
retornada a coluna produto, que foi a condição da nossa cláu-
sula PARTITION BY. Os resultados estão ordenados de acordo 
com a cláusula ORDER BY da linha 17 da Listagem 4. Foram 
encontrados dois padrões para o produto JAVA, conforme as 
linhas 2 e 3, e três padrões para o produto ORACLEDB, que 
estão representados nas linhas 4 a 6. Para cada um dos padrões 
encontrados é mostrada a data de início, do menor preço e do 
maior preço, juntamente com os respectivos preços nessas 
datas. Para cada padrão é exibida apenas uma linha, como 
uma espécie de resumo. Podemos constatar esse resultado 
verificando a Figura 2.
Compare os resultados da Listagem 5 com a Figura 2. Vamos 
pegar um exemplo de padrão e comparar com o nosso gráfico. 
Veja a linha 5 da Listagem 5. Esse padrão indica que o ponto 
de partida foi no dia 03/06/2016, se olhar no gráfico, verá que 
o valor do produto ORACLEDB, linha em laranja, era de 210, 
após isso o valor do produto sofreu uma queda chegando a 
ser cotado com o menor valor em 150, no dia 04/06/2016. Após 
isso, o preço começou a subir tendo o valor de 180 e depois 300 
como o valor mais alto no dia 07/06/2016. Isso foi um exemplo 
de padrão em forma de "V", que foi definido como sendo o 
PATTERN INI, DES+, SUB+. 
Se for necessária mais informação no retorno do padrão, como 
cada preço e cadadata que foi mapeado, deve ser utilizada a 
cláusula ALL ROWS PER MATCH. Mas talvez fique difícil de 
identificar cada linha com os seus respectivos padrões e o que 
significa cada uma dessas linhas. Para auxiliar nisto, existem 
duas funções que podem nos ajudar 
a identificar cada linha.
Classifier e Match_number
Existem duas funções que podem 
nos ajudar a identificar cada linha 
retornada quando é utilizado ALL 
ROWS PER MATCH, visto que o 
Listagem 5. Resultado gerado.
01 PRODUTO DATA_INICIO DATA_MENOR_PRECO MENOR_PRECO DATA_MAIOR_PRECO MAIOR_PRECO
02 JAVA 30/05/16 01/06/16 10 08/06/16 35
03 JAVA 08/06/16 09/06/16 25 10/06/16 30
04 ORACLEDB 25/05/16 27/05/16 185 29/05/16 210
05 ORACLEDB 03/06/16 04/06/16 150 07/06/16 300
06 ORACLEDB 07/06/16 11/06/16 250 12/06/16 350
Edição 145 • SQL Magazine 29 
Figura 2. Dados em Gráfico
número de linhas retornado pode ser muito grande para cada pa-
drão. A primeira função, a CLASSIFIER, retorna a variável padrão 
na qual aquela linha foi mapeada. Já a função MATCH_NUMBER 
retorna um número inteiro positivo em sequência, para cada 
padrão encontrado, representando a qual padrão aquela linha 
pertence. Para visualizar melhor como essas funções funcionam, 
iremos analisar mais um exemplo. 
Contudo, antes disso, devemos entender que ao mudar de 
ONE ROW para ALL ROWS, o comportamento das medidas em 
MEASURES pode ser alterado. Isso ocorre por que, por default, é 
utilizado o modificador ou função de navegação RUNNING, que 
significa que o valor calculado naquela linha não será considerado 
o padrão inteiro, mas será considerada a linha corrente e as ante-
riores da mesma, mesmo que existam muitas linhas depois dela 
que serão incluídas no mesmo padrão. Um exemplo disso seria 
a medida do menor preço, que talvez não exiba o menor preço 
que foi encontrado no padrão inteiro, mas sim o menor preço até 
aquela linha. Para corrigir isso, caso não seja o desejado, deve-se 
utilizar o modificador FINAL, que irá então considerar todas as 
linhas do mesmo padrão. Observe agora a Listagem 6.
Vamos entender as alterações feitas nessa listagem. A primeira 
modificação foi feita na cláusula MEASURES. Nela alteramos 
algumas medidas, removemos algumas listagens e adicionamos 
outras. Na linha 7 adicionamos o modificador FINAL, conforme 
já foi discutido, e assim podemos comparar com o retorno da 
medida na linha 6, que é a mesma medida, mas sem modificador 
nenhum explícito, ou seja, utilizou o modificador default, que 
Listagem 6. Utilização do CLASSIFIER, MATCH_NUMBER e FINAL.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 PARTITION BY produto
04 ORDER BY data_venda
05 MEASURES INI.data_venda AS inicio,
06 LAST(SUB.preco) AS maior_preco,
07 FINAL LAST(SUB.preco) AS maior_final,
08 MATCH_NUMBER() AS padrao,
19 CLASSIFIER() AS var_padrao
10 ALL ROWS PER MATCH
11 AFTER MATCH SKIP TO LAST SUB
12 PATTERN (INI DES+ SUB+)
13 DEFINE
14 DES AS DES.preco < PREV(DES.preco),
15 SUB AS SUB.preco > PREV(SUB.preco)
16 ) MR
17 ORDER BY MR.produto, MR.data_veda;
é o RUNNING no caso. Nas linhas 8 e 9 adicionamos o uso das 
funções CLASSIFIER e MATCH_NUMBER para identificar me-
lhor o que cada linha representa, já que na linha 11 foi informado 
para retornar todas as linhas por padrão, e não apenas uma como 
no exemplo anterior. Observe o resultado agora na Listagem 7.
Agora podemos analisar todas as linhas em cada padrão. 
A coluna PADRAO poderá nos ajudar nessa tarefa, já que com a 
ajuda da função MATCH_NUMBER, podemos identificar a qual 
padrão aquela linha pertence. Já para a coluna VAR_PADRAO foi 
utilizada a função CLASSIFIER, que nos retorna para qual variável 
de padrão aquela linha foi mapeada. Agora podemos saber quem 
é o início, a descida de preço e depois a subida de preço.
Oracle 12c: Conheça a cláusula pattern matching 
30 SQL Magazine • Edição 145
Listagem 7. Uso das funções CLASSIFIER e MATCH_NUMBER.
PRODUTO DATA_VENDA INICIO MAIOR_PRECO MAIOR_FINAL PADRAO VAR_PADRAO PATTERN_ID PRECO
JAVA 30/05/16 30/05/16 35 1 INI 7 25
JAVA 31/05/16 30/05/16 35 1 DES 8 15
JAVA 01/06/16 30/05/16 35 1 DES 9 10
JAVA 02/06/16 30/05/16 25 35 1 SUB 10 25
JAVA 05/06/16 30/05/16 30 35 1 SUB 13 30
JAVA 08/06/16 30/05/16 35 35 1 SUB 16 35
JAVA 08/06/16 08/06/16 30 2 INI 16 35
JAVA 09/06/16 08/06/16 30 2 DES 17 25
JAVA 10/06/16 08/06/16 30 30 2 SUB 18 30
ORACLEDB 25/05/16 25/05/16 210 1 INI 2 200
ORACLEDB 26/05/16 25/05/16 210 1 DES 3 190
ORACLEDB 27/05/16 25/05/16 210 1 DES 4 185
ORACLEDB 28/05/16 25/05/16 190 210 1 SUB 5 190
ORACLEDB 29/05/16 25/05/16 210 210 1 SUB 6 210
ORACLEDB 03/06/16 03/06/16 300 2 INI 11 210
ORACLEDB 04/06/16 03/06/16 300 2 DES 12 150
ORACLEDB 06/06/16 03/06/16 180 300 2 SUB 14 180
ORACLEDB 07/06/16 03/06/16 300 300 2 SUB 15 300
ORACLEDB 07/06/16 07/06/16 350 3 INI 15 300
ORACLEDB 11/06/16 07/06/16 350 3 DES 19 250
ORACLEDB 12/06/16 07/06/16 350 350 3 SUB 20 350
Agora vamos analisar em conjunto duas colunas, a MAIOR_
PRECO e MAIOR_FINAL. As medidas que essas colunas 
fazem são as mesmas, a única diferença é que uma utiliza o 
modificador FINAL e a outra, sem o modificador, utiliza o 
RUNNING. Vamos pegar o primeiro padrão do produto JAVA 
como exemplo. Repare que desde a primeira linha a coluna 
MAIOR_FINAL já mostra o maior preço encontrado em todo 
o padrão. Já a linha MAIOR_PRECO retorna NULL no início e 
na descida. Já na subida, conforme ele encontra um preço maior 
que o anterior ele vai retornando esse valor, até encontrar o 
maior preço em todo o padrão, apenas na última linha do pa-
drão. Isso ocorre por que essa coluna usa o modificar default 
RUNNING e ele verifica apenas a linha atual e as anteriores 
que já foram analisadas.
Note também que algumas linhas foram mapeadas duas ve-
zes, uma em cada padrãodiferente. Um exemplo foi a linha com 
o PATTERN_ID 15. Perceba que ela foi mapeada como SUB, úl-
tima linha, no segundo padrão do produto ORACLEDB e como 
INI no terceiro padrão do mesmo produto. Esse comportamento 
ocorrer a depender de como foi utilizada a cláusula AFTER 
MATCH SKIP. No nosso exemplo foi para SKIP TO LAST SUB, 
indicando que o ponto de partida para a procura de um novo 
padrão será a última linha mapeada como SUB. Ou seja, essa 
linha poderá ser contada como início de outro padrão.
Diferentes tipos de padrão
Até aqui, em todos os nossos testes e exemplos, foi conside-
rado um único tipo de padrão em forma de "V". Vejamos agora 
como definir outros tipos de padrão, como um em forma de 
"W", onde o padrão anterior que já utilizamos, em forma de "V", 
ocorre duas vezes seguidas. Confira como ficaria essa query 
na Listagem 8.
Listagem 8. Padrão em Forma de “W”.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 PARTITION BY produto
04 ORDER BY data_venda
05 MEASURES MATCH_NUMBER() AS padrao,
06 CLASSIFIER() AS variavel_padrao
07 ALL ROWS PER MATCH
08 AFTER MATCH SKIP TO LAST SUB
09 PATTERN (INI DES+ SUB+ DES+ SUB+)
10 DEFINE
11 DES AS DES.preco < PREV(DES.preco),
12 SUB AS SUB.preco > PREV(SUB.preco)
13 ) MR
14 ORDER BY MR.produto, MR.data_venda;
Para melhorar a compressão do retorno da query, a cláusula 
MEASURES, nas linhas 5 e 6, foi modificada para retornar pouca 
informação. Na linha 9 foi criado o nosso padrão, utilizando as 
variáveis de padrão nas linhas 11 e 12. Lá foi informado que após 
o início do padrão, o preço iria descer, depois subir e refazer o 
processo de descer e subir, formando assim uma forma de "W" 
no gráfico da Figura 2. Vamos analisar o retorno dessa query na 
Listagem 9.
Essa listagem retorna dois padrões, um para cada produto, 
onde foi encontrada uma combinação positiva do padrão em 
forma de "W". Pode-se constatar isso observando o gráfico na 
Figura 2. Podemos verificar isso também pela ordem em que 
as variáveis de padrão retornadas pela função CLASSIFIER na 
coluna VARIAVEL_PADRAO é retornada, sendo exatamente a 
mesma ordem que aparece na cláusula PATTERN. Isso evidência 
que as possibilidades de criar padrões diferentes são muitas. 
Até agora, todos os nossos exemplos foram baseados no valor 
de cada produto em uma determinada data. 
Edição 145 • SQL Magazine 31 
Listagem 9. Padrão em Forma de “W”.
PRODUTO DATA_VENDA PADRAO VARIAVEL_PADRAO PATTERN_ID PRECO
JAVA 30/05/16 1 INI 7 25
JAVA 31/05/16 1 DES 8 15
JAVA 01/06/16 1 DES 9 10
JAVA 02/06/16 1 SUB 10 25
JAVA 05/06/16 1 SUB 13 30
JAVA 08/06/16 1 SUB 16 35
JAVA 09/06/16 1 DES 17 25
JAVA 10/06/16 1 SUB 18 30
ORACLEDB 03/06/16 1 INI 11 210
ORACLEDB 04/06/16 1 DES 12 150
ORACLEDB 06/06/16 1 SUB 14 180
ORACLEDB 07/06/16 1 SUB 15 300
ORACLEDB 11/06/16 1 DES 19 250
ORACLEDB 12/06/16 1 SUB 20 350
Listagem 10. Padrão de quantidade de determinados produtos vendidos.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 ORDER BY data_venda
04 MEASURES MATCH_NUMBER() AS padrao,
05 CLASSIFIER() AS variavel_padrao
06 ALL ROWS PER MATCH
07 AFTER MATCH SKIP PAST LAST ROW
08 PATTERN (JAV{2,4} ORA{1,2})
09 DEFINE
11 JAV AS JAV.produto = ‘JAVA’,
12 ORA AS ORA.produto = ‘ORACLEDB’
13 ) MR
14 ORDER BY MR.data_venda, MR.padrao;.
15
16 DATA_VENDA PADRAO VARIAVEL_PADRAO PATTERN_ID PRODUTO PRECO
17 30/05/16 1 JAV 7 JAVA 25
18 31/05/16 1 JAV 8 JAVA 15
19 01/06/16 1 JAV 9 JAVA 10
20 02/06/16 1 JAV 10 JAVA 25
21 03/06/16 1 ORA 11 ORACLEDB 210
22 04/06/16 1 ORA 12 ORACLEDB 150
23 08/06/16 2 JAV 16 JAVA 35
24 09/06/16 2 JAV 17 JAVA 25
25 10/06/16 2 JAV 18 JAVA 30
26 11/06/16 2 ORA 19 ORACLEDB 250
27 12/06/16 2 ORA 20 ORACLEDB 350
Vejamos agora, na Listagem 10, um exemplo de padrão um pou-
co diferente, referente à quantidade de determinados produtos 
vendidos em uma certa sequência.
O primeiro item a se notar é que não criamos partição nenhuma. 
Nas linhas 11 e 12 foram definidas duas variáveis de padrão onde 
verificamos qual foi o produto vendido. Após isso, o padrão foi 
criado na linha 8 utilizando essas mesmas variáveis já citadas. 
Aqui utilizamos o poder das expressões regulares, onde defini-
mos que primeiro teria que ter sido vendido entre dois e quatro 
produtos JAVA e, após isso, entre um e dois produtos ORACLEDB. 
Toda essa verificação é feita respeitando a ordem das linhas que 
foi imposta na linha 3, no ORDER BY. A última diferença desse 
exemplo para os anteriores se encontra na linha 7, onde foi defi-
nido que a busca de uma nova combinação de padrão será feita 
na linha seguinte à última linha do padrão atual encontrado. 
Sendo assim, uma mesma linha não poderá ser mapeada em dois 
padrões diferentes.
Todos os exemplos utilizados aqui foram apenas para ajudar no 
entendimento dessa nova cláusula, que em um primeiro momento 
pode parecer um pouco complicada, mas que na verdade não é. 
A versão 12c do Oracle trouxe grandes avanços na sintaxe do SQL, 
um bom exemplo disso é a nova cláusula MATCH_RECOGNIZE. 
Com ela podemos fazer tudo nativamente, a criação e a pesquisa 
de padrões, ganhando assim bastante em performance, escalabi-
lidade e manutenção. Caso seja necessário, sempre utilize essas 
novas cláusulas e, para formas mais avançadas do que as apre-
sentadas aqui, consulte a documentação do Oracle. 
Tércio Costa
terciosilvacosta@gmail.com - https://oraclepress.wordpress.com/
Formado em Ciências da Computação pela UFPB com experi-
ência em Servidores Windows Server e Linux e banco de dados 
Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento 
de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado 
Oracle Database SQL Certified Expert, mantendo o blog http://oraclepress.wordpress.
com/ reconh ecido pela OTN onde também sou articulista e no portal http://www.
profissionaloracle.com.br/gpo
Autor
32 SQL Magazine • Edição 145
Existem diversas razões pelas quais será necessária a extração de 
grande volume de dados, a principal e muito utilizada é para backup. No 
entanto, existem diversas outras, como salvar arquivos históricos antes 
de serem removidos, atualização de ambientes DW (Data Warehouse), 
atualizar ambientes de desenvolvimento de homologação e afins. 
O artigo expõe conceitos e práticas de uso do utilitário Oracle Data 
Pump Export (EXPDP) e Data Pump Import (IMPDP) na atualização de 
schemas, geração de backup e outros em servidores demonstrativos de 
produção, homologação e desenvolvimento. O artigo é muito útil para 
entender melhor sobre os conceitos de exportação e importação de 
dados no Oracle e conhecer, na prática, alguma das possibilidades de 
utilização dessa ferramenta, que é bastante útil no dia a dia de um DBA.
Cenário
Atualização de schemas 
no Oracle 11gR2 com o 
Oracle Data Pump
No curso normal dos eventos em um banco de da-dos, os comandos DMLs (INSERT, UPDATE e DELETE) e SELECT são utilizados para extrair 
e inserir dados no banco de dados, porém há situaçõesem que será necessário um método muito mais rápido 
e eficiente para realizar operações de grande volume. 
Antes da versão Oracle 10g, era utilizado o utilitário 
export e import, que realizava a extração/importação 
dos dados como um processo de usuário no sistema 
operacional, usando uma sessão para recuperar os dados 
e transformá-los em arquivos, para depois salvá-los no 
sistema operacional. 
Após a chegada da versão Oracle 10g, o utilitário 
padrão passou a ser o Oracle Data Pump usando os 
clientes expdp e impdp, que rodam como um processo de 
servidor. Isso melhora consideravelmente o desempenho 
da extração e importação de dados, pois, por ser um 
processo de servidor, o acesso aos arquivos de dados e a 
SGA serão diretos, não sendo mais necessário recuperar 
os mesmos através de uma sessão como era realizado 
nos utilitários anteriores. 
O Oracle Data Pump é também base para muitos outros 
recursos-chave no banco de dados Oracle, como AWR, 
replicação Streams-Based, Logical Standby, Grid e em 
tablespaces transportáveis (Transportable tablespace). 
No entanto, não é o objetivo deste artigo detalhar cada 
um desses recursos.
Além do ganho em performance, os utilitários expdp 
e impdp trouxeram ganho na administração. É possível 
gerenciar o andamento de vários jobs se conectando a 
eles através de um usuário e senha que foram utilizados 
na execução da rotina ou com algum usuário que tenha 
os privilégios necessários para administrar o job. 
EstE artigo é do tipo mEntoring
saiba mais: www.dEvmEdia.com.br/mEntoring-saibamais
Após conectado a um determinado job, o mesmo poderá ser 
consultado, pausado, reiniciado, interrompido, ou até mesmo ter 
alguns parâmetros alterados. Iremos realizar a administração de 
um job mais adiante. 
Quando um job do Data Pump é iniciado, ao menos dois pro-
cessos se iniciam: um processo Data Pump Master (DMnn) e 
um ou mais processos Worker (DWnn). Como o nome sugere, o 
processo mestre irá controlar os outros processos workers. Se for 
utilizado paralelismo, cada DWnn poderá fazer uso de dois ou 
mais servidores de execução paralela (Pnnn). O processo interno 
do Oracle Data Pump não é o escopo desse artigo, sendo assim, 
não entraremos em mais detalhes referentes ao funcionamento 
interno do utilitário. 
Modos de exportação usando Oracle Data Pump
O Oracle Data Pump possui alguns modos específicos de 
exportação de dados que podem ser especificados na linha de 
comando ou usando o arquivo de parâmetros. Os modos que 
estão disponíveis para o Data Pump Export são: 
• “FULL EXPORT MODE”;
• “SCHEMA MODE”;
• “TABLE MODE”;
Edição 145 • SQL Magazine 33 
exportação deverá deter os privilégios necessários para que seja 
possível exportar todas as tabelas contidas nas tablespaces que 
forem especificadas, caso contrário, o usuário não conseguirá 
exportar tabelas que não são de sua propriedade. 
O modo “TRANSPORTABLE TABLESPACES” é especificado 
utilizando o parâmetro TRANSPORT_TABLESPACES. Nesse 
modo, a estrutura dos objetos será exportada assim como a 
estrutura dos objetos que são dependentes daqueles que estão 
armazenados no conjunto de tablespaces especificado. Nesse 
modo, existem algumas restrições como colunas que estiverem 
encriptadas não serão exportadas. 
Não se pode exportar uma tablespace e importá-la em uma ver-
são inferior à que foi exportada. O banco de dados destino deve 
ter a mesma ou uma versão superior ao banco de dados para onde 
que estão sendo exportada as tablespaces. 
Considerações de rede
Podemos especificar uma string de conexão quando formos 
executar o utilitário Data Pump Export. Este identificador pode 
especificar uma instância de banco de dados diferente do banco 
em que a variável SID está definida no momento. O identificador 
pode ser um alias contido no arquivo tnsnames.ora que aponta 
para um banco específico. 
A exportação irá utilizar o TNS para se conectar ao banco de 
dados e, através da autenticação de usuário e senha, será realizada 
a conexão no banco e então iniciado o processo de exportação dos 
dados. Para utilizar o TNS na execução da exportação de dados, 
é necessário que o listener esteja ativo e registrado. 
Um exemplo simples de como utilizar o TNS seria: “User/ 
password@TNS_NAME”. Assim, o cliente local se conectará ao 
banco de dados destino e realizará o procedimento. A especifica-
ção de uma string de conexão é diferente da utilização do parâme-
tro NETWORK_LINK, o mesmo será utilizado posteriormente.
O Oracle Data Pump permite filtrar dados de tabelas e os me-
tadados de objetos para ajudar a limitar os tipos de informação 
que estão sendo exportados. Os filtros são divididos em filtros 
de dados e metadados. 
O filtro de dados é realizado através dos parâmetros “QUERY” 
e “SAMPLE”, que especificam as restrições daqueles objetos que 
serão exportados. 
O parâmetro SAMPLE é usado para exportar ou importar 
uma determinada porcentagem de dados, seja de uma tabela 
específica ou do banco de dados inteiro (não iremos usá-lo 
nos exemplos). Uma breve descrição de como usar ele seria: 
“SAMPLE=”HR”.”EMPLOYEES”:70”. Esse parâmetro iria forçar 
o Data Pump a exportar somente 70% dos dados da tabela EXM-
PLOYEES no schema HR. 
Os filtros de dados podem ocorrer indiretamente por causa de 
um filtro de metadados. Ele poderá incluir ou excluir determina-
das tabelas com qualquer linha de dados associado. Os filtros de 
metadados são implementados através dos parâmetros INCLUDE 
e EXCLUDE. Os parâmetros INCLUDE e EXCLUDE são mutu-
almente exclusivos, independendo um do outro. 
• “TABLESPACE MODE”;
• “TRANSPORTABLE TABLESPACE MODE”.
Neste artigo iremos utilizar alguns desses modos, os demais 
poderão ser consultados na documentação de referência da 
Oracle 11gR2.
O modo “FULL EXPORT MODE” equivale à exportação 
completa do banco de dados, todos os dados e meta dados que 
estiverem no banco de dados, sendo uma exportação de todos os 
schemas e objetos presentes no banco. Para a execução do Data 
Pump Export nesse nível, é necessário que o usuário que estiver 
executando a rotina de exportação tenha o privilégio DATA 
PUMP_EXP_FULL_DATABASE. 
O modo de exportação por schema, “SCHEMA MODE”, ocorre 
quando é especificado o parâmetro schemas e equivale à expor-
tação específica de alguns determinados que forem especificados 
na linha de comando ou através dos arquivos de parâmetro. 
Esse modo é o padrão, sendo necessário ter o privilégio DATA 
PUMP_EXP_FULL_DATABASE para então poder especificar uma 
lista de schemas a serem exportados. 
Se o usuário utilizado na execução da rotina não tiver os 
privilégios necessários para exportação completa (FULL) do 
banco de dados, o mesmo poderá somente exportar seu pró-
prio schema. 
Objetos que estejam cruzados entre schemas, como uma trigger 
que está habilitada para uma tabela residente em um determina-
do schema, mas que pertence a outro que não for especificado 
no momento da exportação, não serão exportados. A menos que 
o schema em que o objeto pertence esteja ou seja adicionado na 
lista dos exportados. É possível especificar todos eles da lista um 
a um ou através do parâmetro “FULL”, o qual irá exportá-los no 
banco, exceto se houver alguma restrição utilizada com o parâ-
metro EXCLUDE. 
O modo de exportação conhecido como "TABLE MODE" é es-
pecificado utilizando o parâmetro TABLES. Nesse modo, somente 
aquelas tabelas que forem especificadas serão exportadas além de 
outros objetos pertencentes ao schema em questão. Para utilização 
desse modo, também é necessário que o usuário tenha o privilégio 
DATA PUMP_EXP_FULL_DATABASE para exportar tabelas que 
não estejam no próprio schema, caso contrário, o usuário poderá 
exportar somente suas próprias tabelas. 
As definições dos tipos de colunas presentes na tabela não serão 
exportadas, uma vez que deverão existirno banco de dados de 
destino. Assim como o modo de exportação de schemas, o modo 
de exportação de tabelas também não exporta referências de 
schemas cruzados. 
O modo de exportação de "TABLESPACES", é especificado com 
a utilização do parâmetro TABLESPACES. Nesse modo, somente 
as tabelas contidas no conjunto de tablespaces especificado se-
rão exportadas, se uma determinada tabela é exportada, então 
os objetos que dependem da mesma também serão exportados. 
É exportado tanto a estrutura física (meta dados) quanto os dados 
propriamente ditos. O usuário que executar o procedimento de 
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
34 SQL Magazine • Edição 145
Os filtros de metadados incluem ou excluem objetos de uma 
operação de exportação. 
Por exemplo: podemos especificar o modo “FULL”, mas espe-
cificar alguns filtros de metadados para, por exemplo, excluir 
determinados packages. Diversas adaptações poderão ser feitas 
com a base de alguns dos principais parâmetros utilizados e 
descritos neste artigo. 
O Oracle Data Pump gera três tipos de arquivo: arquivos de log, 
arquivos binários do Oracle (dumps), e os arquivos SQL (Structured 
Query Language). O arquivo SQL gerado são instruções DDL's que 
descrevem os objetos no banco que foram ou serão exportados e/
ou importados. É possível exportar os objetos sem nenhum dado 
(somente a estrutura física) para documentação do banco ou para 
criação dos mesmos em outros ambientes, entre outros fins. 
Os arquivos dumps unem todos os dados exportados em tags 
XML formatadas, o que causa um overhead nesses arquivos 
quando for descrever os dados. Os arquivos de logs contêm 
as informações referentes ao processo de exportação ou de 
importação de dados e ficam armazenados no diretório que for 
especificado no comando. 
O Oracle Data Pump requer que um diretório seja especificado 
e que o usuário usado na inicialização do job tenha os privilégios 
corretos. A versão do Oracle 11g que será abordada neste artigo já 
vem com alguns diretórios configurados. No entanto, não basta 
que os diretórios estejam criados no banco, os usuários que forem 
executar operações utilizando o mesmo deverão receber os privi-
légios de leitura e escrita nesses diretórios para que os arquivos 
possam ser criados no sistema operacional. 
Os diretórios são mapeados internamente no banco Oracle no 
momento da criação do mesmo com o comando CREATE OR RE-
PLACE DIRECTORY. Para este artigo, iremos criar um diretório 
específico mapeando um diretório já criado no sistema e nome-
ando o mesmo para facilitar na visualização e identificação do 
arquivo no sistema operacional. 
Estudo de caso
Para demonstrar como iremos atualizar os schemas no Oracle 
usando o Data Pump, serão utilizados uma VM Oracle Linux 
Server Release 6.3 e o Oracle Enterprise Edition 11.2.0. Vale lembrar 
que a utilização de alguns recursos requer a licença de uso e só 
estão disponíveis na versão Enterprise Edition, como paralelismo, 
compressão e encriptação.
Vamos iniciar criando um diretório no banco de dados Oracle, 
como mostra a Figura 1, executando o seguinte comando: 
CREATE OR REPLACE DIRECTORY DUMP_DIR AS ‘/u02/app/backup/’;
O comando OR REPLACE é utilizado para que não seja emitido 
um erro, reportando a existência do diretório. Caso o mesmo já 
exista, ele será recriado com o novo DIRECTORY_PATH que estiver 
no comando. É muito importante que seja analisada a localização 
ideal para a criação do diretório tendo em vista que serão arma-
zenados arquivos de grande volume de dados. 
Figura 1. Sintaxe do comando de criação de diretórios
Vale lembrar também que o Oracle não verifica se o diretório 
realmente existe no momento da criação. Para ter certeza de que o 
diretório será utilizado, crie-o antes de apontar com um diretório 
no banco de dados. A criação do diretório pode ser consultada 
através da view DBA_DIRECTORIES, conforme a Figura 2. 
Figura 2. Verificando a criação do diretório
Com o diretório criado, podemos iniciar a explicação dos pa-
râmetros que serão utilizados na exemplificação de uma atuali-
zação de schemas e também outros parâmetros que poderão ser 
utilizados em outros cenários. 
Vamos iniciar usando o schema SH para explicar uma maneira 
de exportar o próprio schema usando o utilitário Oracle Data 
Pump Export (expdp). A criação do usuário não é necessária caso 
os schemas de exemplos tenham sido criados junto com o banco 
de dados. 
O usuário que for realizar a exportação deverá ter privilégios 
de leitura e escrita sobre o diretório em que foi criado no Oracle. 
Vamos tentar realizar a exportação sem os devidos privilégios. 
Inicialmente, vamos realizar a primeira exportação com o usuário 
SH com todos os privilégios revogados (somente foi atribuído o 
privilégio CREATE SESSION). 
A Figura 3 representa o privilégio do usuário SH que será usado 
para exportar seu próprio schema. 
Figura 3. Privilégios do usuário SH
Como esperado, o comando falhou por conta de privilégios 
(Figura 4), mas os privilégios de leitura e escrita já foram conce-
didos ao usuário SH. Por que ainda assim falhou por esse motivo? 
O usuário em questão, que será exportado, precisará de alguns 
privilégios além desse, como cota de gravação em uma determi-
nada tablespace. 
Além disso, será necessário que o schema tenha privilégios 
de criação de objetos que podemos conceder através da role 
RESOURCE, que possui alguns privilégios específicos inclusive, 
Edição 145 • SQL Magazine 35 
dentre eles, o UNLIMITED TABLESPACE que garante ao schema 
a gravação em qualquer tablespace no banco de dados (o que é 
um erro de administração e deverá ser revogado). 
Vamos alterar o schema SH, oferecer cota ilimitada na tablespace 
USERS a ele e também conceder o privilégio de RESOURCE e re-
vogar o UNLIMITED TABLESPACE. Após conceder esses passos, 
o usuário RH possuirá os privilégios apresentados na Figura 5.
Figura 5. Após revogação do privilégio UNLIMITED TABLESPACE
Agora será necessário conceder privilégio de cota em uma de-
terminada tablespace. Nesse caso, vamos definir na tablespace 
USERS que é default na instalação dos schemas de exemplo, como 
mostra a Figura 6.
Vamos novamente testar a exportação do schema usando o SH. 
Para isso, execute o seguinte comando:
expdp sh/oracle@NEW directory=DUMP_DIR dumpfile=schema_sh.dmp 
logfile=exoprt_sh.log schemas=SH
Dessa vez o comando é executado corretamente e gera o arquivo 
no diretório DUMP_DIR, como mostra a Figura 7.
Bom, inicialmente a primeira exportação ocorreu com sucesso. 
Os parâmetros utilizados que não foram descritos ainda são 
DUMPFILE e LOGFILE. 
Figura 4. Erro ao realizar a exportação
Figura 6. Adicionando cota ilimitada na tablespace users ao schema SH
Figura 7. Sucesso na exportação do schema SH
O parâmetro DUMPFILE é utilizado para especificar o nome do 
arquivo que será gerado. Dessa forma, não foram especificados 
múltiplos arquivos para serem criados, mas poderíamos utilizar 
o parâmetro “%U” junto com o nome do arquivo de dump (dump-
file) e especificar outro parâmetro FILESIZE com o tamanho dos 
arquivos para que fosse distribuído em arquivos com tamanhos 
definidos. 
O parâmetro LOGFILE especifica o nome do arquivo de log da 
rotina. O mesmo será criado no diretório que estiver mapeado pelo 
DUMP_DIR no banco de dados e registrará cada uma das etapas 
em que o job Data Pump estiver. Já no parâmetro SCHEMAS, 
especificamos somente os schemas que serão exportados. Nesse 
exemplo somente SH foi especificado. 
Usamos também uma configuração de rede para se conectar à 
instância NEW usando o TNS @NEW. É interessante usar o TNS 
para ter certeza de que a instância correta está definida como 
alvo da operação. Após a criação do arquivo, o mesmo poderá ser 
reutilizado em qualquer outro servidor que tenha um utilitáriocom versão compatível para realizar o import. 
Nesse primeiro exemplo foi usado o SH para exportar seu pró-
prio schema. O mesmo não possui privilégios especiais, como 
o de DBA que é o mais indicado para realizar determinados 
procedimentos de administração no banco de dados. Portanto, 
vamos usar o usuário TESTE para os próximos exemplos com 
privilégio de DBA atribuído a ele.
Uma dica é que seja muito bem analisado o comando, a sintaxe 
da instância alvo da importação, os schemas alvos, as tabelas 
alvo e os dados para que não tenhamos maiores problemas (por 
exemplo, alguma atualização a mais ou no lugar errado). 
Um parâmetro que ajuda bastante a compreender o que será feito 
na importação é SQLFILE. Ele é utilizado para criar um arquivo 
SQL com todas as linhas de comandos que serão executadas du-
rante a importação do arquivo de dump. 
Assim, quando quisermos saber o que será importado e quais 
objetos serão importados, ou até mesmo para compreender a 
estrutura do banco de dados, usamos esse parâmetro. A seguir, 
realizaremos a criação de um arquivo SQL com o parâmetro 
SQLFILE. 
O uso do SQLFILE só pode ser gerado através de um DUMP, 
dessa forma, somente antes de uma importação. É importante 
lembrar que, em relação ao uso do SQLFILE, quando o mesmo 
for especificado, somente o arquivo é criado, não há nenhuma 
importação. A Figura 8 apresenta um exemplo de uso do SQLFILE.
O arquivo SQL gerado é demonstrado na Figura 9. Esse arquivo 
é criado na mesma localização dos arquivos de log e dos arquivos 
de dumps, aquele que estiver especificado na criação do diretório. 
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
36 SQL Magazine • Edição 145
Figura 9. Arquivo SQL gerado com o uso do parâmetro SQLFILE
Figura 8. Uso do parâmetro SQLFILE
Os comandos SQL que serão executados no momento da im-
portação estarão presentes nesse arquivo. Em alguns cenários, é 
importante levar os privilégios do usuário ao novo ambiente, para 
isso, precisamos verificar quais privilégios o schema em questão 
tem no ambiente de origem e criar, através dessas informações, 
concessão de privilégios no novo ambiente. 
 O SELECT apresentado na Listagem 1 recupera informações 
referentes à concessão de privilégios provenientes de outros 
schemas ao usuário SH que estamos testando. É um SQL dinâ-
mico e retornará comandos que deverão ser executados no novo 
ambiente, caso seja solicitado pelo cliente. Lembrando que o uso 
de privilégios não é o foco do artigo. Para entender mais sobre 
privilégios consulte a docu mentação oficial da Oracle.
Listagem 1. Recuperação de informações referentes a concessão de privilégios
SELECT ‘GRANT ‘||privilege||’ ON ‘||owner||’.’||table_name||’ TO ‘||grantee||’;’ 
FROM DBA_TAB_PRIVS 
WHERE grantee = ‘&NOME_SCHEMA’;
Serão apresentados a partir de agora alguns parâmetros que po-
demos usar na exportação e importação de dados com o utilitário 
Oracle Data Pump para atender uma solicitação de replicação de 
um determinado schema no banco de dados na qual será neces-
sário manter o de origem, além de criar um novo com um nome 
diferente, porém com os mesmos objetos. Para isso, podemos usar 
o parâmetro REMAP_SCHEMA. 
Edição 145 • SQL Magazine 37 
O parâmetro REMAP_SCHEMA serve 
para tornar aquele schema especificado em 
um novo com um novo nome. Caso seja 
necessário, é possível redirecionarmos a 
tablespace em que o novo schema irá gra-
var seus dados também, para isso, teremos 
que usar o parâmetro REMAP_TABLE-
SPACE que gravará nessa nova tablespace 
os dados do schema de origem usado na 
importação. 
Um exemplo de utilização do parâmetro 
REMAP_SCHEMA e REMAP_TABLESPA-
CE será demonstrado a seguir. Imagine-
mos que o cliente pediu para clonarmos 
um determinado schema. O schema HR 
de um servidor origem (NEW), simbo-
lizando um servidor de produção, para 
outra instância, simbolizando o servidor 
de homologação (ORCL).
 Esse, por sua vez, em produção, está 
usando a tablespace USERS e o cliente 
quer que o clone desse schema se cha-
me HR_HML e que grave na tablespace 
EXAMPLE. Para atendermos a essa soli-
citação, o procedimento deverá: 
• Exportar o schema HR de produção (ver 
código a seguir);
• Importar o schema HR remapeando 
tanto ele, quanto a tablespace;
• Compilar os objetos inválidos.
Podemos ver um exemplo no código a 
seguir:
expdp teste/teste directory=DUMP_DIR dumpfile=HR_
prod.dmp logfile=HR_prod.log schemas=HR cluster=no 
reuse_dumpfiles=yes 
Veja que estamos usando o parâmetro 
CLUSTER=NO que força o utilitário a 
usar somente um node em um servidor 
RAC. Esse parâmetro é usado para não 
onerar o banco de dados no momento da 
rotina. A Figura 10 mostra a finalização 
da exportação. 
Com o arquivo DUMP disponível, po-
demos realizar a importação utilizando 
os parâmetros que serão necessários para 
atender à solicitação do cliente. 
 Temos assim configurado duas instân-
cias de testes nesse servidor: uma com o 
SID NEW e outra com o SID orcl. O DUMP 
de origem assume que a instância NEW 
se trata de uma instância de produção e 
que a instância ORCL é a de homologação, 
nesse caso, a instância alvo da atualização 
(Figura 11). 
A exportação foi realizada usando o 
TNS @NEW como pode ser verificado 
no comando de exportação realizado. 
Nosso exemplo de importação, a pedido 
do cliente, será na instância ORCL, então 
devemos trocar o TNS e usar @ORCL 
para que a importação ocorra na instância 
correta sem ter que se preocupar com o 
ORACLE_SID que estiver definido na 
variável de sistema.
 Os comandos de importação seguem a 
mesma ideia daqueles de exportação que 
usamos. Portanto, a sintaxe é semelhante e 
fácil de entender. Antes de falar da impor-
tação, é interessante falar dos privilégios e 
da segurança de conceder os mesmos em 
ambientes reais.
Para realizar os procedimentos de expor-
tação e importação de diversos schemas, o 
usuário que estiver realizando a chamada 
ao utilitário deverá ter os privilégios IMP_
FULL_DATABASE e EXP_FULL_DATA-
BASE. Nesse caso, é fortemente recomen-
dado que o procedimento seja realizado por 
usuários DBAs, para que não comprometa 
a segurança no banco de dados, pois caso 
sejam concedidos os privilégios citados 
a um usuário schema qualquer de uma 
aplicação e o mesmo realizar diariamente 
importações no banco de dados, ele poderá 
afetar outros schemas com um simples 
erro de chamada e, com isso, comprometer 
ou perder diversos dados ou até schemas 
inteiros em ambientes reais. 
O usuário usado para a importação 
apresentada na Figura 12 é um usuário 
com privilégios de DBA e será usado para 
os próximos testes. 
No procedimento apresentado, foi uti-
lizado paralelismo com o parâmetro PA-
RALLEL=2, que é uma feature de versões 
Enterprise Edition e que pode ser usada 
com o coringa “%U” para criar vários 
arquivos de dumps paralelos, conforme 
o número de paralelismo ou de filesize. 
 O schema HR_HML foi criado na instância 
ORCL com sua tablespace remapeada para a 
tablespace solicitada pelo cliente. É interes-
sante lembrar que, como o cliente pediu um 
clone idêntico ao schema HR, isso significa 
que as permissões que ele recebeu no am-
biente de produção deverão ser replicadas no 
ambiente de homologação. Na Figura 13 ele é 
gerado com o mesmo comando que executa-
mos anteriormente, porém com o parâmetro 
SQLFILE para identificar os GRANTS que 
o usuário HR trouxe no arquivo de DUMP 
de produção. Observe que os privilégios que 
vieram são somente aqueles concedidos pelo 
HR, não recebido por ele. 
A Figura 14 contém uma adaptação 
do SQL dinâmico que foi demonstrado 
anteriormente. Ele recupera os grants 
que o schema HR recebeu no ambiente 
de produção e altera o nome do schema 
HR para HR_HML para que sejam repli-
cados no ambiente alvo conforme mostra 
aListagem 2. 
Figura 10. Finalização da exportação
Figura 11. Instâncias em execução no servidor de testes
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
38 SQL Magazine • Edição 145
Figura 12. Clonagem do schema HR de produção para uma instância de homologação
Figura 13. Grants do usuário HR que serão remapeados ao usuário HR_HML
Figura 14. Privilégios do schema HR no ambiente de produção
Listagem 2. Recuperação dos grants e alteração do nome do schema
select grantee schema, 
‘GRANT ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’_HML’||’;’ grants, 
(select name from v$database) instancia
from DBA_TAB_PRIVS where grantee = ‘HR’;
Figura 15. Falha na importação do schema HR remapeando para HR_HML
No caso, o HR recebeu somente esse 
privilégio no nosso exemplo com o sche-
ma SH, no entanto, em ambientes reais, 
a quantidade de permissões é grande e 
deverá ser gerada com a ajuda de um SQL 
dinâmico. 
Como a exportação do schema foi realiza-
da pelo usuário HR, o mesmo não pode ser 
importado no novo servidor, remapeando 
para um novo schema, porque o usuário 
HR não tem privilégios para a criação do 
usuário caso haja a necessidade. Portanto, 
a tentativa de importar, remapeando o 
schema HR para HR_HML, falha como 
pode ser visto na Figura 15. 
Para que o procedimento seja concluí-
do, devemos exportar o schema HR do 
ambiente de origem com um usuário que 
tenha privilégios de DBA para que o sche-
ma HR_HML seja criado no momento da 
importação na nova instância. 
Assim, é necessário realizar o procedi-
mento com usuário que tenha os privilé-
gios necessários, no caso, foi utilizado o 
nosso usuário criado para testes o schema 
TESTE. 
Após exportar novamente o schema 
HR, agora com o schema TESTE em pro-
dução, o DUMP deverá ser copiado para 
a localização do diretório DUMP_DIR na 
nova instância e, então, reexecutado o 
procedimento de importação. Feito isso, 
o schema HR_HML será criado na nova 
instância e terá os mesmos objetos que o 
HR na instância de produção. Se o proce-
dimento de exportação for realizado com 
usuário DBA, os privilégios de sistema 
já estão no arquivo de DUMP e aqueles 
privilégios de objetos de outros schemas 
foram gerados através do SQL dinâmico 
que criamos. A Figura 16 compara as 
duas instâncias, onde todos os objetos 
foram recriados. Nesse exemplo o schema 
HR possui uma variedade bem limitada 
de objetos, se comparado a schemas de 
produção reais, que em alguns casos 
deverão ter um tratamento diferenciado 
para os dados. 
Com o NETWORK_LINK, podemos 
importar um schema sem ter a necessi-
dade de gerar o arquivo de dump (.dmp). 
Fazemos essa importação especificando o 
parâmetro NETWORK_LINK, disponí-
vel desde a versão 10g do Oracle. Vamos 
agora mostrar um exemplo de uso do 
NETWORK_LINK. 
Suponha que é necessário atualizar o 
schema SH no ambiente de homologa-
ção (ORCL) com os dados da instância 
do banco de produção (NEW). O cliente 
deseja atualizar as tabelas do schema SH 
de homologação com os dados atuais de 
produção. O cliente quer que na tabela 
promotions, coluna promo_name, os da-
Edição 145 • SQL Magazine 39 
dos possuam em alguma parte a palavra 
INTERNET e/ou MAGAZINE. Também 
quer que o total vendido da tabela sales 
AMOUNT_SOLD seja maior ou igual a 
500 e que os produtos vendidos estejam no 
intervalo de 25 a 110. Além disso, também 
quer que sejam somente atualizados os 
dados, sem estruturas, sem privilégios, 
sem estatísticas e que o job seja nomeado 
por IMPDPDATAv1.
Para atender à solicitação, deve-se usar 
o modo de importação em rede com o 
parâmetro network_link. Para usar o 
parâmetro, devemos ter configurado os 
links entre os bancos de dados. Nesse caso, 
somente um link é necessário, pois a atua-
lização é somente de uma via, de produção 
para homologação, mas nos servidores de 
teste foram configurados dois links para 
explicar melhor como a ligação deve ser 
realizada (Figura 17). 
É importante ter configurado o tnsnames 
apontando para os servidores alvos para 
que a conexão seja realizada com sucesso. 
Um teste simples de conexão via TNS pode 
ser realizada usando o comando TNSPING 
(nome do alias), que corresponde à ligação 
entre o servidor e outro servidor remoto ou 
o mesmo, como mostra a Figura 18.
Para o exemplo vamos usar os seguintes 
parâmetros:
• USER;
• DIRECTORY;
• CONTENT;
• QUERY;
• EXCLUDE;
• NETWORK_LINK;
• LOGFILE;
• PARFILE.
Inicialmente vamos tratar sobre o PAR-
FILE que é um arquivo que contém os 
comandos que serão executados pelo utili-
tário. Ele é muito usado para organizar me-
lhor os parâmetros e tornar compreensível 
um comando de exportação ou importação 
que seja complexo demais. 
Figura 16. Imagem comparativa entre os schemas nas instâncias de produção e homologação
Figura 17. Teste de ligação usando os database links
Os comandos apresentados na Listagem 3 
estão presentes no arquivo de testes 
chamado imdpdatav1.par que será usa-
do depois na chamada do comando de 
importação.
Embora a solicitação seja de produção 
para homologação, o processo de im-
portação deve ser executado no servidor 
de homologação, que irá se conectar ao 
servidor de produção e importará os 
dados do schema SH conforme solicitado 
(Figura 19).
Vamos agora explicar alguns dos parâ-
metros que foram usados. 
• PARFILE: serve para armazenar os 
comandos que serão usados na chamada 
dos utilitários expdp ou impdp;
• NETWORK_LINK: usado para es-
tabelecer uma conexão remota com um 
determinado banco de dados de origem 
para realizar as operações;
• QUERY: serve para filtrar os dados que 
serão exportados ou importados;
• EXCLUDE: serve para excluir aqueles 
objetos que não devem ser considerados 
no arquivo de DUMP ou na importação;
• JOB_NAME: usado para nomear a roti-
na de importação ou de exportação;
Listagem 3. Comandos chamados durante a importação
NETWORK_LINK=dblink_new
DIRECTORY=DUMP_DIR
SCHEMAS=SH
QUERY=SALES:’”WHERE AMOUNT_SOLD >= 500 AND PROD_ID BETWEEN 25 AND 110”’
QUERY=PROMOTIONS:’”WHERE PROMO_NAME LIKE UPPER(‘%INTERNET%’) OR PROMO_NAME LIKE 
UPPER(‘%MAGAZINE%’)”’
LOGFILE=IMPDPDATAv1.LOG
EXCLUDE=STATISTICS,CONSTRAINT,VIEWS,DIMENSIONS,TABLE:”=’COSTS’”
JOB_NAME=IMPDPDATAv1
Figura 18. Teste de conexão via TNSPINGFigura 18. Teste de conexão via TNSPING
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
40 SQL Magazine • Edição 145
• ATTACH: usado para se conectar ao painel de controle do 
IMPDP ou EXPDP durante a execução de um determinado job. 
Ele pode ter parâmetros alterados durante sua execução, pode ser 
pausado ou terminado através do painel de controle. 
Como ainda não usamos o parâmetro ATTACH, que é bastante 
útil para gerenciar as rotinas quando as mesmas estiverem em 
execução, vamos iniciar um exemplo exportando o banco de dados 
inteiro. Para isso, precisamos especificar o parâmetro FULL=Y. En-
tão, inicie um job com os seguintes parâmetros somente para teste: 
EXPDP teste DIRECTORY=dump_dir DUMPFILE=dump_full.dmp FULL=y
A Figura 20 mostra a conexão ao painel do job de exportação. 
Observe que o número máximo de paralelismo utilizado neste 
caso foi 1, o que pode prejudicar o desempenho das operações 
realizadas no contexto dessa exportação. 
Podemos alterar o paralelismo da rotina. Para isso, basta utilizar 
o comando “Export> parallel=4” que o job irá passar a usar parale-
lismo igual a 4. O uso de paralelismo é mais recomendado quando 
se divide os arquivos usando o atributo “%U” após o nome do 
arquivo de dump, por exemplo, DUMPFILE=dump_full_part_%
U.dmp. Podemos observar esse ajuste na Figura 21. 
Conforme apresentado na imagem, podemos identificar que o 
paralelismo passou a ser 4 ao invés de 1. Nesse caso, é instruído 
ao job que ele utilizeos recursos do servidor e execute em para-
lelismo igual a 4. A utilização de paralelismo em ambientes reais 
requer licença de uso, portanto, antes de utilizar alguns recursos 
disponíveis no enterprise edition, tenha conhecimento das options 
que fazem parte do suporte do cliente.
Para interrompermos um job, basta utilizarmos o comando 
stop_job. Após interrompida a execução do job, ao se conectar ao 
mesmo, veremos o status dele como indefinido. 
Figura 19. Importação em andamento
Figura 20. Conexão ao painel do JOB de exportação EXPDP
Figura 21. Verificando alteração no paralelismo
Edição 145 • SQL Magazine 41 
O mesmo poderá ser restabelecido 
ou ter parâmetros alterados antes de 
reiniciar conforme pode ser observado 
na Figura 22.
Após conectado no job, podemos conti-
nuar a execução do mesmo com o comando 
CONTINUE. Se usarmos o comando con-
tinue client, o job irá imprimir novamente 
as mensagens no prompt. Para retornar ao 
menu do EXPDP, pode-se usar o comando 
Ctrl+C para interromper a impressão dos 
logs da rotina e retornar ao menu. Após 
a finalização da rotina, não podemos nos 
conectar ao job novamente (Figura 23). Os 
dados das execuções do Data Pump po-
dem ser consultados na view DBA_DATA 
PUMP_JOBS.
Muitos outros comandos e possibilidades 
de uso do Data Pump podem ser consul-
tados através da documentação oficial da 
Oracle 11gR2 (ver seção Links).
O Oracle Data Pump é uma excelente 
ferramenta que incorpora recursos dos 
antigos EXPORT (exp) e IMPORT (imp), 
além de muitos outros recursos. O novo 
Data Pump da Oracle trouxe muito mais 
velocidade na movimentação de dados 
e um grande avanço na administração 
das rotinas de exportação e importação 
com o painel de controle oferecido pela 
ferramenta. 
Os comandos do utilitário são bastante 
amigáveis e de fácil entendimento. A fer-
ramenta é muito poderosa com a utilização 
de alguns parâmetros. Pode ser usada, por 
exemplo, na movimentação de tablespaces 
em uma eventual migração de versão ou 
de plataforma com o uso do remapea-
mento de datafiles. Além disso, existem 
inúmeros cenários no qual o Oracle Data 
Pump poderá ser utilizado para realizar 
as operações de um grande de volume 
de dados.
Figura 22. Reconectando no JOB export_full e verificando status
Figura 23. Tentando reconectar no JOB
Links:
Oracle Database 11gR2
https://docs.oracle.com/cd/E11882_01/server.112/
e22490/toc.htm
Oracle Data Pump 11gR2
http://download.oracle.com/otndocs/products/database/
enterprise_edition/utilities/pdf/Data Pump11gr2_techo-
ver_1009.pdf
Oracle Data Pump Import 11gR2
https://docs.oracle.com/cd/E11882_01/server.112/
e22490/dp_import.htm#SUTIL300
Oracle Data Pump Export 11gR2
https://docs.oracle.com/cd/E11882_01/server.112/
e22490/dp_export.htm#SUTIL825
Marcelo Thomaz Cicala
marcelocicala@gmail.com
Brasileiro, solteiro, residente 
em São Paulo capital, Tecnó-
logo em banco de dados pela FATEC, 
DBA Oracle na empresa DBACorp.
Autor
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
C
M
Y
CM
MY
CY
CMY
K
Porta80_AnINst.pdf 1 18/09/2011 14:58:37