Disciplina de Banco de Dados II

Prof. André Gustavo Schaeffer

Instituto Federal de Educação, Ciência

e Tecnologia do Rio Grande do Sul
Campus Erechim
Universidade Federal da Fronteira Sul
Campus Erechim

[email protected]
https://youtube.com/user/andreschaeffer

Apresentação

Objetivos

Avaliação

Ementa e tópicos abordados

A ementa de nossa disciplina compreende tópicos que serão divididos em 3 partes.

Parte 1 - Transações em Bancos de Dados

➔ Commit
➔ Rollback
➔ Savepoints
➔ Locks
➔ Deadlocks
➔ Suporte a esquemas Objeto-Relacionais

Parte 2 - Programação procedural – PL-SQL

➔ Estrutura do bloco PL-SQL
➔ Construções de programa
➔ Declarando variáveis
➔ Tipos de dados básicos
➔ O atributo %type
➔ Variáveis do tipo LOB
➔ Blocos aninhados e escopo de variável
➔ Consultando dados
➔ Inserindo dados
➔ Atualizando dados
➔ Excluindo dados
 ➔ Instruções COMMIT e ROLLBACK
➔ Procedimentos, Funções e Gatilhos em PL-SQL
➔ Cursores SQL
➔ Atributos de um cursor SQL
➔ Criando estruturas de controle
➔ Instruções IF
➔ Loop
➔ O atributo %ROWTYPE
➔ Vantagens do uso do atributo %ROWTYPE
➔ Cursores implícitos e explícitos
➔ Declarando um cursor explícito
➔ Abrindo um cursor
➔ Extraindo dados de um cursor
➔ Fechando o cursor
➔ Atributos %ISOPEN, %NOTFOUND e %ROWCOUNT
➔ Cursores com registros
➔ Loops FOR de cursores
➔ Loops FOR de cursores usando subconsultas
➔ Cursores com parâmetros
➔ Cursores com subconsultas
➔ Tratando exceções

Parte 3 - Administração de Bancos de Dados

➔ Instalação do Oracle 10 XE no Linux
➔ Oracle Application Express
➔ Backup e recovery do banco de dados (algumas formas de realizar)
➔ Utilitários de importação e exportação - Export e Import
➔ Utilitário de carga de dados - SQL Loader
➔ Flash Back Queries
➔ Visões e Sinônimos
➔ Database Links
➔ Tuning de consultas SQL
➔ Processamento de consultas SQL
➔ Criação de Snapshots para replicação de dados
➔ Criação de Jobs de replicação
➔ Shell Scripts na administração de bancos de dados
➔ Ferramentas de administração
 Principais características da abordagem de um banco de dados
versus abordagem de processamentos de arquivos

 natureza autodescritiva do sistema de banco de dados

a. dados e metadados no catalogo do BD
b. no processamento de arquivos, a definição dos dados fica interna ao programa. Ex.: Structs
em C++ e a Data Division em COBOL

 Exer - Exemplo de Programa em C

 isolamento entre os programas e os dados, e a abstração de dados

a. alterações na estrutura de arquivos implicam em alteraçoes nos programas
b. BD orientados a objeto e objeto-relacionais suportam operações abstratas. A abstração
engloba tanto a estrutura quanto as operações sobre os dados

 suporte para múltiplas visões dos dados

a. “views” permitem conceder acesso somente a dados específicos de uma ou mais tabelas

 compartilhamento de dados e processamento de transações multi-usuários

a. controle de concorrência
b. controle de deadlocks
c. transações
d. isolamento de transações
e. atomicidade de transações
 Vantagens e/ou pré-requisitos de sistemas de banco de dados

1. evitar redundância de informações

2. possibilitar restrição de acesso por perfis de segurança
3. garantir armazenamento persistente
4. exibir resultados em tempo hábil
5. possibilitar a execução de backups do banco de dados e suas respectivas restaurações
6. dispor de ferramentar para garantir a integridade dos dados
7. permitir fazer uso de padronizações
8. propiciar facilidade de uso visando reduzir o tempo de desenvolvimento de uma aplicação
9. possuir flexibilidade no que diz respeito a alterações estruturais, sem impactar nos dados
 Arquitetura de um Banco de Dados e seus Módulos Componentes

