Abordando o uso de Database Links em conexões heterogêneas. Acessando o PostgreSQL à partir do Oracle ...

Olá,

Para quem utiliza o serviço heterogêneo de conectividade "heterogeneous services" (hsodbc) para a realização de conexão entre um banco de dados Oracle e um banco de dados não-oracle, perceberá que no Oracle 11g o programa hsodbc foi substituído pelo dg4odbc (Database Gateway for ODBC). Percebi isso quando tentei, para fins de teste, "interligar" um banco de dados Oracle 11g à um banco de dados PostgreSQL. Lembro-me de que alguns anos atrás, não tive maiores problemas ao realizar este tipo de conexão entre um Oracle 10g (instalado em um Suse Linux 32 bits) e um banco de dados PostgreSQL 7x. Naquela época fiz uso das bibliotecas genéricas UNIXODBC. Hoje, utilizando o Oracle 11g em uma plataforma 64 bits, e após ter realizado algumas tentativas mal sucedidas utilizando o UNIXODBC, acabei apelando por fazer o uso de uma versão para avaliação de bibliotecas de terceiros. No mais, neste artigo, irei demonstrar os passos que realizei para acessar um banco de dados PostgreSQL 8.2.4 através de um objeto (database link) criado à partir do Oracle 11g (11.1.0.7) 64 bits instalado em um Linux CentOS release 5.2.

Como eu mencionei o termo (database link), primeiramente irei fazer uma breve apresentação sobre esse objeto:

Um database link (link de banco de dados) é um tipo especial de objeto criado em um esquema de banco de dados Oracle que permite o acesso direto a objetos de outro banco de dados, seja ele Oracle (homogêneo) ou não (heterogêneo).

Um database link geralmente é utilizado para suportar conexões utilizadas freqüentemente em bancos de dados remotos. Este objeto, na verdade, especifica o descritor de conexão a ser utilizado em uma conexão e também pode especificar o nome de usuário a ser conectado ao banco de dados remoto. Vale a pena salientar que, no geral, se utiliza links de banco de dados de forma a criar objetos locais (como views ou sinônimos) que acessam bancos de dados remotos.

Os sinônimos locais para objetos remotos, fornecem transparência de localização para os usuários locais do banco de dados, ou seja, quando um link de banco de dados é referenciado diretamente ou através de um sinônimo ou uma view, a instrução SQL é enviada e executada em uma nova sessão no banco de dados remoto. Ao ser executada, os dados são retornados para a sessão no banco de dados de origem.

Os links de banco de dados podem ser criados como links públicos (por DBA's) tornando os mesmos disponíveis para todos os usuários de banco de dados local ou como links privados. Para a criação deste objeto, se utiliza a sintaxe abaixo:

CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password [ dblink_authentication ]
    }
  | dblink_authentication
  ]
    [ CONNECT TO
      { CURRENT_USER
      | user IDENTIFIED BY password [ dblink_authentication ]
      }
    | dblink_authentication
    ]...
  [ USING connect_string ] ;

Por exemplo, o comando abaixo iria criar um link de banco de dados público chamado DBLINK:

SQL> create public database link DBLINK
  2  connect to SCOTT identified by TIGER 
  3  using 'DB02';

onde, 'DB02' é o nome de serviço de banco dados existente no arquivo TNSNAMES.ORA local do meu servidor Oracle, na qual estão configuradas as informações da localização do servidor de banco de dados remoto.

Para utilizar esse database link, bastaria apenas adicioná-lo como sufixo aos nomes das tabelas como demonstrado na criação da view abaixo:

SQL> create view emp_remoto
  2  as 
  3  select * FROM emp@DBLINK
  4  where cod_dept > 10;

Podemos ver no exemplo acima que a cláusula "FROM" refere-se à EMP@DBLINK, onde, EMP é a tabela de destino, e DBLINK o objeto (database link) que contém as informações "encapsuladas" das informações do banco de dados de destino.

A propósito, no exemplo anterior foi criada uma view de banco de dados (emp_remoto), com o objetivo de simular uma regra de negócio na qual seria limitado os registros que os usuários poderiam recuperar, mas, se nenhuma restrição desse tipo for necessária, então poderíamos criar um sinônimo público como mostrado pelo comando abaixo:

SQL> create public synonym emp_remoto for emp@DBLINK;

Os usuários locais que consultarem o sinônimo público local EMP_REMOTO terão suas consultas automaticamente redirecionadas à tabela EMP do banco de dados remoto e, portanto, de forma transparente.

Irei demonstrar abaixo um exemplo prático na qual criarei um database link em um banco de dados Oracle 10g Express Edition para permitir acesso a um banco de dados Oracle 11g instalado em outro servidor. Observe que passarei a string de conexão diretamente no comando de criação do database link, sem a necessidade de ter definido previamente um serviço TNS de banco de dados, referente à instância Oracle remota no arquivo local TNSNAMES.ORA:

oracle@xe-developer:~> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Qui Jul 02 08:03:52 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create database link dblink11g
  2  connect to system identified by minhasenha
  3  using '(description = (address = (protocol = tcp)
  4  (host = 11g_production)(port = 1521))
  5  (connect_data = (sid = bdprod)))';

Vínculo de banco de dados criado.

-- Selecionando informações do banco de dados local
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- --------------------------------------
XE               xe-developer


-- Selecionando informações do banco de dados remoto
SQL> select instance_name,host_name from v$instance@dblink11g;

INSTANCE_NAME    HOST_NAME   
---------------- --------------------------------------
BDPROD           11g_production

Bom, depois dessa breve introdução ao conceito de Database Links, vamos voltar ao assunto que originou este artigo ...

Para início, é bom ficar claro que na versão 11g do banco de dados Oracle, não será mais possível executar o utilitário abaixo como nas versões anteriores:

[oracle@Linux ~]$ hsodbc

Oracle Corporation --- QUINTA-FEIRA   JUL 02 2009 08:17:25.104

Heterogeneous Agent Release 10.2.0.1.0 - Production  Built with
   Driver for ODBC

Caso alguma tentativa de acesso ao programa hsodbc seja realizada utilizando o Oracle 11g, o erro abaixo será retornado:

[oracle@Linux ~]$ hsodbc
-bash: hsodbc: command not found

