O que é uma tabela aninhada (Nested Table)?

Este artigo faz uma abordagem sobre tabelas aninhadas (nested table) e em seguida, apresenta como criar, consultar uma tabela aninhada e também mecanismos para os DBAs poderem verificar e descrever as estruturas.

Primeiramente, o que é uma tabela aninhada?

Uma tabela aninhada, e também conhecida como (nested table) é uma tabela que é representada como uma coluna dentro de outra tabela. É um conjunto não ordenado de elementos do mesmo tipo. Tem uma única coluna e o tipo da coluna é um tipo pré-definido ou um tipo de objeto.

Exemplo:

create type  TELEFONES_NT as table of VARCHAR2(14) – (TABELA TIPO)

create table  EMPREGADOS
(Nome       VARCHAR2(25),
 CPF        NUMBER,

 Telefones  TELEFONES_NT )

 NESTED TABLE Telefones store as TELEFONES_ST;  - (Tabela aninhada)

Para melhor ilustrar a explicação vamos de criar um tipo chamado ADDRESS_TYPE. Depois criar o tipo chamado ADDRESS_TYPE criaremos uma tabela chamada ADDRESS_TABLE_TYPE. A tabela ADDRESS_TABLE_TYPE  será usada como um tipo de coluna de uma tabela aninhada.

CONFUSO? Parece bastante confuso no início, mas vale lembrar que todos os atributos de uma tabela precisam de alguma ter um tipo de dado. Estamos criando nosso próprio tipo de dados chamado ADDRESS_TABLE_TYPE que é uma tabela de tipos chamada ADDRESS_TYPE.

Abaixo estão as duas instruções de DDL para mostrar os detalhes de  ADDRESS_TYPE e ADDRESS_TABLE_TYPE realmente tem os mesmos atributos. Esta tabela poderá armazenar vários endereços para cada linha na tabela. Conforme apresentado abaixo:

SQL> CREATE TYPE address_type AS OBJECT

       (ADDR_LINE        VARCHAR2(50),

        CITY             VARCHAR2(50),

        STATE            VARCHAR2(02),

        ZIP              VARCHAR2(05)); - (TABELA TIPO)

SQL> CREATE TYPE address_table_type AS TABLE OF address_type;

SQL> DESC address_type

 Name               Type

 ------------------ --------------

 ADDR_LINE          VARCHAR2(50)

 CITY               VARCHAR2(50)

 STATE              VARCHAR2(2)

 ZIP                VARCHAR2(5)

Para verificar a criação da tabela aninhada pode-se observar a visão DBA_OBJECTS e para verificar o tipo criado deve-se observar a visão DBA_TYPES, usando os comandos abaixo:

SQL> select object_name,object_type 

       from dba_objects 

      where object_name like 'ADDRESS_%';

OBJECT_NAME          OBJECT_TYPE

-------------------- --------------

ADDRESS_TYPE          TYPE

ADDRESS_TABLE_TYPE    TYPE

SQL> select TYPE_NAME,TYPECODE,ATTRIBUTES 

       from dba_types 

      where type_name like 'ADDRESS_%';

TYPE_NAME           TYPECODE         ATTRIBUTES

------------------- ---------------- ----------

ADDRESS_TYPE        OBJECT           4

ADDRESS_TABLE_TYPE  COLLECTION       0

Para extrair o DDL do tipo criado deve-se acessar a visão DBA_SOURCE, usar o comando abaixo:

SQL> select text 

       from dba_source 

      where name = 'ADDRESS_TYPE';

TEXT

-------------------------------------------

TYPE address_type AS OBJECT

       (ADDR_LINE        VARCHAR2(50),

        CITY             VARCHAR2(50),

        STATE            VARCHAR2(02),

        ZIP              VARCHAR2(05));

7 rows selected.

SQL> select text 

       from dba_source 

      where name = 'ADDRESS_TABLE_TYPE';

TEXT

-------------------------------------------------------------------

TYPE address_table_type AS TABLE OF address_type;

