Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.3
This problem can occur on any platform.
Symptoms
A select query on a Table with in a particular schema fail with the below error :
ORA-28100: policy function schema DVSYS is invalid
Changes
You have recently imported the failing schema
The database of the failing schema may not have Data vault installed.
Cause
If the Data vault was removed incorrectly either in the source database from which the schema is imported or in the target database , the default policies of the Data vault like DVSYS##S will still apply to the corresponding schema's. Owner of these polices will be DVSYS.
The same can be verified by executing the following SQL statements as sysdba “
Solution
Ensure that the Data vault is not installed in the database by executing the following statement :“
SQL>select parameter,value from v$option where parameter like '%Vault%';
- connect /as sysdba
- shutdown immediate
- startup migrate
- Unlock the DVSYS account and set the password [ if the account is locked ] :
`SQL> CONNECT SYS/ as SYSDBA
SQL> ALTER USER DVSYS ACCOUNT UNLOCK;
SQL> ALTER USER DVSYS IDENTIFIED BY DVSYS;` -Manually disable the policies by using the below SQL statement :
SQL>connect sys as sysdba
SQL>EXEC DBMS_RLS.DROP_POLICY('<object owner>','<object name>',' ');
example :
EXEC DBMS_RLS.DROP_POLICY('scott','emp','DVSYS##S');