Portanto, deverá ser utilizado o programa dg4odbc que, inclusive, poderá ser instalado e utilizado em versões anteriores ao Oracle 11g conforme a nota 466228.1 do Oracle Metalink:

"Starting with 11g Oracle now provides a Database Gateway for ODBC for 32bit Linux operating systems. The difference between the older product (Generic Connectivity or sometimes also called HSODBC) is DG4ODBC is now a stand alone product. It is no longer required to have DG4ODBC running on the same machine as the Oracle database. The gateway is certified also for older Oracle releases 9.2.0.8, 10.1.0.5, or 10.2.0.3. But please be aware those pre-11g Oracle databases require a patch to work properly with V11 Gateways."

[oracle@Linux ~]$ dg4odbc

Oracle Corporation --- QUINTA-FEIRA   JUL 02 2009 08:20:35.379

Heterogeneous Agent Release 11.1.0.7.0 - 64bit Production  Built with
   Oracle Database Gateway for ODBC

[oracle@Linux ~]$

Após ter realizado o download das bibliotecas (64 bits) de avaliação do site em questão, irei iniciar a instalação das mesmas:

[root@Linux tmp]# tar xzvf evlinuxx64.tar.Z
autorun.dat
install.mi
unixmi.ksh
etc/lang/
etc/lang/msg.dat
etc/lang/license.txt
etc/lang/useng.msg
etc/lic/
etc/lic/makelica
etc/lic/makeliclx
etc/lic/makelicl
etc/lic/makelich
etc/lic/makelicsx
etc/lic/makelics
linuxx/ICULicense.txt
linuxx/mysqllicense.txt
linuxx/ODBC64FIXES.TXT
linuxx/ODBC64README.TXT
linuxx/scr1
linuxx/tarfiles/
linuxx/tarfiles/teradata.tar
linuxx/tarfiles/mssql.tar
linuxx/tarfiles/oracle.tar
linuxx/tarfiles/sybase.tar
linuxx/tarfiles/mysql.tar
linuxx/tarfiles/oraclewp.tar
linuxx/tarfiles/linuxx.tar
linuxx/tarfiles/greenplum.tar
linuxx/tarfiles/drda.tar
linuxx/tarfiles/postgresql.tar
linuxx/tarfiles/infcl.tar

[root@Linux tmp]# ksh unixmi.ksh
DataDirect Technologies Product Setup is preparing....

English has been set as the installation language.

Log file : /tmp/logfile.9216.1
----------------------------------------------------------------------
DataDirect Technologies Product Setup (DPS)
for UNIX operating systems
----------------------------------------------------------------------

The following operating system has been detected:

LinuxX64
Is this the current operating system on your machine (Y/N) ? [Y]
END USER PRODUCT LICENSE AGREEMENT ("AGREEMENT")

DataDirect EULA Revised 02/12/2009

Enter YES to accept the above agreement : yes
Please enter the following information for proper registration.

In the Key field, enter either EVAL or the Key provided.

Name          :EVAL
Company       :legatti - evaluation
Serial Number :EVAL
Key           :EVAL

You can install drivers for all supported databases or
install a single driver.

Drivers for All Supported Databases includes one driver for each supported database.
In the case of Oracle, DataDirect Connect64 for ODBC 6.0 SP1 has two available drivers
that support the database--a wire protocol driver that does not require any database
client software to connect to the database, and a client-based driver that does require
client software.  By default, the Oracle Wire Protocol driver is installed.

Single Driver allows you to choose one from any of the DataDirect Connect64 for 
ODBC 6.0 SP1 drivers, including the client-based Oracle driver.

If you want to evaluate more than one single driver, you will have the opportunity to do
so after completion of the initial installation.

1.   Drivers for All Supported Databases
2.   Single Driver
Please enter an option [1]:2


1.   DB2 Wire Protocol
2.   Greenplum Wire Protocol
3.   Informix Wire Protocol
4.   MySQL Wire Protocol
5.   Microsoft SQL Server Wire Protocol
6.   Oracle Wire Protocol
7.   PostgreSQL Wire Protocol
8.   Sybase Wire Protocol
9.   Teradata (XE)
10.  Oracle (client)
Please enter driver option:7

You have chosen to eval the PostgreSQL Wire Protocol driver.

To change this information, enter C. Otherwise, press Enter to continue. :

You have chosen to eval the PostgreSQL Wire Protocol driver.

To change this information, enter C. Otherwise, press Enter to continue. :
DataDirect Technologies Product Setup is preparing the installation.
Choose a temporary directory.

Enter the full path to the temporary install directory.[/tmp]:
Checking for available space...

There is enough space.
Extracting files...

Choose a destination directory.
Enter the full path to the install directory.[/opt/Connect64forODBC60]:
Checking for available space...

There is enough space.
Extracting files...

Creating license file......


DataDirect Technologies Product Setup successfully removed all of the temporary files.


Thank you for using DataDirect Technologies products.

Installation ended successfully.

Would you like to install another product (Y/N) ? [Y]N
Thank you for using DataDirect Technologies products.

Installation ended successfully.

Após realizada a instalação das bibliotecas, iniciarei a configuração dos arquivos odbc.ini e odbinst.ini como demonstrado abaixo:

[root@Linux /]# cd /opt/Connect64forODBC60/

-- Arquivo odbc.ini
[root@Linux Connect64forODBC60]# cat odbc.ini
[ODBC Data Sources]
PostgreSQL Wire Protocol=DataDirect 6.0 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/Connect64forODBC60
Trace=0
TraceFile=odbctrace.out
TraceDll=/opt/Connect64forODBC60/lib/odbctrac.so

[PostgreSQL Wire Protocol]
Driver=/opt/Connect64forODBC60/lib/ddpsql24.so
Description=DataDirect 6.0 PostgreSQL Wire Protocol
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=bdlegatti
EnableDescribeParam=1
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchRefCursor=1
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=10.10.10.2
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=postgres
MaxPoolSize=100
MinPoolSize=0
Password=postgres
Pooling=0
PortNumber=5432
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
ValidateServerCertificate=1
XMLDescribeType=-10


-- Arquivo odbcinst.ini
[root@Linux Connect64forODBC60]# cat odbcinst.ini
[ODBC Drivers]
DataDirect 6.0 PostgreSQL Wire Protocol=Installed

[ODBC Translators]
OEM to ANSI=Installed

