sjk's questions

Eu tenho um usuário no banco de dados Oracle 12c que será usado para executar exportação e importação de bomba de dados completa. Gostaria de restringir logons desse usuário para localhost.

Eu tentei criar o gatilho de logon:

create or replace trigger logon_trigger 
  after logon on database
declare
  host varchar2(50);
begin 
  host := trim(lower(sys_context('USERENV', 'HOST')));
  if lower(user) = '<data pump user>' then
    if host <> '<hostname>' then
      raise_application_error(-20000, 'Login not allowed');
    end if;
  end if;
end;
/

Eu posso ver na trilha de auditoria que esse gatilho foi acionado (ORA-20000: Login não permitido), mas o usuário ainda tem permissão para fazer logon do host remoto.

Essa restrição funciona para usuários normais, mas não para o usuário da bomba de dados. Eu suspeito que isso tenha algo a ver com as funções exp_full_database e imp_full_database.

Existe alguma maneira de fazer esse gatilho funcionar ou devo usar a autenticação do sistema operacional local?

Eu tenho um servidor com três controladores de disco conectados diretamente. Cada um deles pode fornecer redundância interna e IO-striping em discos, mas estou me perguntando qual é a melhor maneira de configurar todo o sistema.

Seria sensato alocar três arquivos de dados para cada espaço de tabela, sendo cada arquivo de dados criado em uma montagem fornecida por um único controlador? O Oracle parece distribuir os dados de maneira um tanto uniforme entre os arquivos de dados ao fazer inserções, mas isso seria suficiente para consultas?

Estamos principalmente preocupados com o desempenho de leitura para consultas de agregação única (armazém de dados) e queremos ler os dados em paralelo de cada controlador.

Uma resposta seria usar ASM ou LVM para fazer o striping, o Oracle veria apenas um ponto de montagem onde armazenar arquivos de dados. Temos uma configuração com ASM + número de discos JBOD; ele funciona razoavelmente bem, mas o ASM tem seus próprios problemas e estamos considerando alternativas.

Atualmente estou usando o formato de data:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI';

O problema (cosmético) é que sempre mostra a hora mesmo que seja zero, por exemplo: "27.03.2014 00:00". É possível ocultar 00:00 com NLS_DATE_FORMAT, mas exibir a hora quando não é zero?

Sei que não é uma boa ideia para nenhum tipo de processamento automático, mas é apenas para uso interativo. Sei que posso formatar colunas individuais na consulta, mas estou interessado em uma solução mais geral.

Eu verifiquei a documentação do modelo de formato, mas não encontrei nenhum parâmetro relevante, então talvez não seja possível.

Tenho uma tabela com coluna numérica:

create table test (a number(2,0));

Em seguida, desejo criar uma coluna virtual com base na coluna a:

alter table test add (b varchar2(3) as ('A:'||to_char(a)));

Isso resulta em erro:

ORA-12899: value too large for column "B" (actual: 12, maximum: 42)

Por que recebo esse erro? Isso funciona se eu substituir to_char por substr, mas estou curioso para saber como o Oracle determina os valores "reais" e "máximos"?

Suspeito que o valor máximo esteja relacionado à precisão máxima dos tipos numéricos (Oracle ignorando a precisão do tipo de dados), mas ainda não entendi muito bem esse erro. O conjunto de caracteres é UTF-8.

No momento, estou testando os procedimentos de restauração do Oracle 11g a partir de backups do RMAN. Posso restaurar instâncias para outro host, mas o problema que estou tendo é que, assim que abro a instância restaurada, os trabalhos do agendador são iniciados imediatamente.

Posso desabilitar o agendador executando:

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

Mas não posso fazer isso até que a instância seja iniciada.

Existe uma maneira de desativar os trabalhos antes de iniciar a instância?

Eu tenho um requisito para auditar todas as consultas SELECT (por acesso) em tabelas especificadas por usuários especificados.

Tanto quanto eu posso dizer, isso não pode ser feito com a auditoria normal; ele suporta apenas a auditoria de todas as consultas por usuários especificados ou todas as consultas em tabelas especificadas (por qualquer usuário). Isso é um problema porque existem contas do sistema que gerariam muita auditoria e precisam ser excluídas da auditoria.

Isso deixa a auditoria refinada, onde é possível fazer isso especificando o predicado apropriado para o parâmetro audit_condition em dbms_fga.add_policy . No entanto, o FGA tem o efeito infeliz de gravar linhas duplicadas na trilha de auditoria quando a consulta paralela é usada; não é desejável quando o objetivo é minimizar as informações de auditoria.

Atualmente, estou planejando usar o FGA e remover com frequência as linhas duplicadas. Existe alguma maneira melhor de implementar essa estratégia de auditoria?

Posso fazer um backup do diretório inicial do Oracle (com cp -r) antes da atualização enquanto as instâncias que usam o home estão em execução ou é necessário desligar as instâncias primeiro?

Os arquivos de rastreamento/logs de alerta estão em diferentes estruturas de diretório e todos os arquivos de dados, arquivos de redo log, arquivos de controle e spfiles estão no ASM.

Eu tenho uma grande tabela no banco de dados Oracle 11g que contém dados históricos de vários anos, então gostaria de particioná-la por ano. O problema é que a tabela tem várias colunas de data e todas são usadas em consultas, então não posso simplesmente escolher uma coluna de data e usá-la como chave de partição.

A maioria das datas de tempo estão próximas umas das outras, então criei partições para cada ano, além de uma partição "overflow" que contém as linhas que cruzam o limite do ano. Aqui está um exemplo simplificado:

create table t (
  start_year int,
  end_year int,
  partition_year int as (case when start_year=end_year then start_year else 0 end),
  data blob 
)
partition by range(partition_year) (
  partition poverflow values less than (1000),
  partition p2000 values less than (2001),
  partition p2001 values less than (2002),
  partition p2002 values less than (2003),
  partition p2003 values less than (2004),
  partition p2004 values less than (2005)
);

O problema com essa abordagem é que partition_year deve ser referenciado explicitamente em consultas ou a remoção de partição (altamente desejável porque a tabela é grande) não terá efeito. Essa tabela é usada para consultas agregadas ad hoc por vários usuários; Não posso esperar que todos se lembrem dessa lógica.

Isso pode ser resolvido com uma visão

create or replace view v as
select *
from t
where partition_year=start_year 
  and partition_year=end_year 
  and partition_year>1000
union all
select *
from t partition (poverflow);

Agora consultas como esta

select * from v where start_year >= 2003 and end_year <= 2004;

Use as partições corretas (5-6 + 1 no plano abaixo):

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   1 |  VIEW                      | V    |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|*  4 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|   5 |    PARTITION RANGE SINGLE  |      |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004 AND "END_YEAR">=2003 AND 
              "START_YEAR"<=2004 AND "PARTITION_YEAR"<=2004 AND "PARTITION_YEAR"="START_YEAR" AND 
              "PARTITION_YEAR"="END_YEAR")
   6 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004)

O problema é que se eu substituir tipos int por datas, isso não funcionará mais. Tentei extrair o componente do ano das datas e adicionar restrições correspondentes à exibição, mas as partições não foram removidas. Alterar o tipo de partition_year até a data também não ajudou.

Existe alguma maneira de eu ter várias colunas de data em uma tabela e ainda poder usar a remoção de partição?