A arquitetura de um banco de dados é o design estrutural que define como os dados são organizados,
armazenados e acessados dentro de um sistema de gerenciamento de banco de dados. A figura abaixo mostra
a arquitetura do SGBD Oracle que utilizaremos em nossas aulas práticas.
 Sobre a nossa configuração de laboratório
Nosso banco de dados para as práticas será uma versão gratuita do banco de dados Oracle, o Oracle
Database XE (eXpress Edition) 10g. Levando em conta aspectos pedagógicos e custos relacionados,
utilizamos essa versão de banco de dados em um Ubuntu Linux 11.10 executando em uma máquina virtual,
no caso a Oracle VM Virtual Box 7.0. Maiores detalhes de utilização da máquina virtual, do ambiente
Linux e do banco de dados serão vistos no decorrer desta disciplina.

Um esquema lógico-abstrato do funcionamento do banco de dados pode ser visto na figura a seguir.

Uma instância do banco de dados Oracle é composta

por sua área de memória alocada e mais os processos
em background. A área de memória se divide em
SGA e PGA. A PGA representa porções de memória
controladas por um processos que gerenciam
conexões com clientes do banco de dados. A PGA de
uma conexão é usada também para guardar
informações sobre a sessão que a criou. A SGA se
divide em:
 Buffer Cache: armazena os dados sendo
consultados ou atualizados. É o primeiro
lugar onde o banco de dados procura uma
informação para evitar um acesso a disco
desnecessário, que é menos performático.
 Shared Pool: armazena informações
compartilháveis por todos os usuários do BD,
como sentenças SQL compiladas,
informações do dicionário de dados,
descrições de índices, privilégios, etc ...
 Redo Log Buffers: é um espaço usado para
aprimorar a geração dos Redo Log Files,
base de qualquer processo de recovery do
banco de dados Oracle.
 Large Pool: área usada para armazenamento
de requisições de controle para processos e
operações de I/O.

Os Background Processes são os processos responsáveis por gerenciar a instância do banco de dados.
Ex.: DBWR, LGWR, CKPT, SMON, PMON, ARCH, RECO, etc …

Se formos agora ao sistema operacional e o banco de dados da máquina Ub11Exer estiver ativado, ao
executarmos o comando
$ ps -ef | grep xe_ veremos todos os processos Linux responsáveis pelo gerenciamento do nosso
BD.
 O banco de dados é um conjunto de
estruturas lógicas e físicas que juntas contêm
dados e metadados para as aplicações.

 Tablespaces são agrupamentos

lógicos de datafiles
 Control File é um arquivo binário
para controle da estrutura física do
BD e informações sobre backup e
recovery
 Server Parameter File é um arquivo
binário com os parâmetros de
inicialização da instância
 Password File é usado para
autenticar acessos remotos como
usuário SYS
 A Flash Recovery Área é uma área
física para armazenamento de dados
para fins de backup e recovery

A Flash Recovery Área é composta basicamente por:

 arquivos físicos que compõe o BD (datafiles, control file e o server parameter file)
 online redo log files, que gravam todas as alterações feitas no banco de dados
 archived redo log files, que juntamente com os online redo log files, constituem um registro de
todas as alterações “comitadas” no BD desde o último backup executado

Na Flash Recovery Área, os backup são guardados na forma de coleções chamadas de Conjuntos de Backup,
acessíveis pelas ferramentas do próprio banco de dados Oracle.
 Árvore de diretórios da instalação padrão

Linux:
/usr/lib/oracle/xe/oradata/XE

Windows:
C:\oraclexe\oradata\XE

Linux:
/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE

Windows:
C:\oraclexe\app\oracle\flash_recovery_area\XE

Outros diretórios importantes:

Linux /usr/lib/oracle/xe/app/oracle/admin/XE Pastas com arquivos de log gerados pela instância, inclusive o
Windows C:\oraclexe\app\oracle\admin\XE “alert_xe.log”

Linux /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
Pastas onde fica instalado o aplicativo
Windows C:\oraclexe\app\oracle\product\10.2.0\server
Configuração da máquina servidora como cliente de outros
Linux /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin
bancos de dados e do processo responsável por estabelecer
Windows C:\oraclexe\app\oracle\product\10.2.0\server\network\admin
conexões com o próprio banco de dados

 Exer – Instalação do Oracle 10 XE no Ubuntu

 Exer – Oracle Application Express