[Administrator]
HelpRootDirectory=/opt/Connect64forODBC60/adminhelp

[ODBC]
#This section must contain values for DSN-less connections
#if no odbc.ini file exists. If an odbc.ini file exists,
#the values from that [ODBC] section are used.

[DataDirect 6.0 PostgreSQL Wire Protocol]
Driver=/opt/Connect64forODBC60/lib/ddpsql24.so
Setup=/opt/Connect64forODBC60/lib/ddpsql24.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=/opt/Connect64forODBC60/help
SQLLevel=0

O próximo passo é configurar os arquivos listener.ora e tnsnames.ora de forma a fazer referência ao serviço de banco de dados que chamarei de POSTGRESQL conforme demonstrado no exemplo abaixo:

[oracle@Linux ~]$ cd $ORACLE_HOME/network/admin

-- Arquivo listener.ora
[oracle@Linux admin]$ cat listener.ora
# listener.ora Network Configuration File: $ORACLE_HOME/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = BD01)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = BD01)
    )
    (SID_DESC =
      (SID_NAME= POSTGRESQL)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH = /opt/Connect64forODBC60/lib:
                              /u01/app/oracle/product/11.1.0/db_1/lib:
                              /lib:/usr/lib:/etc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Apenas como observação, caso uma versão anterior do Oracle (10g, 9i ...) esteja sendo utilizada, no trecho do código acima deverá ser substituído a linha onde está (PROGRAM=dg4odbc) para (PROGRAM=hsodbc).

-- Arquivo tnsnames.ora
[oracle@Linux admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: $ORACLE_HOME/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BD01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BD01)
    )
  )

PG_SERVICE =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST = Linux)(PORT=1521))
        (CONNECT_DATA=(SID=POSTGRESQL))
        (HS=OK)
      )

Após definida a configuração dos arquivos listener.ora e tnsnames.ora, irei inicializar o processo listener conforme demonstrado no exemplo abaixo:

-- Inicialização do processo listener
[oracle@Linux ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 02-JUL-2009 09:10:53

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Iniciando /u01/app/oracle/product/11.1.0/bd01/bin/tnslsnr: aguarde...

TNSLSNR for Linux: Version 11.1.0.7.0 - Production
O arquivo de parâmetros do sistema é $ORACLE_HOME/network/admin/listener.ora
Atendendo em: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux)(PORT=1521)))
Atendendo em: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Estabelecendo conexão com (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux)(PORT=1521)))
STATUS do LISTENER
------------------------
Apelido              LISTENER
Versão               TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Data Inicial         02-JUL-2009 09:10:53
Funcionamento        0 dias 0 hr. 0 min. 0 seg
Nível de Análise     off
Segurança            ON: Local OS Authentication
SNMP                 OFF
Arq. Parâm. Listn.   /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Resumo de Atendimento...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Resumo de Serviços...
O serviço "BD01" tem 1 instância(s).
  Instância "BD01", status UNKNOWN, tem 1 handler(s) para este serviço...
O serviço "POSTGRESQL" tem 1 instância(s).
  Instância "POSTGRESQL", status UNKNOWN, tem 1 handler(s) para este serviço...
O comando foi executado com êxito

Afim de evitar quaisquer erros de acesso às bibliotecas ODBC instaladas, ou qualquer outra surpresa, irei atualizar também arquivo de profile do usuário "oracle" com o caminho das bibliotecas ODBC instaladas anteriormente:

