Martin's questions

Qual é a maneira mais simples de fazer login como usuário X e ver todas as tabelas,... do esquema Y como se pertencessem a X?

Com um banco de dados Oracle 11g, gostaria de fazer login no banco de dados usando o usuário X, mas todos os dados (tabelas, exibições, procedimentos, ...) seriam armazenados no Schema Y.

Todo o código que acessa as tabelas tem a garantia de usar nomes não qualificados ( TBL_NAME, não Y.TBL_NAME)

A busca por "schema alias" aponta para CREATE SYNONYM, mas isso teria que ser feito para cada objeto DML, certo? E como só tenho chamadas não qualificadas, parece que um gatilho de login ALTER SESSION SET CURRENT_SCHEMA = ...;deve funcionar?

Quaisquer outras opções?

O que é o antipadrão de ID e por que/em quais circunstâncias ele é considerado um antipadrão ?

O antipadrão de id está tendo uma coluna de ID exclusiva em cada tabela. O ID é gerado para cada novo registro. Por que isso é um antipadrão? – Andrew Wolfe 11 minutos atrás

Isenção de responsabilidade: não sou um DBA nem li completamente os documentos do RMAN de capa a capa, portanto, isso pode estar bem coberto, mas não consegui encontrá-lo.

A questão é bem simples, qual a diferença entre esses dois comandos:

• delete copy of archivelog all completed before 'SYSDATE-7';
• delete archivelog all completed before 'SYSDATE-7';

O que é excluído de onde e em quais circunstâncias ao executá-los no RMAN?

Informações possivelmente relevantes: (?)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5785
Next log sequence to archive   5787
Current log sequence           5787
SQL> select * from v$recovery_file_dest;

NAME             SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------- ----------- ---------- ----------------- -----
e:\oracle\backup 2.1475E+11  4.7820E+10 4.6097E+10        1246

-

RMAN> show retention policy;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ****** are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

Não se trata de ler um plano de explicação.

Não se trata de adicionar dicas ou medir o desempenho de uma consulta específica.

Sempre me perguntei se é possível para um determinado plano de explicação para uma consulta (ou um autotrace da consulta) "perguntar" ao Otimizador por que ele escolheu esse plano em vez de outros planos disponíveis para ele.

• Existe uma maneira de pedir ao banco de dados para exibir uma comparação de diferentes planos que considerou para a mesma consulta?
• Existe uma maneira de visualizar quais cálculos foram incluídos no custo exibido para um plano? (Por exemplo, o que o Optimizer assumiu wrt. acesso ao disco vs. coisas na memória, etc.)

Justificativa da pergunta: Com o EXPLAIN PLAN como eu conheço, quando o banco de dados escolhe um determinado plano de execução, e este é insuficiente/muito lento/... no primeiro momento um DBA ou desenvolvedor ad hoc não sabe: Se existe havia alguma alternativa a este plano e, além disso, não sabe por que, se o desenvolvedor conhece alternativas (por exemplo, um índice não utilizado), as alternativas não foram usadas.

Pergunta conceitual: As consultas individuais são mais rápidas do que as junções ou: Devo tentar espremer todas as informações que desejo no lado do cliente em uma instrução SELECT ou apenas usar quantas parecer conveniente?

TL;DR : Se minha consulta associada demorar mais do que a execução de consultas individuais, isso é minha culpa ou isso é esperado?

Em primeiro lugar, não sou muito experiente em banco de dados, então pode ser apenas eu, mas notei que quando tenho que obter informações de várias tabelas, é "muitas vezes" mais rápido obter essas informações por meio de várias consultas em tabelas individuais (talvez contendo uma junção interna simples) e remendar os dados no lado do cliente para tentar escrever uma consulta unida (complexa) onde posso obter todos os dados em uma consulta.

Eu tentei colocar um exemplo extremamente simples juntos:

SQL Fiddle

Configuração do esquema :

CREATE TABLE MASTER 
( ID INT NOT NULL
, NAME VARCHAR2(42 CHAR) NOT NULL
, CONSTRAINT PK_MASTER PRIMARY KEY (ID)
);

CREATE TABLE DATA
( ID INT NOT NULL
, MASTER_ID INT NOT NULL
, VALUE NUMBER
, CONSTRAINT PK_DATA PRIMARY KEY (ID)
, CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID)
);

INSERT INTO MASTER values (1, 'One');
INSERT INTO MASTER values (2, 'Two');
INSERT INTO MASTER values (3, 'Three');

CREATE SEQUENCE SEQ_DATA_ID;

INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);

Consulta A :

select NAME from MASTER
where ID = 1

Resultados :

| NAME |
--------
|  One |

Pergunta B :

