O que devemos conhecer sobre SQL/Oracle Parte 14: Manipulando objetos com as visões do dicionário de dados

Utilizando o dicionário de dados para pesquisar dados sobre os objetos do banco

O dicionário de dados é uma coleção de tabelas (onde as informações estão guardadas) e visões (disponibilizadas para acesso dessas informações) do banco que são automaticamente gerados a cada comando DDL executado.

A informação armazenada nessa coleção inclui a descrição completa dos objetos criados como tabelas, visões, índices, restrições, enfim tudo o que foi criado. Essa coleção pertence ao usuário de sistema SYS, que é o dono de todos os privilégios. As visões podem ser distinguidas pelo prefixo de seu nome, vejamos uma tabela:

Prefixos de algumas visões do dicionário de dados
Prefixo	Quantidade de visões aproximada	Descrição
USER_	359	Objetos que pertencem ao usuário acessando a visão.
ALL_	334	Objetos que pertencem a qualquer usuário cujo usuário acessando a view possui acesso,
DBA_	670	Todos os objetos do banco.
V_$ (visões) V$ (sinônimos públicos)	488	Visões de desempenho dinâmico, armazenam informações sobre a instância local.
GV_$ (visões) GV$ (sinônimos públicos)	450	Visões dinâmicas globais de desempenho,
SM$, AUDIT_, CHANGE_, TABLE_, CLIENT_, COLUMN_, DICT_, DATABASE_, DBMS_, GLOBAL_, INDEX_, LOGSTDBY_, NLS_, RESOURCE_, ROLE_, SESSION_, CLIENT_RESULT_CACHE_STATS$, etc.	40	O restante das visões possuem diversos prefixos e nomes únicos.

É importante salientar que os sinônimos públicos não são listados em USER_SYNONYMS, que mostra apenas os sinônimos privados. Para encontra-los é necessário consultar ALL_SYNONYMS e DBA_SINONYMS.

Veja agora em quais visões podemos encontrar determinados objetos:

Visões dos objetos privados de um usuário
Sufixo	Descrição
USER_CATALOG	Todas as tabelas, visões, sinônimos e sequências do usuário.
USER_COL_PRIVS	Grants em colunas de tabelas que pertencem ao usuário.
USER_CONSTRAINTS	Restrições de tabelas que pertencem ao usuário.
USER_CONS_COLUMNS	Colunas acessíveis em definições de restrições de tabelas que pertencem ao usuário.
USER_DEPENDENCIES	Dependências de/para dos objetos do usuário.
USER_ERRORS	Erros atuais de objetos armazenados pertencentes ao usuário.
USER_INDEXES	Índices do usuário.
USER_IND_COLUMNS	Colunas utilizadas para os índices.
USER_OBJECTS	Objetos do usuário.
USER_SEQUENCES	Sequências do usuário.
USER_SYNONYMS	Sinônimos do usuário (privados).
USER_TABLES	Tabelas do usuário.
USER_TAB_COLUMNS	Colunas das tabelas e visões do usuário.
USER_TAB_PRIVS	Grants em objetos do usuário.
USER_VIEWS	Visões do usuário.

Um caso que merece atenção especial são as visões dinâmicas (V_$ ou GV_$), pois elas apresentam informações da atividade atual da base de dados em tempo real, os dados são recebidos por meio de mecanismos do Oracle. Isso quer dizer que essas informações são geradas de tempo em tempo, por isso para a consulta desses dados não é indicado junções complexas, o recomendado é que esses dados sejam inseridos em tabelas temporárias e por meio dessas tabelas realizar as consultas, construindo consultas complexas e ate mesmo disponibilizando outras views:

Visões dinâmicas
Visão	Descrição
V$DATABASE	Inclui informações sobre a base, incluindo o nome, data de criação, sistema operacional, entre outros.
V$INSTANCE	Inclui o nome da instancia, o nome do host, o horário de inicio entre outros.
V$PARAMETER	Parâmetros da configuração atual como NLS_LANGUAGE, NLS_DATE_LANGUAGE, NLS_CURRENCY, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, SQL_VERSION entre outros.
V$SESSION	Parâmetros de configuração da sessão atual, mostrando conexões ativas, horários de login, nome das maquinas dos usuários logados, estado atual das transações entre outros.
V$RESERVED_WORDS	Lista atual das palavras reservadas, indicando se a palavra é sempre reservada, quando não em qual circunstância.
V$OBJECT_USAGE	Útil para monitorar o uso de índices.
V$TIMEZONE_NAMES	Possui duas colunas, região e TZABBREV, que é a abreviação dessa região.

