Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


segunda-feira, 2 de março de 2015

Abordando uma "New Feature" do particionamento por referência (Reference Partitioning) do Oracle 12c

Por Eduardo Legatti

Olá,

No artigo de Fevereiro/2011 fiz uma breve introdução sobre o conceito de tabelas particionadas de forma a demonstrar as operações de manutenção envolvidas nas mesmas e no artigo de Abril/2011 demonstrei como particionar uma tabela de forma on-line. Já nos artigos de Junho/2011 e Maio/2012 eu demonstrei como utilizar o Interval Partitioning e o Reference Partitioning respectivamente, ambos introduzidos no Oracle 11g.

Neste artigo, irei abordar uma inovação feita no Oracle 12c que permite utilizar o método de particionamento Reference Partitioning em uma tabela filha, tendo como pai, uma tabela particionada pelo método Interval Partitioning. No Oracle 11g isso não é possível.

Apenas para recapitular, o interessante sobre o particionamento por referência (Reference Partitioning), é que o mesmo permite que uma restrição de integridade (Foreign Key) definida na tabela filha, seja utilizada como chave de partição. Isso permite que a tabela filha herde a chave de particionamento da tabela pai sem a necessidade de duplicar a coluna chave de partição da tabela pai na tabela filha. Em outras palavras, a tabela filha é particionada da mesma maneira que a tabela pai. Consultas SQL executadas nas tabelas pai e filha através de "Joins" terão seu desempenho melhorado, pois dependendo do predicado utilizado na consulta, o otimizador do Oracle irá determinar a partição envolvida na tabela pai (partition pruning) e identificar partição da tabela filha referente. No caso do particionamento por intervalo (Interval Partitioning), o mesmo automatiza a criação de novas partições de uma tabela por uma determinada faixa ou intervalo (RANGE), de forma que o DBA não precise se preocupar em criar novas partições manualmente.

Abaixo, irei criar a tabela PEDIDO com 3 partições iniciais, tendo como coluna chave de partição, o campo DATA_PEDIDO. O tipo de particionamento utilizado será por faixa de valores (RANGE) utilizando o Interval Partitioning de forma que cada partição armazene os registros de um mês específico. Depois irei criar a tabela ITEM_PEDIDO utilizando o tipo de particionamento por referência (Reference Partitioning).

C:\>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:37:34 2015

Copyright (c) 1982, 2014, 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 pedido (
  2     cod_pedido  number primary key,
  3     data_pedido date not null,
  4     cod_cliente number not null)
  5  partition by range (data_pedido)
  6  interval(numtoyminterval(1,'month'))
  7  store in (users)
  8  (
  9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
 10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
 11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
 12  );

Tabela criada.