Com os tipos criados agora para criar uma tabela, apenas é necessário especificar o nome de uma coluna (para este exemplo, se chamara ADDRESSES) e o tipo recém-criado (ADDRESS_TABLE_TYPE. Abaixo está o DDL para criar a tabela aninhada, alem dos outros comandos SQL  para verificar a criação, deve-se acessar a visão DBA_TAB_COLS, para verificar as colunas do aninhamento deve-se verificar as visões DBA_NESTED_TABLES e DBA_NESTED_TABLE_COLS.

CREATE TABLE employee (

Name          VARCHAR2(20),

addresses     ADDRESS_TABLE_TYPE) 

NESTED TABLE addresses STORE AS addresses_table;

SQL> DESC employee

Name                 Type

-------------------- -----------------

NAME                 VARCHAR2(20)

ADDRESSES            ADDRESS_TABLE_TYPE

SQL> DESC addresses_table

Name                Type

------------------- -------------

ADDR_LINE           VARCHAR2(50)

CITY                VARCHAR2(50)

STATE               VARCHAR2(2)

ZIP                 VARCHAR2(5)

SQL> SELECT table_name, column_name, data_type, data_length

       FROM dba_tab_cols

      WHERE table_name = 'EMPLOYEE';

TABLE_NAME COLUMN_NAME         DATA_TYPE           DATA_LENGTH

---------- ------------------- ------------------- -----------

EMPLOYEE   ADDRESSES           ADDRESS_TABLE_TYPE  16

EMPLOYEE   NAME                VARCHAR2            20

EMPLOYEE   SYS_NC0000200003$   RAW                 16

SQL> SELECT table_name, table_type_name,parent_table_column

       FROM dba_nested_tables

      WHERE table_name = 'ADDRESSES_TABLE';

TABLE_NAME       TABLE_TYPE_NAME            PARENT_TABLE_COLUMN

---------------- -------------------------- -----------------------

ADDRESSES_TABLE  ADDRESS_TABLE_TYPE         ADDRESSES 

SQL> select table_name,column_name,data_type

       from dba_nested_table_cols

      where table_name = 'ADDRESSES_TABLE';

TABLE_NAME           COLUMN_NAME                    DATA_TYPE

-------------------- ------------------------------ ------------

ADDRESSES_TABLE      NESTED_TABLE_ID                RAW

ADDRESSES_TABLE      STATE                          VARCHAR2

ADDRESSES_TABLE      CITY                           VARCHAR2

ADDRESSES_TABLE      SYS_NC_ROWINFO$                ADDRESS_TYPE

ADDRESSES_TABLE      ADDR_LINE                      VARCHAR2

ADDRESSES_TABLE      ZIP                            VARCHAR2

Se quisermos realmente usar esta tabela e a tabela aninhada podemos executar  alguns comandos DML. 

Abaixo estaremos inserindo informações na tabela, deixando a tabela aninhada vazia e em seguida inserindo dados na tabela aninhada. Para gerar um conjunto de resultados que você pode fazer uso da função tabela.

SQL> INSERT INTO employee (name, addresses)

     VALUES ('Dude', address_table_type());

1 row created.

SQL> INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = 'Dude')

      VALUES ('addr1', 'city-1', '01', '11111');

1 row created.

SQL> INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = 'Dude')

     VALUES ('addr2', 'city-2', '02', '22222');

1 row created.

SQL> INSERT INTO TABLE(SELECT addresses FROM employee WHERE name = 'Dude')

     VALUES ('addr3', 'city-3', '03', '33333');

1 row created.

SQL> select t1.name, t2.* from employee t1, table(t1.addresses) t2;

NAME  ADDR_L CITY    ST ZIP

----- ------ ------- -- -----

Dude  addr1  city-1  01 11111

Dude  addr2  city-2  02 22222

Dude  addr3  city-3  03 33333

Tabelas aninhadas, embora pareçam confusas, possuem uma característica interessante. As tabelas aninhadas podem dar algumas opções interessantes para definir objetos, especialmente quando se pode reutilizar  atributos comuns entre tabelas.

Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com

Clonagem manual de um banco de dados Oracle em Linux

Este artigo descreve os passos necessários para clonar manualmente uma instalação existente do banco de dados Oracle para um novo servidor Linux. Antes de iniciar o processo é interessante ler as seguintes notas:

·      Para fazer uma clonagem num ambiente de produção esse método pode não ser recomendado. O ideal é fazer as instalações e clonar somente a base de dados, utilizando “export/import” ou via “backup/restore”

