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;