Um pouco sobre o ORATOP: Utilitário para monitoramento de bancos de dados Oracle

Olá,

Assim como temos o comando top em sistemas Linux para exibir os processos em execução no sistema operacional bem como analisar a carga de trabalho do sistema, a Oracle criou o comando oratop que vem com o mesmo propósito de análise de carga de trabalho para bancos de dados Oracle. Com o utilitário oratop, podemos visualizar as sessões que estão conectadas na instância de um banco de dados Oracle e investigar, por exemplo, quais sessões estão impactando de forma negativa na performance geral do sistema. Assim como o comando top, o comando oratop também fornece as informações em tempo real. Enfim, com ele é possível identificar os principais eventos de espera (wait events) de forma cumulativa ou por sessão de banco de dados que estão ocorrendo no banco de dados. Vale a pena salientar que o oratop está disponível para download através do My Oracle Support (Metalink) através da note Doc ID 1500864.1.

Segue abaixo as versões de bancos de dados que atualmente suportam o oratop.

• Oracle 11g R2 (11.2.0.3, 11.2.0.4)
• Oracle 12cR1 (12.1.0.1, 12.1.0.2) 

Segue abaixo as plataformas de O/S que atualmente suportam o oratop.

• IBM AIX on POWER Systems (64-bit)
• HP-UX PA-RISC (64-bit)
• HP-UX Itanium
• Linux x86-64
• Linux x86
• Oracle Solaris on x86-64 (64-bit)
• Oracle Solaris on SPARC (64-bit)

Para que o oratop funcione é necessário que pelo menos um Oracle Client esteja instalado e que as variáveis de ambiente $ORACLE_HOME, $LD_LIBRARY_PATH e $PATH estejam setadas corretamente:
 

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:$PATH
export ORACLE_TERM=vt100
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT=DD/MM/YYYY
export EDITOR=vi

Particularmente, prefiro utilizá-lo no próprio servidor onde já existe uma instalação do Oracle Database e com instâncias que poderão ser monitoradas, mas nada impede de utilizá-lo para monitorar instâncias Oracle remotamente já que o mesmo aceita um serviço TNS.

$ ./oratop username/password@tns_alias

Segue abaixo uma figura que mostra em detalhes as sessões que podemos analisar com o oratop.

Por padrão o oratop é executado utilizando o formato standard com refresh de 5 segundos e com os wait events sendo computados de forma cumulativa. Particularmente gosto de ter uma visão um pouco mais detalhada e geralmente executo da seguinte forma.

export ORACLE_SID=BD01
./oratop -f -d -i 1 / as sysdba

Onde:

• f – opção de formato detalhado
• d – eventos de espera em tempo real (o default é cumulativo)
• i – atualização das informações (em segundos)

Vale a pena salientar que tais configurações podem ser alteradas de forma interativa. Segue abaixo as opções que podermos utilizar.

$ ./oratop -h
oratop: Release 14.1.2
Usage:
         oratop [ [Options] [Logon] ]

         Logon:
                {username[/password][@connect_identifier] | / }
                [AS {SYSDBA|SYSOPER}]

                connect_identifier:
                     o Net Service Name, (TNS) or
                     o Easy Connect (host[:port]/[service_name])
         Options:
             -d : real-time (RT) wait events, section 3 (default is Cumulative)
             -k : FILE#:BLOCK#, section 4 lt is (EVENT/LATCH)
             -m : MODULE/ACTION, section 4 (default is USERNAME/PROGRAM)
             -s : SQL mode, section 4 (default is process mode)
             -c : database service mode (default is connect string)
             -f : detailed format, 132 columns (default: standard, 80 columns)
             -b : batch mode (default is text-based user interface)
             -n : maximum number of iterations (requires number)
             -i : interval delay, requires value in seconds (default: 5s)
             -v : oratop release version number
             -h : this help

Em relação ao help interativo, segue abaixo várias opções que poderemos setar para visualizar e analisar as informações.

oratop: Release 14.1.2

Interactive Keys: [default]
        d : toggle between [Cumulative (C)] & Real-Time (RT) (section 3)
        k : toggle between [EVENT/LATCH] & object FILE#:BLOCK# (proc section 4)
        m : Toggle between [USERNAME/PROGRAM] & MODULE/ACTION (proc section 4)
        s : switch to SQL mode (section 4)
        f : toggle between [standard] & detailed format (long)
        p : switch to [process] mode (section 4)
        t : tablespace information
        x : basic SQL plan table (requires sql_id input)
        i : refresh interval, requires value in seconds [5s]
        q : quit/ exit program (also, { Q | Esc | function keys })

Abbreviations:
        [N/B]: count(N)/ Byte(B) - (k)illo, (M)ega, (G)iga, (T)erra, [PEZY]
        [T]  : Time - (u)micro, (m)illi, (s)econd, (h)our, (d)ay, (y)ear
        [m/s]: stats interval size, (m) 1 minute, (s) 15s, else, Real Time
        [c]  : database service centric

Acronym Help Menu:
        Section 1 - DATABASE        .. [1]
        Section 2 - INSTANCE        .. [2]
        Section 3 - DB WAIT EVENTS  .. [3]
        Section 4 - PROCESS         .. [4]
        Quit Help                   .. (q|Q)