·    O método apresentado assume que não há serviços relacionados ao cluster, portanto, não abrange as instalações usando o ASM ou RAC.

·      Este método assume que a base de dados origem e a base de dados destino têm exatamente a mesma configuração. Ou seja, todos os pré-requisitos da Oracle foram executados no servidor de destino. Neste exemplo, a diferença entre os dois servidores é o nome do servidor (hostname) e endereço IP (TCP/IP).

·     Existem várias notas do suporte Oracle em instalações usando o Oracle Universal Installer (OUI) e o Grid Control (GC), deve-se considerar essas notas antes de utilizar este método de clonagem. Veja o artigo: Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) (Doc ID 1154613.1).

Com esses pontos em mente, aqui estão a lista dos passos necessários para clonar uma instalação existente do banco de dados para um novo servidor.

• Baixar o banco de dados Oracle
• Criar um arquivo de “backup” (TAR File)
• Transferir o arquivo (TAR File)
• Extrair o arquivo (TAR File)
• Verificar as permissões dos arquivos
• Configurar os scripts de “root”
• Configurar os arquivos de ambiente
• Levantar o banco de dados

Baixar o banco de dados Oracle

Desativar todas os serviços Oracle relacionados, incluindo o Grid Control, o banco de dados e o “listener”.

$ # EM

$ emctl stop dbconsole

 

$ # database and listener (>=10g)

$ dbshut $ORACLE_HOME

 

$ # listener (<10g)

$ lsnrctl stop

 

Criar um arquivo de “backup” (TAR File)

Executar o comando “TAR” e opcionalmente compactar o ponto de montagem que contém o software Oracle. Neste caso, todos os produtos de software Oracle e arquivos de dados estão sob o diretório "/u01". Executar o seguinte comando como usuário "root".

# tar -cvf /tmp/u01.tar /u01

# gzip /tmp/u01.tar

Quando estiver finalizado, pode-se reiniciar o banco de dados Oracle no servidor de origem, levantando o “listener”, o banco de dados e o Grid Control, ver o item “Levantar o banco de dados Oracle”.

 

Transferir o arquivo (TAR File)

Deve-se copiar o arquivo do servidor de origem para o servidor de destino. Para essa transferência será utilizado o comando SCP.


# scp /tmp/u01.tar.gz root@192.108.1.2:/tmp/u01.tar.gz

 

Extrair o arquivo (TAR File)

Descompactar e extrair o conteúdo do arquivo TAR no servidor de destino. Executar os seguintes comandos como o usuário "root" no servidor de destino.

# gunzip /tmp/u01.tar.gz

# cd /

# tar -xvf /tmp/u01.tar

 

Verificar as permissões dos arquivos

Verificar as permissões dos arquivos "/u01" diretórios e seus conteúdos. As permissões devem coincidir com as permissões do servidor de origem, caso contrário deve-se alterá-la, usar:

# chown -R oracle:oinstall /u01

 

Configurar os scripts de “root”

Executar o script “orainstRoot.sh”, gerados como parte da instalação original, o servidor de destino como o usuário "root".

# /u01/app/oraInventory/orainstRoot.sh

# /u01/app/oracle/product/11.2.0/db_1/root.sh

 

 

Configurar os arquivos de ambiente

Ajustar o arquivo ".bash_profile" como parte do programa de configuração do servidor de destino, copie o arquivo de origem para o servidor de destino.


# scp /home/oracle/.bash_profile oracle@192.108.1.2:/home/oracle/.bash_profile


Editar o arquivo ".bash_profile" no servidor de destino, alterar o valor correto para a variável de ambiente ORACLE_SID.
Alterar o nome de “host” ou “IP” referências nos arquivos de "listener.ora" e "tnsnames" no diretório "$ORACLE_HOME/network/admin".


Editar o arquivo "/etc/oratab" certificando-se que todas as instâncias estão referenciadas corretamente.


DB11G:/u01/app/oracle/product/11.2.0/db_1:Y

 

Levantar o banco de dados Oracle

Levantar o “listener” e o banco de dados.

$ # listener (<10g)

$ lsnrctl start

$ # database and listener (>=10g)

$ dbstart $ORACLE_HOME

O banco de dados agora deverá funcionar normalmente no servidor de destino.

Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com