Create new rule set
Edit the TEST_RULE. In Rules Associated To The Rule Set section, click create
If the expression is not valid, you will not be able to progress further.
Once the rule set is created, we need to associate this rule with the schema & the object. So associate this rule set with the command rule.
In the command rule section, click create
So rule set is created & associated with command rule
SQL> select sysdate from dual;
SQL> create index i_deptno on emp(deptno);
create index i_deptno on emp(deptno)
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-47400: Command Rule violation for create index on SCOTT.I_DEPTNO
ORA-06512: at “DVSYS.AUTHORIZE_EVENT”, line 55
ORA-06512: at line 31
In the command rule audit section of reports, we can see if there are any violations
So we have achieved our goal, of not allowing SCOTT to create index between 1500 – 1700.
To revert back the changes, we need to drop the rule set. Sequence of removal should be, remove the command rule & then rule set. Otherwise we’ll get integrity constraint error.