A SQL query walks into an empty coffee shop toward

the tables and asks: hey guys, can I join you?
 Dados que usaremos no laboratório

Os modelos que usaremos em nosso laboratório são simplificações de sistemas reais para fins didáticos. São
3 usuários ou esquemas de banco de dados: POS, EOR e HR.

Tabelas e Modelo E/R do esquema EOR

(Transações em Bancos de Dados)

CIDADE 6129 linhas

CLIENTE 3500 linhas
ESTOQUE 229 linhas
PRODUTO 1756 linhas
VENDA 1536512 linhas

Tabelas e Modelo E/R do esquema HR

(PL-SQL)

EMP 14 linhas
DEPT 5 linhas
Tabelas e Modelo E/R do esquema POS
(Administração de Bancos de Dados)

INTERFACE_VEICULOS 0 linhas
TCIDADES 9866 linhas
TCLIENTES 26535 linhas
TEMPRESAS 1 linhas
TESTABELECIMENTOS 26931 linhas
TITENS_NFS 386115 linhas
TNFS_SAIDA 50066 linhas
TPAISES 75 linhas
TUFS 114 linhas
 Parte 1 - Transações em Bancos de Dados
Um curso introdutório de Bancos de Dados ou mesmo a disciplina de Bancos de Dados I talvez já tenha
abordado a temática das transações. No entanto, faremos algumas operações práticas sobre este tópico a fim
de aprimorar seu conhecimento sobre o assunto ou mesmo introduzir o mesmo para quem não conhece.

Transações em bancos de dados são operações lógicas e atômicas que manipulam dados dentro de um
sistema de gerenciamento de banco de dados. Uma transação pode ser composta por uma ou mais operações,
como inserção, atualização, exclusão ou consulta de dados.

A principal característica das transações é a atomicidade, que significa que todas as operações dentro da
transação são tratadas como uma unidade indivisível. Isso significa que ou todas as operações dentro da
transação são executadas com sucesso e os dados são atualizados no banco de dados, ou nenhuma operação é
realizada e o banco de dados permanece inalterado. Além da atomicidade, as transações também devem
obedecer a propriedades ACID:

Atomicidade: Como mencionado, uma transação é uma operação atômica, ou seja, todas as operações dentro
dela são tratadas como uma única unidade, garantindo que todas sejam concluídas com sucesso ou que
nenhuma seja executada.

Consistência: A transação deve garantir que o banco de dados passe de um estado consistente para outro
estado consistente. Isso significa que todas as restrições de integridade referencial e regras de negócios
devem ser mantidas antes e após a transação.

Isolamento: Cada transação deve ser isolada das outras transações concorrentes que estão ocorrendo
simultaneamente no sistema. Isso garante que o resultado de uma transação não seja afetado por outras
transações concorrentes e vice-versa.

Durabilidade: Após o commit de uma transação (confirmação das operações realizadas), as mudanças feitas
por essa transação devem ser permanentes e persistir mesmo em caso de falha do sistema, garantindo que os
dados permaneçam consistentes mesmo após eventos como falhas de energia ou reinicializações do sistema.

 Exer - Transações
 Exer – Esquema Objeto-Relacional
 Parte 2 - Programação procedural – PL-SQL
Neste ponto de seu estudo sobre o funcionamento dos bancos de dados, você já deve ter algum nível de
familiaridade com a linguagem SQL, e já deve ter notado que, apesar de poderosa, ela não consegue por si só
implementar uma série de recursos e necessidades que temos e que são atendidos pelas linguagens de
programação, como por exemplo loops, estruturas if-then-else, procedimentos e funções. No contexto do
banco de dados Oracle, isso é atendido pela Procedural Language (PL-SQL). Veremos em nossos exercícios
apenas exemplos simples direcionados a alunos que estão tendo seu primeiro contato com o assunto. A
linguagem PL-SQL oferece recursos como tratamento de exceções, ocultação de informações, orientação a
objetos, assim como a inserção dos recursos e necessidades citados anteriormente. Permite que as instruções
da linguagem SQL sejam incluídas em unidades procedurais de código e estruturadas em blocos. Ela é usada
na construção de objetos do banco de dados Oracle como procedimentos, funções, gatilhos e pacotes
(procedures, functions, triggers e packages).

