ORA-01555: snapshot too old: rollback segment number 5 with

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Mensagemem Ter, 05 Mai 2009 11:47 am

Amigos, estou querendo salvar uma consulta que realizo em uma base Oracle em TXT. Como mencionei no titulo exibe esse erro. Conversei com o DBA que dá suporte ao Banco e me falou que a consulta que estou realizando é muito grande e que alguns registros sofrem atualização.

Na essência gostaria de pegar esse arquivo txt e importar para o SQL SERVER, porém todas as outras ferramentas(Wizard, SSIS) não funcionam e exibe o mesmo erro. Segue abaixo a consulta que estou realizando.

Ah a consulta é para pegar a ultima ação de cobrança para cada cpf, portanto essa tabela possui todas as ações sofridas pelo cpf.

Código: Selecionar todos
SELECT ACCOUNT_ID, ACTION_ID, EXECUTION_DATE, STATUS , Launcher_Id
FROM ICS_E_ACTION_LAUNCHER A
WHERE STATUS IN ('ACTIVE','OK')
AND EXISTS (SELECT B.ACCOUNT_ID,MAX(B.EXECUTION_DATE)
FROM ICS_E_ACTION_LAUNCHER B
WHERE A.ACCOUNT_ID = B.ACCOUNT_ID
AND B.STATUS IN ('ACTIVE','OK')
GROUP BY B.ACCOUNT_ID HAVING MAX(B.EXECUTION_DATE) = A.EXECUTION_DATE)
dralves
Localização: rj

Mensagemem Ter, 05 Mai 2009 2:34 pm

Essa consulta está meio confusa.

Acho que com essa modificação o custo dela via melhroar...

Código: Selecionar todos
SELECT ACCOUNT_ID, ACTION_ID, EXECUTION_DATE, STATUS , Launcher_Id
  FROM ICS_E_ACTION_LAUNCHER A
WHERE STATUS IN ('ACTIVE','OK')
   AND EXISTS (SELECT 1
                 FROM ICS_E_ACTION_LAUNCHER B
                WHERE A.ACCOUNT_ID = B.ACCOUNT_ID
                  AND B.STATUS IN ('ACTIVE','OK')
             GROUP BY B.ACCOUNT_ID
           HAVING MAX(B.EXECUTION_DATE) = A.EXECUTION_DATE)


