Forum Stats

  • 3,875,405 Users
  • 2,266,911 Discussions
  • 7,912,196 Comments

Discussions

Redact Policies wont go away

User_SLX05
User_SLX05 Member Posts: 1 Green Ribbon

Redact Policies won't go away when I use dbms_redact.drop_policy. I can't delete them because the Oracle SQL developer is saying that there is no policy associated with the table, but when I select all redact_policies I can clearly see that the policy I am trying to delete is present and duplicated multiple times. The only difference is that the object_names have random numbers and letters starting with BIN instead of the name of the table. Please help. Thank you.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,951 Red Diamond

    The only difference is that the object_names have random numbers and letters starting with BIN instead of the name of the table. Please help. Thank you.

    That simply means table was dropped but not purged and recycle bin is enabled in your dataaae, so it was moved into recycle bin:

    SQL> create table test(
      2                    customer_id number not null,
      3                    card_no     number not null
      4                   )
      5  /
    
    Table created.
    
    SQL> begin
      2      dbms_redact.add_policy(
      3                             object_schema => user,
      4                             object_name   => 'test',
      5                             column_name   => 'card_no',
      6                             policy_name   => 'redact_card_no',
      7                             function_type => dbms_redact.full,
      8                             expression    => '1=1'
      9                            );
     10  end;
     11  /
    
    PL/SQL procedure successfully completed.
    
    SQL> drop table test
      2  /
    
    Table dropped.
    
    SQL> select  policy_name,
      2          object_name
      3    from  redaction_policies
      4  /
    
    POLICY_NAME     OBJECT_NAME
    --------------- ------------------------------
    redact_card_no  BIN$yG1kI4jOSYS0iY0ko8tnbw==$0
    
    SQL>
    

    So you can either restore dropped table or purge recycle bin. For example:

    SQL> purge recyclebin
      2  /
    
    Recyclebin purged.
    
    SQL> select  policy_name,
      2          object_name
      3    from  redaction_policies
      4  /
    
    no rows selected
    
    SQL>
    

    SY.