Section 1 - database Global Database information
------------------------------------------------

   Version        : Oracle major version
   role           : database_role
   db name        : db_unique_name
   time        [s]: time as of the most recent stats (hh24:mi:ss)
   up          [T]: database uptime
   ins         [N]: total number of instance(s)
   sn        [c,N]: total user sessions (active/inactive)
   us        [c,N]: number of distinct users
   mt        [s,N]: global database memory total (sga+pga)
   fra         [N]: flashback recovery area %used, (red > 90%)
   er          [N]: diag active problem count (faults)
   % db      [s,N]: database time as %(dbtime/cpu) (red if > 99%)


Section 2 - instance Top 5 Instance(s) Activity Ordered by Database time desc
-----------------------------------------------------------------------------

   ID        [c,N]: inst_id (instance id)
   %CPU      [m,N]: host cpu busy %(busy/busy+idle). (red if > 90%)
   LOAD      [m,N]: current os load. (red if > 2*#cpu & high cpu)
   %DCU      [m,N]: db cpu otusef as %host cpu. (red if > 99% & high AAS)
   AAS       [s,N]: Average Active Sessions. (red if > #cpu)
   ASC       [c,N]: active Sessions on CPU
   ASI       [c,N]: active Sessions waiting on user I/O
   ASW       [c,N]: active Sessions Waiting, non-ASI (red if > ASC+ASI)
   ASP       [m,N]: active parallel sessions (F/G)
   AST       [c,N]: Active user Sessions Total (ASC+ASI+ASW)
   UST       [c,N]: user Sessions Total (ACT/INA)
   MBPS      [m,N]: i/o megabytes per second (throughput)
   IOPS      [m,N]: i/o requests per second
   IORL      [m,T]: avg synchronous single-block read latency. (red > 20ms)
   LOGR      [s,N]: logical reads per sec
   PHYR      [s,N]: physical reads per sec)
   PHYW      [s,N]: physical writes per sec
   %FR       [s,N]: shared pool free %
   PGA       [s,N]: total pga allocated
   TEMP      [s,N]: temp space used
   UTPS      [s,N]: user transactions per sec
   UCPS    [c,m,N]: user calls per sec
   SSRT    [c,m,T]: sql service response time (T/call)
   DCTR      [m,N]: database cpu time ratio
   DWTR      [m,N]: database wait time ratio. (red if > 50 & high ASW)
   %DBT      [s,N]: instance %Database Time (e.g. non-rac shows 100%)


Section 3 - db wait events Top 5 Timed Events Cluster-wide, non-idle Ordered by wait time
-----------------------------------------------------------------------------------------

  EVENT      : wait event name. (red if active)
        (C)  : Cumulative since instance startup
  WAITS      : total waits
  TIME(s)    : total wait time in seconds)
  AVG_MS     : average wait time in milliseconds
  PCT        : percent of wait time (all events)
  WAIT_CLASS : name of the wait class


