Usando visões do Dicionário de Dados

Este capítulo descreve uma ferramenta valiosa que todos os profissionais da Oracle devem entender. O dicionário de dados é a fonte de referência incorporada em tempo real da Oracle para todas as informações sobre os aplicativos que você cria no seu banco de dados. Armado com todos os recursos do dicionário de dados, você pode obter informações sobre os objetos de banco de dados que foram criados em seu banco de dados, quem os criou, quando e muito mais.

Consultar várias views do dicionário de dados

O dicionário de dados é uma coleção de tabelas e visualizações de banco de dados. Ele é automaticamente construído e preenchido pelo banco de dados Oracle. As informações armazenadas no dicionário de dados incluem a descrição completa de todos os objetos de banco de dados criados como parte do seu aplicativo, incluindo tabelas, visualizações, índices, restrições, sequências e muito mais. Em outras palavras, o resultado de cada instrução DDL (Data Definition Language) é gravado no dicionário e as informações são automaticamente mantidas pelo sistema Oracle em tempo real, conforme você altera os objetos de banco de dados e suas estruturas. As informações armazenadas no dicionário de dados incluem (mas não estão limitadas a):

Os nomes dos objetos de banco de dados, seus proprietários e quando eles foram criados.
Os nomes das colunas de cada tabela, juntamente com os tipos de dados, precisão e escala.
Quaisquer restrições
Visualizações, índices e seqüências

O dicionário de dados costuma ser chamado de metadados, que significa “dados sobre dados”. É o que é o dicionário de dados – é um banco de dados detalhado e abrangente que rastreia tudo o que há para saber sobre os aplicativos de banco de dados criados no sistema Oracle. Sempre que você cria um objeto de banco de dados, o Banco de Dados Oracle trabalha em segundo plano para registrar o nome e a estrutura desse objeto e disponibiliza essas informações por meio do dicionário de dados. Todo objeto criado por cada usuário é documentado no dicionário de dados.

O dicionário de dados consiste em tabelas e visualizações pertencentes à conta do usuário SYS. Como proprietário, o SYS tem privilégios totais sobre essas tabelas e visualizações. Nenhum usuário deve alterar os dados pertencentes ao SYS ou a integridade do banco de dados pode ser comprometida.

Todas as informações do dicionário de dados são armazenadas em tabelas, mas muitos desses dados são apresentados aos usuários por meio de visualizações. Em outras palavras, os usuários geralmente não têm acesso direto às tabelas do dicionário de dados; eles obtêm acesso às visualizações, que fornecem acesso um pouco limitado para proteger a integridade do dicionário de dados.

Além disso, muitos objetos de dicionário de dados são renomeados por sinônimos públicos e esses são os nomes pelos quais você conhece os dados. Em outras palavras, existem vários níveis de abstração que separam os usuários dos dados subjacentes. Não importa – a capacidade de ler as informações no dicionário de dados é um grande trunfo para todos os profissionais de SQL.

No momento da redação deste artigo, existem mais de 2.000 visualizações no dicionário de dados. Um em particular é um bom ponto de partida: DICTIONARY. Esta visualização contém informações sobre as visualizações que compõem o dicionário de dados. Ele inclui o nome de cada visualização, além de uma breve explicação de cada visualização.

A visualização USER_TABLES contém informações sobre as tabelas pertencentes à conta de usuário atual. Em outras palavras, não importa em qual conta você se conecta, você pode consultar a visualização USER_TABLES e obter informações detalhadas sobre as tabelas pertencentes a qualquer conta com a qual você esteja conectado. Uma descrição completa da visualização USER_TABLES mostraria que ela consiste em mais de 50 colunas. Algumas das colunas incluem:

TABLE_NAME O nome da tabela.
STATUS Indica se a tabela é atualmente válida e, portanto,
disponível para uso.
ROW_MOVEMENT Indica se ROW MOVEMENT foi
ativado para a tabela. (Veja nossa discussão sobre o Instrução FLASHBACK TABLE para obter mais informações sobre como ativar
e desativando ROW_MOVEMENT.)
AVG_ROW_LEN O comprimento médio das linhas atualmente armazenadas e na tabela.

E se você quiser ver informações sobre outras tabelas além da sua? Bem, acontece que existem outras duas visualizações no dicionário de dados que têm quase o mesmo conjunto de colunas que USER_TABLES.

