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


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
 

segunda-feira, 1 de setembro de 2014

DROP USER: Removendo um schema no Oracle com a stored procedure DROP_USER_DISCONNECT_SESSION

Por Eduardo Legatti

Olá,

Imagine vários ambientes de bancos de dados como desenvolvimento, teste, homologação, produção, entre outros. Particularmente, em um ambiente de desenvolvimento, é comum na rotina de um DBA realizar tarefas de exportação e importação de schemas de bancos de dados. Dependendo da frequência desta tarefa, por exemplo, no caso de realizar uma importação para substituir um schema existente em um banco de dados, o schema atual precisará ser dropado. O problema acontece quando ao realizarmos o DROP de um schema, o erro "ORA-01940: não é possível eliminar um usuário conectado no momento" surge na tela. O que fazemos então? Acessamos a view dinâmica de desempenho V$SESSION para descobrir o SID e SERIAL# das sessões conectadas para então desconectá-las.

Bom, neste artigo irei compartilhar uma stored procedure chamada de DROP_USER_DISCONNECT_SESSION criada por mim para facilitar um pouco esta tarefa. O objetivo dela será basicamente dropar um schema no banco de dados. A vantagem de usá-la é que a mesma fará todo o trabalho de desconectar as sessões correntes, bloquear o schema (account lock) de forma a impedir que novas conexões sejam realizadas enquanto a stored procedure estiver desconectando as sessões atuais, e por fim, dropar o schema. Segue abaixo uma simulação para demonstrar o seu uso.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Seg Set 1 11:58:37 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> drop user scott cascade;
drop user scott cascade
*
ERRO na linha 1:
ORA-01940: não é possível eliminar um usuário conectado no momento

Acima, é possível verificar  que, ao tentar o dropar o schema SCOTT, a mensagem de erro ORA-01940 foi emitida. No resultado do SQL abaixo poderemos verificar consultando a view de desempenho dinâmico V$SESSION que 3 sessões estão conectadas atualmente.
 

SQL> select sid,serial#,username,status,terminal
  2    from v$session
  3   where username='SCOTT';

       SID    SERIAL# USERNAME               STATUS      TERMINAL
---------- ---------- ---------------------- ----------- ----------------
        26         16 SCOTT                  INACTIVE    SERVER01
        28         17 SCOTT                  INACTIVE    MACHINE09
        34         41 SCOTT                  ACTIVE      MACHINE03

Irei criar abaixo a stored procedure DROP_USER_DISCONNECT_SESSION e executá-la. 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.
 

