Um pouco do Flashback Versions Query e do Flashback Transaction Query disponíveis a partir do Oracle 10g ...

Olá,

A funcionalidade do recurso Flashback Query disponível desde a versão Oracle 9i e abordada também nos artigos de Maio/2007 e Maio/2008, foi aperfeiçoada no Oracle 10g para incluir dois novos tipos de consultas: Flashback Versions Query e Flashback Transaction Query. O Flashback Versions Query fornece uma maneira simples e fácil de mostrar todas as versões de todas as linhas em uma tabela entre dois SCN's ou tempos de intervalo (time stamps), informando se as linhas foram inseridas, deletadas ou atualizadas. Na verdade, acredito que o Flashback Versions Query é uma extensão à linguagem SQL que permite ao DBA recuperar diferentes versões de linhas de tabela em qualquer intervalo de tempo. Uma nova versão de registro será criada toda vez que o comando COMMIT for emitido, e mesmo que uma linha seja deletada e re-inserida várias vezes, todas estas alterações estarão disponíveis para acesso. É importante lembrar que o parâmetro UNDO_RETENTION controla e especifica quanto tempo os blocos de dados Oracle alterados ficarão disponíveis no segmento de UNDO até serem sobre-gravados, portanto é bom certificar que não só este parâmetro esteja corretamente setado, mas também que o tablespace de UNDO seja grande suficiente para que as alterações realizadas no banco de dados estejam disponíveis para uso do Flashback Query quando necessário. No mais, para invocar esta funcionalidade é necessário utilizar a cláusula VERSIONS BETWEEN na sentença SELECT.

A sintaxe para uso do Flashback Versions Query é a seguinte:

 SELECT [pseudo_columns]...FROM table_name
   VERSIONS BETWEEN
  {SCN | TIMESTAMP {expr | MINVALUE} AND
  {expr | MAXVALUE}}
  [AS OF {SCN|TIMESTAMP expr}]
 WHERE [pseudo_column | column] . . .

Já o Flashback Transaction Query usa a view de dicionário de dados FLASHBACK_TRANSACTION_QUERY para recuperar informações de transações de banco de dados para todas as tabelas envolvidas em uma transação. Esta view possui uma coluna UNDO_SQL que fornece a sentença SQL que poderá ser utilizada para desfazer uma mudança feita anteriormente. A propósito, nas versões anteriores ao Oracle 10g, o LogMiner poderia ser utilizado para fornecer estas informações. É importante salientar que para ter acesso a esta view, o usuário de banco de dados deverá ter o privilégio de sistema SELECT ANY TRANSACTION. Em resumo, caso seja identificado que um dado em uma linha de tabela foi modificado erroneamente, poderemos então usar o Flashback Transaction Query para identificar o histórico das sentenças de undo SQL e utilizá-las para reverter estas modificações.

A view FLASHBACK_TRANSACTION_QUERY contém as seguintes colunas:

Nome             Descrição
---------------- ---------------------------------------------------------------------------
XID              Identificador da transação.
START_SCN        Número do SCN que inicia a transação.
START_TIMESTAMP  Horário de início da transação.
COMMIT_SCN       SCN gerado durante o COMMIT da transação. Será nulo para transações ativas.
COMMIT_TIMESTAMP Horário do COMMIT. Será nulo para transações ativas.
LOGON_USER       Usuário de banco de dados logado que realizou a transação.
UNDO_CHANGE#     Identificador de UNDO.
OPERATION        Operações realizadas pela transação (insert, delete, update).
TABLE_NAME       Nome da tabela onde as operações DML foram aplicadas.
TABLE_OWNER      Nome do usuário de banco de dados proprietário da tabela.
ROW_ID           O ROWID que foi modificado pela operação DML.
UNDO_SQL         SQL que poderá ser usado para desfazer a operação DML realizada.

Vale a pena salientar que a coluna LOGON_USER mostrada acima, poder ser útil para filtrar as transações realizadas por um usuário específico que possui privilégios de acesso às tabelas de propriedade de outro usuário. Vamos então a um pequeno exemplo prático onde usarei a procedure DBMS_LOCK.SLEEP apenas para gerar um intervalo de tempo (60 segundos) entre as transações:

-- Criando um usuário para teste
SYS> create user scott identified by tiger
 2  default tablespace users
 3  quota unlimited on users;

Usuário criado.

SYS> grant connect,resource to scott;

Concessão bem-sucedida.

SYS> grant select any transaction to scott;

Concessão bem-sucedida.

SYS> grant execute on dbms_lock to scott;

Concessão bem-sucedida.

SYS> connect scott/tiger
Conectado.

-- Criando de uma tabela para teste
SCOTT> create table t1 (id number);

Tabela criada.

-- Simulando operações DML's na tabela T1
SCOTT> set time on
09:12:34 SCOTT> insert into t1 values (1);

1 linha criada.

09:12:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:13:34 SCOTT> commit;

Validação completa.

09:13:34 SCOTT> insert into t1 values (2);

1 linha criada.

09:13:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:14:34 SCOTT> commit;

Validação completa.

09:14:34 SCOTT> insert into t1 values (3);

1 linha criada.

09:14:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:15:34 SCOTT> commit;

Validação completa.

09:15:34 SCOTT> update t1 set id = id*10;

3 linhas atualizadas.

09:15:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:16:34 SCOTT> commit;

Validação completa.