select ID, VALUE from DATA
where MASTER_ID = 1

Resultados :

| ID | VALUE |
--------------
|  1 |   1.3 |
|  2 |   1.5 |
|  3 |   1.7 |

Consulta C :

select M.NAME, D.ID, D.VALUE 
from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID
where M.ID = 1

Resultados :

| NAME | ID | VALUE |
---------------------
|  One |  1 |   1.3 |
|  One |  2 |   1.5 |
|  One |  3 |   1.7 |

Claro, não medi nenhum desempenho com estes, mas pode-se observar:

• A consulta A+B retorna a mesma quantidade de informações utilizáveis ​​que a consulta C.
• A+B tem que retornar 1+2x3==7 "Células de Dados" para o cliente
• C tem que retornar 3x3==9 "Células de Dados" para o cliente, porque com a junção eu naturalmente incluo alguma redundância no conjunto de resultados.

Generalizando a partir disso (por mais improvável que seja):

Uma consulta combinada sempre deve retornar mais dados do que as consultas individuais que recebem a mesma quantidade de informações. Como o banco de dados precisa juntar os dados, para grandes conjuntos de dados , pode-se supor que o banco de dados precisa fazer mais trabalho em uma única consulta unida do que nas consultas individuais, pois (pelo menos) precisa retornar mais dados ao cliente.

A partir disso, quando observo que dividir uma consulta do lado do cliente em várias consultas produz um desempenho melhor, esse é apenas o caminho a seguir ou significaria que estraguei a consulta unida?

Um de nossos produtos suporta Oracle e SQL Server como back-end de banco de dados. Temos um cliente que deseja mudar de um back-end Oracle para o Microsoft SQL Server, o que não é uma transição típica para nós.

Qual é a maneira mais fácil de obter todos os dados de todo o Oracle Schema no banco de dados do SQL Server?

O esquema contém apenas tabelas antigas e nada extravagantes. Pode haver um ou dois procedimentos armazenados que não teremos problemas para migrar manualmente.

Eu poderia usar o SQLDeveloper da Oracle para exportar os dados da tabela como CREATEe INSERTinstruções, mas eles não corresponderão à sintaxe usada no SQL Server e não estou ansioso para corrigir manualmente os erros de sintaxe.

Eu sei como o conjunto de caracteres do banco de dados ( NLS_CHARACTERSETin select * from v$nls_parameters;) e o conjunto de caracteres do cliente (a configuração do ambiente do cliente NLS_LANG) interagem.

O que não consigo descobrir, no entanto, é como ou se posso determinar, para uma sessão estabelecida , o que a Oracle pensa que é o conjunto de caracteres do cliente atual.

Isso é possível em tudo?

Nota: SELECT * FROM NLS_SESSION_PARAMETERS;não inclui o conjunto de caracteres (em 10g2).

Para deixar absolutamente claro o que eu gostaria de realizar:

1. NLS_LANG é definido no ambiente do cliente para um valor arbitrário (por exemplo GERMAN_GERMANY.WE8MSWIN1252)
2. O aplicativo de banco de dados[*] inicia e estabelece uma conexão/sessão com o banco de dados Oracle.
3. O aplicativo de banco de dados[*] deseja "perguntar" ao Oracle (não ao ambiente do sistema operacional) qual é o conjunto de caracteres do cliente que o Oracle assumirá.

[*]: Se o aplicativo db for sqlplus, o exemplo ficaria assim:

...
sqlplus /nolog
connect user/pass@example
*magic command*;
   CLIENT CHARACTERSET = ...

A nota de Jack em sua resposta levanta dois pontos importantes:

• Com a Oracle, que faz a tradução do conjunto de caracteres. É o código da biblioteca do cliente ou é feito no lado do servidor?
• Como parece que é o cliente, o cliente precisaria expor essa configuração - o que a lib/tool ​​do cliente assume que é essa configuração. Existe alguma biblioteca/ferramenta do cliente Oracle (sqlplus, OCI/OCCI, Pro*C, ...) que possa ser consultada para saber o que essa configuração é?

No Oracle (e provavelmente em outros lugares), a execução de uma instrução ALTER TABLE fará um commit implícito na transação atual.

Temos uma ferramenta (escrita em Java) que deve modificar um esquema por:

• adicionando algumas colunas
• removendo algumas colunas
• atualizando uma tabela de descrição com o novo layout de esquema

A ferramenta conforme escrita exibirá o layout do esquema atual para o usuário e permitirá que ele o modifique. (Basicamente, adicionando ou removendo colunas de "atributos" personalizadas para algumas tabelas) Quando estiver satisfeito, ele poderá aplicar suas alterações.

