ORACLE同步正式环境组织用户信息到测试环境

--导出正式环境表信息，在CMD命令行下执行

exp scnbpmtest/scnbpmtest@orcl file=d:\userinfo.dmp statistics=none TABLES=(BPMU_GROUP,BPMU_LEADER,BPMU_MEMBER,BPMU_ROLE,BPMU_ROLEMEMBER,BPMU_USER,BPMU_USERPROFILE)

--删除测试环境相关的表，在PL/SQL下执行

drop table BPMU_GROUP;
drop table BPMU_LEADER;
drop table BPMU_MEMBER;
drop table BPMU_ROLE;
drop table BPMU_ROLEMEMBER;
drop table BPMU_USER;
drop table BPMU_USERPROFILE;

--导入数据到测试环境，在CMD命令行下执行

imp scnbpmtest/scnbpmtest@orcl file=d:\userinfo.dmp tables=(BPMU_GROUP,BPMU_LEADER,BPMU_MEMBER,BPMU_ROLE,BPMU_ROLEMEMBER,BPMU_USER,BPMU_USERPROFILE)

--更新用户的邮箱、电话、默认密码等信息

update bpmu_user set pwd='c4ca4238a0b923820dcc509a6f75849b',email='97081817@qq.com'

--编译下所有视图，在PL/SQL下执行

DECLARE
str_sql varchar2(200);
p_owner varchar2(200) :='SCNBPMTEST';--所有者
begin 
  for invalid_views in (select object_name from all_objects
       where status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))
  loop
      str_sql := 'alter view ' ||invalid_views.object_name || ' compile';
      begin
          execute immediate str_sql;
      exception
        --When Others Then Null;
          when OTHERS Then
              dbms_output.put_line(sqlerrm);
      end;
  end loop;
end;

--编译下所有存储过程，在PL/SQL下执行

DECLARE
str_sql varchar2(200);
p_owner varchar2(200) :='SCNBPMTEST';--所有者
begin
  for invalid_procedures in (select object_name from all_objects
         where status = 'INVALID' and object_type = 'PROCEDURE' and owner=upper(p_owner))
    loop
        str_sql := 'alter procedure ' ||invalid_procedures.object_name || ' compile';
        begin
            execute immediate str_sql;
        exception
          --When Others Then Null;
            when OTHERS Then
                dbms_output.put_line(sqlerrm);
        end;
    end loop;
end;