3

I was given a problem to solve , in which there is table called Scenarios in the Master Db which contains the details of all the Tablespace for which I have to find the size. The O/P should contain Table size(actually consumed) and index size and no of rows.

So, I wrote a sizing script(PL/SQL) to find the size of all the Table Space on that particular DB server.

But I am getting this particular exception after it runs for days.

ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small

I am not sure what might be causing this, as the data size is not that huge.

I am attaching the 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;

The size of the table is found out this way :

  1. If the field is of type Lob then to calculate its size I use nvl(DBMS_LOB.GETLENGTH(),0)
  2. If the field is of type Long then I loop over all the Long values and find their size
    using the built in Length() function
  3. If the field is of any other Type I use nvl(vsize(),0) Just to specify the user has permissions on all the DBs

And then I sum all of them up to find the total Data size in the table.

Can some one tell me what I am doing wrong or What I should do to fix the error ?

Thanks.

2 Answers 2

3

Gaius is right, use DBA_TABLES for NUM_ROWS and DBA_SEGMENTS for size:

select owner,table_name,num_rows,last_analyzed from dba_tables;

The num_rows count is as of LAST_ANALYZED date, which should be close enough even without running DBMS_STATS.

For sizing:

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

or (depending what level of details you need):

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

For LOBs you'll need to join it to DBA_LOBS, for indexes to DBA_INDEXES, for tables to DBA_TABLES. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:

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

Your solution would really work only with small DB as it would not be feasible to read all data on large DB.

I am hesitant to post how to address ORA-1555 as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention in your database (the ixora link is relevant to a DB without auto undo management).

2
  • So, the DBA_SEGMENTS, DBA_LOBS and DBA_INDEXES contain the allocated size or do they contain the actual size, as I read in one of the articles that these tables contain the Allocated size not the actual size . Apr 28, 2011 at 4:30
  • DBA_SEGMENTS contains allocated size. Why do you need the "actual" size? I think this might be a case of misguided requirement. Whatever is the reason for getting the actual size it will (1) not be feasible to get in any real life DB (due to size) (2) any attempt to get this will wast a lot of processing (3) and it will waste a lot of space on UNDO.
    – gabrielp
    Apr 30, 2011 at 3:32
1

Do an analyze (DBMS_STATS.GATHER_SCHEMA_STATS) and look in DBA_TABLES for NUM_ROWS. Look in DBA_SEGMENTS.BYTES to find the size of each object.

You are getting ORA-1555 because your logs have wrapped all the way 'round in that time.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.