ALL_TABLES descreve as tabelas relacionais acessíveis ao usuário atual.

DBA_TABLES Mostra todas as mesmas informações da tabela, mas para todas as tabelas em todo o banco de dados, independentemente dos privilégios de proprietário ou tabela.
Essas das visões tem uma coluna adicional:
OWNER O proprietário da tabela em questão.

E isso faz sentido – não há necessidade de OWNER na exibição USER_TABLES, pois essa exibição mostra informações sobre apenas um proprietário, a saber, o proprietário atual.

Esse padrão de nomenclatura do uso de um desses três prefixos (USER_, ALL_, DBA_) é um padrão usado em todo o dicionário de dados. Muitas das visualizações do dicionário de dados que armazenam informações sobre objetos no banco de dados têm nomes que começam com um desses três prefixos. Uma visão geral dessas visualizações de dicionário de dados é apresentada na Tabela 12-1. Como você pode ver na tabela, a grande maioria das visualizações do dicionário de dados tem um prefixo USER_, ALL_ ou DBA_. Um conjunto de três visualizações que têm o prefixo USER_, ALL_ e DBA_ e compartilham o mesmo sufixo, como TABLES, extraem seus dados de uma única tabela de dicionário de dados. Por exemplo, USER_CONSTRAINTS, ALL_CONSTRAINTS e DBA_CONSTRAINTS compartilham a mesma tabela de dicionário de dados. A Tabela 12-2 mostra as visualizações USER_ mais comuns que você usará regularmente.

Exibições do dicionário de dados selecionados mostrando objetos de propriedade do usuário atual:

Sinônimos Privados são listados em USER_SYNONYMS
Sinônimos Públicos são listados em (ALL_SYNONYMS e DBA_SYNONYMS) a não ser que esteja logado com o usuário PUBLIC ou usuário que criou o sinônimo público aí estará em USER_SYNONYMS.

Dynamic Performance Views

A Tabela 12-1 inclui referências a um conjunto de visualizações que começam com os prefixos V_$ e GV_$. Elas são definidas como as visualizações de desempenho dinâmico e as visualizações de desempenho dinâmico global. As visualizações de desempenho dinâmico exibem informações sobre a atividade atual do banco de dados em tempo real. Eles recebem dados dinamicamente do banco de dados através de mecanismos que vão além do escopo do exame.

Para nossos propósitos, é importante saber que eles são mantidos automaticamente pelo sistema e estão disponíveis para consulta – com algumas limitações. As visualizações de desempenho dinâmico começam com o prefixo V_$. Existem sinônimos públicos criados para cada uma das visualizações e eles têm nomes semelhantes, mas começam com o prefixo V$. Consultas simples em visualizações de desempenho dinâmico são aceitas, mas consultas complexas, com ou sem junções, requerem atenção especial. A Oracle recomenda formalmente que a natureza dinâmica dessas exibições não garanta consistência de leitura para nada além das consultas mais simples de exibição única; portanto, é recomendável que você execute junções e / ou consultas complexas:

Criando um conjunto de tabelas temporárias para espelhar as exibições
Copiando os dados das exibições e para um conjunto de tabelas temporárias.
Executando a junção nas tabelas temporárias

Dessa forma, você evitará obter resultados ruins causados pela falta de consistência de leitura. Alguns dos sinônimos de desempenho dinâmico (que apontam para visualizações que apontam para tabelas) incluem o seguinte:
V$DATABASE Inclui informações sobre o próprio banco de dados, incluindo o nome do banco de dados, a data de criação, a plataforma atual do sistema operacional e muito mais
V$INSTANCE Inclui o nome da instância, o nome do host, o horário de inicialização e muito mais.
V$PARAMETER As configurações atuais dos parâmetros do sistema, como NLS_LANGUAGE, NLS_DATE_LANGUAGE, NLS_CURRENCY, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT, NLS_TIMESTAMP_TZ.

Lembre-se de que apenas consultas simples são recomendadas ao consultar diretamente as visualizações de V$ (v-dollar)

V$SESSION Muitas configurações atuais para cada sessão de usuário individual, mostrando conexões ativas, horários de login, nomes de máquinas nos quais os usuários estão conectados, o estado atual das transações e muito mais
V$RESERVED_WORDS Lista atual de palavras reservadas, incluindo informações indicando se a palavra-chave é sempre reservada e, se não, em que circunstâncias está reservada
V$OBJECT_USAGE Útil para monitorar o uso de objetos INDEX
V$TIMEZONE_NAMES Inclui duas colunas: TZNAME, que é a região do fuso horário, e TZABBREV, que é a abreviação do fuso horário.