SQL> create table item_pedido (
  2     cod_pedido  number not null,
  3     cod_produto number not null,
  4     quantidade  number,
  5     constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
  6  partition by reference (fk_itempedido_pedido);
create table item_pedido (
*
ERRO na linha 1:
ORA-14659: O método de particionamento da tabela mãe não é suportado

Com o erro demonstrado acima, é possível perceber que com o Oracle 11g não é possível criar uma tabela particionada por referência na qual a tabela pai (referenciada), esteja particionada por intervalo (Interval Partitioning). Iremos verificar abaixo que à partir do Oracle 12c isto já será possível.

C:\>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:48:20 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 table pedido (
  2     cod_pedido  number primary key,
  3     data_pedido date not null,
  4     cod_cliente number not null)
  5  partition by range (data_pedido)
  6  interval(numtoyminterval(1,'month'))
  7  store in (users)
  8  (
  9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
 10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
 11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
 12  );

Tabela criada.

SQL> create table item_pedido (
  2     cod_pedido  number not null,
  3     cod_produto number not null,
  4     quantidade  number,
  5     constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
  6  partition by reference (fk_itempedido_pedido);

Tabela criada.  

Pronto. Como demonstrado acima, no Oracle 12c é possível criar uma tabela particionada por referência tendo como tabela referenciada, uma tabela particionada por RANGE através do Interval Partitioning.
SQL>  select table_name,
  2          partitioning_type,
  3          partition_count,
  4          interval
  5     from user_part_tables;

TABLE_NAME           PARTITION PARTITION_COUNT INTERVAL
-------------------- --------- --------------- --------------------------
PEDIDO               RANGE             1048575 NUMTOYMINTERVAL(1,'MONTH')
ITEM_PEDIDO          REFERENCE         1048575 YES 

Agora irei inserir dados nas duas tabelas de forma a demonstrar como o Oracle será capaz de criar automaticamente uma partição na tabela PEDIDO para acomodar registros com datas de um determinado mês, bem como demonstrar que a partição criada automaticamente será herdada pela tabela ITEM_PEDIDO.
 
SQL> insert into pedido
  2  select level,
  3         to_date('31/12/2014')+level,
  4         trunc(dbms_random.value(1,1000))
  5    from dual
  6  connect by level <= 120;

120 linhas criadas.

SQL> insert into item_pedido
  2  select level,
  3         trunc(dbms_random.value(1,1000)),
  4         trunc(dbms_random.value(1,100))
  5    from dual
  6  connect by level <= 120;

120 linhas criadas.

SQL> exec dbms_stats.gather_schema_stats('SCOTT');

Procedimento PL/SQL concluído com sucesso.

SQL> select table_name,
  2         partition_name,
  3         high_value,
  4         num_rows
  5    from user_tab_partitions;

TABLE_NAME           PARTITION_NAME  HIGH_VALUE                       NUM_ROWS
-------------------- --------------- ------------------------------ ----------
PEDIDO               P_2015_01       TO_DATE(' 2015-02-01 00:00:00'         31
PEDIDO               P_2015_02       TO_DATE(' 2015-03-01 00:00:00'         28
PEDIDO               P_2015_03       TO_DATE(' 2015-04-01 00:00:00'         31
PEDIDO               SYS_P501        TO_DATE(' 2015-05-01 00:00:00'         30
ITEM_PEDIDO          P_2015_01                                              31
ITEM_PEDIDO          P_2015_02                                              28
ITEM_PEDIDO          P_2015_03                                              31
ITEM_PEDIDO          SYS_P501                                               30

8 linhas selecionadas.
  
Podemos ver pelo resultado acima que a partição SYS_P501 foi criada automaticamente na tabela PEDIDO para acomodar os registros referentes ao mês de Abril, e que essa mesma partição também foi herdada pela tabela ITEM_PEDIDO.

segunda-feira, 2 de fevereiro de 2015

Abordando a recuperação de um datafile sem backup através do RMAN e do SQL*Plus

Por Eduardo Legatti

Olá,

Imagine um cenário na qual em uma manhã de segunda-feira às 08:00, foi criada uma nova tablespace em um banco de dados qualquer, operando no modo ARCHIVELOG. Tabelas foram criadas nessa tablespace, os dados foram inseridos e o sistema começou a funcionar. Imagine que neste ambiente existe uma rotina que executa um backup físico FULL via RMAN todas as noites às 22:00. Agora imagine que às 19:00 neste mesmo dia, o datafile pertencente à essa tablespace foi apagado acidentalmente ou, até mesmo, corrompido. Como não foi realizado nenhum backup desse datafile, já que faltavam ainda 3 horas para o início da rotina de backup, alguém poderia imaginar que todo trabalho realizado no dia estaria perdido.

A boa notícia é que há uma luz no fim do túnel! O objetivo principal deste artigo será demonstrar como podemos recuperar um datafile que não possui backup. Embora se possa utilizar uma técnica em ambientes Linux/Unix para recuperar um arquivo deletado fazendo uso de "file descriptors", irei abordar neste artigo apenas técnicas que envolvam o uso do RMAN e do SQL*Plus para recuperar um arquivo de dados que não possui backup. Vale a pena salientar que para esta técnica funcionar, o banco de dados precisa estar operando no modo ARCHIVELOG e que os archive logs precisam existir desde a criação do datafile.

Segue abaixo o ambiente de simulação na qual existe uma tabela T1 com cerca de 8 milhões de registros e de propriedade do schema SCOTT. A tabela T1 está devidamente criada na tablespace TBS_DATA_01, conforme demonstrado abaixo.
 
SQL> select owner,table_name,tablespace_name,num_rows
  2    from dba_tables
  3   where owner='SCOTT';

OWNER                TABLE_NAME           TABLESPACE_NAME           NUM_ROWS
-------------------- -------------------- ----------------------- ----------
SCOTT                T1                   TBS_DATA_01                8000000

1 linha selecionada. 
  
Em relação a tablespace TBS_DATA_01, foi criado apenas um arquivo de dados conforme demonstrado a seguir.

SQL> select file#,name,status from v$datafile where file#=49;

FILE# NAME                                              STATUS
----- ------------------------------------------------- -------
   49 /data/oradata/BD01/tbs_data_01.dbf                ONLINE

1 linha selecionada.
 
Recuperando um datafile sem backup usando o RMAN


Utilizando o RMAN, irei me certificar da existência, ou não, de algum backup relacionado ao datafile pertencente à tablespace TBS_DATA_01.
 
[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 2 10:19:05 2015

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

connected to target database: BD01 (DBID=618743438)

RMAN> list backup of tablespace tbs_data_01;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

Confirmado que não há qualquer backup do datafile 49, irei simular a perda do mesmo apagando o arquivo.

[oracle@linux1 ~]$ rm /data/oradata/BD01/tbs_data_01.dbf

Após apagar o arquivo, podemos verificar abaixo que o Oracle já começa a refletir a ausência do mesmo.

SQL> select count(*) from scott.t1;
select count(*) from scott.t1
*
ERRO na linha 1:
ORA-01116: erro ao abrir o arquivo 49 do banco de dados
ORA-01110: 49 do arquivo de dados: '/data/oradata/BD01/tbs_data_01.dbf'
ORA-27041: não é possível abrir arquivo
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Para comprovar que o datafile realmente foi apagado, irei colocar o mesmo no estado OFFLINE e imediatamente ONLINE.

SQL> alter database datafile 49 offline;

Banco de dados alterado.

SQL> alter database datafile 49 online;
alter database datafile 49 online
*
ERRO na linha 1:
ORA-01157: não é possível identificar/bloquear arquivo de dados 49
ORA-01110: 49 do arquivo de dados: '/data/oradata/BD01/tbs_data_01.dbf'

SQL> select name,status from v$datafile where file#=49;

NAME                                                STATUS
--------------------------------------------------- -------
/data/oradata/BD01/tbs_data_01.dbf                  RECOVER

1 linha selecionada.

Consultando novamente a view dinâmica de desempenho V$DATAFILE, podemos verificar que a coluna STATUS apresenta a palavra RECOVER, ou seja, que o arquivo de dados para ficar ONLINE novamente precisa de um restore/recover. Para demonstrar como poderemos recuperar o datafile, irei utilizar o RMAN (Data Recovery Advisor) conforme demonstrado a seguir.
 
[oracle@linux1 ~]$ rman target / 
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 2 10:27:18 2015

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

connected to target database: BD01 (DBID=618743438)

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- ------------------------------------------------------------------
44802      HIGH     OPEN      02/02/2015    Um ou mais arquivos de dados que não são do sistema estão ausentes

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- ------------------------------------------------------------------
44802      HIGH     OPEN      02/02/2015    Um ou mais arquivos de dados que não são do sistema estão ausentes

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 device type=DISK
analyzing automatic repair options complete

Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- ------------------------------------------------------------------
44802      HIGH     OPEN      02/02/2015    Um ou mais arquivos de dados que não são do sistema estão ausentes

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. Se o arquivo /data/oradata/BD01/tbs_data_01.dbf foi renomeado ou movido intencionalmente, restaure-o

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restaurar e recuperar o arquivo de dados 49
  Strategy: O reparo inclui recuperacão completa de midia sem perda de dados
  Repair script: /u01/app/oracle/diag/rdbms/bd01/BD01/hm/reco_4238280548.hm


Após a execução do comando "advise failure", foi criado o arquivo "reco_4238280548.hm" contendo instruções SQL que precisarão ser executadas conforme demonstrado abaixo: 

[oracle@linux1 ~]$ cat /u01/app/oracle/diag/rdbms/bd01/BD01/hm/reco_4238280548.hm
   # restore and recover datafile
   sql 'alter database datafile 49 offline';
   restore datafile 49;
   recover datafile 49;
   sql 'alter database datafile 49 online';
 
Como orientado pelo RMAN - Data Recovery Advisor, irei executar os comandos a seguir.

RMAN> sql 'alter database datafile 49 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 49 offline

RMAN> restore datafile 49;

Starting restore at 02/02/2015
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

creating datafile file number=49 name=/data/oradata/BD01/tbs_data_01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 02/02/2015

RMAN> recover datafile 49; 
Starting recover at 02/02/2015
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7261 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7261_bcb23hpc_.arc
archived log for thread 1 with sequence 7262 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7262_bcb2402b_.arc
archived log for thread 1 with sequence 7263 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7263_bcb2qs11_.arc
archived log for thread 1 with sequence 7264 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7264_bcb2qty2_.arc
archived log for thread 1 with sequence 7265 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7265_bcb2r1kl_.arc
archived log for thread 1 with sequence 7266 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7266_bcb2r3p6_.arc
archived log for thread 1 with sequence 7267 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7267_bcb2r5ro_.arc
archived log for thread 1 with sequence 7268 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7268_bcb2r7yg_.arc
archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7261_bcb23hpc_.arc thread=1 sequence=7261
archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7262_bcb2402b_.arc thread=1 sequence=7262
archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7263_bcb2qs11_.arc thread=1 sequence=7263
archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7264_bcb2qty2_.arc thread=1 sequence=7264
archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7265_bcb2r1kl_.arc thread=1 sequence=7265
archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7266_bcb2r3p6_.arc thread=1 sequence=7266
media recovery complete, elapsed time: 00:00:05
Finished recover at 02/02/2015

RMAN> sql 'alter database datafile 49 online';

sql statement: alter database datafile 49 online

Pronto. Após a execução das instruções SQL acima foi possível notar que o RMAN criou um novo datafile e aplicou os archive logs da sequência 7261 até 7266. Para comprovar que a recuperação do datafile foi bem sucedida, segue abaixo o resultado de um SELECT COUNT(*) na tabela T1 de propriedade do schema SCOTT.

SQL> select count(*) from scott.t1;

  COUNT(*)
----------
   8000000

1 linha selecionada.


Recuperando um datafile sem backup usando o SQL*Plus


Agora irei utilizar o SQL*PLus para demonstrar como recuperar o datafile que foi apagado. Para recuperar o arquivo de dados, será necessário basicamente criar um novo datafile com o comando ALTER DATABASE CREATE DATAFILE e aplicar os archive logs necessários utilizando a opção AUTO, conforme demonstração a seguir:
 
[oracle@linux1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 2 10:44:21 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> alter database datafile 49 offline drop;

Banco de dados alterado.

SQL> alter database create datafile '/data/oradata/BD01/tbs_data_01.dbf';

Banco de dados alterado.

SQL> recover datafile 49;
ORA-00279: alterar 7840579434155 gerado em 01/13/2015 09:52:10 necessario para o thread 1
ORA-00289: sugestão :
/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7261_bcb23hpc_.arc
ORA-00280: alterar 7840579434155 para o thread 1 esta na sequencia  #7261

Especificar log: {=nome de arquivo | sugerido | AUTO | CANCEL}
AUTO
ORA-00279: alterar 7840579434734 gerado em 01/13/2015 09:58:39 necessario para o thread 1
ORA-00289: sugestão :
/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7262_bcb2402b_.arc
ORA-00280: alterar 7840579434734 para o thread 1 esta na sequencia  #7262

ORA-00279: alterar 7840579434929 gerado em 01/13/2015 09:58:55 necessario para o thread 1
ORA-00289: sugestão :
/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7263_bcb2qs11_.arc
ORA-00280: alterar 7840579434929 para o thread 1 esta na sequencia  #7263

ORA-00279: alterar 7840579435636 gerado em 01/13/2015 10:09:29 necessario para o thread 1
ORA-00289: sugestão :
/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7264_bcb2qty2_.arc
ORA-00280: alterar 7840579435636 para o thread 1 esta na sequencia  #7264

ORA-00279: alterar 7840579435639 gerado em 01/13/2015 10:09:30 necessario para o thread 1
ORA-00289: sugestão :
/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7265_bcb2r1kl_.arc
ORA-00280: alterar 7840579435639 para o thread 1 esta na sequencia  #7265

ORA-00279: alterar 7840579435644 gerado em 01/13/2015 10:09:37 necessario para o thread 1
ORA-00289: sugestão :
/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7266_bcb2r3p6_.arc
ORA-00280: alterar 7840579435644 para o thread 1 esta na sequencia  #7266

Log aplicado.
Recuperação de midia concluida.

SQL> alter database datafile 49 online;

Banco de dados alterado.

Pronto. Após a execução das instruções SQL acima podemos verificar abaixo, consultado a tabela T1, que o datafile foi recuperado com sucesso.

SQL> select count(*) from scott.t1;

  COUNT(*)
----------
   8000000

1 linha selecionada.
 

segunda-feira, 5 de janeiro de 2015

Capturando erros DML/DDL através da opção "Error Logging" disponível no SQL*Plus do Oracle 11g

Por Eduardo Legatti

Olá,

No artigo de Julho/2010 eu demonstrei de forma prática, como capturar os erros gerados pelas instruções SQL executadas no banco de dados, fazendo uso do gatilho (trigger) de sistema chamado AFTER SERVERERROR. O objetivo da trigger é capturar quaisquer erros gerados pelas instruções SQL executadas de todas as sessões conectadas no Oracle. O SQL*Plus do Oracle 11g veio com uma inovação um pouco parecida com esse propósito. A diferença é que somente as instruções executadas pela sessão que está executando o SQL*PLus é que será auditada. O parâmetro ERRORLOGGING quando setado para ON, irá criar uma tabela chamada de SPERRORLOG no schema do usuário que está conectado pleo SQL*Plus. Par padrão, o parâmetro é setado para OFF. Segue abaixo uma demonstração na qual irei conectar com o usuário SCOTT.
 
C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Seg Jan 5 08:17:09 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> show errorlogging;
errorlogging is OFF

SQL> set errorlogging ON;

SQL> show errorlogging;
errorlogging is ON TABLE SCOTT.SPERRORLOG

Após setar o parâmetro ERRORLOGGING para ON acima, podemos ver abaixo qua a tabela SPERRORLOG foi criada. Vale a pena salientar que essa tabela ficará criada até que a mesma seja dropada manualmente.

SQL> desc SPERRORLOG
 Nome                          Nulo?    Tipo
 ----------------------------- -------- ------------------------
 USERNAME                               VARCHAR2(256)
 TIMESTAMP                              TIMESTAMP(6)
 SCRIPT                                 VARCHAR2(1024)
 IDENTIFIER                             VARCHAR2(256)
 MESSAGE                                CLOB
 STATEMENT                              CLOB

Irei simular a execução de uma instrução SQL e também a execução de um script SQL afim de gerar alguns erros:

SQL> create table t1 (id numberr);
create table t1 (id numberr)
*
ERRO na linha 1:
ORA-00902: tipo de dados inválido

SQL> @script01.sql
select * from t2 where id=1
*
ERRO na linha 1:
ORA-00942: a tabela ou view não existe

Após a execução das instruções SQL acima, poderemos consultar a tabela SPERRORLOG.

SQL> select timestamp,script,message,statement from sperrorlog; 
TIMESTAMP         SCRIPT        MESSAGE                                STATEMENT
----------------- ------------- -------------------------------------- ----------------------------
05/01/15 08:21:54               ORA-00902: tipo de dados inválido      create table t1 (id numberr)
05/01/15 08:22:59 script01.sql  ORA-00942: a tabela ou view não existe select * from t2 where id=1

2 linhas selecionadas.

Por fim, vale a pena salientar que os registros inseridos na tabela SPERRORLOG participam da mesma transação da instrução SQL que originou o erro. Portanto, caso um comando de ROLLBACK seja emitido após uma instrução gerar algum erro, a instrução não será persistida na tabela SPERRORLOG.

segunda-feira, 1 de dezembro de 2014

Mover segmentos de LOB deixam os índices da tabela inutilizáveis (UNUSABLE)

Por Eduardo Legatti

Olá,

No artigo de Janeiro/2014 eu abordei sobre a realocação de objetos de bancos de dados entre tablespace distintas. Dentre os objetos que foram abordados no artigo, estão as tabelas, os índices e os segmentos de LOB. Neste artigo irei tratar mais especificamente sobre as consequências de se mover um segmento LOB. Vale a pena salientar que segmentos LOB tem sua origem em colunas de tabelas com tipos de dados (CLOB e BLOB). Abaixo, realizarei uma simulação na qual irei mover um segmento de LOB proveniente de uma coluna BLOB, utilizando o Oracle 12c (12.1.0.2).
 
C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Seg Dez 1 13:14:21 2014

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> alter session set current_schema=SCOTT;

Sessão alterada.

SQL> create table t1 (id number, nome varchar2(100), imagem blob);

Tabela criada.

SQL> alter table t1 add constraint t1_pk primary key (id);

Tabela alterada.

SQL> create index t1_idx_nome on t1 (nome);

Índice criado.

SQL> insert into t1 values (1,'Miguel',empty_blob());

1 linha criada.

SQL> insert into t1 values (2,'Laura',empty_blob());

1 linha criada.

SQL> commit;

Commit concluído.
 
Após a criação da tabela T1 no schema SCOTT, podemos verificar abaixo que os índices que foram criados na tabela estão com os status VALID.

SQL> select rowid,owner,index_name,status from dba_indexes where owner='SCOTT';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ --------
SCOTT      T1_PK                          VALID
SCOTT      T1_IDX_NOME                    VALID
SCOTT      SYS_IL0000020439C00003$$       VALID
  
Abaixo estão listados os registros da tabela com os respectivos ROWIDs de cada linha.
 
SQL> select rowid,id,nome from t1;

ROWID                      ID NOME
------------------ ---------- --------------------
AAAFAFAAEAAAACHAAA          1 Miguel
AAAFAFAAEAAAACHAAB          2 Laura


O objetivo agora será mover o segmento de LOB, proveniente da coluna IMAGEM que está atualmente armazenado na tablespace USERS, para a tablespaces TS_LOB. Como demonstrado abaixo, poderemos consultar a view DBA_LOBS para verificar em qual tablespace está armazenado o segmento de LOB.

SQL> select owner,table_name,column_name,tablespace_name
  2  from dba_lobs
  3  where owner='SCOTT';

OWNER      TABLE_NAME      COLUMN_NAME     TABLESPACE_NAME
---------- --------------- --------------- --------------------
SCOTT      T1              IMAGEM          USERS


Apenas para fins de demonstração, irei utilizar a instrução SQL abaixo para automatizar a criação do comando ALTER TABLE que irá mover o segmento de LOB para a tablespace TS_LOB. Vale a pena salientar que essa instrução é útil quando queremos mover vários segmentos de LOB.

SQL>   SELECT   'alter table '
  2           || owner
  3           || '.'
  4           || table_name
  5           || ' move lob ('
  6           || column_name
  7           || ') store as (tablespace TS_LOB);' as DDL
  8      FROM dba_lobs
  9      where owner='SCOTT'
 10      ORDER BY owner, table_name;

DDL
--------------------------------------------------------------------
alter table SCOTT.T1 move lob (IMAGEM) store as (tablespace TS_LOB);
  
Vamos então a execução do comando ALTER TABLE ... MOVE LOB.
 
SQL> alter table SCOTT.T1 move lob (IMAGEM) store as (tablespace TS_LOB);

Tabela alterada.

SQL> select rowid,id,nome from t1;

ROWID                      ID NOME
------------------ ---------- ---------------------
AAAFALAAEAAAACzAAA          1 Miguel
AAAFALAAEAAAACzAAB          2 Laura  

Após a execução do comando ALTER TABLE, podemos verificar abaixo que os ROWIDs das linhas da tabela T1 mudaram. Como consequência, os índices da tabela ficaram com status UNUSABLE e assim como acontece quando movemos uma tabela, será necessário reconstruir os índices (REBUILD). 
 
SQL> select owner,index_name,status from dba_indexes where owner='SCOTT';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ --------
SCOTT      T1_PK                          UNUSABLE
SCOTT      T1_IDX_NOME                    UNUSABLE
SCOTT      SYS_IL0000020439C00003$$       VALID


A não ser que o parâmetro SKIP_UNUSABLE_INDEXES esteja setado como TRUE, qualquer comando DML efetuado na tabela irá ocasionar o erro ORA-01502 como demonstrado abaixo.

SQL> insert into t1 values (3,'Angela',empty_blob());
insert into t1 values (3,'Angela',empty_blob())
*
ERRO na linha 1:
ORA-01502: índice 'SCOTT.T1_PK' ou a sua partição está em estado não utilizável 

Para resolver o problema, será necessário realizar o REBUILD dos índices conforme demonstrado abaixo.

SQL> alter index t1_pk rebuild;

Índice alterado.

SQL> alter index t1_idx_nome rebuild;

Índice alterado.

SQL> select owner,index_name,status from dba_indexes where owner='SCOTT';

OWNER      INDEX_NAME                     STATUS
---------- ------------------------------ --------
SCOTT      T1_PK                          VALID
SCOTT      T1_IDX_NOME                    VALID
SCOTT      SYS_IL0000020439C00003$$       VALID

Dependendo do tamanho de uma tabela e do tamanho dos segmentos de LOB envolvidos, mover um segmento de LOB de modo OFFLINE poderá ocasionar efeitos indesejados no ambiente pois a tabela ficará bloqueada (LOCK) durante a realocação do segmento de LOB. Para evitar esse problema eu aconselho realizar a realocação ONLINE através do uso da package DBMS_REDEFINITION. Os artigos de Abril/2011 e Dezembro/2012 eu abordo o uso desta package.

Por fim, existe uma nota (Doc ID 1228324.1) no My Oracle Support (Metalink) que aborda esse comportamento dos índices ficarem no estado UNUSABLE ao mover um segmento de LOB. A nota não é muito clara se isso seria um bug do Oracle ou um bug na documentação que não chama a atenção para esse comportamento.
 

domingo, 2 de novembro de 2014

Abordando o RMAN - MULTISECTION BACKUP disponível a partir do Oracle 11g

Por Eduardo Legatti

Olá,

Quando falamos de paralelismo de backups no RMAN, geralmente estamos falando de paralelização de arquivos, ou seja, cada canal (channel) trabalhando em um determinado arquivo, seja ele um datafile, controlfile ou spfile. No RMAN, quando executamos um comando, a quantidade de canais disponíveis para uso em um determinado dispositivo (device) é que determina se a operação de leitura ou escrita realizada será feita em paralelo. Quando uma tarefa é realizada em paralelo, o backup dos arquivos de banco de dados é realizada por mais de um canal (cada canal trabalhando em um arquivo). Bom, e se quisermos paralelizar o backup de apenas um arquivo, é possível? A partir do Oracle 11g é possível paralelizar a realização de backups de um único arquivo, ou seja, mais de um canal realizando backup de um único arquivo. Esta característica se chama MULTISECTION BACKUP e pode ser realizada através da cláusula SECTION SIZE do comando BACKUP. No mais, o objetivo deste artigo será demonstrar como os Multisection backups podem melhorar a performance em relação ao tempo na realização de backups físicos.

[oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 2 11:30:14 2014

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> select file#,name,bytes/1024/1024/1024 as GB from v$datafile where file#=10;

     FILE# NAME                                       GB
---------- ---------------------------------- ----------
        10 /oradata/BD01/users01.dbf          7,67773438

Como exemplo, irei realizar o backup do datafile 10 listado acima que possui tamanho de 7,6 GB.


[oracle]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Nov Sun 2 11:41:30 2014

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

connected to target database: BD01 (DBID=618743438)

RMAN> show all;

using target database control file instead of recovery catalog
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 7;
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
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default 

Como demonstrado acima pelo comando SHOW ALL, atualmente o paralelismo está configurado para 7, ou seja, 7 canais serão alocados pelo RMAN no início do processo de backup ou recover.

RMAN> backup datafile 10;

Starting backup at 02/11/2014 11:41:51
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=69 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=70 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=101 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=132 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=162 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=193 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02/11/2014 11:41:53
channel ORA_DISK_1: finished piece 1 at 02/11/2014 11:43:41
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T114156_b3bm9ng8_.bkp
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:50
Finished backup at 02/11/2014 11:43:41

Após a finalização do backup acima, podemos perceber que realmente foram alocados 7 canais (ORA_DISK_1-7), mas somente o canal ORA_DISK_1 foi utilizado na realização do backup do datafile, ou seja, não houve paralelismo na operação. Foram gastos 00:01:50 (110 segundos) para realização do backup. Abaixo irei executar a mesma operação, mas agora informando a opção SECTION SIZE 1G, o que significa que eu quero que os backup pieces gerados tenham no máximo 1 GB de tamanho cada.

[oracle]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Nov 2 11:50:52 2014

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

connected to target database: BD01 (DBID=618743438)

RMAN> backup datafile 10 section size 1G;

Starting backup at 02/11/2014 11:51:09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=70 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=69 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=102 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=131 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=162 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=193 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 1 through 131072
channel ORA_DISK_1: starting piece 1 at 02/11/2014 11:51:11
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 131073 through 262144
channel ORA_DISK_2: starting piece 2 at 02/11/2014 11:51:12
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 262145 through 393216
channel ORA_DISK_3: starting piece 3 at 02/11/2014 11:51:12
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 393217 through 524288
channel ORA_DISK_4: starting piece 4 at 02/11/2014 11:51:12
channel ORA_DISK_5: starting compressed full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 524289 through 655360
channel ORA_DISK_5: starting piece 5 at 02/11/2014 11:51:12
channel ORA_DISK_6: starting compressed full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 655361 through 786432
channel ORA_DISK_6: starting piece 6 at 02/11/2014 11:51:12
channel ORA_DISK_7: starting compressed full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 786433 through 917504
channel ORA_DISK_7: starting piece 7 at 02/11/2014 11:51:12
channel ORA_DISK_2: finished piece 2 at 02/11/2014 11:51:12
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv07h_.bkp
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:00
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/BD01/users01.dbf
backing up blocks 917505 through 1006336
channel ORA_DISK_2: starting piece 8 at 02/11/2014 11:51:12
channel ORA_DISK_3: finished piece 3 at 02/11/2014 11:51:12
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv08z_.bkp
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:00
channel ORA_DISK_4: finished piece 4 at 02/11/2014 11:51:12
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cl_.bkp
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:00
channel ORA_DISK_5: finished piece 5 at 02/11/2014 11:51:12
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cx_.bkp
channel ORA_DISK_5: backup set complete, elapsed time: 00:00:00
channel ORA_DISK_6: finished piece 6 at 02/11/2014 11:51:12
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0f8_.bkp
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:00
channel ORA_DISK_1: finished piece 1 at 02/11/2014 11:51:15
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmtzp4_.bkp
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_2: finished piece 8 at 02/11/2014 11:51:27
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0lj_.bkp
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_7: finished piece 7 at 02/11/2014 11:52:34
piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0h1_.bkp
channel ORA_DISK_7: backup set complete, elapsed time: 00:01:25
Finished backup at 02/11/2014 11:52:34

Após a finalização do backup acima, podemos perceber que foram alocados 7 canais (ORA_DISK_1-7), e que todos eles foram utilizados na realização do backup do datafile, ou seja, houve paralelismo na operação. Cada canal iniciou a operação de backup de uma determinada faixa de blocos do datafile. Foram gastos 00:01:25 (85 segundos) para realização do backup, ou seja, 25 segundos a menos comparado com a primeira simulação, o que representa uma melhora na performance de cerca de 30%. A figura abaixo demonstra a ação dos canais durante a operação de backup. Vale a pena salientar que é possível constatar que o canal ORA_DISK_2 iniciou a operação do backup piece 8 após finalizar o backup piece 2.


Para finalizar, segue abaixo a evidência dos backups pieces que foram gerados durante a realização do backup do datafile 10. O comando LIST mostra que foram gerados 8 backup pieces.
RMAN> list backup of datafile 10; 
using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5480    Full    544.80M    DISK        00:01:25     02/11/2014 11:52:34
  List of Datafiles in backup set 5480
  File LV Type Ckp SCN       Ckp Time            Name
  ---- -- ---- ------------- ------------------- -------------------------
  10      Full 7840571897377 02/11/2014 11:51:11 /oradata/BD01/users01.dbf

  Backup Set Copy #1 of backup set 5480
  Device Type Elapsed Time Completion Time     Compressed Tag
  ----------- ------------ ------------------- ---------- ------------------
  DISK        00:01:25     02/11/2014 11:52:34 YES        TAG20141008T115111

    List of Backup Pieces for backup set 5480 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ---------------------------------------------------------------------------
    5499    1   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmtzp4_.bkp
    5494    2   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv07h_.bkp
    5495    3   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv08z_.bkp
    5496    4   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cl_.bkp
    5497    5   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cx_.bkp
    5498    6   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0f8_.bkp
    5501    7   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0h1_.bkp
    5500    8   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0lj_.bkp

quarta-feira, 1 de outubro de 2014

Concedendo privilégios de objetos no Oracle com a stored procedure CREATE_SYNONYM_GRANT_DML_PRIVS

Por Eduardo Legatti

Olá,

No artigo de Setembro/2014 eu compartilhei aqui no blog a stored procedure DROP_USER_DISCONNECT_SESSION que tem como objetivo fazer o trabalho de desconectar as sessões correntes de um determinado usuário de banco de dados, bloquear o schema (account lock) enquanto as sessões correntes vão sendo eliminadas, e por fim, dropar o schema. Neste artigo irei compartilhar uma outra stored procedure chamada de CREATE_SYNONYM_GRANT_DML_PRIVS que foi desenvolvida com o intuito de facilitar a criação/exclusão de sinônimos e a concessão/revogação de privilégios de objetos (grant e revoke) aos usuários de bancos de dados. Por questões de segurança, é comum ter aplicações que se conectam no banco de dados à partir de múltiplos usuários. Por exemplo, imagine uma aplicação de RH que contém os objetos de schema de banco de dados criados no schema chamado RH. A aplicação não irá se conectar com o usuário RH, mas sim com um outros usuários criados no banco de dados que possuem privilégios para acessar os objetos do schema RH. Neste caso, esses usuários teriam apenas sinônimos apontando para os objetos do schema RH e privilégios DML (SELECT, INSERT, UPDATE, DELETE). Para exemplificar, teríamos o schema RH que seria o owner (dono) das tabelas, índices, etc e outros usuários como PAULA, MARCELO e JOAO.

Os usuários PAULA, MARCELO e JOAO somente teriam privilégios DML (SELECT, INSERT, UPDATE, DELETE) para acessarem os objetos do schema RH. Por questões de segurança, nenhum privilégio DDL como ALTER, DROP, CREATE seriam concedidos a estes usuários. Para evitar que os objetos tenham que ser qualificados com o nome do schema, sinônimos privados serão criados para cada usuário.

Enfim, para facilitar a concessão de privilégios para usuários que deverão ter acessos DML a objetos de um outro usuário, eu criei a stored procedure CREATE_SYNONYM_GRANT_DML_PRIVS que permitirá que à partir de um OWNER e um USER passados como parâmetros, sejam criados scripts ou executados os comandos de criação de sinônimos e de concessão de privilégios DML sobre todos os objetos do OWNER para o USER.

Irei criar a procedure CREATE_SYNONYM_GRANT_DML_PRIVS como demonstrado abaixo. Para evitar quaisquer problemas com privilégios, irei criá-la no usuário SYS. Se desejar, a criação de um sinônimo e a concessão do privilégio EXECUTE poderá ser concedido a um outro usuário no banco de dados, como por exemplo, o SYSTEM.
 
C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Qua Out 1 08:51:01 2014

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option 
SQL> create or replace procedure create_synonym_grant_dml_privs
  2    (p_owner VARCHAR2,
  3     p_user VARCHAR2,
  4     p_operation VARCHAR2,
  5     p_type VARCHAR2,
  6     p_access VARCHAR2 DEFAULT 'A'
  7    )
  8  as
  9     l_word VARCHAR2(10);
 10     l_operation VARCHAR2(10);
 11     l_type VARCHAR2(10);
 12     l_errm VARCHAR2 (2000);
 13     l_dml VARCHAR2 (100);
 14     l_qtd_owner NUMBER;
 15     l_qtd_user NUMBER;
 16     invalid_parameter EXCEPTION;
 17     invalid_owner EXCEPTION;
 18     invalid_user EXCEPTION;
 19     invalid_operation EXCEPTION;
 20     invalid_type EXCEPTION;
 21     invalid_owneruser EXCEPTION;
 22     invalid_schema EXCEPTION;
 23  BEGIN
 24    IF p_owner is NULL or p_user is NULL or p_operation is NULL or p_type is NULL THEN
 25       RAISE invalid_parameter;
 26    END IF;
 27
 28    IF UPPER (p_owner) IN ('PUBLIC','SYSTEM','SYS') OR UPPER (p_user) IN ('PUBLIC','SYSTEM','SYS') THEN
 29        RAISE invalid_schema;
 30    END IF;
 31
 32    SELECT COUNT (*) INTO l_qtd_owner FROM all_users WHERE username = UPPER (p_owner);
 33      IF l_qtd_owner = 0 THEN
 34          RAISE invalid_owner;
 35    END IF;
 36
 37    IF UPPER (p_owner) = UPPER (p_user) THEN
 38       RAISE invalid_owneruser;
 39    END IF;
 40
 41    l_operation := UPPER (p_operation);
 42    l_type := UPPER (p_type);
 43
 44    IF l_operation NOT IN ('GRANT','REVOKE') THEN
 45       RAISE invalid_operation;
 46    END IF;
 47
 48    IF l_type NOT IN ('EXECUTE','SPOOL') THEN
 49       RAISE invalid_type;
 50    END IF;
 51
 52    IF l_operation = 'GRANT' THEN
 53       l_word := 'TO';
 54       IF upper(p_access) = 'R' THEN
 55          l_dml := 'SELECT';
 56       ELSE
 57          l_dml := 'SELECT, INSERT, UPDATE, DELETE';
 58       END IF;
 59    ELSIF l_operation = 'REVOKE' THEN
 60       l_word := 'FROM';
 61       l_dml := 'ALL';
 62    END IF;
 63
 64    IF l_type = 'EXECUTE' THEN
 65       SELECT COUNT (*) INTO l_qtd_user FROM all_users WHERE username = UPPER (p_user);
 66       IF l_qtd_user = 0 THEN
 67           RAISE invalid_user;
 68       END IF;
 69    END IF;
 70
 71    FOR C1 IN (SELECT OBJECT_NAME, OBJECT_TYPE
 72                 FROM ALL_OBJECTS
 73                WHERE OWNER = UPPER (p_owner)
 74                      AND OBJECT_TYPE IN ('TABLE')
 75                      AND OBJECT_NAME NOT LIKE ('MLOG$\_%') ESCAPE '\'
 76                      AND OBJECT_NAME NOT LIKE ('RUPD$\_%') ESCAPE '\'
 77                      AND OBJECT_NAME NOT IN
 78                             (SELECT OBJECT_NAME FROM ALL_OBJECTS
 79                               WHERE OWNER = UPPER (p_owner)
 80                                     AND OBJECT_TYPE IN ('MATERIALIZED VIEW')
 81                              UNION ALL
 82                              SELECT OBJECT_NAME FROM DBA_RECYCLEBIN
 83                               WHERE OWNER = UPPER (p_owner))
 84               UNION ALL
 85               SELECT OBJECT_NAME, OBJECT_TYPE
 86                 FROM ALL_OBJECTS
 87                WHERE OWNER = UPPER (p_owner) AND OBJECT_TYPE IN ('MATERIALIZED VIEW')
 88               UNION ALL
 89               SELECT OBJECT_NAME, OBJECT_TYPE
 90                 FROM ALL_OBJECTS
 91                WHERE OWNER = UPPER (p_owner)
 92                      AND OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE')
 93               ORDER BY OBJECT_TYPE, OBJECT_NAME)
 94    LOOP
 95      IF C1.OBJECT_TYPE IN ('TABLE') THEN
 96        IF l_type = 'EXECUTE' THEN
 97          BEGIN
 98            EXECUTE IMMEDIATE l_operation||' '||l_dml||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
 99          EXCEPTION
100            WHEN OTHERS THEN NULL;
101          END;
102        ELSIF l_type = 'SPOOL' THEN
103          dbms_output.put_line (l_operation||' '||l_dml||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
104        END IF;
105      ELSIF C1.OBJECT_TYPE IN ('SEQUENCE','VIEW','MATERIALIZED VIEW') THEN
106        IF l_type = 'EXECUTE' THEN
107          BEGIN
108            EXECUTE IMMEDIATE l_operation||' SELECT'||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
109          EXCEPTION
110            WHEN OTHERS THEN NULL;
111          END;
112        ELSIF l_type = 'SPOOL' THEN
113          dbms_output.put_line (l_operation||' SELECT'||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
114        END IF;
115      ELSIF C1.OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
116        IF l_type = 'EXECUTE' THEN
117          BEGIN
118            EXECUTE IMMEDIATE l_operation||' EXECUTE ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
119          EXCEPTION
120            WHEN OTHERS THEN NULL;
121          END;
122        ELSIF l_type = 'SPOOL' THEN
123          dbms_output.put_line (l_operation||' EXECUTE ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
124        END IF;
125      END IF;
126
127      IF l_operation = 'GRANT' THEN
128        IF l_type = 'EXECUTE' THEN
129          BEGIN
130            EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'" FOR "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'"';
131          EXCEPTION
132            WHEN OTHERS THEN NULL;
133          END;
134        ELSIF l_type = 'SPOOL' THEN
135          dbms_output.put_line ('CREATE OR REPLACE SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'" FOR "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'";');
136        END IF;
137      ELSIF l_operation = 'REVOKE' THEN
138        IF l_type = 'EXECUTE' THEN
139          BEGIN
140            EXECUTE IMMEDIATE 'DROP SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'"';
141          EXCEPTION
142            WHEN OTHERS THEN NULL;
143          END;
144        ELSIF l_type = 'SPOOL' THEN
145          dbms_output.put_line ('DROP SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'";');
146        END IF;
147      END IF;
148    END LOOP;
149  EXCEPTION
150     WHEN invalid_parameter THEN
151        raise_application_error (-20001,'Invalid parameters. ');
152     WHEN invalid_owner THEN
153        raise_application_error (-20001,'The OWNER especified does not exist.');
154     WHEN invalid_user THEN
155        raise_application_error (-20001,'The USER especified does not exist.');
156     WHEN invalid_owneruser THEN
157        raise_application_error (-20001,'The OWNER/USER cannot be the same.');
158     WHEN invalid_schema THEN
159        raise_application_error (-20001,'The OWNER/USER specified is not permitted.');
160     WHEN invalid_operation THEN
161        raise_application_error (-20001,'Invalid operation. Use (GRANT/REVOKE).');
162     WHEN invalid_type THEN
163        raise_application_error (-20001,'Invalid type. Use (EXECUTE/SPOOL).');
164     WHEN OTHERS THEN
165        l_errm := SQLERRM;
166        raise_application_error (-20001, l_errm);
167  END;
168  /

Procedimento criado.

Segue abaixo os parâmetros que precisarão ser informados: 

SQL> desc create_synonym_grant_dml_privs
PROCEDURE create_synonym_grant_dml_privs
 Nome do Argumento                  Tipo                In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_OWNER                        VARCHAR2                IN
 P_USER                         VARCHAR2                IN
 P_OPERATION                    VARCHAR2                IN
 P_TYPE                         VARCHAR2                IN
 P_ACCESS                       VARCHAR2                IN     DEFAULT
 

  • P_OWNER: Nome do OWNER dos objetos.
  • P_USER: Nome do USER para o qual serão criados/excluídos os sinônimos dos objetos e concedidos/revogados os privilégios sobre os mesmos.
  • P_OPERATION: Operação a ser realizada. Possui suas opções:
    • GRANT: concede privilégios de acordo com o tipo de cada objeto e cria de sinônimos.
    • REVOKE: revoga privilégios concedidos e exclui sinônimos criados.
  • P_TYPE: Tipo do resultado da execução da stored procedure. Possui duas opções:
    • EXECUTE: executa os comandos de criação de sinônimos e concessão de privilégios.
    • SPOOL: gera uma saída (spool) dos comandos de criação de sinônimos e concessão de privilégios na tela.
  • P_ACCESS: Intruções DML que serão concedidas. Possui duas opções:
    • A: É o valor o default, ou seja, todas as instruções DML (SELECT, INSERT, DELETE , UPDATE) serão concedidas ao usuário.
    • R: Concede ao usuário apenas acesso leitura (SELECT).

Vale a pena salientar que somente serão aceitos parâmetros válidos. Por exemplo, NULL não é um valor válido. Deverão ser especificados usuários OWNER/USER existentes e distintos. Usuários como PUBLIC, SYSTEM e SYS não poderão ser utilizados.

Segue abaixo os objetos que a stored procedure irá analisar.

+----------------------------------------------------+
|Objeto             |  Privilégios                   |
|----------------------------------------------------|
| Table             | Select, Insert, Update, Delete |
| View              | Select                         |
| Materialized View | Select                         |
| Sequence          | Select                         |
| Procedure         | Execute                        |
| Function          | Execute                        |
| Package           | Execute                        |
+----------------------------------------------------+

OBS: Caso seja passado no parâmetro P_ACCESS o valor 'R', então apenas o privilégio SELECT será concedido ao usuário.

A stored procedure irá gerar os comandos de concessão de privilégios e criação de sinônimos dos objetos do OWNER para o USER de acordo com o tipo do objeto. Para demonstração, irei usar o schema RH como owner e o usuário JOAO como user. Segue abaixo os objetos existentes e de propriedade do schema RH.

SQL> select owner,object_name,object_type from dba_objects where owner='RH';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
RH                             CUSTOMER                       TABLE
RH                             DEPT_VIEW                      VIEW
RH                             SEQUENCE_CUSTOMER              SEQUENCE
RH                             FC_CONCATENATE                 FUNCTION
RH                             SP_INCREASE_SALARY             PROCEDURE
RH                             MVIEW_EMPLOYEE                 TABLE
RH                             MVIEW_EMPLOYEE                 MATERIALIZED VIEW
RH                             SALARY                         TABLE

8 linhas selecionadas.  

Irei agora executar a procedure conforme exemplo abaixo:

SQL> set serveroutput on size 1000000
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOL');

GRANT EXECUTE ON "RH"."FC_CONCATENATE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."FC_CONCATENATE" FOR "RH"."FC_CONCATENATE";
GRANT SELECT ON "RH"."MVIEW_EMPLOYEE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."MVIEW_EMPLOYEE" FOR "RH"."MVIEW_EMPLOYEE";
GRANT EXECUTE ON "RH"."SP_INCREASE_SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SP_INCREASE_SALARY" FOR "RH"."SP_INCREASE_SALARY";
GRANT SELECT ON "RH"."SEQUENCE_CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SEQUENCE_CUSTOMER" FOR "RH"."SEQUENCE_CUSTOMER";
GRANT SELECT, INSERT, UPDATE, DELETE ON "RH"."CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."CUSTOMER" FOR "RH"."CUSTOMER";
GRANT SELECT, INSERT, UPDATE, DELETE ON "RH"."SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SALARY" FOR "RH"."SALARY";
GRANT SELECT ON "RH"."DEPT_VIEW" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."DEPT_VIEW" FOR "RH"."DEPT_VIEW";

Procedimento PL/SQL concluído com sucesso.


No exemplo acima eu pedi apenas para gerar os comandos, em vez de executá-los diretamente. Para executar os comandos diretamente bastará apenas informar a opção EXECUTE:

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','EXECUTE');

Procedimento PL/SQL concluído com sucesso.
 

Após a execução da procedure, poderemos verificar abaixo que foram criados sinônimos privados no usuário JOAO e que os privilégios foram concedidos com sucesso.

SQL> select owner,object_name,object_type from dba_objects where owner='JOAO';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
JOAO                           EXAMPLE                        SYNONYM
JOAO                           FC_CONCATENATE                 SYNONYM
JOAO                           MVIEW_EMPLOYEE                 SYNONYM
JOAO                           SP_INCREASE_SALARY             SYNONYM
JOAO                           SEQUENCE_CUSTOMER              SYNONYM
JOAO                           CUSTOMER                       SYNONYM
JOAO                           SALARY                         SYNONYM
JOAO                           DEPT_VIEW                      SYNONYM

8 linhas selecionadas.
  
SQL> select grantee,owner,table_name,grantor,privilege
  2    from dba_tab_privs
  3   where grantee='JOAO';

GRANTEE      OWNER        TABLE_NAME           GRANTOR            PRIVILEGE
------------ ------------ -------------------- ------------------ ----------------------
JOAO         RH           CUSTOMER             RH                 UPDATE
JOAO         RH           CUSTOMER             RH                 SELECT
JOAO         RH           CUSTOMER             RH                 INSERT
JOAO         RH           CUSTOMER             RH                 DELETE
JOAO         RH           DEPT_VIEW            RH                 SELECT
JOAO         RH           FC_CONCATENATE       RH                 EXECUTE
JOAO         RH           MVIEW_EMPLOYEE       RH                 SELECT
JOAO         RH           SALARY               RH                 UPDATE
JOAO         RH           SALARY               RH                 SELECT
JOAO         RH           SALARY               RH                 INSERT
JOAO         RH           SALARY               RH                 DELETE
JOAO         RH           SEQUENCE_CUSTOMER    RH                 SELECT
JOAO         RH           SP_INCREASE_SALARY   RH                 EXECUTE

13 linhas selecionadas.
  
Para fazer o inverso, ou seja, remover os sinônimos que foram criados no usuário JOAO e revogar os privilégios concedidos, bastará apenas informar a opção REVOKE. No exemplo abaixo irei usar a opção SPOOL para mostrar os comandos.

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','REVOKE','SPOOL');

REVOKE EXECUTE ON "RH"."FC_CONCATENATE" FROM "JOAO";
DROP SYNONYM "JOAO"."FC_CONCATENATE";
REVOKE SELECT ON "RH"."MVIEW_EMPLOYEE" FROM "JOAO";
DROP SYNONYM "JOAO"."MVIEW_EMPLOYEE";
REVOKE EXECUTE ON "RH"."SP_INCREASE_SALARY" FROM "JOAO";
DROP SYNONYM "JOAO"."SP_INCREASE_SALARY";
REVOKE SELECT ON "RH"."SEQUENCE_CUSTOMER" FROM "JOAO";
DROP SYNONYM "JOAO"."SEQUENCE_CUSTOMER";
REVOKE ALL ON "RH"."CUSTOMER" FROM "JOAO";
DROP SYNONYM "JOAO"."CUSTOMER";
REVOKE ALL ON "RH"."SALARY" FROM "JOAO";
DROP SYNONYM "JOAO"."SALARY";
REVOKE SELECT ON "RH"."DEPT_VIEW" FROM "JOAO";
DROP SYNONYM "JOAO"."DEPT_VIEW";
 
Caso seja necessário conceder apenas privilégios de leitura para o usuário, bastará apenas informar a opção 'R' como último parâmetro.

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOL','R');

GRANT EXECUTE ON "RH"."FC_CONCATENATE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."FC_CONCATENATE" FOR "RH"."FC_CONCATENATE";
GRANT SELECT ON "RH"."MVIEW_EMPLOYEE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."MVIEW_EMPLOYEE" FOR "RH"."MVIEW_EMPLOYEE";
GRANT EXECUTE ON "RH"."SP_INCREASE_SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SP_INCREASE_SALARY" FOR "RH"."SP_INCREASE_SALARY";
GRANT SELECT ON "RH"."SEQUENCE_CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SEQUENCE_CUSTOMER" FOR "RH"."SEQUENCE_CUSTOMER";
GRANT SELECT ON "RH"."CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."CUSTOMER" FOR "RH"."CUSTOMER";
GRANT SELECT ON "RH"."SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SALARY" FOR "RH"."SALARY";
GRANT SELECT ON "RH"."DEPT_VIEW" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."DEPT_VIEW" FOR "RH"."DEPT_VIEW";

Procedimento PL/SQL concluído com sucesso.
 
Para finalizar, segue algumas validações que stored procedure realiza.

SQL> exec create_synonym_grant_dml_privs ('RH','MARIA','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('RH','MARIA','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The USER especified does not exist.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 155
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','RH','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('RH','RH','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The OWNER/USER cannot be the same.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 157
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('SYSTEM','RH','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('SYSTEM','RH','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The OWNER/USER specified is not permitted.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 159
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANTS','SPOOL');
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANTS','SPOOL'); END;
*
ERRO na linha 1:
ORA-20001: Invalid operation. Use (GRANT/REVOKE).
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 161
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOLS');
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOLS'); END;
*
ERRO na linha 1:
ORA-20001: Invalid type. Use (EXECUTE/SPOOL).
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 163
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT',NULL);
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANT',NULL); END;
*
ERRO na linha 1:
ORA-20001: Invalid parameters.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 151
ORA-06512: em line 1
 

Postagens populares