_{Observe: O layout básico do esquema e o fato de que você precisa ALTER TABLE para alterar algumas coisas é predefinido por um padrão e não pode ser alterado por nós, pois outras ferramentas não funcionariam mais.}

O problema agora é que não podemos executar essas alterações em uma única transação, pois, AFAIK, não é possível fazer várias instruções ALTER TABLE em uma transação.

Que opções temos para "reverter" ao estado inicial se algo der errado ao aplicar as alterações?

Nota: Alguém aqui propôs PONTO DE RESTAURAÇÃO + FLASHBACK - é uma boa ideia chamar isso a partir desta ferramenta (Java)? (Não controlamos totalmente a instância do banco de dados em alguns sites onde a ferramenta deve ser usada.)

Nota: Oracle 10g2 e superior

Pesquisando por aí parece haver relatórios mistos se o tamanho de uma VARCHAR2coluna no Oracle afeta o desempenho ou não.

Gostaria de dar VARCHARuma pequena reviravolta na questão do tamanho e espero obter algumas informações sobre isso:

Dado (multilinha) campos de texto livre ( não coisas curtas como nomes) que você deseja armazenar em um banco de dados (Oracle), há algum ponto (desempenho de escrita ou outro) em não maximizar a VARCHARcapacidade ( VARCHAR2(4000)no Oracle), mas escolher um valor menor, como 1024 ou 512, porque provavelmente será suficiente em 98% dos casos.

Existe alguma diferença conceitual entre uma SERIALIZABLETransação e uma Consulta Flashback?

Isenção de responsabilidade: por favor, tenha paciência comigo como alguém que usa bancos de dados apenas uma pequena fração de seu tempo de trabalho. (Na maioria das vezes eu faço programação C++ no meu trabalho, mas todo mês preciso pesquisar/consertar/adicionar algo em um banco de dados Oracle.)

Eu precisei repetidamente escrever consultas SQL complexas, tanto para consultas ad-hoc quanto para consultas incorporadas a aplicativos, onde grandes partes das consultas eram apenas "código" repetido.

Escrever tais abominações em uma linguagem de programação tradicional causaria sérios problemas, mas eu ( eu ) ainda não consegui encontrar nenhuma técnica decente para evitar a repetição do código de consulta SQL.

Editar: 1º, quero agradecer aos respondentes que forneceram excelentes melhorias ao meu exemplo original . No entanto, esta questão não é sobre o meu exemplo. É sobre repetitividade em consultas SQL. Como tal, as respostas ( JackP , Leigh ) até agora fazem um ótimo trabalho em mostrar que você pode reduzir a repetitividade escrevendo consultas melhores . No entanto, mesmo assim, você enfrenta alguma repetitividade que aparentemente não pode ser removida: isso sempre me incomodou com o SQL. Em linguagens de programação "tradicionais", posso refatorar bastante para minimizar a repetitividade no código, mas com o SQL parece que não há ferramentas (?) Que permitam isso, exceto para escrever uma instrução menos repetitiva para começar.

Observe que removi a tag Oracle novamente, pois ficaria genuinamente interessado se não houvesse banco de dados ou linguagem de script que permitisse algo mais.

Aqui está uma joia que montei hoje. Ele basicamente informa a diferença em um conjunto de colunas de uma única tabela. Por favor, dê uma olhada no código a seguir, esp. a consulta grande no final. Continuo abaixo.

--
-- Create Table to test queries
--
CREATE TABLE TEST_ATTRIBS (
id NUMBER PRIMARY KEY,
name  VARCHAR2(300) UNIQUE,
attr1 VARCHAR2(2000),
attr2 VARCHAR2(2000),
attr3 INTEGER,
attr4 NUMBER,
attr5 VARCHAR2(2000)
);