Lendo Comentários

O dicionário de dados é rico em comentários que ajudam a descrever a intenção das várias visualizações do dicionário de dados e das colunas dentro deles. Além dos comentários fornecidos na view DICTIONARY para cada uma das visualizações individuais do dicionário de dados, é possível visualizar comentários sobre as colunas nessas visualizações ou sobre qualquer objeto armazenado em qualquer lugar do banco de dados:

ALL_TAB_COMMENTS Exibe comentários para todos os objetos no banco de dados
ALL_COL_COMMENTS Exibe comentários para todas as colunas de todas as tabelas e visualizações no banco de dados.

Digamos que você está visualizando uma exibição de dicionário de dados como USER_SYNONYMS e deseja aprender mais sobre suas colunas. Aqui está uma consulta que o ajudará:

Essa é a consulta, o resultado é o seguinte:

Adicionando Comentários

Você pode adicionar seus próprios comentários ao dicionário de dados para adicionar notas e descrições sobre as tabelas e colunas criadas. A instrução COMMENT é o que usamos para adicionar comentários ao dicionário de dados para um objeto de banco de dados específico. Sua sintaxe é a seguinte:

COMMENT ON objectType fullObjectName IS c1;

Onde:

objectType é uma das palavras-chave TABLE, COLUMN ou alguns outros objetos que não são objetos do exame de certificação, como INDEXTYPE, OPERATOR, MATERIALIZED VIEW e outros.
fullObjectName é o nome do objeto ao qual você deseja adicionar um comentário. Se for uma TABLE, nomeie a tabela. Mas se é uma coluna use a sintaxe TABLE.COLUMN.
c1 é o texto completo do comentário que você deseja adicionar.

Quando você adiciona um comentário à tabela, o comentário será exibido nas visualizações USER_TAB_COMMENTS, ALL_TAB_COMMENTS e DBA_TAB_COMMENTS.
Quando você adiciona um comentário a uma coluna em uma tabela, o comentário será exibido nas visualizações USER_COL_COMMENTS, ALL_COL_COMMENTS e DBA_COL_COMMENTS.

Exemplos Práticos

Ao criar uma tabela como na figura acima não há comentários para a tabela nem para coluna, como podemos ver no resultado das queries em ALL_COL_COMMENTS e ALL_TAB_COMMENTS, a coluna COMMENTS não retorna nada, retorna ‘-‘ que significa vazio ou NULL. Vamos adicionar comentários a eles (TABLE, COLUMN).

Sucesso total, nas duas cláusulas COMMENT inserimos comentários para a tabela e para a sua coluna e depois fizemos querie e obteve na coluna COMMENTS os comentários que inserimos. Vamos ver agora como deletar um comentário:

DICTIONARY

A visualização DICIONÁRIO é um excelente ponto de partida para qualquer investigação do dicionário de dados. Se você estiver procurando por algo específico, como algo que endereça objeto INDEX, tente uma consulta como esta:

SELECT TABLE_NAME, COMMENTS 
FROM DICTIONARY 
WHERE UPPER(COMMENTS) LIKE '%INDEX%' 
ORDER BY TABLE_NAME

Essa consulta localizará qualquer coisa na tabela DICTIONARY que mencione “index” nos comentários. O resultado incluirá o nome da exibição do dicionário de dados que lista todos os índices, aquela que lista todas as colunas nas quais um índice se baseia e assim por diante.

Para localizar comentários de um objeto específico, como por exemplo USER_DEPENDENCIES, use a seguinte consulta:

SELECT COLUMN_NAME, COMMENTS 
FROM ALL_COL_COMMENTS 
WHERE OWNER='SYS' AND TABLE_NAME = 'USER_DEPENDENCIES';

Identificando os objetos de propriedade de um usuário

Há várias visualizações de dicionário de dados nas quais você pode coletar dados sobre os objetos da sua própria conta de usuário. Duas visualizações em particular são um bom ponto de partida: USER_CATALOG e USER_OBJECTS.

USER_CATALOG

A visualização USER_CATALOG exibe uma lista resumida de tabelas, visualizações, sinônimos e sequências pertencentes ao usuário.

