使用exp备份数据库时丢失数据库表解决方案

可以在当前用户下运行如下名为 test_setblank_segment的存储过程方可解决此问题
prompt
prompt Creating package PP_TEST_YZH
prompt ============================
prompt
create or replace package PP_Test_Yzh is

 procedure test_create_index;
 
 procedure test_set_default;
 
 procedure test_clear_gw;
 
 procedure test_setblank_segment;
 procedure p_rebuild_all_index;
end PP_Test_Yzh;
/

prompt
prompt Creating package body PP_TEST_YZH
prompt =================================
prompt
create or replace package body PP_Test_Yzh is

procedure test_create_index
   is
   v_I INTEGER;
  V_ExceptionInt INTEGER;
  v_ErrorMsg VARCHAR2(2000);
  v_tab tabs%rowtype;
  v_colName VARCHAR2(100);
  v_tabname VARCHAR2(100);
  V_SQL VARCHAR2(4000);
  cursor cur_tab
  is
  select *
  from tabs
 where exists(select 1
              from col
              where col.tname = tabs.table_name and
                    col.cname in('健康档案编码','录入时间','录入机构编码')
              );
  cursor cur_col(V_TAB VARCHAR2)
  is
  SELECT cname,tname
      FROM col
      WHERE COL.tname =V_TAB and
            COL.cname  IN('健康档案编码','录入时间','录入机构编码');
begin
   v_I :=0;
   OPEN cur_tab; ---open cursor  
   LOOP        
      v_I:=v_I+1;
      Fetch cur_tab into v_tab;
      Exit when cur_tab%notfound;     
      
      OPEN cur_col(v_tab.table_name);
      LOOP        
        Fetch cur_col into v_colName,v_tabname;
        Exit when cur_col%notfound;     
        V_SQL :='create index IDX_'||TO_CHAR(v_I)||v_colName||' on '||v_tabname||'('||v_colName||')';
        BEGIN
          execute immediate V_SQL;  
        EXCEPTION
           WHEN OTHERS THEN
           v_ErrorMsg:='';
        END;
      END LOOP;
      IF cur_col%ISOPEN THEN
       Close cur_col;
    END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN  
    v_ErrorMsg:=SQLERRM;
    IF cur_tab%isopen THEN
       Close cur_tab;
    End If;
    IF cur_col%ISOPEN THEN
       Close cur_col;
    END IF;
end test_create_index;
   
procedure test_set_default
is
  V_SQL VARCHAR2(2000);    
  v_tab tabs%rowtype;
  v_ErrorMsg VARCHAR2(2000);
 cursor cur_tab
  is
  select *
  from tabs
  where tabs.table_name like '妇女保健%' or
        tabs.table_name like '儿童保健%' or
        tabs.table_name like '老年人管理%' or
        tabs.table_name like '预防接种%' OR
        tabs.table_name like '疾病管理%' ;
 begin
     OPEN cur_tab; ---open cursor  
      LOOP        
          Fetch cur_tab into v_tab;
          Exit when cur_tab%notfound;
           BEGIN
            V_SQL:='DELETE FROM '||v_tab.table_name;
            execute immediate V_SQL;
         EXCEPTION
           WHEN OTHERS THEN
            v_ErrorMsg:=SQLERRM;
          END;      
          COMMIT;
          BEGIN
               execute immediate V_SQL;      
          EXCEPTION
           WHEN OTHERS THEN
            v_ErrorMsg:=SQLERRM;
          END;    
          V_SQL:='alter table '||v_tab.table_name||' modify 录入时间 not null';
          BEGIN
               execute immediate V_SQL;   
          EXCEPTION
            WHEN OTHERS THEN
              v_ErrorMsg:=SQLERRM;
          END;
          
          V_SQL:='alter table '||v_tab.table_name||' modify 录入机构编码 not null';
          BEGIN
               execute immediate V_SQL;   
          EXCEPTION
            WHEN OTHERS THEN
              v_ErrorMsg:=SQLERRM;
          END;
          V_SQL:='alter table '||v_tab.table_name||' modify 录入人编码 not null';
          BEGIN
               execute immediate V_SQL;   
          EXCEPTION
            WHEN OTHERS THEN
              v_ErrorMsg:=SQLERRM;
          END;
      END LOOP;    
EXCEPTION
   WHEN OTHERS THEN  
    v_ErrorMsg:=SQLERRM;
    IF cur_tab%isopen THEN
       Close cur_tab;
    End If;   