Você poderia questionar: mas tais necessidades e recursos, se não fossem contemplados pela linguagem PL-
SQL, seriam contemplados pela linguagem de programação da aplicação que conecta com o BD. Por que
usar um ou outro?

É uma questão de escolha que depende de alguns fatores:

Desempenho: PL/SQL pode ser mais eficiente para operações que envolvem manipulação intensiva de dados
no banco de dados. Isso ocorre porque as operações realizadas no banco de dados são executadas no servidor
de banco de dados, minimizando a transferência de dados entre o banco de dados e a interface da aplicação.

Segurança: o uso de PL/SQL pode ajudar a garantir a integridade dos dados e aplicar regras de negócios
diretamente no banco de dados. Isso ajuda a garantir que as operações de banco de dados sejam realizadas de
maneira consistente e segura, independentemente da interface do usuário.

Manutenção: separar a lógica de negócios da aplicação pode facilitar a manutenção e o gerenciamento do

código. Alterações nas regras de negócios podem ser feitas no banco de dados sem afetar diretamente o
programa.

Consistência de Dados: ao usar PL/SQL é mais fácil garantir a consistência dos dados, especialmente em
sistemas multiusuários, onde várias partes da aplicação podem estar acessando e modificando os mesmos
dados simultaneamente.

Reutilização de Código: procedimentos e funções PL/SQL podem ser reutilizados em várias partes da
aplicação, o que pode reduzir a duplicação de código e promover a consistência e a modularidade.

 Exer - PL-SQL
 Parte 3 - Administração de Bancos de Dados
A administração do banco de dados é uma tarefa fundamental no âmbito da tecnologia da informação, que
envolve o gerenciamento e a manutenção de sistemas de armazenamento de dados. Este campo abrange uma
variedade de tarefas, desde o projeto e implementação de esquemas de banco de dados até a otimização de
consultas e a garantia da segurança e integridade dos dados. Os administradores de banco de dados são
responsáveis por garantir que os bancos de dados estejam operando de maneira eficiente, confiável e segura,
além de colaborarem com outros profissionais de TI para garantir que as necessidades de armazenamento e
acesso aos dados sejam atendidas de forma adequada.

Também envolve a implementação de políticas de backup e recuperação de dados, a monitorização do

desempenho do sistema, a aplicação de patches e atualizações de software, bem como a resolução de
problemas que possam surgir. Em um ambiente cada vez mais digital e interconectado, a administração
eficaz de bancos de dados é essencial para garantir a disponibilidade e a integridade dos dados, garantindo
que as organizações possam tomar decisões informadas com base em informações precisas e confiáveis.

A sequência de tópicos e exercícios apresentados a seguir aborda algumas das principais atividades do DBA,
o DataBase Administrator.

Exportação e importação de dados

Utilitários de Exportação e Importação de dados

A esta altura de nossa experiência profissional, pouco precisamos comentar a respeito da importância de se
ter uma boa política de backup para nossas informações. Da mesma forma, pouco ajuda fazermos o backup
se não soubermos recuperar essas informações no caso de uma ‘tragédia’. Rotinas de recuperação devem ser
testadas com alguma periodicidade para evitar surpresas na hora em que os dados precisam ser recuperados.

Para facilitar a vida de administradores de bancos de dados, às vezes o próprio fabricante do software
disponibiliza algum programa para realizar as tarefas de backup e recovery. No caso do banco de dados
Oracle não é diferente. Existem ainda ferramentas desenvolvidas por terceiros para tais tarefas, mas elas
precisam ser configuradas de forma adequada para que não decepcionem quando forem necessárias.

Os utilitários de exportação e importação de dados geram arquivos em formato proprietário para transferência
de informações entre diferentes esquemas do mesmo banco de dados, diferentes bancos de dados, ou mesmo
para backup e recovery. É possível exportar, ou fazer uma cópia, de qualquer tabela do banco de dados.
Logicamente, toda sua definição estrutural é exportada juntamente com os dados para que a tabela possa ser
recriada novamente no destino.