Existem apenas duas colunas em USER_CATALOG; eles são TABLE_TYPE e TABLE_NAME, onde TABLE_NAME é realmente o nome da tabela, view, sequence ou objeto de synonym. Um sinônimo para USER_CATALOG é CAT.

USER_OBJECTS

A visualização USER_OBJECTS contém informações sobre todos os objetos pertencentes ao usuário. Um sinônimo para USER_OBJECTS é OBJ.

Inspecionando Tabelas e Colunas

A tabela USER_TABLES (sinônimo TABS) é útil para inspecionar os metadados da tabela, assim como seu companheiro USER_TAB_COLUMNS (sinônimo COLS). Esta seção examinará USER_TAB_COLUMNS em particular.

Compilando View

Uma das muitas tarefas úteis que você pode realizar com o dicionário de dados é verificar o status de uma view que você criou. Lembre-se que uma visualização é uma consulta nomeada com base em uma tabela e que, após a criação da visualização, se a tabela for alterada por qualquer motivo, talvez seja necessário recompilar a visualização.

Por exemplo, se a estrutura de uma tabela for alterada, como por uma alteração no tipo de dados de uma coluna ou talvez se uma coluna for completamente descartada da tabela (uma coluna usada pela visualização), poderá alterar o status do view para INVALID. Você pode verificar a visualização USER_OBJECTS do dicionário de dados para determinar o status de qualquer uma de suas visualizações, desta forma:

Então agora sabemos que precisamos recompilar essas visualizações. O dicionário de dados contém muitas informações sobre visualizações, incluindo a consulta na qual a exibição se baseia, que pode ser encontrada na visualização USER_VIEWS e na coluna TEXT. Aqui está uma consulta no dicionário de dados que solicita a consulta que foi usada para criar a visualização VW_EMPLOYEES:

Checking Privileges

Observe que os privilégios podem ser inspecionados usando as seguintes visualizações:

USER_SYS_PRIVS Privilégios do sistema concedidos ao usuário atual.
USER_TAB_PRIVS Privilégios concedidos em objetos para os quais o usuário é proprietário, concedente ou donatário.
USER_ROLE_PRIVS Funções concedidas ao usuário atual. DBA_SYS_PRIVS Privilégios do sistema concedidos a usuários e funções.
DBA_TAB_PRIVS Todas privilégios concedidos aos objetos do banco de dados.
DBA_ROLE_PRIVS Privilégios concedido a usuários e funções. ROLE_SYS_PRIVS Privilégios do sistema concedidos a funções
ROLE_TAB_PRIVS Privilégios de tabela concedidos a funções. SESSION_PRIVS Privilégios de sessão que o usuário definiu atualmente.

Inspecionando Constraints

A visualização USER_CONSTRAINTS é uma das visualizações mais úteis. Aqui está uma consulta de exemplo que você pode executar para verificar o estado atual das restrições da tabela CRUISES:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, STATUS
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CRUISES';

A saída lista todas as restrições na tabela CRUISES. Estamos vendo quatro: uma chave primária e três chaves estrangeiras. Algumas das entradas possíveis na coluna CONSTRAINT_TYPE são:

P = CHAVE PRIMÁRIA
R = CHAVE ESTRANGEIRA (o R é para “integridade referencial”)
U = ÚNICO
C = Restrição CHECK ou NOT NULL

Nota: Existem restrições adicionais; estes estão além do escopo do exame. A coluna DELETE_RULE mostra se uma restrição de chave estrangeira foi criada com ON DELETE CASCADE ou ON DELETE SET NULL. A coluna SEARCH_CONDITION é particularmente útil para inspecionar os critérios de restrição CHECK. Aqui está um exemplo:

O dicionário de dados fornece informações adicionais sobre restrições na exibição do dicionário de dados USER_CONS_COLUMNS. Essa visualização contém todas as informações sobre quais colunas em CRUISES estão restritas e quais são os nomes das tabelas e colunas referenciadas que compõem as restrições FOREIGN KEY.

Aqui está um tipo de consulta de dicionário de dados que acho útil:

Essa é uma consulta que procura todas as tabelas na conta de usuário atual que possuem uma coluna específica – nesse caso, uma chamada EMPLOYEE_ID. Acho útil pesquisar nomes de colunas específicos em todas as tabelas ou visualizações em uma determinada conta de usuário (por exemplo, um esquema).