09:16:34 SCOTT> delete from t1;

3 linhas deletadas.

09:16:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:17:34 SCOTT> commit;

Validação completa.

09:17:36 SCOTT> set time off

-- Obtendo informações das versões das linhas
SCOTT> select versions_starttime stime,
   2         versions_endtime endtime,
   3         versions_xid xid,
   4         case
   5           when versions_operation = 'I' then 'INSERT'
   6           when versions_operation = 'U' then 'UPDATE'
   7           when versions_operation = 'D' then 'DELETE'
   8         end as operation,
   9         id
  10  from t1 versions between timestamp minvalue and maxvalue
  11  order by stime;

STIME             ENDTIME            XID              OPERAT         ID
----------------- ------------------ ---------------- ------ ----------
28/07/08 09:13:32 28/07/08 09:16:37  06001F00E9000000 INSERT          1
28/07/08 09:14:32 28/07/08 09:16:37  01000500F3000000 INSERT          2
28/07/08 09:15:32 28/07/08 09:16:37  02001000EF000000 INSERT          3
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         10
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         20
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         30
28/07/08 09:17:32                    04001D00F4000000 DELETE         10
28/07/08 09:17:32                    04001D00F4000000 DELETE         20
28/07/08 09:17:32                    04001D00F4000000 DELETE         30

9 linhas selecionadas.

De acordo com o resultado acima, podemos perceber, por exemplo, que o valor 1 contido na coluna ID da tabela T1, foi inserido pela transação 06001F00E9000000 às 09:13:32 e permaneceu com este valor até às 09:16:37 quando teve seu valor alterado para 10 até ser deletada por volta das 09:17:32. Podemos perceber também que as operações DELETE e UPDATE foram realizadas em uma mesma transação, ou seja, a transação 03001100F1000000 para UPDATE e a 04001D00F4000000 para DELETE.

Agora, vamos ver abaixo o que podemos obter selecionando dados a partir da view FLASHBACK_TRANSACTION_QUERY:

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('06001F00E9000000');

UNDO_SQL
-------------------------------------------------------------------------
delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAA';

O resultado acima mostra o comando DML necessário que deverá ser utilizado para desfazer a alteração realizada na transação identificada pelo id 06001F00E9000000.

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('03001100F1000000');

UNDO_SQL
-------------------------------------------------------------------------
update "SCOTT"."T1" set "ID" = '1' where ROWID = 'AAADsWAAEAAAMtlAAA';
update "SCOTT"."T1" set "ID" = '2' where ROWID = 'AAADsWAAEAAAMtlAAB';
update "SCOTT"."T1" set "ID" = '3' where ROWID = 'AAADsWAAEAAAMtlAAC';

O resultado acima mostra os comandos DML's necessários que deverão ser utilizados para desfazerem as alterações realizadas na transação identificada pelo id 03001100F1000000.

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('04001D00F4000000');

UNDO_SQL
-------------------------------------------------------------------------
insert into "SCOTT"."T1"("ID") values ('10');
insert into "SCOTT"."T1"("ID") values ('20');
insert into "SCOTT"."T1"("ID") values ('30');

O resultado acima mostra os comandos DML's necessários que deverão ser utilizados para desfazerem as alterações realizadas na transação identificada pelo id 04001D00F4000000.

Por fim, irei obter abaixo, o histórico de todos os comandos DML's que poderão, de alguma forma, serem utilizados para desfazerem as alterações nos registros da tabela T1 desde a sua criação:

SCOTT> select to_char(commit_timestamp,'hh24:mi:ss') time,
   2         operation,
   3         undo_sql
   4  from flashback_transaction_query
   5  where table_name='T1' order by 1;

TIME     OPERATION UNDO_SQL
-------- --------- ----------------------------------------------------------------------
09:13:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAA';
09:14:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAB';
09:15:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAC';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '1' where ROWID = 'AAADsWAAEAAAMtlAAA';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '2' where ROWID = 'AAADsWAAEAAAMtlAAB';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '3' where ROWID = 'AAADsWAAEAAAMtlAAC';
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('10');
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('20');
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('30');

9 linhas selecionadas.

Para maiores informações e exemplos de uso sobre ambas as tecnologias, você poderá acessar a documentação (em inglês) disponível no site da Oracle: Usando Flashback Version Query e Usando Flashback Transaction Query.

Lançado o blog do Programa de Certificação Oracle ...

Olá,

Desde o dia 10 de Julho de 2008, está no ar o Oracle Certification Blog que com certeza, além de trazer valiosas informações sobre o programa de certificação Oracle, acredito que será de grande utilidade para todos àqueles que queiram postar questões, reclamações, sugestões, entre outros, relacionados aos exames de certificação, preparação para realização de exames, entregas de certificados, requisitos para obtenção de certificação na área desejada, etc... Para maiores informações acesse http://blogs.oracle.com/certification/



Para envio de idéias, comentários e sugestões, foi disponibilizado o e-mail do autor no seguinte endereço: paul-blog_ww@oracle.com

Extraindo comando DDL com o pacote DBMS_METADATA ...

Olá,

