SYMPTOMS

On : 12.1.0.1 version, Database Security

When attempting to recompile the package DVSYS.DBMS_MACADM
the following error occurs.

ERROR
———————–
PL/SQL: ORA-00942: table or view does not exist

STEPS
———————–
The issue can be reproduced at will with the following steps:
1. SQL> connect secadmin/fiction.5
SQL> EXEC DBMS_MACADM.DISABLE_DV;

2. SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup

3. SQL> alter package dvsys.dbms_macadm compile;

CAUSE

Body 242 14 PL/SQL: ORA-00942: table or view does not exist DV$FACTOR
Body 264 40 PL/SQL: ORA-00942: table or view does not exist select DVSYS.DV_SQL_TEXT FROM DUAL;
Body 273 40 PL/SQL: ORA-00942: table or view does not exist DV$FACTOR
Body 317 14 PL/SQL: ORA-00942: table or view does not exist DV$REALM
Body 381 14 PL/SQL: ORA-00942: table or view does not exist DV$RULE
Body 421 14 PL/SQL: ORA-00942: table or view does not exist DV$RULE_SET
Body 480 14 PL/SQL: ORA-00942: table or view does not exist DV$MAC_POLICY
Body 1087 28 PL/SQL: ORA-00942: table or view does not exist DV$FACTOR
Body 1384 32 PL/SQL: ORA-00942: table or view does not exist DV$FACTOR
Body 1490 32 PL/SQL: ORA-00942: table or view does not exist DV$FACTOR

Some DV objects are missing

SOLUTION

Disable and then remove(uninstall) Oracle Database Vault.

Be aware that after you disable Oracle Database Vault, Oracle Label Security, which is required to run Database Vault, is still enabled.

1) To disable Oracle Database Vault:

In SQL*Plus, log in as the Oracle Database Owner (DV_OWNER) account, and then disable Oracle Database Vault.

sqlplus psmith
Enter password: password

EXEC DBMS_MACADM.DISABLE_DV;

2) In a multitenant environment, connect to the appropriate pluggable database (PDB).

For example:

CONNECT psmith@hrpdb
Enter password: password

To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

3) Restart the database.

CONNECT SYS AS SYSOPER — Or, CONNECT SYS@hrpdb AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE
STARTUP

4) For Oracle RAC installations, repeat these steps for each node on which the database is installed.

5) Run:

sqlplus / as sysdba

@?/rdbms/admin/dvremov.sql

This script will remove/uninstall Database Vault

In multitenant 12c DB, Database Vault can be disabled/re-enabled, but Database Vault can’t be uninstalled in CDB environment ( dvremov.sql can’t be run in CDB environment).