END test_set_default;

procedure test_clear_gw
is
  V_SQL VARCHAR2(2000);    
  v_tab tabs%rowtype;
  v_ErrorMsg VARCHAR2(2000);
 cursor cur_tab
  is
 select *
  from tabs
  where (tabs.table_name like '妇女保健%' or
        tabs.table_name like '儿童保健%' or
        tabs.table_name like '老年人管理%' or
        tabs.table_name like '预防接种%' OR
        tabs.table_name like '疾病管理%') and
        (tabs.table_name <> '妇女保健_高危孕产妇评分标准' or
         tabs.table_name <> '预防接种_疫苗免疫程序' or
         tabs.table_name <> '儿童保健_身高比体重标准值' or
         tabs.table_name <> '儿童保健_男童身高标准值' or
         tabs.table_name <> '儿童保健_男童体重标准值' or
         tabs.table_name <> '儿童保健_女童身高标准值' or
         tabs.table_name <> '儿童保健_女童体重标准值');
 begin
     OPEN cur_tab; ---open cursor  
      LOOP        
          Fetch cur_tab into v_tab;
          Exit when cur_tab%notfound;
           BEGIN
            V_SQL:='DELETE FROM '||v_tab.table_name;
            execute immediate V_SQL;
         EXCEPTION
           WHEN OTHERS THEN
            v_ErrorMsg:=SQLERRM;
          END;      
          COMMIT;
          BEGIN
               execute immediate V_SQL;      
          EXCEPTION
           WHEN OTHERS THEN
            v_ErrorMsg:=SQLERRM;
          END;    
         
      END LOOP;    
EXCEPTION
   WHEN OTHERS THEN  
    v_ErrorMsg:=SQLERRM;
    IF cur_tab%isopen THEN
       Close cur_tab;
    End If;   
end test_clear_gw;



procedure test_setblank_segment
is
  V_SQL VARCHAR2(2000);    
  v_ErrorMsg VARCHAR2(2000);
 cursor cur_tab
  is
 select 'alter table '||table_name||' allocate extent' as extendsql from user_tables where num_rows=0;
 begin
     OPEN cur_tab; ---open cursor  
      LOOP        
          Fetch cur_tab into V_SQL;
          Exit when cur_tab%notfound;
           BEGIN
             execute immediate V_SQL;
         EXCEPTION
           WHEN OTHERS THEN
            v_ErrorMsg:=SQLERRM;
          END;      
          COMMIT;
          BEGIN
               execute immediate V_SQL;      
          EXCEPTION
           WHEN OTHERS THEN
            v_ErrorMsg:=SQLERRM;
          END;    
         
      END LOOP;    
EXCEPTION
   WHEN OTHERS THEN  
    v_ErrorMsg:=SQLERRM;
    IF cur_tab%isopen THEN
       Close cur_tab;
    End If;   
end test_setblank_segment;

procedure p_rebuild_all_index
--(tablespace_name in varchar2,--这里是表空间名，如果不改变表空间，可以传入null
--   only_unusable in boolean)    --是否仅对无效的索引操作
as
   sqlt varchar(200);
   tablespace_name varchar2(200);--这里是表空间名，如果不改变表空间，可以传入null
   only_unusable  boolean;    --是否仅对无效的索引操作
begin
   tablespace_name:=null;--这里是表空间名，如果不改变表空间，可以传入null

   only_unusable:=true ;    --是否仅对无效的索引操作
    --只取非临时索引
    for idx in (select index_name, tablespace_name, status from user_indexes where temporary = 'N') loop
        --如果是如重建无效的索引，且当索引不是无效时，则跳过
        if only_unusable = true and idx.status <> 'UNUSABLE' then
           goto continue;
        end if;

        if (tablespace_name is null) or idx.status = 'UNUSABLE' then
           --如果没有指定表空间，或索引无效，则在原表空间重建
           sqlt := 'alter index ' || idx.index_name || ' rebuild ';
        elsif upper(tablespace_name) <> idx.tablespace_name then
           --如果指定的不同的表空间，则在指定表空间待建索引
           sqlt := 'alter index ' || idx.index_name || ' rebuild tablespace ' || tablespace_name;
        else
           --如果表空间相同，则跳过
           goto continue;
        end if;

        dbms_output.put_line(idx.index_name);
        EXECUTE IMMEDIATE sqlt;
        <<continue>>
        null;
     end loop;
end p_rebuild_all_index;
end PP_Test_Yzh;
/