[oracle@Linux ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export TEMP=/tmp
export TMPDIR=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=BD01
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/ctx/bin:/opt/Connect64forODBC60/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:
                       /opt/Connect64forODBC60/lib
export NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
export NLS_DATE_FORMAT=DD/MM/YYYY

Agora vem a parte em que criamos o arquivo de configuração para que seja possível a realização da conexão entre o Oracle e o banco de dados remoto (no meu caso, o PostgreSQL). Os programas dg4odbc/hsodbc geralmente procuram por um arquivo init[SID].ora localizado em $ORACLE_HOME/hs/admin. No meu caso, o SID que utilizei no arquivo listener.ora foi POSTGRESQL, portanto o arquivo a ser criado será initPOSTGRESQL.ora conforme demonstrado no trecho de código abaixo:

[oracle@Linux ~]$ cd $ORACLE_HOME/hs/admin

[oracle@Linux admin]$ cat initPOSTGRESQL.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

# HS init parameters
#
HS_FDS_CONNECT_INFO = "PostgreSQL Wire Protocol"
HS_FDS_TRACE_LEVEL = OFF
HS_LANGUAGE = "BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
HS_NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS"
HS_OPEN_CURSORS = 200
HS_FDS_SHAREABLE_NAME = /opt/Connect64forODBC60/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/opt/Connect64forODBC60/odbc.ini
set ODBCINSTINI=/opt/Connect64forODBC60/odbcinst.ini
set LD_LIBRARY_PATH=/opt/Connect64forODBC60/lib:
                    /u01/app/oracle/product/11.1.0/bd01/lib:
                    /lib:/usr/lib
set PATH=/opt/Connect64forODBC60/lib:/u01/app/oracle/product/11.1.0/bd01/lib:
         /lib:/usr/lib
#
# Environment variables required for the non-Oracle system
#
#set [envvar]=[value]

Basicamente, os valores e parâmetros acima mais importantes são:

* set ODBCINI define o local onde está localizado o arquivo odbc.ini
* HS_FDS_CONNECT_INFO aponta para o nome da fonte de dados ODBC (DSN).
* HS_FDS_SHAREABLE_NAME aponta para a biblioteca de driver ODBC compartilhada.

Caso algum problema ocorra na tentativa de realizar a conexão com o banco de dados remoto, setando o parâmetro HS_FDS_TRACE_LEVEL com um valor maior que zero ou (ON ou DEBUG) é que nos permitirá verificar com mais detalhes o que aconteceu de errado. Para maiores informações sobre estes e outros parâmetros, acesse a página Oracle Database Gateway for ODBC User's Guide.

Por fim, após realizado todo este trabalho acima, bastará agora, para fins de teste, criar um link de banco de dados privado no meu banco de dados Oracle local que chamarei de "pgsql" para, então, realizar o teste de conexão com o banco de dados remoto. Vale a observação de que eu já criei e populei no banco de dados remoto (PostgreSQL), uma tabela de teste chamada "emp" (empregados) com a estrutura a seguir:

create table emp
(
    cod smallint NOT NULL,
    data_cadastro date NOT NULL,
    nome varchar(80) NOT NULL,
constraint pk_emp primary key (cod)
);

--  Criando o link de banco de dados (database link)
C:\>sqlplus system/*********

SQL*Plus: Release 11.1.0.6.0 - Production on Qui Jul 02 09:25:44 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link pgsql connect to "postgres" identified by "postgres"
  2  using 'PG_SERVICE';

Vínculo de banco de dados criado.

-- Realizando o teste de conexão com o PostgreSQL
SQL> select * from "emp"@pgsql;

       cod data_cadas nome
---------- ---------- ---------------------------------
         1 25/06/2009 EDUARDO
         2 28/06/2009 MARIA
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA


SQL> delete from "emp"@pg where "cod"=2;

1 linha deletada.

SQL> commit;

Commit concluído.

SQL> select * from "emp"@pgsql;

       cod data_cadas nome
---------- ---------- ---------------------------------
         1 25/06/2009 EDUARDO
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA

Em relação ao PostgreSQL, será necessário colocar os nomes das tabelas e colunas entre "aspas duplas", de forma a acessar os objetos do banco de dados, caso contrário, o erro abaixo será emitido:

SQL> select * from emp@pgsql;
select * from emp@pgsql
              *
ERRO na linha 1:
ORA-00942: a tabela ou view não existe
[DataDirect][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: relation "EMP" does 
not exist(File namespace.c;Line 221;Routine RangeVarGetRelid;)[DataDirect][ODBC 
PostgreSQL Wire Protocol driver][PostgreSQL]Failed transaction. 
The current transaction rolled back.
ORA-02063: precedendo 2 lines a partir de PGSQL


SQL> select owner,table_name from all_tables@pgsql where table_name='emp';

OWNER           TABLE_NAME
--------------- ------------------------------
public          emp


SQL> desc "emp"@pgsql
 Nome                       Nulo?     Tipo
 -------------------------- --------- ----------------------------
 cod                        NOT NULL  NUMBER(5)
 data_cadastro              NOT NULL  DATE
 nome                       NOT NULL  VARCHAR2(80)


SQL> select nome from "emp"@pgsql;
select nome from "emp"@pgsql
       *
ERRO na linha 1:
ORA-00904: "NOME": identificador inválido

SQL> select "nome" from "emp"@pgsql;

nome
-------------------------------------------------
EDUARDO
ANGELA
LAURA


SQL> create view emp_remoto
  2  as
  3  select * from "emp"@pgsql;

View criada.

SQL> select * from emp_remoto;

       cod data_cadas nome
---------- ---------- ---------------------------
         1 25/06/2009 EDUARDO
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA


SQL> create synonym emp for "emp"@pgsql;

Sinônimo criado.

SQL> select * from emp;

       cod data_cadas nome
---------- ---------- ---------------------------
         1 25/06/2009 EDUARDO
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA


SQL> create table "emp2"@pgsql (id integer);
create table "emp2"@pgsql (id integer)
                   *
ERRO na linha 1:
ORA-02021: as operações DDL não são permitidas em um banco de dados remoto

Para concluir, podemos ver acima que o último comando falhou, pelo fato de não ser possível qualquer operação DDL (Data Definition Language) no banco de dados remoto.

Atribuição ou Função?

Olá,

Apesar de ser um assunto nada relevante no que se refere à administração de banco de dados Oracle, por incrível que pareça o mesmo sempre me intrigou e que, agora, parece que o "problema" era simplesmente um erro de tradução do termo "role" na camada de mensagens do Oracle Client 10g (release 1) e versões anteriores. No Oracle client 10g (release 2) e 11g, a tradução da mensagem informativa que aparece após a execução do comandos create role e drop role foi devidamente corrigida:

-- Oracle Client 10g release 1
C:\>sqlplus system/******

SQL*Plus: Release 10.1.0.2.0 - Production on Qui Jun 25 13:45:32 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role teste;

Função criada.

Podemos ver acima a mensagem "Função criada.", mas eu criei uma atribuição e não uma função! Se alterarmos a sessão para utilizar o idioma Inglês, poderemos perceber abaixo a mensagem "Role dropped." e não "Function dropped.":

SQL> alter session set nls_language=american;

Session altered.

SQL> drop role teste;

Role dropped.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit 
Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Irei agora utilizar o SQL*Plus do Oracle Client 11g release 1 ...

-- Oracle Client 11g release 1
C:\>sqlplus system/******

SQL*Plus: Release 11.1.0.6.0 - Production on Qui Jun 25 13:47:02 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role teste;

Atribuição criada.

SQL> drop role teste;

Atribuição eliminada.

Realizando o teste utilizando o SQL*Plus do Oracle 10g (release 2) diretamente do servidor onde o banco de dados está instalado, o termo "Atribuição" é corretamente mostrado, o que indica que o "problema", ao meu ver, estava apenas no Oracle Client do Oracle 10g release 1 e versões anteriores:

[oracle@linux ~]$ export NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
[oracle@linux ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Qui Jun 25 15:13:07 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role teste;

Atribuição criada.

SQL> drop role teste;

Atribuição eliminada.

Definição de senhas case-sensitive no Oracle 11g ...

Olá,

Para quem está planejando migrar o seu atual banco de dados Oracle para a mais recente versão Oracle 11g, ao utilizar o DBCA (Database Configuration Assistant), irá se deparar com uma nova tela de configuração intitulada de "Definições de Segurança". O objetivo desta tela é configurar, além de algumas definições padrão de auditoria, um novo perfil de senhas como mostrado na figura abaixo:

As configurações de segurança padrão ativam a auditoria para muitos privilégios de sistemas diferentes, como create session, create user, drop user, create any procedure, alter system, alter database, entre outras, de forma a seguir algumas exigências normativas definidas na legislação americana Sarbanes-Oxley (SOX).

O objetivo deste artigo é demonstrar a nova funcionalidade de senhas case-sensitive do Oracle 11g que, até então, eram case-insensitive, ou seja, no Oracle 11g existe a diferenciação entre letras maiúsculas e minúsculas na definição de uma senha de usuário de banco de dados. Por exemplo: Nas versões anteriores ao Oracle 11g, caso criássemos um usuário de banco de dados SCOTT e definíssemos sua senha para TIGER, poderíamos então nos conectar no banco de dados através deste usuário digitando a senha TIGER, Tiger, tiger, TiGer ou tiGER, sem qualquer distinção entre letras maiúsculas e minúsculas.

Agora no Oracle 11g, ao escolher a primeira opção conforme mostrado na figura acima, o banco de dados fará distinção entre letras maiúsculas e minúsculas ao entrar com a senha do usuário. No exemplo anterior, a senha do usuário SCOTT foi definida como TIGER (todas em maiúsculas), portanto, a conexão com o banco de dados só será bem sucedida caso a senha seja fornecida seja TIGER, não mais Tiger, tiger, TiGer ou tiGER.

Podemos ver abaixo o novo parâmetro de inicialização responsável por implementar este novo perfil de senhas case-sensitives no Oracle 11g:

SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
sec_case_sensitive_logon             boolean     TRUE

Como demonstrado acima, o parâmetro SEC_CASE_SENSITIVE_LOGON está atualmente configurado como TRUE, ou seja, para realizar uma conexão bem sucedida no banco de dados, um usuário deverá fornecer a senha exatamente como foi definida durante a criação do usuário (diferenciando maiúsculas e minúsculas), isso supondo que a mesma tenha sido definida já no próprio Oracle 11g. Vamos então a um exemplo prático:

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Qua Jun 17 09:32:49 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> create user scott identified by tiger;

Usuário criado.

SYS> grant connect to scott;

Concessão bem-sucedida.

SYS> connect scott/TIGER
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


Advertência: Você não está mais conectado ao ORACLE.
@> connect scott/Tiger
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


@> connect scott/TiGer
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


@> connect scott/tiGER
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


@> connect scott/tiger
Conectado.

De acordo com a demonstração acima, podemos perceber que o usuário SCOTT só conseguiu se logar no banco de dados quando a senha "tiger" (todas em minúsculas) foi fornecida. Ao consultarmos a view de dicionário de dados DBA_USERS, poderemos perceber duas mudanças significativas:

SYS> select username, password, password_versions from dba_users;

USERNAME              PASSWORD                 PASSWORD_VERSIONS
--------------------- ------------------------ -----------------
MGMT_VIEW                                      10G 11G
SYS                                            10G 11G
SYSTEM                                         10G 11G
DBSNMP                                         10G 11G
SYSMAN                                         10G 11G
SCOTT                                          10G 11G
OUTLN                                          10G 11G
ANONYMOUS
WMSYS                                          10G 11G
XDB                                            10G 11G
DIP                                            10G 11G
ORACLE_OC                                      10G 11G
TSMSYS                                         10G 11G
XS$NULL                                        10G 11G

14 linhas selecionadas.

A primeira mudança que podemos notar é que a informação do valor hash da senha na coluna PASSWORD não é mais mostrada como nas versões anteriores do Oracle, portanto, a mesma foi omitida no Oracle 11g, mas nada nos impede visualizar o seu valor hash na tabela base de dicionário de dados SYS.USER$.

SYS> select name,password from sys.user$ where name='SCOTT';

NAME                       PASSWORD
-------------------------- ------------------------------
SCOTT                      F894844C34402B67

Vale a pena salientar que o novo valor hash gerado pela senha case-sensitive é armazenada na coluna SPARE4 da tabela base SYS.USER$ do dicionário de dados.

Uma outra mudança perceptível na view de dicionário de dados DBA_USERS foi adição de uma nova coluna chamada PASSWORD_VERSIONS que indica em qual versão do Oracle a senha do usuário foi criada.

O valor "10g 11g" significa que a senha em questão possui informações hash tanto na coluna PASSWORD quanto na coluna SPARE4 da tabela base SYS.USER$, e que a mesma pode ou não ser case-sensitive (Isso vai depender se o parâmetro SEC_CASE_SENSITIVE_LOGON está ativado ou não).

O valor "10g" significa que somente a coluna PASSWORD possui a informação hash da senha do usuário, indicando que independente do parâmetro SEC_CASE_SENSITIVE_LOGON estar ativado ou não, a senha deste usuário vai ser case-insensitive até que a mesma seja alterada.

O valor "11g" indica que somente a coluna SPARE4 possui a informação hash da senha do usuário, indicando que a mesma é case-sensitive, portanto, o usuário conseguirá se conectar no banco de dados apenas quando o parâmetro SEC_CASE_SENSITIVE_LOGON estiver configurado com o valor TRUE (ativado). Abaixo irei realizar uma pequena demonstração:

SYS> alter user scott identified by TIGER;

Usuário alterado.

SYS> select password,spare4 from user$ where name='SCOTT';

PASSWORD                   SPARE4
-------------------------- --------------------------------------------------------------
F894844C34402B67           S:2CA14104071F06830C95527377C53B0DBF0349DE1B7BF4238EA663C806FD

SYS> select password_versions from dba_users where username='SCOTT';

PASSWORD
--------
10G 11G

SYS> alter user scott identified by values 'F894844C34402B67';

Usuário alterado.

SYS> select password,spare4 from user$ where name='SCOTT';

PASSWORD                   SPARE4
-------------------------- ----------------------------------------------------------
F894844C34402B67

SYS> select password_versions from dba_users where username='SCOTT';

PASSWORD
--------
10G

SYS> alter user scott identified 
  2  by values 'S:2CA14104071F06830C95527377C53B0DBF0349DE1B7BF4238EA663C806FD';

Usuário alterado.

SYS> select password,spare4 from user$ where name='SCOTT';

PASSWORD                   SPARE4
-------------------------- --------------------------------------------------------------
                           S:2CA14104071F06830C95527377C53B0DBF0349DE1B7BF4238EA663C806FD

SYS> select password_versions from dba_users where username='SCOTT';

PASSWORD
--------
11G

Em resumo, quando se define uma nova senha de usuário utilizando o Oracle 11g, a mesma irá atualizar os valores hash nas colunas PASSWORD e SPARE4 independente do parâmetro SEC_CASE_SENSITIVE_LOGON estiver ativado ou não.

Portanto, quando migramos usuários de versões Oracle anteriores (8i, 9i, 10g) para o Oracle 11g, seja utilizando uma importação FULL com o tradicional utilitário de importação (imp), ou usando o utilitário Import Datapump (impdp), nas quais as senhas dos usuários são case-insensitives, as senhas desses usuários continuarão case-insensitives, independente do parâmetro SEC_CASE_SENSITIVE_LOGON estar ativado ou não.

No caso de usuários com privilégios SYSDBA e SYSOPER, um outro parâmetro foi adicionado ao utilitário orapwd de forma a controlar senhas case-sensitives no arquivo de senhas:

$ orapwd file=orapwBD01 entries=5 ignorecase=y password=minha_senha

O parâmetro ignorecase, demonstrado no comando acima, pode ter o valor "n" (default), informando que as senhas serão tratadas como case-sensitives, ou "y" informando que as senhas serão tratadas como case-insensitives.

Para finalizar, e aproveitando o assunto de segurança de acesso e senhas de usuários, agora no Oracle 11g foi também disponibilizada uma view de dicionário de dados DBA_USERS_WITH_DEFPWD que ajuda o DBA a identificar quais usuários possuem a senha padrão "default" desde a criação do banco de dados, de forma que as mesmas possam ser alteradas quando possível:

SYS> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
XS$NULL
TSMSYS
OUTLN
EXFSYS
ORACLE_OCM
XDB
WMSYS

8 linhas selecionadas.

Este mercado é promissor? Vale a pena a certificação?

Olá,

Recebi de um leitor uma questão relacionada ao mercado de banco de dados e certificações Oracle, e resolvi então publicar aqui de forma a compartilhar e expor o meu humilde ponto de vista.

Olá Eduardo Legatti,

primeiramente quero parabenizar pelo blog, sendo uma ótima fonte de estudos, cheguei até ele através do google, quando procurava informações sobre certificação Oracle OCA.

Estou começando meus estudos na área, faço Análise e Desenvolvimento de Sistemas e ao estudar pela primeira vez Banco de Dados, tive uma enorme vontade de me especializar em algum banco específico, pesquisando pela web percebi que é uma boa estudar Oracle, desde então venho buscando fontes de estudo.

Até mais.. e mais uma vez parabéns.

ps: ao seu ver, esse mercado é promissor, vale a pena a certificação?

Primeiramente, obrigado pelo comentário. Realmente acho que o mercado de banco de dados, seja ele Oracle, MS SQL Server, DB2, etc.. é promissor.

Na minha visão, o mercado de trabalho para um DBA é a mesma para qualquer outra área de tecnologia. Tudo depende da demanda do mercado. Por exemplo, às vezes acontece de empresas estarem muito precisando de um profissional trainee para trabalhar com administração de banco de dados Oracle e/ou MS SQL Server. Também, não é raro existirem muitas demandas de profissionais que tenham conhecimentos básicos em DB2 da IBM, ou PostgreSQL ou MySQL, ou Oracle para cargo de trainee.

Acredito que para entrar nesta área, o profissional necessitará ter o conhecimento mínimo básico para iniciar um trabalho de trainee ou DBA Júnior.

Olha, tem muita gente que tira certificação sem qualquer experiência na área achando que com isso irá conseguir um emprego rápido. Na verdade, a certificação vai dar uma maior visibilidade no currículo e chamar a atenção de quem está recrutando. Isso mostra que um profissional certificado tem mais oportunidades de quem não tem certificação. Quem tira certificação em uma determinada tecnologia, na verdade, está assinando que é um "expert" na mesma, ou seja, a certificação ajudou o profissional a ser chamado para uma entrevista. Agora, se nessa entrevista, o mesmo não demonstrar nenhuma experiência e/ou habilidades necessárias para ocupar o cargo, então pode ter certeza de que o mesmo não será contratado. Infelizmente ou felizmente, é o mercado quem dita as regras, portanto, é necessário que estejamos sempre preparados. No mais, com certeza vale a pena a certificação.

Em relação às fontes de estudo, quando eu estava estudando para tirar a certificação OCA/OCP 9i, eu estava estudando pelos manuais oficiais de quando eu fiz os cursos do Oracle 9i. Eu também tinha comprado os livros "OCA/OCP Introdução ao Oracle 9i SQL (1Z0-007)" e "OCA/OCP Oracle9i Database Fundamentos I (1Z0-031)" ambos da Oracle Press - Autor Jason Couchman. Posteriormente, quando estava estudando para atualizar a minha certificação OCP 9i para OCP 10g, passei mais tempo praticando no próprio banco de dados Oracle 10g e lendo mais a documentação oficial.

Agora, se você está estudando para tirar a certificação do Oracle 10g ou 11g (Recomendo o 11g), então eu sempre indico os manuais da SYBEX porque eles abordam todos os tópicos que caem nos exames. Outra coisa que também acho importante é a de praticar nos simulados da SelfTest ou Transcender. Caso você consiga algum material (no Google), os mesmos serão muito úteis para o seu aprendizado.

Aproveitando a sua questão, e já que você está cursando "Análise e Desenvolvimento de Sistemas", vou comentar um pouco de perfis de DBA's. Bom, acredito que existam no mínimo 2 tipos de DBA's hoje no mercado.

O tipo 1 seria aquele DBA que trabalha no servidor de produção, realizando manutenções necessárias de tuning, sugerindo ajustes de memória e I/O quando necessários, fazendo realocações de arquivos de banco de dados, criando e executando políticas de backup/recovery, realizando aplicações de patches e correções críticas, entre outras, sem ter uma noção clara de como os SQL’s foram codificados nas aplicações de terceiros que são executadas no servidor.

O outro tipo de DBA (tipo 2) que é mais o meu caso, é aquele voltado para o desenvolvimento de aplicações na qual, é de fundamental importância, o conhecimento das regras de negócios em questão, de forma a fornecer a melhor solução de banco de dados para um determinado tipo de problema, além de possuir conhecimentos sólidos de modelagem de dados, ser apto a sugerir a criação de um padrão de nomenclatura de objetos de banco de dados e, conseqüentemente, ser apto de fiscalizar e garantir o uso desse padrão pelos analistas de sistemas e desenvolvedores. Outra tarefa seria a criação de uma política de melhores práticas de construções de instruções SQL, realização de análise de performance dos SQL’s construídos de forma a capturar o melhor plano de acesso aos registros e, assim, realizar os ajustes necessários avaliando a necessidade de criação de índices (btree/bitmap), views materializadas, tabelas/índices particionados, etc…

Em resumo, o trabalho do DBA tipo 2 seria mais de prover suporte à equipe de desenvolvimento, garantindo asimm a qualidade das aplicações desenvolvidas no que se refere ao modelo de dados e acesso ao banco de dados.

Fazendo uma comparação, acredito que um bom trabalho realizado pelo DBA tipo 2 facilitaria muito, em parte, o trabalho realizado pelo DBA tipo 1.

Boa sorte e até mais ...

Dúvidas sobre modelagem de dados ...

Olá,

Recentemente recebi de um colega, uma dúvida sobre modelagem de dados e resolvi então compartilhar aqui.

Cenário:

Possuo um cadastro de clientes que usam o serviço de hospedagem de sites, e um outro cadastro que utiliza o serviço de registro de domínios. A grande questão é como representar isso, os casos são os seguintes:

• Um cliente pode ter somente o serviço de hospedagem de sites, logo, todos os seus dados estariam na tabela de hospedagem.

• Um cliente pode ter somente o serviço de registro de domínios, logo, todos os seus dados estariam na tabela de registro de domínios.

• Um cliente pode ter ambos os serviços, logo, teria dados nas 2 tabelas, mas, esses dados poderiam ser os mesmos, como também não poderiam.

Detalhe: Essa estrutura precisaria ser preparada para num futuro próximo aumentar os serviços, ou seja, além de hospedagem e domínios, possuir outros, loja virtual, etc.

Enfim, imaginei uma terceira tabela para controlar isso, mandei um esboço em anexo mas estou receoso devido ao fato de com isso existirem muitas chaves estrangeiras nulas e a aplicação precisaria descobrir em qual tabela estariam os dados do cliente.

Solução:

Bem, em relação ao último parágrafo do texto acima, posso dizer que chaves estrangeiras nulas não são problema algum. Imagine que eu tenha uma tabela de clientes onde a mesma possua uma coluna (id_servico) que armazene códigos de serviços, e que a mesma referencie a tabela de serviços.

Se pode haver clientes que não prestam nenhum serviço, então poderiam existir vários clientes onde a coluna id_serviço estaria NULA.

Não se pode confundir chave estrangeira com chave primária. Chave primária é obrigatória, mas chave estrangeira pode ou não ser obrigatória e isso vai depender da regra de negócio em questão.

Voltando ao cenário proposto, isso está me parecendo um relacionamento simples onde "Um Cliente pode prestar vários serviços" e "Um serviço pode ser prestado por vários clientes".

Se você quer uma estrutura flexível e, já pensando em um futuro próximo, permitir o registro de mais serviços, então criar tabelas específicas para armazenar cada serviço seria praticamente inviável e sem sentido. Acredito que com apenas 3 tabelas o seu problema seria solucionado, mas para complicar um pouco avalie se uma quarta tabela seria útil para você. Então, neste caso, avalie a solução abaixo:

1) criar uma tabela de clientes
2) criar uma tabela de serviços
3) criar uma tabela que armazenará os serviços que cada cliente poderá prestar

4) criar uma tabela com colunas adicionais que registrará todos os serviços de todos os clientes

Portanto, tome como exemplo os dados abaixo de acordo com o modelo acima proposto:

=======
CLIENTE
=======

ID_CLIENTE NOME_CLIENTE
---------- ------------
         1 Cliente A
         2 Cliente B
         3 Cliente C


=======
SERVICO
=======

ID_SERVICO NOME_SERVICO
---------- -------------------
         1 Hospedagem de sites 
         2 Registro de domínio
         3 Loja Virtual
         4 Outro, etc...

Abaixo irei definir quais serviços cada cliente poderá prestar:

===============
CLIENTE_SERVICO
===============

ID_CLIENTE ID_SERVICO
---------- ----------
         1          1
         1          2
         2          3
         3          2
         4          1

Para finalizar, a tabela REGISTRO abaixo armazenaria todos os serviços de todos os clientes sendo que a coluna ID_REGISTRO seria um seqüencial.

========
REGISTRO
========

ID_REGISTRO ID_CLIENTE ID_SERVICO ENDERECO_WEB ETC...
----------- ---------- ---------- ------------ ------
          1          1          1 http:// .... ... 
          2          1          2 http:// .... ...
          3          2          3 http:// .... ...
          4          3          2 http:// .... ...
          5          4          1 http:// .... ...

Como você viu acima, a tabela REGISTRO, a princípio, parece ser redundante no modelo pois faz o mesmo papel da tabela CLIENTE_SERVICO mas, neste caso, você poderia armazenar mais registros do mesmo serviço para o mesmo cliente. Por outro lado, bastaria também fazer um relacionamento não identificado na tabela CLIENTE_SERVICO onde as colunas ID_CLIENTE e ID_SERVICO seriam apenas chaves estrangeiras, e não chaves primárias.

Dependendo do caso, você poderá trabalhar somente com a tabela CLIENTE_SERVICO deixando a tabela REGISTRO fora do modelo, ou seja, a tabela REGISTRO seria opcional.

No mais, isso realmente vai depender das regras de negócios envolvidas na sua aplicação de forma a atingir os requisitos necessários.

Enfim, de acordo com o modelo e na minha visão, a tabela CLIENTE_SERVICO teria apenas o papel de armazenar de forma parametrizável o que cada cliente poderia fornecer, obedecendo assim, o requisito na qual você diz que "Um cliente poderá ter somente o serviço X ou Y ou ambos ..."

Um pouco do pacote DBMS_APPLICATION_INFO ...

Olá,

Acredito que este artigo esteja mais direcionado para desenvolvedores de aplicações que fazem uso do banco de dados Oracle.

Problema:

Disponibilizar informações no Oracle, em tempo real, do que está sendo realizado por cada usuário de um sistema. Se um usuário acessar o banco de dado através de uma aplicação, deverá estar disponível para visualização, alguma informação que identifique o quê cada sessão está executando dentro da aplicação.

Solução:

Sabemos que a view dinâmica de desempenho V$SESSION nos oferece algumas informações das sessões que estão atualmente conectadas no banco de dados Oracle. Portanto, para resolver o problema acima, poderemos fazer o uso da view V$SESSION de forma a popular algumas colunas como MODULE, ACTION e CLIENT_INFO como demonstrarei mais abaixo.

Irei utilizar o pacote chamado DBMS_APPLICATION_INFO diponível no Oracle, na qual farei uso das procedures SET_MODULE e SET_CLIENT_INFO como demonstrado abaixo:

SQL> desc DBMS_APPLICATION_INFO;

PROCEDURE READ_CLIENT_INFO
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                OUT
PROCEDURE READ_MODULE
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                OUT
 ACTION_NAME                    VARCHAR2                OUT
PROCEDURE SET_ACTION
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_CLIENT_INFO
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                IN
PROCEDURE SET_MODULE
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_SESSION_LONGOPS
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT

Imagine que estamos desenvolvendo um sistema de Controle de Estoque. Agora imagine um relatório fornecido pelo sistema que consolida as entradas e saídas de todos os materiais de um almoxarifado e que esse relatório possa ou não levar um tempo considerável para ser executado. Se quisermos visualizar no banco de dados através da view V$SESSION as sessões que estão executando este relatório, precisaríamos então incluir no fonte do sistema (antes do usuário submeter a execução do relatório), o comando abaixo:

Irei realizar uma simulação como se o sistema em uso fosse o próprio SQL*PLUS, utilizando o usuário SCOTT para realizar a conexão com o banco de dados:

SQL> exec dbms_application_info.set_module(
          module_name => 'Controle de Estoque',
          action_name => 'Executando Relatório de Materiais');

Procedimento PL/SQL concluído com sucesso.

Ao executar o procedimento acima, poderemos então obter tais informações selecionando as mesmas da view dinâmica de desempenho V$SESSION:

SQL> select osuser,machine,program,module,action from 
  2  v$session where username='SCOTT';

OSUSER     MACHINE     PROGRAM      MODULE              ACTION
---------- ----------- ------------ ------------------- ---------------------------------
legatti    MICRO_001   sqlplus.exe  Controle de Estoque Executando Relatório de Materiais

Como demonstrado acima, podemos perceber que as colunas MODULE e ACTION foram populadas. Em resumo, poderemos então utilizar o pacote DBMS_APPLICATION_INFO em nossas aplicações de forma a disponibilizar ao administrador do sistema ou até mesmo ao DBA, uma opção para visualizar em tempo real "quem" está fazendo "o quê" dentro do sistema em questão.

Caso seja necessário disponibilizar mais informações, poderemos também fazer uso da coluna CLIENT_INFO como demonstrado abaixo:

SQL> exec dbms_application_info.set_client_info('data, hora, meu IP, etc...');

Procedimento PL/SQL concluído com sucesso.

SQL> select client_info from v$session where sid=(select sid from v$mystat where rownum=1);

CLIENT_INFO
--------------------------------
data, hora, meu IP, etc...

Como eu citei acima a informação de endereço IP, poderemos também fazer uso da função SYS_CONTEXT de forma a acessar esta e outras informações da sessão em questão:

SQL> select SYS_CONTEXT('USERENV', 'TERMINAL') micro,
  2             SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip,
  3             SYS_CONTEXT('USERENV', 'OS_USER') usuario_rede from dual;

MICRO          IP             USUARIO_REDE
-------------- -------------- --------------
MICRO_001      192.168.1.10   legatti

No mais, restaria ao desenvolvedor encontrar a forma mais apropriada de implementar tal solução na linguagem de programação ou framework em uso (Java, PHP, Delphi, etc...).

Arquivos dump de exportação armazenam as senhas dos usuários SYS e SYSTEM?

Olá,

Sabemos que o conteúdo de todo o banco de dados Oracle pode ser exportado para um arquivo dump de exportação usando a opção FULL=Y, seja usando o utilitário de exportação tradicional (exp) disponível em todas as versões do banco de dados Oracle ou usando o utilitário Export Data Pump (expdp) disponível apenas à partir do Oracle 10g. Vale a pena salientar que somente os usuários com a role EXP_FULL_DATABASE podem fazer isso, mas geralmente isso é realizado usando o usuário padrão SYSTEM.

Este artigo terá apenas o propósito de responder uma dúvida comum para aqueles (iniciantes ou não), que realizam uma exportação completa do banco de dados usando o utilitário de exportação tradicional (exp) através da cláusula FULL=Y.

1) O arquivo dump de exportação armazena as senhas dos usuários de banco de dados de origem SYS e SYSTEM? Sim.

2) Quando for realizado a importação para um outro banco de dados, as senhas dos usuários SYS e SYSTEM do banco de dados de destino serão alteradas de acordo com as senhas dos usuário SYS e SYSTEM armazenadas no arquivo dump de exportação? Sim.

Abaixo, nada melhor do que provar esta afirmação ...

C:\>exp system/***** file=fullexport full=y

Export: Release 11.1.0.6.0 - Production on Sáb Mai 2 15:10:25 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Exportação executada no conjunto de caracteres de WE8PC850  e no conjunto de caracteres
de AL16UTF16 NCHAR o servidor usa WE8MSWIN1252 conjunto de caracteres (conversão de
conjunto de caracteres possível)

Sobre exportar o banco de dados inteiro ...
. exportando definições de tablespace
. exportando perfis
. exportando definições de usuário
. exportando funções
. exportando custos de recurso
. exportando definições de segmento de rollback
. exportando vínculos de banco de dados
. exportando números de seqüência
. exportando nomes alternativos de diretórios
. exportando namespaces de contexto
. exportando nomes da biblioteca de função externa
. exportando sinônimos do tipo PÚBLICO
. exportando sinônimos do tipo privado
. exportando definições de tipos de objeto^C [CTRL+C]

C:\>findstr /C:"ALTER USER \"SYS" fullexport.dmp
ALTER USER "SYS" IDENTIFIED BY VALUES 'CA077AA8495E3C7C' TEMPORARY TABLESPACE "TEMP"
ALTER USER "SYSTEM" IDENTIFIED BY VALUES '6CAB666F511E3E36' TEMPORARY TABLESPACE "TEMP"
ALTER USER "SYSMAN" DEFAULT ROLE ALL
ALTER USER "SYSTEM" DEFAULT ROLE ALL
ALTER USER "SYSMAN" QUOTA UNLIMITED ON "SYSAUX"

No mais, esta regra também vale para aqueles que usam o utilitário de exportação Export Data Pump (expdp).