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 :
- If the field is of type Lob then to calculate its size I use nvl(DBMS_LOB.GETLENGTH(),0)
- 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 - 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.