Início / dba / Perguntas / 2358
Accepted
Egalitarian
Asked: 2011-04-26 00:31:08 +0800 CST

Encontrando a exceção ORA-01555

  • 772

Deram-me um problema para resolver, no qual existe uma tabela chamada Cenários no Master Db que contém os detalhes de todo o Tablespace para o qual tenho que encontrar o tamanho. O O/P deve conter o tamanho da tabela (realmente consumido) e o tamanho do índice e o número de linhas.

Então, escrevi um script de dimensionamento (PL/SQL) para encontrar o tamanho de todo o Table Space naquele servidor de banco de dados específico.

Mas estou recebendo essa exceção específica depois que ela é executada por dias.

ORA-01555: snapshot muito antigo: segmento de rollback número 9 com nome "_SYSSMU9$" muito pequeno

Não tenho certeza do que pode estar causando isso, pois o tamanho dos dados não é tão grande.

estou anexando o script

    SET SERVEROUTPUT ON size '10000000'
declare
TYPE cur_typ IS REF CURSOR;
a_Temp number := 0;
x_Total number := 0;
i number := 0;
c_cursor cur_typ;
query_str varchar2(500);
num_long Long;
currentScenarioDB nvarchar2(255);
tableExists number := 0;
scenarioId varchar2(50);
scenarioName varchar2(100);
dbIdentifier nvarchar2(50);
queryToFindScenarioNameAndId varchar2(400) := 'select scenarioId,name from scenarios where dbidentifier =  ';
selectQuery varchar2(400) := 'select scenarioId,name from scenarios where dbidentifier =  ';
insertStatement varchar2(2000) := 'Insert Into ScenarioTableAndIndexSize  values (:1,:2,:3,:4,:5,:6,:7) ';
-- scenarioId,scenarioname,,dbIdentifier,tablename,dataSize,IndexSize,rowNumber
tableIndexSize number := 0;
numOfRows number := 0;
rowNum number := 0;
tableDataSize number := 0;
Cursor getScenarioDb is select dbidentifier from scenarios where dbidentifier IN (select Distinct(TABLESPACE_NAME) from dba_tables);
begin
DBMS_OUTPUT.ENABLE(10000000);
execute immediate 'truncate table ScenarioTableAndIndexSize';
open getScenarioDb;
fetch getScenarioDb into currentScenarioDB;
while getScenarioDb%found
loop
queryToFindScenarioNameAndId := selectQuery || '''' || currentScenarioDB || '''';
execute immediate queryToFindScenarioNameAndId  into scenarioId,scenarioName;
              declare
              queryToFindNoofRows varchar2(1000);
        queryConstruct varchar2(32767) := '';
        outputTableInScenarioDb nvarchar2(256);
        Cursor getTablesInScenario is select DISTINCT TABLE_NAME from dba_tables where owner =  currentScenarioDB and TABLE_NAME not like 'BIN%' and table_name != 'SCENARIOTABLEANDINDEXSIZE' order by table_name;
        begin
              tableExists := 0;
        open getTablesInScenario;
        fetch getTablesInScenario into outputTableInScenarioDb;
        while getTablesInScenario%found
        loop
              queryConstruct  := 'select nvl( sum (';
              tableIndexSize  := 0;
              tableDataSize := 0;
              numOfRows := 0;
              queryToFindNoofRows := 'select count(*) from  '||  currentScenarioDB || '.' ||outputTableInScenarioDb;
              execute immediate queryToFindNoofRows into numOfRows;
              if numOfRows > 0 then