Para àqueles que ainda não conhecem a package DBMS_METADATA, o mesma está disponível desde a versão Oracle 9i. Esta package é definida durante a criação do banco de dados através do script $ORACLE_HOME/rdbms/admin/dbmsmeta.sql. Mas, para que serve esta package? Na verdade, esta package disponibiliza uma interface pública com uma API utilizada para a extração de metadados de objetos de banco de dados. Todas as ferramentas GUI que fornecem suporte à extração de comandos DDL's (Linguagem de definição de dados) de objetos de banco de dados Oracle, acredito eu, fazem uso do pacote DBMS_METADATA.

Dentre as ferramentas mais conhecidas, posso citar o TOAD, o Oracle PL/SQL Developer, o Oracle SQL Developer, DBArtisan, o próprio Oracle Enterprise Manager Database Control, entre outras. Neste artigo irei apenas demonstrar através de exemplos práticos, como extrair comandos DDL's de criação de alguns objetos, não só invocando algumas funções do pacote DBMS_METADATA diretamente do SQL*Plus, mas também utilizando o Oracle SQL Developer e os utilitários de exportação/importação (antigos exp/imp e os novos expdp/impdp). No caso de invocar a função GET_DDL do pacote DBMS_METADATA a partir do SQL*Plus, eu irei executar o procedimento DBMS_METADATA.SET_TRANSFORM_PARAM() para alterar alguns parâmetros default.

Portanto, eu vou alterar o parâmetro SQLTERMINATOR para TRUE de modo a adicionar um terminador SQL (; ou /) para cada sentença DDL gerada, já que o seu valor padrão é FALSE. Outra alteração que realizarei será a de alterar o parâmetro SEGMENT_ATTRIBUTES para FALSE, de forma a suprimir informações de armazenamento de segmentos (storage attributes) dos comandos DDL's gerados. Vale a pena salientar que o parâmetro STORAGE mesmo estando setado para TRUE, será ignorado quando o parâmetro SEGMENT_ATTRIBUTES for setado para FALSE. Sem mais, vamos então a alguns exemplos práticos:

-- Criando um schema e alguns objetos para teste
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Ter Jul 22 14:55:04 2008

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

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

-- Criando o usuário SCOTT
SYS> create user scott identified by tiger
2  default tablespace users
3  quota unlimited on users;

Usuário criado.

-- Criando o usuário ADAM
SYS> create user adam identified by wayne
2  default tablespace users
3  quota unlimited on users;

Usuário criado.

-- Concedendo privilégios de sistema e roles ao usuário SCOTT
SYS> grant connect,
2  resource,
3  create view,
4  create materialized view
5  to scott;

Concessão bem-sucedida.

-- Criando objetos de teste no usuário SCOTT
SYS> connect scott/tiger
Conectado.

-- Criando a tabela T1
SCOTT> create table t1 (id number constraint pk_t1 primary key,
  2  nome varchar2(100)
  3  );

Tabela criada.

-- Criando um índice para a coluna NOME na tabela T1
SCOTT> create index i_t1_nome on t1 (nome);

Índice criado.

-- Criando um gatilho de teste para a tabela T1
SCOTT> create or replace trigger trg_bi_t1
  2  before insert on t1
  3  for each row
  4  begin
  5   :new.id := dbms_random.random;
  6  end;
  7  /

Gatilho criado.

-- Criando uma view de teste baseada na tabela T1
SCOTT> create view view_t1_nome as select nome from t1;

View criada.

-- Criando a tabela T2 com uma chave estrangeira referenciando T1
SCOTT> create table t2 (id number constraint fk_t2_t1 references t1);

Tabela criada.

-- criando uma view materializada
SCOTT> create materialized view mview_t1
  2  build immediate
  3  using index
  4  refresh force
  5  start with to_date('18/07/2008','dd/mm/yyyy hh24:mi:ss')
  6  next sysdate + 1/1440
  7  as
  8  select * from t1;

View materializada criada.

-- Concendendo privilégios de objeto para o usuário ADAM
SCOTT> grant select,update on t1 to adam;

Concessão bem-sucedida.

-- Verificando os objetos criados de propriedade do usuário SCOTT
SCOTT> select object_name,object_type from user_objects order by 2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
I_T1_NOME                      INDEX
PK_T11                         INDEX
PK_T1                          INDEX
MVIEW_T1                       MATERIALIZED VIEW
T2                             TABLE
MVIEW_T1                       TABLE
T1                             TABLE
TRG_BI_T1                      TRIGGER
VIEW_T1_NOME                   VIEW

9 linhas selecionadas.

-- Verificando a chave estrangeira criada
SCOTT> select table_name,constraint_name
  2  from user_constraints
  3  where constraint_type = 'R';

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
T2                             FK_T2_T1

 
Usando o Oracle SQL Developer

Após a criação dos objetos acima, utilizarei o Oracle SQL Developer e selecionarei a aba SQL para mostrar os comandos DDL's de alguns destes objetos. A figura abaixo mostra as configurações que utilizo de forma a formatar os comandos DDL's gerados.

Obtendo os comandos DDL's de criação da tabela T1 e seus dependentes
 

Obtendo o comando DDL de criação da tabela T2
 

 
Usando o pacote DBMS_METADATA no SQL*Plus

-- Verificando algumas das funções que utilizarei para geração dos comandos DDL's
SCOTT> desc dbms_metadata;

FUNCTION GET_DDL RETURNS CLOB
Nome do Argumento              Tipo                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE                    VARCHAR2                IN
NAME                           VARCHAR2                IN
SCHEMA                         VARCHAR2                IN     DEFAULT
VERSION                        VARCHAR2                IN     DEFAULT
MODEL                          VARCHAR2                IN     DEFAULT
TRANSFORM                      VARCHAR2                IN     DEFAULT