porém, teste também essa query abaixo, que ao meu ver, estaria mais leve e retornaria o mesmo resultado... (levando em conta pegar somente a max execution_date pra cada informacao abaixo

Código: Selecionar todos
SELECT ACCOUNT_ID, ACTION_ID, MAX(EXECUTION_DATE), STATUS , Launcher_Id
FROM ICS_E_ACTION_LAUNCHER B
WHERE STATUS IN ('ACTIVE','OK')
GROUP BY ACCOUNT_ID, ACTION_ID, STATUS , Launcher_Id


ou dessa forma, caso você não queira agrupar por tudo e sim, somente pelo ACCOUNT_ID

Código: Selecionar todos
SELECT ACCOUNT_ID, ACTION_ID, EXECUTION_DATE, STATUS , Launcher_Id
  FROM ICS_E_ACTION_LAUNCHER A
WHERE STATUS IN ('ACTIVE','OK')
   AND (A.ACCOUNT_ID, A.EXECUTION_DATE) IN (SELECT B.ACCOUNT_ID,MAX(B.EXECUTION_DATE)
                                              FROM ICS_E_ACTION_LAUNCHER B
                                             WHERE B.STATUS IN ('ACTIVE','OK')
                                          GROUP BY B.ACCOUNT_ID)


Espero ter ajudado
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Ter, 05 Mai 2009 3:50 pm

Rodrigo,

valeu pela dica mais estou rodando o que você sugeriu mais acredito que irá dar o mesmo erro, visto que o arquivo está carregando aos poucos e a quantidade de registros é muito grande.

Tem alguma ideia de como faz para quebrar essa consulta e ir carregando aos poucos?

Desde já agradeço pela ajuda.
dralves
Localização: rj

Mensagemem Ter, 05 Mai 2009 4:26 pm

O mais certo nesse caso é você trabalhar com range de ID. Tipo..

Código: Selecionar todos
SELECT MAX(ACCOUNT_ID)
  FROM ICS_E_ACTION_LAUNCHER


Depois você faz as consultas com
Código: Selecionar todos
ACCOUNT_ID between 0 and 10000000

proximo range você coloca
Código: Selecionar todos
ACCOUNT_ID between 10000001 and 20000000


e assim por diante... se você pudesse trabalhar com PL/SQL seria fácil resolver isso.. pode? se puder, faz um cursor abrindo todas as ACCOUNT_ID e esse abre outro pegando o max(ACCOUNT_ID).
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Ter, 05 Mai 2009 4:37 pm

Rodrigo estou utilizando o TOAD. Tem algum problema?

você poderia me mostrar um exemplo de como se constroi um curso?

Grande abraço
dralves
Localização: rj

Mensagemem Ter, 05 Mai 2009 5:25 pm

Exemplo pratico

se você tiver permissão, executará tranquilo as querys abaixo.

porém, você pode modifica-la para atender sua necessidade.

Código: Selecionar todos
DECLARE

  CURSOR c1 IS --cursor que abre todos os ids
    SELECT se.SQL_HASH_VALUE
      FROM v$session se
     WHERE se.STATUS = 'ACTIVE';
     
  CURSOR c2 (x NUMBER) IS --cursor que vai abrir seu id e pegar o max dele
    SELECT s.SQL_TEXT
      FROM v$sql s
      WHERE s.HASH_VALUE = x;
     
BEGIN
  FOR reg IN c1 LOOP
    FOR reg2 IN c2(reg.SQL_HASH_VALUE) LOOP
      dbms_output.put_line('Texto sql '||reg2.sql_text);
    END LOOP;
  END LOOP;
END;
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Ter, 05 Mai 2009 6:25 pm

Rodrigo eu não entendi algumas partes do seu código. No meu código eu preciso trazer um campo que é primordial "Action_Id" que significa a ação que o CPF sofreu, porém eu preciso saber somente a ultima.

Bom vamos lá.

Na linha "FOR reg2 IN c2(reg.SQL_HASH_VALUE) "
O que esse loop faz?
Qual é o valor ou campo que devo colocar para "reg.SQL_HASH_VALUE"?


Na linha "dbms_output.put_line('Texto sql '||reg2.sql_text)"

O que é dbms_output.put_line?

O que faz ('Texto sql '||reg2.sql_text)"?

Desculpe pela a insistência, mais estou precisando muito dessa ajuda.
dralves
Localização: rj

Mensagemem Qua, 06 Mai 2009 8:57 am

Sem problemas....

Vamos lá..

desculpe por não comentar melhor o código... vou tentar adequar a sua necessidade.
Código: Selecionar todos
DECLARE
/*
No cursor cAccId você vai listar todas as ACCOUNT_ID que existem na tabela ICS_E_ACTION_LAUNCHER com status de ACTIVE / OK
*/
  CURSOR cAccId IS
    SELECT ACCOUNT_ID
      FROM ICS_E_ACTION_LAUNCHER
     WHERE STATUS IN ('ACTIVE', 'OK');

/*
Depois de abrir o cursor acima, vou passar um parametro para o cursor abaixo, assim, pego o max de forma mais rápida
*/     
  CURSOR cAcc (vId number) IS --cursor que abre todos os ids
    SELECT ACCOUNT_ID, MAX(EXECUTION_DATE), STATUS , Launcher_Id
      FROM ICS_E_ACTION_LAUNCHER
     WHERE STATUS IN ('ACTIVE', 'OK')
       AND ACCOUNT_ID = vId
       GROUP BY ACCOUNT_ID, ACTION_ID, STATUS , Launcher_Id;
     
BEGIN
  FOR regid IN cAccId LOOP /*Abrindo todos os IDs*/
    FOR regacc IN cAcc(regid.ACCOUNT_ID) LOOP
    /*depois de abrir o primeiro cursor, vou abrir esse segundo com base no primeiro,
      assim, filtrando a ID e coletando somente a ultima movimentação*/
     
   /*
   o comando dbms_output.put_line serve pra você exibir no output window o que retornou no cursor,
   nesse ponto, você vai colocar a rotina que gera o seu txt.
   coloquei o dbms_output.put_line so para que você veja se o qeu vai ser exibido é realmente o que você deseja.
   */     
      dbms_output.put_line('Texto sql '||reg2.sql_text);
     
    END LOOP;
  END LOOP;
END;


na verdade, essa rotina nada mais é do que duas consultas, onde a segunda só é executada quando pego o parametro da primeira, lhe retornando a informação mais rápida (dependendo da situação)
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Qua, 06 Mai 2009 9:29 am

Rodrigo me desculpa a minha burrice.

O código dbms_output.put_line('Texto sql '||regid.sql_text);

Deverá ficar assim:

dbms_output.put_line('SELECT ACCOUNT_ID, ACTION_ID, EXECUTION_DATE, STATUS , Launcher_Id
FROM ICS_E_ACTION_LAUNCHER A
WHERE STATUS IN ('ACTIVE','OK')
AND EXISTS (SELECT B.ACCOUNT_ID,MAX(B.EXECUTION_DATE)
FROM ICS_E_ACTION_LAUNCHER B
WHERE A.ACCOUNT_ID = B.ACCOUNT_ID
AND B.STATUS IN ('ACTIVE','OK')
GROUP BY B.ACCOUNT_ID HAVING MAX(B.EXECUTION_DATE) = A.EXECUTION_DATE)'||regid.sql_text);


Desde já agradeço
dralves
Localização: rj

Mensagemem Qua, 06 Mai 2009 10:32 am

Dralvels, o comando dbms_output.put_line tem a mesma função do println por exemplo... ele vai jogar na tela um texto, somente. (gosto de usar para saber se a função está ok).

Por onde você gera o seu texto? pelo forms ou pelo sql? explica mais aí sobre sua rotina (não somente a query, essa já temos) mas a rotina em si. Você está querendo desenvolver uma tela que gere esse texto ou usa um script que faz isso? qual funçao está usando para exporta esse texto? text_io?

explica aí... assim posso tentar te ajudar de forma direta.
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Qua, 06 Mai 2009 11:02 am

beleza.

Estou utilizando o TOAD. No momento que aparece alguns resultados no Grid o proprio TOAD me dá a opção de salvar o resultado em arquivo txt.

Porém os dados ainda não foram carregados no Grid e nem no arquivo txt, ou seja, conforme os dados são carregados no grid ele carrega no arquivo txt. "Acho que é assim que funciona."

Por isso eu não consigo carregar a consulta como um todo, pois o registro que já está carregado no grid está sofrendo uma modificação e não consigo salvar o arquivo por inteiro.

Valeu pela ajuda
dralves
Localização: rj

Mensagemem Qua, 06 Mai 2009 12:11 pm

Se da forma q você ta fazendo não ta dando, tente usar a rotina que te mostrei acima e use o UTL_File no lugar do DBMS_OUTPUT.PUT_LINE.

Pesquise por UTL_File aqui no forum.
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Qua, 06 Mai 2009 1:35 pm

Usando o mesmo código que você encaminhou está dando o seguinte erro:

Ah substitui a palavra DBMS_OUTPUT.PUT_LINE por UTL_File

The following error has occurred:

ORA-06550: line 31, column 36:
PLS-00302: component 'SQL_TEXT' must be declared
ORA-06550: line 31, column 7:
PL/SQL: Statement ignored
dralves
Localização: rj

Mensagemem Qui, 07 Mai 2009 9:17 am

Dralves, faz o seguinte, trabalha com range de data, exportando as informações. Não faz um range muito curto nem um muito longo... se tua tabela tem 10 milhoes de linhas, trabalha com range de 1 em 1 milhão e vê se da certo... se continuar o erro, baixa pra 500 e 500 mil.
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Mensagemem Qui, 07 Mai 2009 11:27 am

O erro que está acontecendo não tem haver com o range.

Segue erro.

The following error has occurred:

ORA-06550: line 31, column 368:
PLS-00302: component 'SQL_TEXT' must be declared
ORA-06550: line 31, column 7:
PL/SQL: Statement ignored

O Codigo ficou da seguinte forma:

DECLARE
/*
No cursor cAccId você vai listar todas as ACCOUNT_ID que existem na tabela ICS_E_ACTION_LAUNCHER com status de ACTIVE / OK
*/
CURSOR cAccId IS
SELECT ACCOUNT_ID
FROM ICS_E_ACTION_LAUNCHER
WHERE STATUS IN ('ACTIVE', 'OK');

/*
Depois de abrir o cursor acima, vou passar um parametro para o cursor abaixo, assim, pego o max de forma mais rápida
*/
CURSOR cAcc (vId number) IS --cursor que abre todos os ids
SELECT ACCOUNT_ID, MAX(EXECUTION_DATE), STATUS
FROM ICS_E_ACTION_LAUNCHER
WHERE STATUS IN ('ACTIVE', 'OK')
AND ACCOUNT_ID = vId
GROUP BY ACCOUNT_ID, ACTION_ID, STATUS;

BEGIN
FOR regid IN cAccId LOOP /*Abrindo todos os IDs*/
FOR regacc IN cAcc(regid.ACCOUNT_ID) LOOP
/*depois de abrir o primeiro cursor, vou abrir esse segundo com base no primeiro,
assim, filtrando a ID e coletando somente a ultima movimentação*/

/*
o comando dbms_output.put_line serve pra você exibir no output window o que retornou no cursor,
nesse ponto, você vai colocar a rotina que gera o seu txt.
coloquei o dbms_output.put_line so para que você veja se o qeu vai ser exibido é realmente o que você deseja.
*/
UTL_File('SELECT ACCOUNT_ID, ACTION_ID, EXECUTION_DATE, STATUS FROM ICS_E_ACTION_LAUNCHER A WHERE STATUS IN (''ACTIVE'',''OK'') AND EXISTS (SELECT B.ACCOUNT_ID,MAX(B.EXECUTION_DATE) FROM ICS_E_ACTION_LAUNCHER B WHERE A.ACCOUNT_ID = B.ACCOUNT_ID AND B.STATUS IN (''ACTIVE'',''OK'') GROUP BY B.ACCOUNT_ID HAVING MAX(B.EXECUTION_DATE) = A.EXECUTION_DATE '||regacc.sql_text);

END LOOP;
END LOOP;
END;
dralves
Localização: rj

Mensagemem Qui, 07 Mai 2009 1:09 pm

Dralves, você está misturando as informações... bom, esquece o que te passei e usa a solução que propus de range de informações...

Ex:
Código: Selecionar todos
SELECT ACCOUNT_ID, ACTION_ID, EXECUTION_DATE, STATUS, LAUNCHER_ID
  FROM ICS_E_ACTION_LAUNCHER A
WHERE STATUS IN ('ACTIVE', 'OK')
   AND EXISTS (SELECT B.ACCOUNT_ID, MAX(B.EXECUTION_DATE)
          FROM ICS_E_ACTION_LAUNCHER B
         WHERE A.ACCOUNT_ID = B.ACCOUNT_ID
           AND B.STATUS IN ('ACTIVE', 'OK')
           AND B.ACCOUNT_ID BETWEEN 1 AND 1000000
         GROUP BY B.ACCOUNT_ID
        HAVING MAX(B.EXECUTION_DATE) = A.EXECUTION_DATE)


Observe que fiz a inclusão do parametro de account_id como range
Código: Selecionar todos
           AND B.ACCOUNT_ID BETWEEN 1 AND 1000000

proximo range fica a seu critério.
RodrigoValentim
Localização: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


Voltar para PL/SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 10 visitantes