Para finalizar temos os comentários, automáticos ou criados pelo usuário, onde podemos acessar pelas visões ALL_TAB_COMMENTS (todos os objetos da base) ou ALL_COL_COMMENTS (comentários de todas as colunas de todas as tabelas e visões). 

Os comentários os definidos pelo usuário são definidos pela sintaxe:

COMMENT ON FUNCIONARIOS IS ‘DADOS DE FUNCIONÁRIOS’;

COMMENT ON COLUMN FUNCIONARIOS.CODIGO IS ‘CÓDIGO DO FUNCIONÁRIO’;

No exemplo acima incluímos um comentário para a tabela de funcionário em seguida para uma de suas colunas. Uma vez criados não podemos removê-los, para um efeito parecido basta executar o comando novamente com a descrição vazia.

Resumindo:

• O dicionário de dados é constituído de tabelas com informações sobre o banco;
• O dicionário de dados possui dados sobre os dados;
• Eles contem informações sobre tabelas, visões, restrições, índices, sequências, sinônimos, regras, privilégios e qualquer outro objeto;
• Rastreia todos os usuários, sabendo qual usuário e dono do que, qual privilégio possui, assim como o status de cada objeto;
• O Oracle atualiza de forma automática o dicionário de dados a cada comando DDL;
• Visões com o prefixo USER_ dizem respeito a objetos que pertencem ao usuário utilizando a visão;
• O prefixo ALL_ indica qualquer objeto do banco cujo usuário utilizando a visão possui acesso;
• O prefixo DBA_ é utilizado por todas as visões que contem dados sobre todos os objetos do banco;
• Os prefixos V$ e GV$ identificam visões de desempenho dinâmicas, que apresentam informações de tempo real;
• A maioria (mas não todas) das visões do dicionário de dados é armazenada com comentários, descrições resumidas, assim como muitas das colunas das visões também possuem;
• É possível adicionar os próprios comentários no dicionário de dados para objetos criados;
• O comando COMMENT é a maneira de se adicionar comentários para tabelas e colunas;

Consultando visões do dicionário de dados

Alguns exemplos de uso para as visões do dicionário de dados:

Identificando objetos do usuário:

SELECT TABLE_TYPE TIPO, COUNT(*) TOTAL FROM USER_CATALOG GROUP BY TABLE_TYPE;

Inspecionando tabelas e suas colunas:

SELECT COLUMN_NAME COLUNA, DECODE(DATA_TYPE, ‘DATE’, DATA_TYPE, ‘NUMBER’, DATA_TYPE||DECODE(DATA_SCALE, NULL, NULL, ‘(’||DATA_PRECISION||’,’||DATA_SCALE||’)’), ‘VARCHAR2’, DATA_TYPE||’(’||DATA_LENGTH||’)’, NULL) TIPO_DADO FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ‘FUNCIONARIOS’;

Verificar o status de visões:

SELECT STATUS, OBJECT_TYPE, OBJECT_NAME FROM USER_OBJECTS WHERE STATUS = ‘INVALID’;

Verificando restrições:

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

Resumindo:

• A visão DICTIONARY é um bom ponto inicial para se encontrar aquilo que se procura no dicionário de dados;
• A visão USER_CATALOG contém um resumo das informações sobre os principais objetos do usuário;
• A visão USER_OBJECTS é similar a USER_CATALOGM, porém bem mais completa;
• É possível recuperar uma lista completa das tabelas e suas colunas, tipos de dados, tamanho, precisão e escala;
• O status dos objetos também é armazenado, como por exemplo, flags do dicionário de dados que indicam objetos que necessitam de recompilação;
• Todas as regras e privilégios de todos os usuários sobre todos os objetos são armazenados no dicionário de dados;
• Se você possui o nome de uma coluna e não tem certeza a qual tabela ele pertence é possível utilizar o dicionário de dados para saber;