FUNCTION GET_DEPENDENT_DDL RETURNS CLOB
Nome do Argumento              Tipo                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE                    VARCHAR2                IN
BASE_OBJECT_NAME               VARCHAR2                IN
BASE_OBJECT_SCHEMA             VARCHAR2                IN     DEFAULT
VERSION                        VARCHAR2                IN     DEFAULT
MODEL                          VARCHAR2                IN     DEFAULT
TRANSFORM                      VARCHAR2                IN     DEFAULT
OBJECT_COUNT                   NUMBER                  IN     DEFAULT

FUNCTION GET_GRANTED_DDL RETURNS CLOB
Nome do Argumento              Tipo                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE                    VARCHAR2                IN
GRANTEE                        VARCHAR2                IN     DEFAULT
VERSION                        VARCHAR2                IN     DEFAULT
MODEL                          VARCHAR2                IN     DEFAULT
TRANSFORM                      VARCHAR2                IN     DEFAULT
OBJECT_COUNT                   NUMBER                  IN     DEFAULT

-- Configurando o ambiente
SCOTT> set linesize 1000
SCOTT> set pagesize 1000
SCOTT> set long 9999999

-- Adicionando um terminador SQL (; ou /) para cada sentença DDL gerada
SCOTT> exec dbms_metadata.set_transform_param(
     dbms_metadata.session_transform,'SQLTERMINATOR',true);

Procedimento PL/SQL concluído com sucesso.

