Pesquisar no Programero

quinta-feira, 19 de julho de 2012

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;


Nenhum comentário:

Postar um comentário

Obrigado por Participar do programero, fique a vontade para agradecer, retificar, perguntar ou sugerir.