SQL> create or replace procedure drop_user_disconnect_session (owner varchar)
  2  as
  3  begin
  4     declare
  5        l_cnt   integer;
  6     begin
  7        begin
  8           execute immediate 'alter user ' || upper (owner) || ' account lock';
  9        exception
 10           when others
 11           then
 12              null;
 13        end;
 14
 15        for c1 in (select *
 16                    from v$session
 17                   where username = upper (owner))
 18        loop
 19           begin
 20              execute immediate
 21                    'alter system disconnect session '''
 22                 || c1.sid
 23                 || ','
 24                 || c1.serial#
 25                 || ''' immediate';
 26           exception
 27              when others
 28              then
 29                 null;
 30           end;
 31        end loop;
 32
 33        loop
 34           select count (*)
 35             into l_cnt
 36             from v$session
 37            where username = upper (owner);
 38
 39           exit when l_cnt = 0;
 40           dbms_lock.sleep (10);
 41        end loop;
 42
 43        begin
 44           execute immediate 'drop user ' || owner || ' cascade';
 45        exception
 46           when others
 47           then
 48              null;
 49        end;
 50     end;
 51  exception
 52     when others
 53     then
 54        null;
 55  end;
 56  /

Procedimento criado.

Pronto. Após a criação da mesma, segue abaixo sua execução passando como parâmetro o schema SCOTT.
 

SQL> exec drop_user_disconnect_session('SCOTT');

Procedimento PL/SQL concluído com sucesso.

Após a execução da mesma, poderemos observar abaixo que as sessões foram desconectadas e o schema SCOTT foi dropado com sucesso.
 
SQL> select sid,serial#,username,status,terminal
  2    from v$session
  3   where username='SCOTT';

não há linhas selecionadas

SQL> select * from all_users where username='SCOTT';

não há linhas selecionadas


segunda-feira, 4 de agosto de 2014

Oracle Multitenant: Alterando parâmetros de inicialização do Container Database (CDB) e Pluggable Databases (PDBs) no Oracle 12c

Por Eduardo Legatti

Olá,

Nos artigos de Fevereiro/2014, Março/2014 e Abril/2014 e abordei sobre a arquitetura Multitenant do Oracle 12c no que se refere aos conceitos básicos de administração e de backups dos containers (CDBs) e dos bancos de dados plugáveis (PDBs). Neste artigo irei demonstrar de forma prática a alteração dos parâmetros da instância container (CDB) e dos bancos de dados plugáveis (PDBs).

Para começar, é importante frisar que só existe um arquivo de inicialização SPFILE para a instância CDB. Isso quer dizer que não existe um arquivo de inicialização SPFILE separado para os bancos de dados plugáveis (PDBs). Neste caso, todos os bancos de dados PDBs herdam os valores dos parâmetros do banco de dados CDB. Alguns parâmetros dos bancos de dados PDBs podem ser alterados, outros não. o valor da coluna ISPDB_MODIFIABLE da view V$SYSTEM_PARAMETER irá nos informar se o parâmetro em questão poderá ser modificado. Segue a demonstração abaixo.
 
C:\>set ORACLE_SID=CDB01

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Seg Ago 4 09:43:13 2014

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

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

SQL> show con_name;

CON_NAME
-----------------------
CDB$ROOT

SQL> select a.con_id,a.name,a.dbid,
  2         b.status,a.open_mode,a.total_size
  3    from v$pdbs a, dba_pdbs b
  4   where a.con_id=b.pdb_id;

    CON_ID NAME                    DBID STATUS        OPEN_MODE  TOTAL_SIZE
---------- ----------------- ---------- ------------- ---------- ----------
         2 PDB$SEED          4076210644 NORMAL        READ ONLY   283115520
         3 PDB01             3898013363 NORMAL        READ WRITE  288358400
         4 PDB02             3947824578 NORMAL        READ WRITE  283115520
         5 PDB03             4001514065 NORMAL        READ WRITE  288358400

SQL> show parameter spfile;

NAME      TYPE        VALUE
--------- ----------- --------------------------------------------------------------
spfile    string      C:\ORACLE\APP\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILECDB01.ORA

Acima podemos verificar que a instância CDB foi aberta com o parâmetro de inicialização SPFILECDB01.ORA e que 3 bancos de dados PDBs estão abertos. Abaixo irei alterar o parâmetro "open_cursors" banco de dados de container CDB$ROOT.
 
SQL> alter system set open_cursors=210;

Sistema alterado.

O comando ALTER SYSTEM acima modificou o parâmetro "open_cursors" tanto em memória quanto no arquivo SPFILE. No Oracle 12c foi incluído uma nova cláusula no comando ALTER SYSTEM que indica se a alteração realizada no parâmetro deverá afetar apenas o banco de dados de container atual (CURRENT) ou em todos os containers (ALL). Se o mesmo não for especificado, então o padrão será CURRENT.
 
SQL> show parameter open_cursors;

NAME                            TYPE        VALUE
------------------------------- ----------- ---------------------------
open_cursors                    integer     210

SQL> select name,value,display_value,ispdb_modifiable
  2    from v$system_parameter
  3   where name = 'open_cursors';

NAME            VALUE      DISPLAY_VA ISPDB
--------------- ---------- ---------- -----
open_cursors    210        210        TRUE

Acima podemos verificar que o parâmetro "open_cursors" foi alterado no banco de dados de container CDB$ROOT e que o mesmo pode ser modificado também nos bancos de dados plugáveis. Irei realizar abaixo a alteração no banco de dados plugável PDB01.
 
SQL> alter session set container = PDB01;

Sessão alterada.

SQL> show con_name;

CON_NAME
------------------------------
PDB01

SQL> alter system set open_cursors=220;

Sistema alterado.

SQL> select name,value,display_value,ispdb_modifiable
  2    from v$system_parameter
  3   where name = 'open_cursors';

NAME            VALUE      DISPLAY_VA ISPDB
--------------- ---------- ---------- -----
open_cursors    220        220        TRUE

Após a alteração do parâmetro no banco de dados plugável PDB01, irei conectar novamente no container CDB$ROOT e consultar a view V$SYSTEM_PARAMETER.
 
SQL> alter session set container = CDB$ROOT;

Sessão alterada.

SQL> select name,value,display_value,con_id,ispdb_modifiable
  2    from v$system_parameter
  3   where name = 'open_cursors';

NAME           VALUE      DISPLAY_VA     CON_ID ISPDB
-------------- ---------- ---------- ---------- -----
open_cursors   210        210                 0 TRUE
open_cursors   220        220                 3 TRUE

O resultado acima demonstra que é possível consultar os valores dos parâmetros dos banco de dados plugáveis que tiveram seus valores alterados à partir do container CDB$ROOT. Vale a pena salientar que o banco de dados plugável precisa estar aberto para que a consulta retorne os valores dos parâmetros. Bom, após a alteração do parâmetro "open_cursors" tanto no container CDB quanto no banco de dados PDB01, segue abaixo o conteúdo o SPFILE após o mesmo ser convertido para PFILE.
 
C:\>type INITcdb01.ORA

cdb01.__data_transfer_cache_size=0
cdb01.__db_cache_size=729808896
cdb01.__java_pool_size=4194304
cdb01.__large_pool_size=12582912
cdb01.__oracle_base='C:\oracle\app'
cdb01.__pga_aggregate_target=314572800
cdb01.__sga_target=1073741824
cdb01.__shared_io_pool_size=50331648
cdb01.__shared_pool_size=264241152
cdb01.__streams_pool_size=0
*.audit_file_dest='C:\oracle\app\admin\CDB01\adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='C:\oracle\CDB01\control01.ctl','C:\oracle\CDB01\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CDB01'
*.db_recovery_file_dest_size=53687091200
*.db_recovery_file_dest='C:\oracle\app\fast_recovery_area'
*.diagnostic_dest='C:\oracle\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB01XDB)'
*.enable_pluggable_database=true
*.nls_language='BRAZILIAN PORTUGUESE'
*.nls_territory='BRAZIL'
*.open_cursors=210
*.pga_aggregate_target=300m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'

Verificando o resultado acima é possível perceber que apenas o valor do parâmetro "open_cursors" do container CDB01 se encontra armazenado no SPFILE. Portanto, é possível concluir que os valores dos parâmetros dos bancos de dados PDBs não ficam armazenados no arquivo de inicialização SPFILE e sim nas tabelas de dicionário de dados do banco de dados de container CDB$ROOT como demonstrado pela consulta SQL abaixo. Com ela é possível obter os valores do parâmetros de inicialização dos bancos de dados plugáveis consultando a tabela de dicionário de dados PDB_SPFILE$:
 
SQL> select a.name,value$,con_id 
  2    from pdb_spfile$ a 
  3    join v$pdbs b on (a.pdb_uid=b.con_uid);

NAME                 VALUE$         CON_ID
-------------------- ---------- ----------
open_cursors         220                 3
 

terça-feira, 1 de julho de 2014

Configurando um Script Master para execução de scripts no SQL*Plus

Por Eduardo Legatti

Olá,

Quando precisamos executar vários scripts SQL em um banco de dados, geralmente automatizamos a sua execução para não ter que ficarmos executando script por script. Dependendo do ambiente, como no caso de um banco de produção, a boa prática é colher todas as informações da execução realizada afim de se ter uma evidência ou prova do que foi executado. Portanto, um arquivo de log contendo as informações da execuções dos scripts é fundamental. Dentre as informações que eu considero importante que se tenha no arquivo de log, eu posso citar a instrução SQL que foi executada, o nome do script, a hora da execução, o tempo de execução, o nome do servidor e o nome do banco de dados. No artigo de Janeiro/2011 eu mostrei como habilitar o suporte a acentuação no DOS para execução de scripts SQL. Neste artigo irei compartilhar um modelo de script que utilizo com uma certa frequência que tem como objetivo chamar outros scripts e gerar uma evidência da execução realizada. Apesar do script que utilizo fazer mais validações, irei demonstrar apenas o que considero básico para um Script Master. Segue abaixo o conteúdo do mesmo na qual irei realizar a execução de 9 scripts.
 
C:\SQL>type 00-ScriptMaster.sql

WHENEVER OSERROR EXIT 1;
WHENEVER SQLERROR EXIT 1;
SET ECHO ON
SET DEFINE OFF
SET FEEDBACK ON
SET TIME ON
SET TIMING ON
SET APPINFO ON
SET SERVEROUTPUT ON SIZE 1000000
SPOOL 00-ScriptMaster.log
select host_name,instance_name from v$instance;
alter session set nls_territory='BRAZIL';
alter session set current_schema=SCOTT;
@01-script01.sql
@02-script02.sql
@03-script03.sql
@04-script04.sql
@05-script05.sql
@06-script06.sql
@07-script07.sql
@08-script08.sql
@09-script09.sql
spool off
quit;
 
Como demonstrado acima, o script 00-ScriptMaster.sql é o nome do Script Master. Em resumo, ele contém algumas validações como o uso das cláusulas WHENEVER nas duas primeiras linhas. Elas indicam que se algum script não for encontrado ou se ocorrer algum erro na execução de algum script, a execução do Script Master será abortada. Na minha opinião, esse tratamento é fundamental de forma a evitar que os scripts subsequentes sejam afetados por um erro ocasionado na execução de um script anterior. Dessa forma, um script que der algum problema poderá ser tratado pontualmente.

Outro ponto que acho importante se refere ao SET DEFINE OFF que impede que o caractere "&" seja tratado como "host variable" no SQL*Plus. Em relação ao horário de execução e o tempo de execução de cada instrução, SET TIME ON e SET TIMING ON são fundamentais. Na linha 8, o SET APPINFO ON é útil para fins de monitoramento porque o script em execução será visível na coluna MODULE da view dinâmica de desempenho V$SESSION. Portanto, mesmo que o Script Master esteja em execução no servidor de banco de dados, será possível acompanhar de outra máquina qual script está sendo executado no momento consultando a view V$SESSION. O SET SERVEROUTPUT ON é útil para mostrar resultados de códigos PL/SQL que utilizam o comando DBMS_OUTPUT.

Na linha 10, o comando SPOOL irá gerar o arquivo de log na qual darei o nome de 00-ScriptMaster.log. Para evitar qualquer problema com formatos de data, pontos decimais e de milhar, eu costumo forçar o NLS_TERRITORY como demonstrado na linha 12.

Para evitar qualquer problema em relação à falta de priviégios durante a execução dos scripts, eu prefiro executar o Script Master com um usuário DBA, como por exemplo o SYSTEM. Como a conexão é feita pelo SYSTEM, eu forço a sessão para o schema que será alvo da execução dos scripts (SCOTT) como demonstrado na linha 13. Caso o Script Master envolva a execução de scripts em mais de um schema, então vários "alter session set current_schema" poderão ser inseridos ao longo do Script Master. Por fim, as linhas que começam com @ são exatamente os scripts que serão executados conforme demonstrado abaixo:

C:\SQL>dir
 O volume na unidade C não tem nome.
 O Número de Série do Volume é 1C51-B278

 Pasta de C:\SQL

01/07/2014  15:27    [DIR]          .
01/07/2014  15:27    [DIR]          ..
01/07/2014  15:43               517 00-ScriptMaster.sql
01/07/2014  15:37               118 01-script01.sql
01/07/2014  15:13                20 02-script02.sql
01/07/2014  15:14                38 03-script03.sql
01/07/2014  15:48               103 04-script04.sql
01/07/2014  15:53               202 05-script05.sql
01/07/2014  15:15                36 06-script06.sql
01/07/2014  15:17                97 07-script07.sql
01/07/2014  15:17                37 08-script08.sql
01/07/2014  15:35               256 09-script09.sql
              10 arquivo(s)         1.424 bytes
               2 pasta(s)   102.755.786.752 bytes disponíveis

No mais, segue uma execução do Script Master na qual eu simulo um erro de execução do script 04-script04.sql. Neste caso, a execução do Script Master será abortada e a saída gravada no arquivo de log 00-ScriptMaster.log.

C:\SQL>sqlplus system/manager @00-ScriptMaster.sql

SQL*Plus: Release 11.2.0.1.0 Production on Ter Jul 1 15:47:12 2014

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

15:47:21 SQL> select host_name,instance_name from v$instance;

HOST_NAME                         INSTANCE_NAME
--------------------------------- -------------
server01                          BD01

1 linha selecionada.

Decorrido: 00:00:00.01
15:47:21 SQL> alter session set nls_territory='BRAZIL';

Sessão alterada.

Decorrido: 00:00:00.00
15:47:21 SQL> alter session set current_schema=SCOTT;

Sessão alterada.

Decorrido: 00:00:00.00
15:47:21 SQL> @01-script01.sql
15:47:21 SQL> create table t1 (id number);

Tabela criada.

Decorrido: 00:00:00.12
15:47:21 SQL> create table t2 (id number);

Tabela criada.

Decorrido: 00:00:00.03
15:47:21 SQL> create table t3 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:47:21 SQL> create table t4 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:47:21 SQL> @02-script02.sql
15:47:21 SQL> drop table t3 purge;

Tabela eliminada.

Decorrido: 00:00:00.02
15:47:22 SQL> @03-script03.sql
15:47:22 SQL> alter table t1 add nome varchar2(100);

Tabela alterada.

Decorrido: 00:00:00.08
15:47:22 SQL> @04-script04.sql
15:47:22 SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERRO na linha 1:
ORA-00947: não há valores suficientes
 
Enfim, segue abaixo o conteúdo do arquivo de log após uma execução completa do Script Master.

C:\SQL>sqlplus system/manager @00-ScriptMaster.sql
15:54:35 SQL> select host_name,instance_name from v$instance;

HOST_NAME                         INSTANCE_NAME
--------------------------------- ----------------
server01                          BD01

1 linha selecionada.

Decorrido: 00:00:00.00
15:54:35 SQL> alter session set nls_territory='BRAZIL';

Sessão alterada.

Decorrido: 00:00:00.00
15:54:35 SQL> alter session set current_schema=SCOTT;

Sessão alterada.

Decorrido: 00:00:00.00
15:54:35 SQL> @01-script01.sql
15:54:35 SQL> create table t1 (id number);

Tabela criada.

Decorrido: 00:00:00.06
15:54:35 SQL> create table t2 (id number);

Tabela criada.

Decorrido: 00:00:00.01
15:54:35 SQL> create table t3 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:54:35 SQL> create table t4 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:54:35 SQL> @02-script02.sql
15:54:35 SQL> drop table t3 purge;

Tabela eliminada.

Decorrido: 00:00:00.03
15:54:35 SQL> @03-script03.sql
15:54:35 SQL> alter table t1 add nome varchar2(100);

Tabela alterada.

Decorrido: 00:00:00.01
15:54:35 SQL> @04-script04.sql
15:54:35 SQL> insert into t1 values (1,'x');

1 linha criada.

Decorrido: 00:00:00.04
15:54:35 SQL> insert into t1 values (2,'y');

1 linha criada.

Decorrido: 00:00:00.01
15:54:35 SQL> insert into t1 values (3,'z');

1 linha criada.

Decorrido: 00:00:00.01
15:54:35 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.01
15:54:36 SQL> @05-script05.sql
15:54:36 SQL> insert into t2 values (10);

1 linha criada.

Decorrido: 00:00:00.01
15:54:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.01
15:54:36 SQL> 
15:54:36 SQL> declare
15:54:36   2    tdata varchar2(8);
15:54:36   3  begin
15:54:36   4   select to_char(sysdate,'hh24:mi:ss') into tdata  from dual;
15:54:36   5   dbms_output.put_line(tdata);
15:54:36   6   dbms_lock.sleep(300);
15:54:36   7  end;
15:54:36   8  /
15:54:36

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:05:00.07
15:59:36 SQL> @06-script06.sql
15:59:36 SQL> create view v_1 as select * from t1;

View criada.

Decorrido: 00:00:00.07
15:59:36 SQL> @07-script07.sql
15:59:36 SQL> insert into t2 values (200);

1 linha criada.

Decorrido: 00:00:00.03
15:59:36 SQL> insert into t2 values (300);

1 linha criada.

Decorrido: 00:00:00.02
15:59:36 SQL> insert into t2 values (400);

1 linha criada.

Decorrido: 00:00:00.03
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.03
15:59:36 SQL> @08-script08.sql
15:59:36 SQL> delete from t2 where id=200;

1 linha deletada.

Decorrido: 00:00:00.03
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.05
15:59:36 SQL> @09-script09.sql
15:59:36 SQL> update t1 set id=id*10;

3 linhas atualizadas.

Decorrido: 00:00:00.06
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.04
15:59:36 SQL> 
15:59:36 SQL> begin
15:59:36   2   for i in 1..10 loop
15:59:36   3     insert into t4 select level id from dual connect by level <=10;
15:59:36   4     if mod(i, 2) = 0 then
15:59:36   5        commit;
15:59:36   6        dbms_output.put_line('commit');
15:59:36   7     end if;
15:59:36   8   end loop;
15:59:36   9   commit;
15:59:36  10  end;
15:59:36  11  /
commit
commit
commit
commit
commit

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:00.12
15:59:37 SQL> spool off


Durante a execução do Script Master, foi possível identificar o script 05-script05.sql sendo executado consultado a view V$SESSION como demonstrado abaixo:

C:\>sqlplus system/manager

SQL*Plus: Release 11.2.0.1.0 Production on Ter Jul 1 15:55:04 2014

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> SELECT sid,serial#,username,schemaname,
  2  osuser,program,module
  3  FROM V$SESSION
  4  WHERE schemaname='SCOTT';

    SID  SERIAL# USERNAME     SCHEMANAME      OSUSER    PROGRAM      MODULE
------- -------- ------------ --------------- --------- ------------ -------------------
     68    53805 SYSTEM       SCOTT           legatti   sqlplus.exe  02@ 05-script05.sql

1 linha selecionada.

Postagens populares