Índices, apesar de ocuparem espaço físico no banco de dados, têm somente seu comando de criação
exportado, obrigando o utilitário a recriá-lo no banco de destino. O mesmo acontece com as constraints. Elas
não ocupam espaço no banco de dados. Apenas representam restrições obrigatórias a serem respeitadas.
Visões, da mesma forma, têm apenas exportado seu código SQL. Não ocupam espaço físico. Estes utilitários
também permitem exportar ou importar um banco de dados inteiro. Por isso são quase sempre usados como
complemento do backup físico ou, às vezes, dependendo do caso, como única forma de backup de um banco
de dados.

Para uma lista completa dos possíveis parâmetros a serem usados, deve-se executar na linha de comando

$ exp help = y
Alguns parâmetros mais usados:
Parâmetro Descrição
BUFFER Espaço em memória a ser usado
COMMIT Possibilita executar um commit cada vez que o buffer em memória encher
CONSISTENT Força a exportação respeitando as integridades relacionais
CONSTRAINTS Determina se as constraints do objeto devem ser exportadas/importadas
FEEDBACK Exibe o progresso da importação a cada N linhas importadas
FILE Nome do arquivo a ser criado/lido
FROMUSER Em arquivos com vários usuários, especifica quais usuários importar
FULL Determina que o banco de dados todo/arquivo todo seja exportado/importado
GRANTS Especificar se deve-se exportar/importar concessões de privilégios junto
IGNORE Possibilita importar dados mesmo se a tabela de destino já existir
INDEXES Permite destacar se os índices do objeto serão considerados ou não
INDEXFILE Cria um arquivo somente com as DDLs de criação das tabelas, índices e constraints
LOG Arquivo de registro do processo de exportação/importação
PARFILE Especifica um arquivo de parâmetros que deve ser usado
ROWS Dá a opção de se exportar/importar somente a estrutura do objeto, sem os dados
STATISTICS Determina se a exportação/importação deve ou não considerar estatísticas dos objetos
TABLES Lista de tabelas a serem exportadas/importadas
TOUSER Especifica para qual usuários importar
USERID Usuário a exportar/importar

A essa altura do desenvolvimento de nossos exercícios, cabe lembrar que para todos eles usaremos o usuário
do Linux oracle que também possui a senha padrão 123. É importante estar conectado sempre nesse usuário
para todos os exercícios por duas razões principais. A primeira é que os programas que estão instalados no
Linux são acessíveis (path) somente ao usuário oracle. E segundo porque a estrutura de pastas e arquivos de
nossos exercícios foi montada abaixo do diretório home do usuário oracle, que neste caso é

/usr/lib/oracle/xe

Podemos a qualquer momento nos deslocar para o diretório home de qualquer usuário executando na linha de
comando cd seguido de ENTER.

 Exer - Export e Import

O aplicativo para carga de dados SQL Loader

Quando for necessário carregar dados de sistemas legados ou mesmo programas cuja interface com o mundo
externo pode existir somente através de arquivos-texto, faz-se necessária a utilização de uma ferramenta
capaz de realizar este processo. O banco Oracle possui o aplicativo SQL Loader.

Ele trabalha com um arquivo de configuração chamado controlfile, que mapeia os dados de entrada para
algum formato na saída (tabela do banco de dados). Gera um arquivo de log com o registro da carga dos
dados, um arquivo com os registros que, por uma ou outra razão, não foram carregados ao banco de dados, e
um arquivo contendo todos os registros que também não foram carregados mas porque não atenderam ao
filtro WHEN quando este for usado. Estes últimos são chamados de registros descartados, ou discarded
records.

