OGG相关(oracle报错) 目标端数据库imp之后,查询相关表,报 ORA-28100。这是以为再源端有vpd策略, 在目标端删除这些策略即可 。 ALL_POLICIES 策略信息 DBA_,USER_ ALL_POLICY_GROUPS 定义的策略组 DBA_,USER_
1、查询策略 查看所有用户的策略: select OBJECT_OWNER,OBJECT_NAME,POLICY_GROUP,POLICY_NAME from dba_policies;
查看登录用户的策略: SQL> select OBJECT_NAME,POLICY_GROUP,POLICY_NAME from user_policies;
OBJECT_NAME POLICY_GROUP ------------------------------ ------------------------------ POLICY_NAME ------------------------------ ENMO_EMP SYS_DEFAULT POLICY_ENMO_EMP
2、删除策略 SQL> exec dbms_rls.drop_grouped_policy('AWEN','ENMO_EMP','SYS_DEFAULT','POLICY_ENMO_EMP');
PL/SQL procedure successfully completed.
下面的过程是网上一位同学写的,可以快速删除所有策略。 (有些情况可能不可用) declare -- Local variables here i integer; str_schema varchar2(20) := 'ods_yyjc_buf'; str_obj_name varchar2(20) ; str_group_name varchar2(20); str_policy_name varchar2(30); begin -- Test statements here for item in (select * from all_policies) loop str_obj_name := item.object_name; str_group_name := item.policy_group; str_policy_name := item.policy_name; dbms_rls.drop_grouped_policy(object_schema => str_schema,object_name => str_obj_name, policy_group => str_group_name,policy_name => str_policy_name); end loop; for item in (select * from ALL_POLICY_GROUPS) loop str_obj_name := item.object_name; str_group_name := item.policy_group; str_policy_name := str_schema; dbms_rls.delete_policy_group(str_schema,str_obj_name,str_group_name); end loop; end;