Oracle 中的数据库备份 - 导出数据库还是使用其他工具？

Question

Egalitarian

Asked: 2011-04-26 00:31:08 +0800 CST2011-04-26 00:31:08 +0800 CST 2011-04-26 00:31:08 +0800 CST

遇到异常 ORA-01555

772

我遇到了一个要解决的问题，其中 Master Db 中有一个名为 Scenarios 的表，其中包含我必须找到其大小的所有表空间的详细信息。O/P 应包含表大小（实际消耗）和索引大小以及行数。

因此，我编写了一个大小调整脚本 (PL/SQL) 来查找该特定数据库服务器上所有表空间的大小。

但是在运行几天后我得到了这个特殊的异常。

ORA-01555: 快照太旧: 名称为 "_SYSSMU9$" 的回滚段编号 9 太小

我不确定是什么原因造成的，因为数据量不是那么大。

我附上脚本

    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;

表的大小是这样找到的：

如果该字段是 Lob 类型，那么为了计算它的大小，我使用 nvl(DBMS_LOB.GETLENGTH(),0)
如果该字段是 Long 类型，那么我将遍历所有 Long 值并
使用内置的 Length() 函数找到它们的大小
如果该字段是任何其他类型，我使用 nvl(vsize(),0) 只是为了指定用户对所有数据库都有权限

然后我将所有这些加起来以找到表中的总数据大小。

有人可以告诉我我做错了什么或者我应该怎么做才能修复错误？

谢谢。

2 个回答

Voted

gabrielp · Answer 1 · 2011-04-28T09:32:51+08:00

Gaius 是对的，使用DBA_TABLESforNUM_ROWS和DBA_SEGMENTSfor size：

select owner,table_name,num_rows,last_analyzed from dba_tables;

num_rows 计数是LAST_ANALYZED最新的，即使不运行 DBMS_STATS 也应该足够接近。

对于上浆：

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

或者（取决于您需要的详细信息级别）：

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

对于 LOB，您需要将其连接到DBA_LOBS，索引连接到DBA_INDEXES，表连接到DBA_TABLES。很多将取决于您的具体要求。由于您提到您正在尝试找出表空间中的已用空间，它可能很简单：

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

您的解决方案实际上只适用于小型数据库，因为读取大型数据库上的所有数据是不可行的。

我犹豫要不要发布如何解决ORA-1555，因为在这种情况下，这不是您的主要问题，而只是为了完整性 - 您使用自动撤消管理在 10g 上，因此您的 DBA 将不得不增加undo_retention您的数据库（ixora 链接与数据库相关）没有自动撤消管理）。

Gaius · Answer 2 · 2011-04-26T02:16:31+08:00

Gaius

2011-04-26T02:16:31+08:002011-04-26T02:16:31+08:00

做一个分析 ( DBMS_STATS.GATHER_SCHEMA_STATS) 并DBA_TABLES寻找NUM_ROWS。查看DBA_SEGMENTS.BYTES以找到每个对象的大小。

你得到ORA-1555的是因为你的日志在那个时候一直包裹着。

1

遇到异常 ORA-01555

你如何mysqldump特定的表？

您如何显示在 Oracle 数据库上执行的 SQL？

如何选择每组的第一行？

使用 psql 列出数据库权限

我可以查看在 SQL Server 数据库上运行的历史查询吗？

如何在 PostgreSQL 中使用 currval() 来获取最后插入的 id？

如何在 Mac OS X 上运行 psql？

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

将数组参数传递给存储过程

遇到异常 ORA-01555

2 个回答

相关问题