Uma lista completa dos parâmetros aceitos pelo aplicativo pode ser obtida digitando-se ‘ sqlldr -? ‘ na linha
de comando.
Alguns parâmetros do SQL Loader (obtidos em http://www.oracleutilities.com/OSUtil/sqlldr.html):
 bad – A file that is created when at least one record from the input file is rejected. The rejected data
records are placed in this file. A record could be rejected for many reasons, including a non-unique key or a
required column being null.

 control – The name of the control file. This file specifies the format of the data to be loaded.

 data – The name of the file that contains the data to load.

 discard – The name of the file that contains the discarded rows. Discarded rows are those that fail the
WHEN clause condition when selectively loading records.

 discardmax – [ALL] The maximum number of discards to allow.

 errors – [50] The number of errors to allow on the load.

 log – The name of the file used by SQL*Loader to log results.

 userid – The Oracle username and password.

Outro link útil sobre o SQL Loader (control file reference):

http://www.cs.umbc.edu/help/oracle8/server.815/a67792/ch05.htm

 Exer – SQL Loader

Recuperando tabelas eliminadas na lixeira

Quando eliminamos uma tabela, o banco de dados não remove imediatamente a tabela, e sim, renomeia a
tabela e a coloca na lixeira junto com seus objetos associados (índices, por exemplo), para fins de
recuperação.

 Exer – Recycle Bin

Flash Back Queries

A característica de fazer consultas voltando no tempo permite consultar uma posição passada do BD sem ter
que executar alguma tarefa de recovery ou resgatar algum backup para isso. É útil para saber, por exemplo, a
posição de determinado campo antes de alguma exclusão ou alteração acidental acontecer. Acrescentamos,
para isso, uma cláusula à sentença SQL que nos permite especificar o momento no passado ao qual queremos
retornar, ou ao SCN do banco de dados ao qual queremos voltar.

 Exer – Flash Back Queries

 Visões

Uma visão (view) é como uma tabela virtual, que consiste em linhas e colunas como uma tabela real, mas
seus dados são derivados de uma ou mais tabelas subjacentes por meio de uma consulta. As views podem ser
utilizadas para

Abstração de dados: elas podem ocultar a complexidade subjacente das consultas, permitindo que os
usuários finais vejam os dados de uma maneira mais simples e estruturada.

Segurança: views podem ser usadas para restringir o acesso a determinadas colunas ou linhas de uma tabela.
Isso permite que os administradores de banco de dados controlem quem pode visualizar ou modificar certos
conjuntos de dados.

Reutilização de consultas: podem encapsular consultas comuns e complexas, facilitando a reutilização

dessas consultas em diferentes partes de uma aplicação.

Consistência de dados: podem ser usadas para garantir que os dados estejam sempre apresentados de
maneira consistente, mesmo que a estrutura subjacente das tabelas mude.

No entanto, é importante observar que as views não armazenam os dados fisicamente, elas apenas
armazenam a definição da consulta.

 Exer – Views

Shell Scripts na Administração de Bancos de Dados

Um shell script é um script escrito para o shell, ou seja, o interpretador de linha de comando do sistema
operacional. Com um shell script (termo usualmente associado ao Linux e ao Unix) ou programa de lote
(termo usualmente associado ao Windows), podemos automatizar tarefas rotineiras que precisam se repetir,
como por exemplo:

 rotinas de backup

 processos de limpeza semanal, diária ou mensal de

diretórios

 consultas a bancos de dados que precisam ser executadas

diariamente e, possivelmente, terem suas saídas ou dados
enviados para o email do usuário

 interfaces entre sistemas distintos, etc ...

Bourne Shell (sh), C Shell (csh), Korn Shell (ksh) e Bash são os interpretadores de comando mais populares
no mundo Unix e Linux. O command.com e o cmd.exe são seus correspondentes no Windows.

 Exer – Shell Script

 Do you know what is the magic word to
get everything you want? It's sudo.

Database Links

Um database link é um objeto que permite acessar objetos de outros esquemas ou usuários dentro do mesmo
banco de dados, desde que existam permissões para isso, ou mesmo objetos em outros bancos de dados
remotos, desde que existam permissões e comunicação entre os servidores.

Sintaxe:

O connect string necessário na sintaxe faz referência às strings de conexão configuradas no arquivo
tnsnames.ora, que, em nosso Sistema Operacional Linux, fica localizado em
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/tnsnames.ora

Este é o conteúdo dele:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ub11Exer)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Imagine agora, por exemplo, que quiséssemos acessar um ambiente de produção a partir de nosso ambiente
de desenvolvimento. Poderíamos criar uma string de conexão chamada PRODUCAO para isso. Poderíamos
colocar o host name da máquina servidora ou mesmo seu IP Address. Por fim, o nome da instância do banco
da dados ao qual queremos conexão, porque, via de regra, pode-se ter mais de uma instância de banco de
dados executando no mesmo servidor de banco de dados.

PRODUCAO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.152)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
Uma conexão via database link para esta string de conexão, conectaria a algum usuário do banco de dados
chamado “XE” da máquina 192.168.0.152, cujo listener recebe conexões pela porta 1521.