Section 4 - process Non-Idle processes Ordered by event wait time desc
----------------------------------------------------------------------

   ID          [N]: inst_id. (red if blocking)
   SID         [N]: session identifier. (red if blocking)
   SPID        [N]: server process os id
   USERNAME       : Oracle user name
   PROGRAM        : process program name
   SRV            : SERVER (dedicated, shared, etc.)
   SERVICE        : db service_name
   PGA         [N]: pga_used_mem. (red if continuously growing)
   SQL_ID/BLOCKER : sql_id or the final blocker's (inst:sid, in red)
   OPN            : operation name, e.g. select
   E/T         [T]: session elapsed time (active/inactive)
   STA            : ACTive|INActive|KILled|CAChed|SNIped
   STE            : process state, e.g. on CPU or user I/O or WAIting
   WAIT_CLASS     : wait_class for the named event
   EVENT/*LATCH   : session wait event name. Auto toggle with *latch name.
                    (red if process is hung/spin)
   W/T         [T]: event wait time. (red if > 1s)

Utilizando a opção "t" no modo interativo, podemos obter algumas informações relacionadas às tablespaces. Dependendo do formato da tela (standard ou detailed) mais informações sobre as tablespaces irão ser mostradas. Chamo a atenção apenas para a informação SIZE que não é referente ao tamanho atual dos datafiles pertencentes à tablespace, mas sim a informação de MAX SIZE, ou seja, o tamanho máximo que a tablespace poderá atingir em função do AUTOEXTEND configurado em cada datafile.

TABLESPACE INFORMATION:

TABLESPACE_NAME               SIZE  USED  USE%  STATUS     BIG  NDBF  LOGGING
----------------------------  ----  ----  ----  ---------  ---  ----  ---------
SYSAUX                         32G  1.6G   5.0  ONLINE     NO      1  LOGGING
SYSTEM                         32G  800M   2.4  ONLINE     NO      1  LOGGING
TEMP                           10G  1.0M     0  ONLINE     NO      1  NOLOGGING
UNDOTBS1                      8.0G   71M   0.9  ONLINE     NO      1  LOGGING
USERS                            0  1.1M  111M  ONLINE     NO      1  LOGGING
TBS_DATA_01                    32G   19M     0  ONLINE     NO      1  LOGGING
TBS_DATA_02                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_03                    32G   22M     0  ONLINE     NO      1  LOGGING
TBS_DATA_04                    32G  2.2M     0  ONLINE     NO      1  LOGGING
TBS_DATA_05                    32G   64k     0  ONLINE     NO      1  LOGGING
TBS_DATA_06                    32G  375M   1.1  ONLINE     NO      1  LOGGING
TBS_DATA_07                    32G   96M   0.3  ONLINE     NO      1  LOGGING
TBS_DATA_08                    64G   44G  69.1  ONLINE     NO      2  LOGGING
TBS_DATA_09                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_10                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_11                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_12                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_13                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_14                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_DATA_15                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_01                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_02                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_03                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_04                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_05                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_06                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_07                    32G  2.4G   7.6  ONLINE     NO      1  LOGGING
TBS_INDX_08                    32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_INDX_09                    32G   47M   0.1  ONLINE     NO      1  LOGGING
TBS_INDX_10                    32G  2.6G   8.0  ONLINE     NO      1  LOGGING
TBS_INDX_11                    32G   64k     0  ONLINE     NO      1  LOGGING
TBS_INDX_12                    32G  437M   1.3  ONLINE     NO      1  LOGGING
TBS_INDX_13                    32G   57M   0.2  ONLINE     NO      1  LOGGING
TBS_LOB_01                     96G   70G  73.3  ONLINE     NO      3  LOGGING
TBS_LOB_02                     32G  5.6G  17.4  ONLINE     NO      1  LOGGING
TBS_LOB_03                     32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_LOB_04                     32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_LOB_05                     32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_LOB_06                     32G  1.0M     0  ONLINE     NO      1  LOGGING
TBS_LOB_07                     32G  1.2M     0  ONLINE     NO      1  LOGGING
TBS_LOB_08                     32G  3.8G  11.9  ONLINE     NO      1  LOGGING
TBS_LOB_09                     32G   64k     0  ONLINE     NO      1  LOGGING
TBS_LOB_10                     32G  2.9M     0  ONLINE     NO      1  LOGGING
----------------------------  ----  ----  ----
Total:                        1.4T  132G   9.5

press Enter to return

RMAN - Abordando a política de retenção (RECOVERY WINDOW) em backups incrementais

Olá,

Nos artigos de Outubro/2010 e Julho/2012 eu abordo um pouco sobre estratégias de backup utilizando o RMAN (Recovery Manager). Vale a pena recapitular que no Oracle, quando fazemos backups utilizando o RMAN, além da opção do backup FULL, temos também a opção de utilizarmos os backups incrementais. No RMAN, o termo "backup incremental" é utilizado para fazer referência a dois tipos: incremental diferencial e incremental cumulativo. O backup incremental inicial é conhecido como backup Nivel-0 (nível zero) e cada backup incremental realizado após o inicial é chamado de backup Nivel-1 (nível um). Os backups incrementais Nivel-1 podem ser cumulativos ou diferenciais.

Usar backups incrementais cumulativos significa que cada backup incremental se tornará progressivamente maior e mais demorado até que outro Nivel-0 seja executado, mas durante uma operação de recuperação, somente dois conjuntos de backups (Backup sets) serão necessários (O Nivel-0 e o último Nivel-1).

Os backups incrementais diferenciais somente registram as alterações referentes ao o último backup. Portanto, cada conjunto deles poderá ser menor ou maior do que o anterior, sem nenhuma sobreposição em seus blocos de dados. Entretanto, uma operação de recuperação poderá ser mais demorada pelo fato de terem mais conjuntos de backups para serem lidos em vez de apenas dois como no cumulativo (por exemplo: O Nivel-0, e vários Nivel-1). No mais, em relação aos backups incrementais, já li sobre DBAs levantando dúvidas no que se refere à política de retenção baseada no tempo (RECOVERY WINDOW) quando são utilizados backups incrementais como parte da política de backup. Muitas dessas dúvidas se referem sobre quando um conjunto de backups (backup sets) ficará obsoleto dentro de uma política de backup incremental.

Bom, para começar vale a pena salientar o que seria uma política de retenção no RMAN. Basicamente temos 3 opções:

Redundancy Backup Retention Policy

Com essa política o RMAN mantém X números de backups do banco de dados (número de cópias a serem retidas) para ficarem disponíveis para recuperação. Segue abaixo uma ilustração supondo que é realizada uma operação de backup 1 vez por dia. 

RMAN> configure retention policy to redundancy 2
 

Como demonstrado na figura acima, é possível perceber que o backup realizado na segunda-feira ficou obsoleto após o backup de quarta-feira ter sido realizado, e assim por diante. O que importa neste caso é o número de cópias que precisam ficar retidas. Como demonstrado na imagem, ao realizar o terceiro backup na quarta-feira, o primeiro backup que foi realizado na segunda-feira ficou obsoleto.

Recovery Window Backup Retention Policy

Essa política especifica que o RMAN deve reter todos os backups (baseado no tempo) durante um determinado X número de dias antes de torná-los obsoletos. A questão que devemos fazer ao optar por essa política é perguntar por quanto tempo queremos manter os backups para que seja possível uma recuperação em qualquer período em um tempo no passado (em dias) dentro da janela de retenção. Segue uma ilustração supondo que é realizada uma operação de backup 1 vez por dia.

RMAN> configure retention policy to recovery window of 1 day

Como demonstrado na figura acima, é possível perceber que o backup realizado na segunda-feira ficou obsoleto após o backup de quarta-feira ter sido realizado, e assim por diante. Isso significa que é possível recuperar o banco de dados em qualquer ponto no tempo dentro da janela de retenção de 1 dia.

NONE (os backups nunca ficam obsoletos)

Essa política especifica que o RMAN deve reter todos os backups sem nunca torná-los obsoletos.

RMAN> configure retention policy to none

Como demonstrado na figura acima, todos os backups realizados estarão disponíveis e não ficarão obsoletos.

Em relação ao backups incrementais, um backup de NIVEL-1 é inútil sem a existência de um backup de NIVEL-0. Portanto, se aos domingos são realizados backups de NIVEL-0 e nos demais dias (segunda-feira até sábado) são realizados backups de NIVEL-1, então mesmo que a minha política de retenção baseada em tempo (RECOVERY WINDOW) estiver configurado para 2 dias, na prática na primeira semana a retenção será de 9 dias, pois o backup de NIVEL-0 não poderá ficar obsoleto, já que o mesmo é necessário para realização de recover dos backups de NIVEL-1. Neste caso, um backup de NIVEL-0 só ficará obsoleto após a geração de um novo backup NIVEL-0 realizado na semana seguinte, conforme demonstrado na ilustração abaixo.

Na figura acima, o backup de NIVEL-0 realizado no dia 1 e todos os backups NIVEL-1 realizados até o dia 7 ficarão obsoletos somente a após a geração do backup NIVEL-1 no dia 10.

Segue abaixo uma evidência na qual são realizados backups NIVEL-0 (aos domingos) e NIVEL-1 (resto da semana) em um banco de dados que possui a política de retenção de 2 dias (RECOVERY WINDOW OF 2 DAYS). Conforme exemplificado na imagem acima, podemos verificar abaixo que nenhum backup se tornou obsoleto dentro dos 9 dias de realização dos backups.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name BD01 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---------------
9337652 B  0  A DISK        01/10/2015      1       1       YES        LEVEL-0_SEMANAL
9337696 B  A  A DISK        01/10/2015      1       1       YES        ARCHIVES_DIARIO
9340964 B  1  A DISK        02/10/2015      1       1       YES        LEVEL-1_DIARIO
9341007 B  A  A DISK        02/10/2015      1       1       YES        ARCHIVES_DIARIO
9343776 B  1  A DISK        03/10/2015      1       1       YES        LEVEL-1_DIARIO
9346935 B  A  A DISK        03/10/2015      1       1       YES        ARCHIVES_DIARIO
9346949 B  1  A DISK        04/10/2015      1       1       YES        LEVEL-1_DIARIO
9350199 B  A  A DISK        04/10/2015      1       1       YES        ARCHIVES_DIARIO
9350213 B  1  A DISK        05/10/2015      1       1       YES        LEVEL-1_DIARIO
9353555 B  A  A DISK        05/10/2015      1       1       YES        ARCHIVES_DIARIO
9353569 B  1  A DISK        06/10/2015      1       1       YES        LEVEL-1_DIARIO
9353612 B  A  A DISK        06/10/2015      1       1       YES        ARCHIVES_DIARIO
9356729 B  1  A DISK        07/10/2015      1       1       YES        LEVEL-1_DIARIO
9356772 B  A  A DISK        07/10/2015      1       1       YES        ARCHIVES_DIARIO
9360532 B  0  A DISK        08/10/2015      1       1       YES        LEVEL-0_SEMANAL
9360576 B  A  A DISK        08/10/2015      1       1       YES        ARCHIVES_DIARIO
9363815 B  1  A DISK        09/10/2015      1       1       YES        LEVEL-1_DIARIO
9363858 B  A  A DISK        09/10/2015      1       1       YES        ARCHIVES_DIARIO


RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
no obsolete backups found

Abordando o novo privilégio READ do Oracle 12c

Olá,

Para que um usuário no Oracle acesse objetos de um outro usuário, é necessário conceder privilégios de banco de dados para este usuário. Os privilégios mais comuns que utilizamos são os privilégios de objeto DML (SELECT, INSERT, DELETE, UPDATE). Quando queremos que um usuário tenha acesso apenas de leitura nas tabela de um outro usuário geralmente concedemos apenas o privilégio SELECT. Neste caso, temos a opção de conceder o privilégio de sistema SELECT ANY TABLE, ou conceder o privilégio de objeto SELECT para cada tabela do usuário. O problema com o privilégio SELECT, é que apesar de não haver qualquer risco de que dados sejam alterados com ele, o mesmo oferece a possibilidade de gerar bloqueios (locks) quando fazemos SELECT utilizando a cláusula FOR UPDATE. Por isso, o usuário que até então deveria ter acesso somente leitura, se mal intencionado pode causar sérios problemas de bloqueios no banco de dados. Para resolver este problema, o Oracle 12c (12.1.0.2.0) veio com um novo privilégio chamado READ que impede a geração de locks no banco de dados quando fazemos uso da cláusula FOR UPDATE em uma instrução SELECT. Veja abaixo uma demonstração.
 

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sex Out 2 10:09:52 2015

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

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user scott identified by tiger;

Usuário criado.

SQL> grant create session, select any table, select any dictionay to scott;

Concessão bem-sucedida.

SQL> connect scott/tiger
Conectado.

SQL> select * from ADAM.T1 FOR UPDATE;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 linhas selecionadas.

Após a criação do usuário SCOTT e a concessão do privilégio SELECT ANY TABLE para ele, efetuei o SELECT FOR UPDATE na tabela T1 de propriedade do usuário ADAM. Consultando a view dinâmica de desempenho V$LOCK, é possível notar um ROW EXCLUSIVE LOCK na tabela T1 conforme demonstrado abaixo. 

SQL> select
  2    o.object_name,
  3    o.object_type,
  4    s.sid,
  5    s.serial#,
  6    s.username,
  7    s.type,
  8    Decode( l.lmode,
  9            0, 'None',
 10            1, 'Null',
 11            2, 'Row Share',
 12            3, 'Row Exclu',
 13            4, 'Share',
 14            5, 'Sh Row Ex',
 15            6, 'Exclusive',
 16            'Null' ) "lock_mode"
 17  from dba_objects o,
 18  v$lock l,
 19  v$session s
 20  where l.id1 = + O.object_id
 21    and s.sid = l.sid
 22    and s.username is not null;

OBJECT_NAM OBJECT_TYPE           SID    SERIAL# USERNAME           TYPE       lock_mode
---------- -------------- ---------- ---------- ------------------ ---------- ---------
T1         TABLE                  34      18457 SCOTT              USER       Row Exclu
ORA$BASE   EDITION               254       2207 SYS                USER       Share
ORA$BASE   EDITION               253       6089 SYS                USER       Share
ORA$BASE   EDITION                34      18457 SCOTT              USER       Share

No caso acima, se o usuário ADAM ou qualquer outro usuário que tenha privilégios tentar realizar alguma instrução DML na tabela T1 ficará bloqueado, ou seja, o mesmo ficará aguardando que a sessão do usuário SCOTT seja finalizada (COMMIT ou ROLLBACK). Para resolver esse problema e fazer com que o usuário SCOTT se torne realmente um usuário READ ONLY, bastará revogarmos o privilégio SELECT ANY TABLE e conceder o privilégio READ ANY TABLE, conforme a seguir.

SQL> connect / as sysdba
Conectado.

SQL> revoke select any table from scott;

Revogação bem-sucedida.

SQL> grant read any table to scott;

Concessão bem-sucedida.

SQL> connect scott/tiger
Conectado.

Pronto. Agora o usuário SCOTT poderá somente ler as as tabelas sem impor qualquer tipo de bloqueio nas mesmas. Vale a pena salientar que também é possível conceder o privilégio READ no nível de objeto.
 

SQL> select * from ADAM.T1 FOR UPDATE;
select * from ADAM.T1 FOR UPDATE
                   *
ERRO na linha 1:
ORA-01031: privilégios insuficientes

SQL> select * from ADAM.T1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 linhas selecionadas.

Oracle Data Pump Export: Utilizando o arquivo de parâmetro (parfile)

Olá,

Tanto no utilitário Oracle Datapump Export (expdp) quanto no utilitário Oracle Datapump Import (impdp), é possível utilizar um arquivo de parâmetro "parfile" de forma a facilitar e até automatizar o processo de geração ou importação de um dump. Fazendo uso do arquivo de parâmetro, também é possível colocar o usuário e a senha que fará o processo de exportação ou importação. Segue abaixo o exemplo de um arquivo de parâmetro que utilizarei para realizar uma exportação de alguns schemas do banco de dados BD01.
 

[oracle]$ cat BD01.par
USERID=system/manager
SCHEMAS=SCOTT,ADAM,BLAKE
EXCLUDE=STATISTICS
DIRECTORY=DATA_PUMP_DIR
FLASHBACK_TIME=systimestamp
REUSE_DUMPFILES=y
COMPRESSION=ALL
QUERY=ADAM.T1:"WHERE rownum=0"
QUERY=BLAKE.T1:"WHERE rownum=0"
QUERY=SCOTT.T2:"WHERE rownum=0"
QUERY=ADAM.T2:"WHERE rownum=0"
QUERY=BLAKE.T2:"WHERE rownum=0"
REMAP_DATA=ADAM.T3.COL_BLOB:SYS.PKG_NULL_BLOB.SF_NULL_BLOB
REMAP_DATA=BLAKE.T3.COL_BLOB:SYS.PKG_NULL_BLOB.SF_NULL_BLOB

Acima, o arquivo de parâmetro BD01.par contém as seguintes configurações:

• Utilizará o usuário SYSTEM para fazer a exportação.
• Exportará objetos dos schemas SCOTT, ADAM e BLAKE.
• Não exportará estatísticas de objetos (tabelas, índices).
• Utilizará o objeto directory DATA_PUMP_DIR que será o destino do arquivo dump.
• Fará a exportação de forma consistente (point in time).
• Irá sobrescrever o aquivo dump no destino caso o mesmo já exista.
• Irá comprimir o arquivo dump de exportação.
• Irá exportar a tabela T1 dos schemas ADAM e BLAKE sem os registros.
• Irá exportar a tabela T2 dos schemas ADAM, SCOTT e BLAKE sem os registros.
• Irá realizar a exportação da tabela T3 dos schemas ADAM e BLAKE ignorando o conteúdo da coluna COL_BLOB.

Agora irei realizar a exportação fazendo uso do arquivo de parâmetro BD01.par conforme exemplo abaixo.

[oracle]$ expdp parfile=BD01.par

Export: Release 11.2.0.3.0 - Production on Tue Sep 1 10:00:04 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** parfile=BD01.par
Estimativa em andamento com o mtodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o mtodo de BLOCKS: 90.08 GB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLESPACE_QUOTA
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/SYNONYM/SYNONYM
Processando o tipo de objeto SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processando o tipo de objeto SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/FUNCTION
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processando o tipo de objeto SCHEMA_EXPORT/TYPE/TYPE_BODY
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
. . exportou "ADAM"."T1"               0 KB        0 linhas
. . exportou "ADAM"."T2"           5.132 KB        0 linhas
. . exportou "ADAM"."T3"          891.86 KB    10090 linhas
. . exportou "ADAM"."T4"         623.087 MB   934567 linhas
. . exportou "ADAM"."T5"            1.065 MB  264791 linhas
. . exportou "BLAKE"."T1"               0 KB       0 linhas
. . exportou "BLAKE"."T2"           2.132 KB       0 linhas
. . exportou "BLAKE"."T3"           91.86 KB   10090 linhas
. . exportou "BLAKE"."T4"         755.023 MB  654567 linhas
. . exportou "BLAKE"."T5"           2.065 MB  367791 linhas
. . exportou "SCOTT"."T1"           322.5 KB   44615 linhas
. . exportou "SCOTT"."T2"           1.235 KB       0 linhas
. . exportou "SCOTT"."T3"           91.86 KB   10090 linhas
. . exportou "SCOTT"."T4"         455.023 MB  834567 linhas
. . exportou "SCOTT"."T5"           4.065 MB  594798 linhas

Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 :
  /tmp/BD01.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concludo com sucesso em 10:02:40

Oracle Data Pump Export: Ignorando o conteúdo de colunas BLOB ao gerar o dump de exportação com a cláusula REMAP_DATA

Olá,

No artigo de Junho/2015 eu abordei uma técnica de como realizar um dump de exportação pelo Datapump Export (expdp) ignorando linhas de uma tabela através da cláusula QUERY sem a necessidade de ignorar a tabela através da cláusula EXCLUDE. Recapitulando, no utilitário expdp, podemos utilizar a cláusula EXCLUDE para ignorar alguns objetos que não queremos que sejam exportados. A desvantagem em utilizar a opção EXCLUDE para ignorar tabelas durante a exportação é que, ao importar novamente o dump, as tabelas que foram excluídas poderão fazer falta no novo ambiente. Agora imagine uma tabela que possui uma coluna do tipo BLOB que contém poucos registros, mas pelo fato de armazenar documentos grandes na coluna BLOB, acaba por ter um tamanho de 100 GB. A questão agora é: como fazer para exportar os registros desta tabela ignorando o conteúdo da coluna BLOB? Se o conteúdo do LOB não for necessário no dump de exportação, maximizaremos o tempo da exportação, além de ter um dump muito menor. Utilizando a técnica da cláusula QUERY poderemos exportar a tabela sem nenhum registro, mas não é isso que queremos.

No mais, neste artigo irei abordar como ignorar o conteúdo de uma coluna BLOB durante uma exportação através do utilitário expdp, fazendo uso da cláusula REMAP_DATA que tem como objetivo a especificação de uma função de conversão de dados através de uma package de banco de dados. Segue a demonstração abaixo.
 

[oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Qua Ago 5 10:13:43 2015

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

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

SQL> desc SCOTT.T1
 Nome                Nulo?    Tipo
 ------------------- -------- ------------------
 COD                          NUMBER
 IMAGEM                       BLOB

SQL> select owner,segment_name,segment_type,bytes/1024/1024 size_mb
  2    from dba_segments
  3   where owner='SCOTT';

OWNER            SEGMENT_NAME                 SEGMENT_TYPE        SIZE_MB
---------------- ---------------------------- ---------------- ----------
SCOTT            T1                           TABLE                 ,0625
SCOTT            SYS_IL0000182291C00002$$     LOBINDEX                  3
SCOTT            SYS_LOB0000182291C00002$$    LOBSEGMENT             1669

3 linhas selecionadas.   

No exemplo acima, temos a tabela T1 de propriedade do usuário SCOTT que possui 1669 MB em segmentos de LOB referente à coluna IMAGEM. A tabela possui apenas 256 linhas na qual queremos exportar. No comando abaixo irei exportar o schema SCOTT de forma usual.
 

[oracle ~]$ expdp system/#5ydl3db# dumpfile=SCOTT.dmp schemas=SCOTT

Export: Release 11.2.0.3.0 - Production on Wed Aug 5 10:19:31 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=SCOTT.dmp
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 1.629 GB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
. . exportou "SCOTT"."T1"                                1.590 GB     256 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/SCOTT.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:20:31

Na saída acima, podemos verificar que 1.590 GB referente à tabela T1 foram exportados. Agora irei fazer a mesma exportação só que agora fazendo uso da cláusula REMAP_DATA. Para fazer uso da mesma, o pré-requisito é que uma função incorporada dentro de uma package seja utilizada. Segue abaixo package que irei criar que terá como objetivo retornar um valor empty_blob(), ou seja, um tipo de valor NULL para coluna do tipo BLOB.

SQL> create or replace package pkg_null_blob
  2  as
  3     function sf_null_blob (col_blob in blob)
  4        return blob;
  5  end;
  6  /

Pacote criado.

SQL> create or replace package body pkg_null_blob
  2  as
  3     function sf_null_blob (col_blob in blob)
  4        return blob
  5     is
  6        blob_null   blob := empty_blob();
  7     begin
  8        return blob_null;
  9     end;
 10  end;
 11  /

Corpo de Pacote criado.

SQL> grant execute on pkg_null_blob to public;

Concessão bem-sucedida.

Após a criação da package e concedidas as permissões necessárias, irei realizar novamente a exportação, só que agora fazendo uso da cláusula REMAP_DATA conforme demonstração abaixo.
 

[oracle ~]$ expdp system/#5ydl3db# 
            dumpfile=SCOTT.dmp 
            schemas=SCOTT 
            remap_data=SCOTT.T1.IMAGEM:sys.pkg_null_blob.sf_null_blob

Export: Release 11.2.0.3.0 - Production on Wed Aug 5 10:25:55 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=SCOTT.dmp
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 1.629 GB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
. . exportou "SCOTT"."T1"                                6.906 KB     256 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/SCOTT.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:26:34

Pronto. Podemos perceber pela saída acima que somente 6.906 KB referente à tabela T1 foram exportados, ou seja, o conteúdo da coluna IMAGEM do tipo de dado BLOB, foi mapeado para NULL para cada linha exportada da tabela T1.

Gerando hash MD5 do conteúdo de colunas do tipo LOB no Oracle com a package DBMS_CRYPTO

Olá,

Nos artigos de Novembro/2007 e Dezembro/2009 e eu demonstrei de forma prática como gerar arquivos externos ao banco de dados através das packages UTL_FILE e DBMS_LOB, a utilização do objeto DIRECTORY, bem como a apresentação de uma função para geração de hash MD5 através da package DBMS_OBFUSCATION_TOOLKIT para geração de senhas e pequenos textos. Neste artigo, irei abordar novamente a utilização dos objetos DIRECTORY e da package DBMS_LOB quanto a geração de hash MD5. A diferença é que agora irei demonstrar como gerar um hash MD5 para dados do tipo LOB (BLOB, CLOB) através da package DBMS_CRYPTO disponível à partir do Oracle 10g.

O objetivo do artigo será demonstrar uma função de banco de dados que chamarei de md5_blob que receberá como parâmetro a coluna de uma tabela do tipo de dado BLOB. Em alguns casos ela poderá ser muito útil, como foi pra mim, quando precisei verificar em uma tabela a quantidade de documentos binários duplicados (*.doc, *.xls, *.pdf, *.jpg, etc.) que estavam armazenados em uma coluna do tipo BLOB.

Segue abaixo os comandos executados para criar um ambiente de forma a simular uma tabela contendo documentos binários. No caso em questão, irei armazenar um arquivo chamado imagem.jpg.
 

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:04:59 2015

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

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

SQL> create or replace directory arquivo_dir as '/tmp';

Diretório criado.

-- Criando a tabela de teste
SQL> create table tab_imagem (
  2    id number primary key,
  3    nome varchar2(20),
  4    imagem blob
  5  );

Tabela criada.

SQL> grant write,read on directory arquivo_dir to scott;

Concessão bem-sucedida.

SQL> grant execute on dbms_crypto to scott;

Concessão bem-sucedida.

-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rwxr--r-- 1 oracle oinstall 820 Jul 2 10:09 imagem.jpg

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:14:32 2015

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

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

-- Criando a stored procedure carrega_imagem
SQL> create or replace procedure carrega_imagem (p_nome_arquivo in tab_imagem.nome%type) as
  2   v_bfile bfile;
  3   v_blob blob;
  4  begin
  5    insert into tab_imagem (id,nome,imagem)
  6    values (1,p_nome_arquivo,empty_blob())
  7    return imagem into v_blob;
  8    v_bfile := bfilename('ARQUIVO_DIR',p_nome_arquivo);
  9    dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile));
 11    dbms_lob.fileclose(v_bfile);
 12    commit;
 13  end;
 14  /

Procedimento criado.

-- Carregando o arquivo para a tabela tab_imagem
SQL> execute carrega_imagem('imagem.jpg');

Procedimento PL/SQL concluído com sucesso.

Pronto. O arquivo imagem.jpg foi carregado para a tabela conforme demonstrado abaixo.
 

SQL> select * from tab_imagem;

        ID NOME             IMAGEM
---------- ---------------- ------------------------------------------------------------
         1 imagem.jpg       47494638396109000600F70000000000B1B1B1AFAFAFA3A3A39999997777
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            27CCCCCC191919171717111111C0C0C00D0D0D090909070707AAAAAAA4A4
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            58FFFFFF4848484444444040403838383232322E2E2EDDDDDD2828282626
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            B30000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            002C000000000900060000080E0049081C48B0A0C1830813260C08003B00

Segue abaixo a função de banco de dados que será utilizada para gerar o hash MD5 da coluna do tipo BLOB.
 

SQL> create or replace function md5_blob (p_blob in blob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => p_blob,
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.

Após a criação da função, poderemos ver abaixo através da instrução SELECT que o hash MD5 do conteúdo da coluna IMAGEM da tabela TAB_IMAGEM foi gerado com sucesso.

SQL> select id,nome,md5_blob(imagem) from tab_imagem;

        ID NOME            MD5_BLOB(IMAGEM)
---------- --------------- --------------------------------
         1 imagem.jpg      4B812BDC6240D0770A12DA89E4630BF2

1 linha selecionada.

Apenas para comprovar que o hash MD5 que foi gerado é válido, utilizarei o comando md5sum no Linux para verificar que o valor do hash é o mesmo que foi gerado pelo Oracle.
 

$ md5sum imagem.jpg
4b812bdc6240d0770a12da89e4630bf2  imagem.jpg

Em relação à colunas do tipo CLOB, a função é praticamente idêntica, o que muda apenas é o tipo do parâmetro de entrada que deve ser definido para CLOB.

SQL> create or replace function md5_clob (p_clob in clob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => p_clob,
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.

Oracle Data Pump Export: Ignorando múltiplas tabelas ao gerar o dump de exportação sem fazer uso da cláusula EXCLUDE

Olá,

Em alguns momentos, nós DBAs, somos solicitados a realizar um dump de exportação de um schema de banco de dados. Na maioria das vezes esse dump será importado para um outro ambiente, como por exemplo, no ambiente de DEV ou HOM. O que acontece às vezes é que somos solicitados a fazer o dump de forma que algumas tabelas não precisem estar no dump de exportação. Muitas vezes são tabelas grandes (de muitos gigabytes) que são usadas para armazenar logs, históricos, imagens através de colunas LOB. Enfim, dependendo da situação, são tabelas que não serão necessárias em um novo ambiente. No utilitário expdp (Datapump Export), podemos utilizar a cláusula EXCLUDE para ignorar alguns objetos que não queremos que sejam exportados. A desvantagem em utilizar a opção EXCLUDE para ignorar tabelas durante a exportação é que, ao importar novamente o dump, em alguns casos, seremos acionados a criar no novo ambiente as tabelas que não foram exportadas, inclusive as constraints Foreign Keys caso existam nas tabelas que foram ignoradas durante a exportação.

No mais, o objetivo desse artigo será demonstrar o uso da cláusula EXCLUDE para ignorar tabelas durante uma exportação através do Data Pump Export (expdp), bem como demonstrar uma outra forma, através da cláusula QUERY de ignorar tabelas durante uma exportação sem a necessidade de excluí-las do dump. Segue a demonstração abaixo.
 

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Seg Jun 1 10:13:21 2015

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

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

SQL> create table t1 (cod number constraint pk_t1 primary key);

Tabela criada.

SQL> create table t2 (cod number constraint fk_t2_t1 references t1);

Tabela criada.

SQL> create table t3 (cod number);

Tabela criada.

SQL> insert into t1 values (1);

1 linha criada.

SQL> insert into t2 values (1);

1 linha criada.

SQL> insert into t3 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

 
Acima, eu criei 3 tabelas no schema SCOTT para poder realizar a demonstração. Poderia ter criado dezenas de tabelas, mas não há necessidade. Segue abaixo o exemplo de como poderíamos exportar todas aos objetos do schema SCOTT menos as tabelas T1 e T3 utilizando a cláusula EXCLUDE.
 

$ expdp system/manager schemas=SCOTT
  dumpfile=scott.dmp 
  EXCLUDE=TABLE:\"IN \(\'T1\',\'T3\'\)\"
  nologfile=y

Export: Release 11.2.0.3.0 - Production on Seg Jun 1 10:23:21 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=SCOTT
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 64 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exportou "SCOTT"."T2"                                    5 KB       1 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/scott.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:23:26

Pronto. Podemos ver acima que apenas a tabela T2 foi exportada, ou seja, ao importar o dump gerado com o utilitário impdp (Datapump Import), apenas a tabela T2 será criada no destino. Como a tabela T2 tem uma foreign key para a tabela T1, a mesma será importada sem essa restrição de integridade já que ao final da importação o erro "ORA-00942: a tabela ou view não existe" será emitido pelo fato a tabela T1 não existir. Segue abaixo um outra forma de fazer a mesma coisa, só que com a vantagem de que apenas as linhas das tabelas ignoradas não serão exportadas, ou seja, a estrutura da tabela será exportada para o dump, mas as linhas da tabela não. Para isso, será usado a cláusula QUERY onde será passado para cada tabela um predicado que não retorne nenhuma linha, como por exemplo "WHERE rownum=0". Segue exemplo abaixo.
 

$ expdp system/manager schemas=SCOTT
  dumpfile=scott.dmp
  QUERY=SCOTT.T1:\"WHERE rownum=0\"
  QUERY=SCOTT.T3:\"WHERE rownum=0\"
  nologfile=y

Export: Release 11.2.0.3.0 - Production on Seg Jun 1 10:25:56 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=SCOTT
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 128 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exportou "SCOTT"."T1"                                    0 KB       0 linhas
. . exportou "SCOTT"."T2"                                    5 KB       1 linhas
. . exportou "SCOTT"."T3"                                    0 KB       0 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/scott.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:26:05

Pronto. Podemos acima ver que todas as 3 tabelas foram exportadas. A diferença é que as tabelas T1 e T3 estão vazias, ou seja, nenhuma linha das tabelas T1 e T3 foram exportadas. Ao importar o dump gerado, todas as 3 tabelas serão importadas mas, da mesma forma que no primeiro exemplo, como a tabela T2 tem uma Foreign Key para a tabela T1, a mesma será importada sem essa restrição de integridade. Vale a pena salientar que durante a importação, não será mais emitido o erro "ORA-00942: a tabela ou view não existe", mas o erro "ORA-02298: não é possível validar (SCOTT.FK_T2_T1) - chaves mães não localizadas".