-- Suprimindo qualquer informação de atributos de armazenamento de segmentos
SCOTT> exec dbms_metadata.set_transform_param(
     dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

Procedimento PL/SQL concluído com sucesso.

-- Gerando DDL para a tabela T1
SCOTT> select dbms_metadata.get_ddl('TABLE','T1') "DDL TABLE" from dual;

DDL TABLE
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
(    "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE
);

-- Gerando DDL para a view VIEW_T1_NOME
SCOTT> select dbms_metadata.get_ddl('VIEW','VIEW_T1_NOME') "DDL VIEW" from dual;

DDL VIEW
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SCOTT"."VIEW_T1_NOME" ("NOME") AS
SELECT NOME FROM T1;

-- Gerando DDL apenas para a chave estrangeira definida na tabela T2
SCOTT> select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','T2') "DDL FK" from dual;

DDL FK
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE;

-- Gerando DDL para restrições do tipo (PK/UK/CHK) existentes definidas em T1
SCOTT> select dbms_metadata.get_dependent_ddl('CONSTRAINT','T1') "DDL PK/UK/CHK" from dual;

DDL PK/UK/CHK
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE;

-- Gerando o comando DDL para qualquer gatilho existente para a tabela T1
SCOTT> select dbms_metadata.get_dependent_ddl('TRIGGER','T1') "DDL TRIGGER" from dual;

DDL TRIGGER
--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "SCOTT"."TRG_BI_T1"
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
 :NEW.ID := DBMS_RANDOM.RANDOM;
END;
/
ALTER TRIGGER "SCOTT"."TRG_BI_T1" ENABLE;

-- Gerando comando DDL para qualquer índice existente para a tabela T1
SCOTT> select dbms_metadata.get_dependent_ddl('INDEX','T1') "DDL ÍNDICE" from dual;

DDL ÍNDICE
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."I_T1_NOME" ON "SCOTT"."T1" ("NOME");
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID");

-- Gerando DDL para a view materializada
SCOTT> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MVIEW_T1') "DDL MVIEW" from dual;

DDL MVIEW
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SCOTT"."MVIEW_T1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT sysdate + 1/1440
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "T1"."ID" "ID","T1"."NOME" "NOME" FROM "T1" "T1";

-- Exemplo para geração de comandos DDL's para todas as tabelas existentes
SCOTT> select dbms_metadata.get_ddl(object_type, object_name) ddl
  2  from user_objects
  3  where object_type = 'TABLE';

DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."MVIEW_T1"
(    "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T11" PRIMARY KEY ("ID") ENABLE
);

CREATE TABLE "SCOTT"."T1"
(    "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE
);

CREATE TABLE "SCOTT"."T2"
(    "ID" NUMBER,
CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE
);

Apenas como demonstração, irei gerar abaixo os comandos DDL's necessários para concessão de privilégios, criação do usuário, role e tablespace:

SCOTT> connect / as sysdba
Conectado.

SYS> set linesize 1000
SYS> set pagesize 1000
SYS> set long 9999999

SYS> exec dbms_metadata.set_transform_param(
     dbms_metadata.session_transform,'SQLTERMINATOR',true);

Procedimento PL/SQL concluído com sucesso.

SYS> exec dbms_metadata.set_transform_param(
     dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

Procedimento PL/SQL concluído com sucesso.

-- Gerando DDL para criação da tablespace USERS
SYS> select dbms_metadata.get_ddl('TABLESPACE','USERS') DDL from dual;

DDL
--------------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'C:\ORACLEXE\ORADATA\XE\USERS.DBF' SIZE 104857600
AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DATAFILE
'C:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE 3145728000;

-- Gerando DDL para criação do usuário SCOTT
SYS> select dbms_metadata.get_ddl('USER','SCOTT') DDL from dual;

DDL
--------------------------------------------------------------------------------
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- Gerando DDL de privilégios de sistema concedidos ao usuário SCOTT
SYS> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SCOTT') DDL from dual;

DDL
--------------------------------------------------------------------------------
GRANT CREATE MATERIALIZED VIEW TO "SCOTT";
GRANT CREATE VIEW TO "SCOTT";
GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- Gerando DDL de roles conedidas ao usuário SCOTT
SYS> select dbms_metadata.get_granted_ddl('ROLE_GRANT','SCOTT') DDL from dual;

DDL
--------------------------------------------------------------------------------
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";

-- Gerando DDL de privilégios de objetos que foram concedidos pelo usuário
-- SCOTT ao usuário ADAM
SYS> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','ADAM') DDL from dual;

DDL
--------------------------------------------------------------------------------
GRANT SELECT ON "SCOTT"."T1" TO "ADAM";
GRANT UPDATE ON "SCOTT"."T1" TO "ADAM";

-- Gerando DDL de criação da role CONNECT
SYS> select dbms_metadata.get_ddl('ROLE','CONNECT') DDL from dual;

DDL
--------------------------------------------------------------------------------
CREATE ROLE "CONNECT";

 
Usando os utilitários exp/imp

-- exportando as tabelas do schema SCOTT
C:\exp scott/tiger file=c:\scott grants=n statistics=none rows=n

Export: Release 10.2.0.1.0 - Production on Sex Jul 18 12:58:03 2008

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

Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8PC850  e no conjunto de
caracteres de AL16UTF16 NCHAR o servidor usa WE8MSWIN1252 conjunto de caracteres
(conversão de conjunto de caracteres possível)
OBS: dados (linhas) da tabela não serão exportados
OBS: concessões em tabelas/views/seqüências/funções não serão exportadas
. exportando objetos e ações procedurais anteriores ao esquema
. exportando os nomes da biblioteca de função externa para usuário SCOTT
. exportando sinônimos do tipo PÚBLICO
. exportando sinônimos do tipo privado
. exportando definições de tipos de objeto para usuário SCOTT
Sobre exportar objetos de SCOTT ...
. exportando vínculos de banco de dados
. exportando números de seqüência
. exportando definições de cluster
. sobre exportar tabelas de SCOTT ... via Caminho Convencional ...
. . exportando tabela             MVIEW_T1
. . exportando tabela                   T1
. . exportando tabela                   T2
. exportando sinônimos
. exportando views
. exportando procedimentos armazenados
. exportando operadores
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportando tipos de índices
. exportando índices funcionais, extensíveis e de bitmap
. exportando ações contabilizáveis
. exportando views materializadas
. exportando logs de snapshot
. exportando filas de serviço
. exportando filhos e grupos de renovação
. exportando dimensões
. exportando objetos e ações procedurais posteriores ao esquema
. exportando estatística
Exportação encerrada com sucesso, sem advertências.

-- Gerando os comandos DDL's para arquivo texto
C:\>imp scott/tiger file=c:\scott indexfile=c:\ddl.sql

Import: Release 10.2.0.1.0 - Production on Sex Jul 18 12:59:25 2008

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

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

Arquivo de exportação criado por EXPORT:V10.02.01 via caminho convencional
importação realizada nos conjuntos de caracteres WE8PC850 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres WE8MSWIN1252 (conversão
de charset possível)
. . saltando a tabela "MVIEW_T1"
. . saltando a tabela "T1"
. . saltando a tabela "T2"

Importação encerrada com sucesso, sem advertências.

-- Verificando os comandos DDL's gerados
C:\>type C:\ddl.sql

REM  CREATE TABLE "SCOTT"."MVIEW_T1" ("ID" NUMBER, "NOME" VARCHAR2(100))
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM  LOGGING NOCOMPRESS ;

CONNECT SCOTT;

CREATE UNIQUE INDEX "SCOTT"."PK_T11" ON "MVIEW_T1" ("ID" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;

REM  ALTER TABLE "SCOTT"."MVIEW_T1" ADD CONSTRAINT "PK_T11" PRIMARY KEY
REM  ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
REM  65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  "USERS" LOGGING ENABLE;

REM  CREATE TABLE "SCOTT"."T1" ("ID" NUMBER, "NOME" VARCHAR2(100)) PCTFREE
REM  10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS
REM  1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
REM  NOCOMPRESS;

CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "T1" ("ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;

CREATE INDEX "SCOTT"."I_T1_NOME" ON "T1" ("NOME" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;

REM  ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
REM  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM  LOGGING ENABLE;

REM  CREATE TABLE "SCOTT"."T2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM  GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS;

REM  ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REM  REFERENCES "T1" ("ID") ENABLE NOVALIDATE ;

REM  ALTER TABLE "SCOTT"."T2" ENABLE CONSTRAINT "FK_T2_T1";

 
Usando os utilitários expdp/impdp (Datapump 10g)

-- Realizando a exportação apenas dos metadados
C:\>expdp scott/tiger directory=data_pump_dir dumpfile=scott content=metadata_only

Export: Release 10.2.0.1.0 - Production on Sexta-Feira, 18 Julho, 2008 13:32:50

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

Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Iniciando "SCOTT"."SYS_EXPORT_SCHEMA_01":
scott/******** directory=data_pump_dir dumpfile=scott content=metadata_only
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/MATERIALIZED_VIEW
Processando o tipo de objeto SCHEMA_EXPORT/JOB
Tabela-mestre "SCOTT"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SCOTT.SYS_EXPORT_SCHEMA_01 é:
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\SCOTT.DMP
O job "SCOTT"."SYS_EXPORT_SCHEMA_01" foi concluído com sucesso em 13:33:06

-- Gerando os comandos DDL's para arquivo texto
C:\>impdp scott/tiger directory=data_pump_dir dumpfile=scott sqlfile=ddl.sql

Import: Release 10.2.0.1.0 - Production on Sexta-Feira, 18 Julho, 2008 13:34:24

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

Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39154: Objetos de esquemas estrangeiros foram removidos da importação
Tabela-mestre "SCOTT"."SYS_SQL_FILE_FULL_01" carregada/descarregada com sucesso
Iniciando "SCOTT"."SYS_SQL_FILE_FULL_01":
scott/******** directory=data_pump_dir dumpfile=scott sqlfile=ddl.sql
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/MATERIALIZED_VIEW
O job "SCOTT"."SYS_SQL_FILE_FULL_01" foi concluído com sucesso em 13:34:28

-- Verificando os comandos DDL's gerados
C:\>type ddl.sql
  -- CONNECT SCOTT
  -- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT
('USERENV','CURRENT_SCHEMA'),
export_db_name=>'XE', inst_scn=>'372450');
COMMIT;
END;
/

  -- SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."T1"
(    "ID" NUMBER,
"NOME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";

CREATE TABLE "SCOTT"."T2"
(    "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";

CREATE TABLE "SCOTT"."MVIEW_T1"
(    "ID" NUMBER,
"NOME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";

  -- SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT SELECT ON "SCOTT"."T1" TO "ADAM";

GRANT UPDATE ON "SCOTT"."T1" TO "ADAM";

  -- SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;

ALTER INDEX "SCOTT"."PK_T1" NOPARALLEL;

CREATE INDEX "SCOTT"."I_T1_NOME" ON "SCOTT"."T1" ("NOME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;

ALTER INDEX "SCOTT"."I_T1_NOME" NOPARALLEL;

CREATE UNIQUE INDEX "SCOTT"."PK_T11" ON "SCOTT"."MVIEW_T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;

ALTER INDEX "SCOTT"."PK_T11" NOPARALLEL;

  -- SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;

ALTER TABLE "SCOTT"."MVIEW_T1" ADD CONSTRAINT "PK_T11" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;

  -- SCHEMA_EXPORT/TABLE/COMMENT
COMMENT ON MATERIALIZED VIEW "SCOTT"."MVIEW_T1"  IS
'snapshot table for snapshot SCOTT.MVIEW_T1';

  -- SCHEMA_EXPORT/VIEW/VIEW
CREATE  FORCE VIEW "SCOTT"."VIEW_T1_NOME" ("NOME") AS
SELECT NOME FROM T1;

  -- SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE;

  -- SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "SCOTT"."TRG_BI_T1"
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
 :NEW.ID := DBMS_RANDOM.RANDOM;
END;
/

ALTER TRIGGER "SCOTT"."TRG_BI_T1" ENABLE;

ALTER TRIGGER "SCOTT"."TRG_BI_T1"
COMPILE
PLSQL_OPTIMIZE_LEVEL = 2
PLSQL_CODE_TYPE=  INTERPRETED;

  -- SCHEMA_EXPORT/MATERIALIZED_VIEW
CREATE MATERIALIZED VIEW "SCOTT"."MVIEW_T1" USING
("MVIEW_T1", (8, 'XE', 1, 0, 0, "SCOTT", "T1", '2008-07-18 13:32:43', 0, 14796,
'1950-01-01 12:00:00', '', 0, 372263, 0, NULL, (1, "ID", "ID", 0, 321, 0)),
2097249, 8, ('1950-01-01 12:00:00', 4, 0, 0, 372263, 0, 0, 2, NULL, NULL))
REFRESH FORCE WITH PRIMARY KEY AS
SELECT "T1"."ID" "ID","T1"."NOME" "NOME" FROM "T1" "T1";

ALTER MATERIALIZED VIEW "SCOTT"."MVIEW_T1" COMPILE;

Anunciado exame SQL adicional como requisito para obtenção da certificação Oracle Database 10g OCA

Olá,

Recentemente, o Programa de Certificação da Oracle, anunciou um novo exame como requisito para obtenção da certificação Oracle Database 10g OCA. Portanto, àqueles que estão pensando em se tornar um profissional OCA 10g, é bom se apressarem. Neste artigo, eu apenas transcrevi o conteúdo deste anúncio (do inglês para o português), segundo o meu entendimento, de forma a ajudar àqueles que estão pensando em se tornar um profissional OCA 10g. Para acessar o texto original, acesse este link. Outras informações sobre a certificação Oracle, poderão ser encontradas no artigo de Outubro/2007.

Programa de Certificação Oracle

Anúncio de um novo exame SQL adicionado como requisito para obtenção da certificação Oracle Database 10g OCA

6 de Junho de 2008

Em 1 dezembro de 2008, o Programa de Certificação Oracle irá introduzir um novo exame SQL como requisito para obtenção do título Oracle Database 10g Administrator Certified Associate.

Além do exame Oracle Database 10g: Administration I (1Z0-042), os candidatos também serão obrigados a passar em um segundo exame adicional para obter a certificação. Este exame adicional poderá ser qualquer um dos três seguintes exames Oracle SQL listados abaixo:

Introdução ao Oracle9i: SQL (1Z0-007)
Oracle Database 11g: SQL Fundamentals I (1Z1-051)
Oracle Database SQL Expert (1Z0-047)

Atenção: Não há exigências de realização de cursos oficiais para a obtenção de certificação OCA.

Se você está atualmente em processo para obtenção do seu certificado Oracle 10g OCA, continuaremos a aceitar o único exame existente (1Z0-042) até o dia 30 de novembro de 2008. O novo requisito apenas produzirá efeitos a partir do dia 1 de dezembro de 2008. Todas as certificações obtidas no âmbito do exame atual, continuarão válidas de acordo com as regras que regem a sua validade. Todos os profissionais Oracle 10g OCA's que já obtiveram suas certificações no âmbito do exame atual serão capazes de atualizar as suas certificações para o Oracle 10g OCP através dos mesmos exames já existentes. Portanto, etapas adicionais não serão necessárias.

Por favor, visite a página a seguir para maiores detalhes: Oracle Certification Program Oracle Database 10g Administrator

Se você tem alguma dúvida sobre a nova certificação DBA 10g, entre em contato com a Oracle ocpexam_ww@oracle.com. Além disso, você poderá visitar o site de Programa de Certificação da Oracle em http://www.oracle.com/education/certification para mais informações. Para encontrar informações relativas à formação na sua área visite: http://www.oracle.com/education.

PERGUNTAS

Eu sou um DBA OCA 10g. Eu obtive a minha certificação realizando apenas o exame 1Z0-042. Precisarei realizar o exame novamente ou prestar um dos exames SQL para manter a minha atual certificação atualizada?

Não. O candidato que já detém a certificação OCA 10g, não precisará realizar nenhum exame complementar para manter a sua certificação atualizada.

Eu sou um DBA OCA 10g. Eu obtive a minha certificação realizando apenas o exame 1Z0-042. Precisarei preencher eventuais requisitos além do requisitos "hands on course" e o exame 1Z0-043, a fim de atualizar a minha certificação de OCA 10g para OCP 10g?

Não. Todos os candidatos OCA's 10g, independentemente se eles realizaram um ou mais exames, são elegíveis para atualizarem as suas certificações para OCP 10g ao completar os requisitos "hands on course" e aprovação no exame 1Z0-043.

Por qual motivo o Programa de Certificação Oracle tomou a decisão de adicionar mais este segundo exame para obtenção do certificado OCA 10g?

Na Oracle, freqüentemente realizamos profundas revisões de nosso programa de certificação para garantir que estaremos sempre oferecendo os melhores resultados para os nossos clientes e candidatos. O papel fundamental que a linguagem SQL desempenha na profissão de um DBA Oracle 10g, faz com que a mesma seja um componente altamente importante para obtenção da certificação.

Se eu tiver que realizar os dois exames para me tornar certificado, haverá alguma relação entre o primeiro o segundo exame?

Os certificados Oracle são emitidos com base em requisitos de certificação em vigor no momento da conclusão do último requisito. Na Oracle, poderemos atualizar os requisitos de certificação de modo a garantir que atenderemos não só as necessidades da comunidade de certificação Oracle, mas também a de nossos clientes.

Existem quaisquer exceções para a realização do segundo exame?

Não. Após o dia 30 de novembro de 2008, não existirão quaisquer exceções para a realização do segundo exame. Se você marcar o exame 1Z0-042 antes do dia 30 de novembro de 2008, mas não o realizar até o dia 30 de novembro de 2008, então será necessário realizar ambos os exames a fim de se tornar um DBA OCA 10g. Portanto, a certificação deverá ser concluída até o dia 30 de novembro de 2008.

Eu passei no exame 1Z0-042 antes do dia 30 de novembro de 2008, mas eu ainda não recebi o meu certificado. Será necessário realizar um dos exames SQL adicionais, a fim de me tornar um profissional OCA 10g?

Não. Qualquer candidato que passe no exame 1Z0-042 até o dia 30 de novembro de 2008, passará a ser um profissional OCA, mesmo que o certificado chegue pelo correio após o dia 30 de novembro de 2008.

Índices invisíveis? Abordando uma nova funcionalidade do Oracle 11g

Olá,

Imagine uma tabela e um índice. Se quisermos que durante o processamento de uma sentença SQL o otimizador ignore o índice ao acessar os dados da tabela, teremos que dropar o índice ou marcá-lo como inutilizável. Se utilizarmos a primeira alternativa, e caso houver uma degradação de performance da query pelo fato do índice ter sido dropado, e quisermos que o mesmo seja novamente utilizado, teremos que recriá-lo (CREATE INDEX ...). Agora, se utilizarmos a segunda alternativa, então teremos apenas que reconstruí-lo (ALTER INDEX ... REBUILD). A partir do Oracle 10g, o parâmetro skip_unusable_indexes que pode ser modificado tanto em nível de sessão como em nível de sistema, foi introduzido de forma que se o mesmo estiver setado como TRUE (valor default), o otimizador CBO irá ignorar qualquer índice marcado como inutilizável (unusuable) suprimindo o erro ORA-01502 caso o Oracle tente acessar o índice. Este recurso é útil porque não teremos mais que dropar um índice, mas apenas marcá-lo como inutilizável. A desvantagem desta abordagem é que, se quiséssemos que o otimizador enxergasse novamente o índice, teríamos que reconstruí-lo (rebuild), o que poderia causar uma overhead desnecessária.

A partir do Oracle 11g, um novo recurso foi adicionado ao gerenciamento de índices permitindo que um índice fique invisível ou não ao otimizador. Caso uma degradação de performance seja notada ao marcar um índice como invisível, poderemos então, marcá-lo novamente como visível sem precisar ter que reconstruí-lo. Portanto, no Oracle 11g um índice marcado como invisível será invisível ao otimizador, a não ser que o parâmetro optimizer_use_invisible_indexes que pode ser modificado tanto em nível de sessão (ALTER SESSION ...) como em nível de sistema (ALTER SYSTEM ...), seja setado para TRUE. Aliás, o valor padrão deste parâmetro é FALSE. Abaixo, irei demonstrar tanto a abordagem de marcar um índice como inutilizável, como a de marcar um índice como invisível no Oracle 11g. Vamos então a um exemplo prático:

-- Irei criar uma tabela de teste chamada T1
SQL> create table t1 (id number);

Tabela criada.

-- irei criar um índice no campo ID
SQL> create index i_t1 on t1 (id);

Índice criado.

-- Irei popular a tabela com valores aleatórios
SQL> insert into t1 select level from dual
  2  connect by level <= 10000;   
10000 linhas criadas.
SQL> commit;
Commit concluído.

-- Verificando a configuração atual
SQL> show parameter skip_unusable_indexes

NAME                            TYPE        VALUE
------------------------------- ----------- -----------------------------
skip_unusable_indexes           boolean     TRUE


-- Gerando o plano de execução do SQL
SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 2966378588

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Podemos ver no plano de execução acima, que o otimizador utilizou o índice I_T1. O que acontecerá se marcamos o índice como inutilizável e setarmos o parâmetro skip_unusable_indexes para FALSE?

SQL> alter session set skip_unusable_indexes = FALSE;

Sessão alterada.

SQL> alter index i_t1 unusable;

Índice alterado.

-- Verificando o estado do índice
SQL> select index_name,status from user_indexes where index_name = 'I_T1';

INDEX_NAME                     STATUS
------------------------------ --------
I_T1                           UNUSABLE

SQL> explain plan for
  2  select * from t1 where id = 100;

explain plan for
*
ERRO na linha 1:
ORA-01502: índice 'TEST.I_T1' ou a sua partição está em estado não-utilizável

Podemos ver acima que ao tentar acessar o índice, o Oracle emitiu o erro ORA-01502. Então, vamos setar novamente o parâmetro skip_unusable_indexes para TRUE:

SQL> alter session set skip_unusable_indexes = TRUE;

Sessão alterada.

SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Podemos ver acima, que o otimizador não considerou o índice I_T1 no plano de execução gerado, o que significa que o índice foi totalmente ignorado pelo mesmo. Se quisermos que o índice seja novamente utilizado pelo otimizador, teremos que reconstruir o índice.

-- Reconstruindo o índice
SQL> alter index i_t1 rebuild;

Índice alterado.

-- Verificando o estado do índice
SQL> select index_name,status from user_indexes where index_name = 'I_T1';

INDEX_NAME                     STATUS
------------------------------ --------
I_T1                           VALID

Agora, irei mostrar o conceito de índices invisíveis no Oracle 11g, na qual não precisaremos mais nos preocupar em reconstruir os índices inválidos ou marcados como inutilizáveis.

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

-- Verificando a configuração atual
SQL> show parameter visible

NAME                               TYPE        VALUE
---------------------------------- ----------- --------------------------
optimizer_use_invisible_indexes    boolean     FALSE

-- Verificando a visibilidade do índice
SQL> select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           VISIBLE

Podemos ver no resultado acima, que uma nova coluna VISIBILITY foi incluída na view USER_INDEXES. Esta nova coluna introduzida nas views *_INDEXES, mostra se o índice em questão está visível ou não ao otimizador CBO.

-- Marcando o índice como invisível
SQL> alter index i_t1 invisible;

Índice alterado.

-- Verificando a visibilidade do índice
SQL> select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           INVISIBLE

-- Gerando o plano de execução
SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Podemos ver acima, que o índice foi totalmente ignorado pelo otimizador ocasionando uma varredura integral (FTS) na tabela. Se mesmo assim ainda quisermos que o índice fique visível ao otimizador mesmo estando invisível, teremos ainda a opção de setar o parâmetro optimizer_use_invisible_indexes para TRUE.

SQL> alter session set optimizer_use_invisible_indexes = TRUE;

Sessão alterada.

SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 2966378588

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Agora, independente do valor do parâmetro optimizer_use_invisible_indexes, se quisermos que o índice volte a ser visível ao otimizador, precisaremos apenas marcar o índice como visível sem a necessidade de ter que reconstruí-lo:

-- Marcando o índice como visível
SQL> alter index i_t1 visible;

Índice alterado.

-- Verificando a visibilidade do índice
SQL> select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           VISIBLE

Em resumo, esta nova funcionalidade nos permitirá testar a utilização de um novo índice sem afetar o plano de execução para as sentenças SQL existentes, ou até mesmo, a de testar o efeito da execução de uma sentença SQL ao dropar um índice existente sem a necessidade realmente de ter que dropá-lo.