Portanto, se quiséssemos criar um database link chamado BDPRODUCAO para conectar num suposto
usuário CENTRAL com senha CNT00, pela string de conexão PRODUCAO, criada acima, na máquina
remota 170.66.11.10, que possui um banco Oracle instalado com nome XE, e o listener “ouvindo” na porta
1521, faríamos:

create database link BDPRODUCAO

connect to CENTRAL identified by CNT00
using 'PRODUCAO';

 Exer – Database Links

Replicação de Dados

Replicação é o processo de copiar e manter objetos de um banco de dados em múltiplos outros bancos de
dados que, juntos, compõe um sistema de banco de dados distribuído. Modificações no banco de dados
publicador são capturadas e mantidas localmente antes de serem propagadas para cada um dos bancos de
dados remotos. Os comandos para criação e atualização das réplicas (snapshots), gerenciamento e criação de
processos automáticos (jobs ) de replicação, gerenciamento de database links e configuração de arquivos que
permitem a ocorrência da replicação de dados, estão distribuídos nos exercícios que faremos a seguir.

 Exer - Snapshots
 Exer – Jobs de Replicação

Tuning de consultas SQL

Vários sistemas atuais enfrentam problemas de performance. Estes problemas podem ser de vários tipos e
causados por razões diversas, como por exemplo:

1. servidor de banco de dados com pouco poder de processamento

2. cliente de banco de dados com pouco poder de processamento
3. servidor de banco de dados com pouca memória
4. má configuração do sistema operacional
5. má distribuição física do banco de dados nos discos ou unidades de armazenamento
6. discos ou unidades de armazenamento com má performance
7. concorrência ou tráfego de rede muito intenso
8. meio físico de transmissão de dados com baixa performance
9. modelagem de dados mal feita
10. aplicação (programa) mal escrito
11. consulta (SQL) ao banco de dados mal escrita
12. falta de índices no banco de dados para agilizar a execução das consultas
13. má configuração do banco de dados
14. má configuração do sistema de middleware
15. interface de comunicação entre aplicação e banco de dados de baixa performance (Ex.:ODBC)
 etc ...

Portanto, uma metodologia de ajuste de performance não deve focar somente em um destes pontos, mas sim,
em todos. Uma aplicação, por exemplo, deve “nascer” objetivando uma boa performance além de atender às
necessidades de quem a solicitou. Ainda, se você for responsável por agir sobre um problema de performance
em um sistema, deve definir anteriormente os objetivos e agir primeiro sobre o que é mais crítico, que dá
mais retorno. Administradores de banco de dados, administradores de rede, analistas e programadores devem
trabalhar juntos como um time e não como adversários. Tipicamente, 20% das transações sobre bancos de
dados são responsáveis por 80% do uso do sistema, e estas transações é que devem ser o foco do trabalho.

Processamento de consultas SQL

O conhecimento do mecanismo usado pelo banco de dados no processamento das consultas SQL pode ajudá-
lo a criar consultas mais otimizadas. A figura abaixo mostra de forma básica a organização do banco de
dados na memória do servidor.

A System Global Area (SGA) é a área em memória usada pelo banco de dados. Uma de suas partes é a
Shared Pool, que contém o dicionário de dados e a chamada Library Cache. Dentro da Library Cache
encontram-se as Shared SQL Areas. Dentro da Shared SQL Area, cada sentença SQL é analisada e passa a
ser chamada de cursor. Um cursor guarda consigo as seguintes informações:

- a sentença analisada
- o plano de execução
- a lista de objetos referenciados

Se dois usuários executam a mesma sentença SQL, então estarão usando o mesmo cursor.

Índices

Um índice é um objeto do banco de dados lógica e fisicamente independente dos dados de uma tabela. A
criação de um índice pode reduzir significativamente o tempo de execução de uma consulta. Sempre que uma
restrição (constraint) de chave primária for criada, um índice é criado automaticamente. Os índices podem ser
únicos ou não-únicos. Índices únicos garantem que não exista na tabela indexada duas entradas com o mesmo
valor. Um índice pode ser criado sobre somente uma coluna ou sobre várias colunas de uma mesma tabela,
não sendo necessário que estas colunas, na tabela, sejam adjacentes.
 Exer – Tuning de Consultas SQL