---------------------------Beginning Of Section to find Table data Size------------------------------------------------------------------------------------------------
                  declare
                      Cursor getColumnsInTables is select * from dba_tab_columns where Table_Name = outputTableInScenarioDb and owner = currentScenarioDB;
                      dbaTabColumnRow dba_tab_columns%rowtype;
                      dataType varchar2(40);
                      fields varchar2(1000);
                      begin
                      open getColumnsInTables;
                      fetch getColumnsInTables Into dbaTabColumnRow;
                      while getColumnsInTables%found
                      loop
                      dataType := dbaTabColumnRow.DATA_TYPE;
                     if dataType = 'CLOB' then
                        fields := 'nvl(DBMS_LOB.GETLENGTH(' || dbaTabColumnRow.COLUMN_NAME ||'),0)';
                     elsif dataType = 'BLOB' then
                        fields := 'nvl(DBMS_LOB.GETLENGTH('|| dbaTabColumnRow.COLUMN_NAME ||'),0)';
                     elsif dataType = 'LONG' then
                        fields := 'nvl(VSIZE(''''),0)';
                        x_Total := 0;
                        query_str := 'SELECT  ' || dbaTabColumnRow.COLUMN_NAME || '  FROM  ' || currentScenarioDB || '.' ||outputTableInScenarioDb;
                                      OPEN c_cursor FOR query_str;
                                  LOOP
                                  FETCH c_cursor INTO num_long;
                                  EXIT WHEN c_cursor%NOTFOUND;
                             a_Temp:=length(num_long);
                             x_Total:= x_Total + a_Temp;
                                  END LOOP;
                           CLOSE c_cursor;
                     else
                        fields := 'nvl(vsize(' || dbaTabColumnRow.COLUMN_NAME || '),0)';
                     end if;
                           fetch getColumnsInTables Into dbaTabColumnRow;
                         if getColumnsInTables%found then
                       queryConstruct := queryConstruct || fields||'+';
                     else
                     queryConstruct := queryConstruct || fields;
                     end if;
                      end loop;
                      end;
                                      queryConstruct := queryConstruct || '),0) as sizeOfTable from  ' || currentScenarioDB || '.' ||outputTableInScenarioDb;            
                                      --dbms_output.put_line(queryConstruct);
                                      execute immediate queryConstruct into tableDataSize;
---------------------------End Of Section to find Table data Size-------------------------------------------------------------------------------------------------------------

                      ---------------Section To find index size
                          declare
                Index_Name nvarchar2(4000);
                sql_statement varchar2(1000) := 'select nvl(USED_SPACE,0) from index_stats';
                stat1 varchar2(1000) := 'analyze index ';
                stat2 varchar2(1000) := '  validate structure';
                stat3 varchar2(2000) := '';
                size1 number := 0;
                cursor indexOnTable is select INDEX_NAME from dba_indexes where tablespace_name = currentScenarioDB and  table_name = outputTableInScenarioDb and index_type = 'NORMAL';
                    begin
                    open indexOnTable;
                    fetch indexOnTable into Index_Name;
                    while indexOnTable%found
                    loop
                      stat3 := stat1 || currentScenarioDB ||'.' ||Index_Name || stat2;
                      execute immediate stat3;
                      execute immediate  sql_statement into size1;
                      tableIndexSize := tableIndexSize + size1;
                    fetch indexOnTable into Index_Name;
                    end loop;
                    close indexOnTable;
            end;
                      -----end of section to find index size
              else
                rowNum := rowNum + 1;
              end if;
                            tableDataSize := x_Total + tableDataSize;
              execute immediate insertStatement using   scenarioId,scenarioName,currentScenarioDB,outputTableInScenarioDb,tableDataSize,tableIndexSize,numOfRows;
                               x_Total := 0;
              fetch getTablesInScenario into outputTableInScenarioDb;
        end loop;
        end;
fetch getScenarioDb into currentScenarioDB;
end loop;
close getScenarioDb;
end;

O tamanho da tabela é encontrado desta forma:

  1. Se o campo for do tipo Lob então para calcular seu tamanho eu uso nvl(DBMS_LOB.GETLENGTH(),0)
  2. Se o campo for do tipo Long, então eu faço um loop sobre todos os valores Long e encontro seu tamanho
    usando a função Length() incorporada
  3. Se o campo for de qualquer outro tipo, uso nvl(vsize(),0) Apenas para especificar que o usuário tem permissões em todos os bancos de dados

E então eu resumo todos eles para encontrar o tamanho total dos dados na tabela.

Alguém pode me dizer o que estou fazendo de errado ou o que devo fazer para corrigir o erro?

Obrigado.

oracle oracle-10g

2 respostas

  1. Best Answer

    Caio está certo, use DBA_TABLESpara NUM_ROWSe DBA_SEGMENTSpara tamanho:

    select owner,table_name,num_rows,last_analyzed from dba_tables;

    A contagem de num_rows é a partir da LAST_ANALYZEDdata, que deve ser próxima o suficiente mesmo sem executar DBMS_STATS.

    Para dimensionamento:

    select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;

    ou (dependendo do nível de detalhes que você precisa):

    select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;

    Para LOBs, você precisará uni-lo a DBA_LOBS, para índices a DBA_INDEXES, para tabelas a DBA_TABLES. Muito dependerá de sua exigência específica. Como você mencionou que está tentando descobrir o espaço usado no tablespace, pode ser tão simples quanto:

    select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';

    Sua solução realmente funcionaria apenas com um banco de dados pequeno, pois não seria viável ler todos os dados em um banco de dados grande.

    Estou hesitante em postar como resolver ORA-1555, pois neste caso NÃO é seu problema principal, mas apenas para completar - você está em 10g usando o gerenciamento automático de desfazer, então seu DBA teria que aumentar undo_retentionem seu banco de dados (o link ixora é relevante para um banco de dados sem gerenciamento de desfazer automático).

    • 3

  2. Faça uma análise ( DBMS_STATS.GATHER_SCHEMA_STATS) e procure DBA_TABLESpor NUM_ROWS. Olhe para dentro DBA_SEGMENTS.BYTESpara encontrar o tamanho de cada objeto.

    Você está recebendo ORA-1555porque seus logs foram totalmente enrolados nesse tempo.

    • 1

relate perguntas