ORACLE - TABELAS INTERNAS

TABELAS EXTERNAS

O recurso de tabelas externas permite acessar dados de tabelas externas como se tivesse em uma tabela do banco de dados.  Vamos ver como usar as clausulas para criar uma tabela externa.

Uma tabela externa é somente para leitura. O atributo somente para leitura impede a realização e operações DML e também não permite a criação de índices.

Os metadados das tabelas externas são armazenados no banco de dados enquanto os dados são armazenados fora do banco de dados. Os metadados descrevem os dados e os objetos do esquema. São usados pelas aplicações para extrair e computar dados.

As tabelas externas permitem usar dados externos como tabelas virtuais. Desta forma os dados externos são consultados diretamente sem que precisem ser carregados nos banco de dados. Consultamos as tabelas externas da mesma maneira que o faz em tabelas normais.

As tabelas externas permitem acesso a origem de dados externas. Podemos usar a tabela externa na cláusula AS SELECT da instrução INSERT INTO para carregar dados de origem externa nas tabelas de banco de dados. Sintaxe:

CREATE TABLE nome_tabela (colunas)

ORGANIZATION EXTERNAL

(TYPE tipo_driver_acesso

DEFAULT DIRECTORY caminho do diretório

ACCESS PARAMETERS

(RECORDS DELIMITED BY delimitador

 BADFILE nome do arquivo

 LOGFILE nome do arquivo

 FIELDS TERMINATED BY separador de campos (colunas)

 LOCATION nome do arquivo

 PARALLEL valor

 REJECT LIMIT valor

Onde:

·         A cláusula ORGANIZATION permite especificar a ordem em que as linhas de dados são armazenadas.

·         O parâmetro EXTERNAL indica que a tabela é somente de leitura e que está localizada fora do banco de dados.

·         A cláusula TYPE especifica o drive de acesso da tabela externa, este, interpreta os dados externos para o banco de dados. O servidor Oracle oferece dois drives de acesso:

o   ORDER_LOADER (default);

o   ORACLE_INTERNAL.

·         A cláusula DEFAULT DIRECTORY permite especificar um ou mais diretórios onde os dados externos serão armazenados. Os diretórios default também são usados pelos drivers de acesso para armazenar arquivos auxiliares como os logs de erros.

·         A cláusula ACCESS PARAMETERS designa valores para os parâmetros de acesso do drive específico, este drive interpreta as informações fornecidas nesta cláusula. Especifica o delimitador usado para separar colunas no arquivo sem formatação. Especifica o nome do arquivo que registra erros. Especifica o finalizados de campos do arquivo sem formatação.

·         A cláusula LOCATION é usada para especificar um localizador externo para cada origem de dados externa. Este parâmetro é seguido do nome do arquivo externo. As informações fornecidas na clausula não são interpretadas pelo servidor. O drive de acesso interpreta as informações no contexto dos drives externos, este interpreta o contexto dos dados externos.

·         A cláusula PARALLEL especifica o número de servidores em execução paralela que trabalha com as origens de dado simultaneamente. Por exemplo, se for especificado 5, esta será a quantidade de servidores que poderão trabalhar os dados simultaneamente.

·         O tipo de dados especificado para as colunas da cláusula FIELDS TERMINATED BY pode ser diferente da especificada na CREATE TABLE. Podem ocorrer erros quando o servidor converte o tipo de dados para que corresponda a cláusula CREATE TABLE.

·         A cláusula REJECT LIMIT especifica o número de erros de conversão que podem ocorrer antes da consulta ser abortada. O limite default é zero.

CRIANDO DIRETÓRIOS

Para converter um arquivo em uma origem de dados, é necessário criar um objeto de diretório, este objeto é o diretório default onde reside a origem de dados. Podemos ter um ou vários objetos de diretório default. O uso de diversos objetos de diretório default facilita o balanceamento de carga entre as várias unidades de disco. Exemplo:

10,Maria,11-Dec-1990

12,Joao,20-Mai-1978

Exemplo de origem de dados. O arquivo EMPLOYEE1.TXT é um arquivo sem formatação que contém duas linhas de dados. As colunas são separadas por vírgulas e as linhas de dados ficam em duas linhas separadas. O arquivo é armazenado na pasta FLAT_FILE na unidade de disco rígido. Sintaxe:

CREATE OR REPLACE DIRECTORY AS ‘caminho’

Para acessar a origem de dados externa é necessário criar um objeto de diretório, que especifica um apelido para um diretório no sistema de arquivo do servidor onde reside uma origem de dados externa. A sintaxe para criar o objeto do diretório está acima.

A instrução CREATE DIRECTORY cria um diretório.

A cláusula OR REPLACE recria um diretório já existente. O parâmetro PATH NAME especifica o caminho completo do sistema operacional do servidor onde se localiza os arquivos.

Devemos ter o privilégio de sistema CREATE ANY DIRECTORY para criar diretórios.  Quando cria um diretório recebemos automaticamente o privilegio READ para este objeto. Também podemos conceder o privilegio READ para outros usuários ou atribuições.

No exemplo acima, o objeto do diretório foi criado. Podemos usar este nome do diretório em vez de usar o caminho externo do arquivo.

ESTUDO DE CASO PRÁTICO

1. Criar na unidade C a pasta EMP_DIR;

2. Conectar como usuário SYSTEM e  conceder os privilégios:

    CREATE OR REPLACE DIRECTORY EMP_DIR AS ''C:\EMP_DIR'';

    GRANT CREATE ANY DIRECTORY TO HR;

   GRANT READ, WRITE ON DIRECTORY EMP_DIR TO HR;

3. Criar um arquivo chamado EMP.DAT e gravar no diretório C:\EMP_DIR contendo numero e nome dos empregados. Exemplo:

1,Genoveva

2,Serafim

4. Conectar como usuário HR e digitar o comando abaixo:

CREATE TABLE oldemp

 (empno number, empname char(20))

ORGANIZATION EXTERNAL

 (TYPE ORACLE_LOADER

DEFAULT DIRECTORY EMP_DIR

ACCESS PARAMETERS

 (RECORDS DELIMITED BY NEWLINE

NOBADFILE

NOLOGFILE

FIELDS TERMINATED BY '',''

 (EMPNO CHAR, EMPNAME CHAR))

LOCATION (''EMP1.DAT''))

PARALLEL 5

REJECT LIMIT 200;

5. Em seguida, basta consultar a tabela OLDEMP.