--
-- insert some test data
--
insert into TEST_ATTRIBS values ( 1, 'Alfred',   'a', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 2, 'Batman',   'b', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 3, 'Chris',    'c', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values ( 4, 'Dorothee', 'd', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 5, 'Emilia',   'e', 'Barfoo', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 6, 'Francis',  'f', 'Barfoo', 99, 44, 'e');
insert into TEST_ATTRIBS values ( 7, 'Gustav',   'g', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 8, 'Homer',    'h', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 9, 'Ingrid',   'i', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values (10, 'Jason',    'j', 'Bob',    33, 44, 'e');
insert into TEST_ATTRIBS values (12, 'Konrad',   'k', 'Bob',    66, 44, 'e');
insert into TEST_ATTRIBS values (13, 'Lucas',    'l', 'Foobar', 99, 44, 'e');

insert into TEST_ATTRIBS values (14, 'DUP_Alfred',   'a', 'FOOBAR', 33, 44, 'e');
insert into TEST_ATTRIBS values (15, 'DUP_Chris',    'c', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values (16, 'DUP_Dorothee', 'd', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values (17, 'DUP_Gustav',   'X', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values (18, 'DUP_Homer',    'h', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values (19, 'DUP_Ingrid',   'Y', 'foo',    99, 44, 'e');

insert into TEST_ATTRIBS values (20, 'Martha',   'm', 'Bob',    33, 88, 'f');

-- Create comparison view
CREATE OR REPLACE VIEW TA_SELFCMP as
select 
t1.id as id_1, t2.id as id_2, t1.name as name, t2.name as name_dup,
t1.attr1 as attr1_1, t1.attr2 as attr2_1, t1.attr3 as attr3_1, t1.attr4 as attr4_1, t1.attr5 as attr5_1,
t2.attr1 as attr1_2, t2.attr2 as attr2_2, t2.attr3 as attr3_2, t2.attr4 as attr4_2, t2.attr5 as attr5_2
from TEST_ATTRIBS t1, TEST_ATTRIBS t2
where t1.id <> t2.id
and t1.name <> t2.name
and t1.name = REPLACE(t2.name, 'DUP_', '')
;

-- NOTE THIS PIECE OF HORRIBLE CODE REPETITION --
-- Create comparison report
-- compare 1st attribute
select 'attr1' as Different,
id_1, id_2, name, name_dup,
CAST(attr1_1 AS VARCHAR2(2000)) as Val1, CAST(attr1_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr1_1 <> attr1_2
or (attr1_1 is null and attr1_2 is not null)
or (attr1_1 is not null and attr1_2 is null)
union
-- compare 2nd attribute
select 'attr2' as Different,
id_1, id_2, name, name_dup,
CAST(attr2_1 AS VARCHAR2(2000)) as Val1, CAST(attr2_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr2_1 <> attr2_2
or (attr2_1 is null and attr2_2 is not null)
or (attr2_1 is not null and attr2_2 is null)
union
-- compare 3rd attribute
select 'attr3' as Different,
id_1, id_2, name, name_dup,
CAST(attr3_1 AS VARCHAR2(2000)) as Val1, CAST(attr3_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr3_1 <> attr3_2
or (attr3_1 is null and attr3_2 is not null)
or (attr3_1 is not null and attr3_2 is null)
union
-- compare 4th attribute
select 'attr4' as Different,
id_1, id_2, name, name_dup,
CAST(attr4_1 AS VARCHAR2(2000)) as Val1, CAST(attr4_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr4_1 <> attr4_2
or (attr4_1 is null and attr4_2 is not null)
or (attr4_1 is not null and attr4_2 is null)
union
-- compare 5th attribute
select 'attr5' as Different,
id_1, id_2, name, name_dup,
CAST(attr5_1 AS VARCHAR2(2000)) as Val1, CAST(attr5_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr5_1 <> attr5_2
or (attr5_1 is null and attr5_2 is not null)
or (attr5_1 is not null and attr5_2 is null)
;

Como você pode ver, a consulta para gerar um "relatório de diferenças" usa o mesmo bloco SQL SELECT 5 vezes (pode facilmente ser 42 vezes!). Isso me parece absolutamente com morte cerebral (posso dizer isso, afinal eu escrevi o código), mas não consegui encontrar nenhuma boa solução para isso.

• Se isso fosse uma consulta em algum código de aplicativo real, eu poderia escrever uma função que remenda essa consulta como uma string e, em seguida, executaria a consulta como uma string.

  • -> Construir strings é horrível e horrível de testar e manter. Se o "código do aplicativo" for escrito em uma linguagem como PL/SQL, parece tão errado que dói.

• Como alternativa, se usado em PL/SQL ou algo semelhante, acho que há alguns meios processuais para tornar essa consulta mais fácil de manter.

  • -> Desdobrar algo que pode ser expresso em uma única consulta em etapas processuais apenas para evitar a repetição de código também parece errado.

• Se essa consulta fosse necessária como uma exibição no banco de dados, então - pelo que entendi - não haveria outra maneira senão manter a definição de exibição conforme postei acima. (!!?)

  • -> Na verdade, tive que fazer alguma manutenção em uma definição de visualização de 2 páginas, uma vez que não estava muito longe da declaração acima. Obviamente, alterar qualquer coisa nessa exibição exigia uma pesquisa de texto regexp na definição da exibição para saber se a mesma subinstrução foi usada em outra linha e se precisava ser alterada lá.

Então, como diz o título - que técnicas existem para evitar ter que escrever tais abominações?

Parece que me lembro que (no Oracle) há uma diferença entre proferir select count(*) from any_tablee select count(any_non_null_column) from any_table.

Quais são as diferenças entre essas duas declarações, se houver?