Durham Oracle

Built a 19c database and had issues with the MDSYS schema claiming that it was invalid. This needs checking but as we are not using spatial we had the option to rebuild it. Otherwise the database has a number of invalid objects when the aim should always be to have zero.

Obviously if you are using spatial and the schema is showing as invalid this does NOT apply.

COMP_ID SCHEMA STATUS
------------------------------ ---------- -------
SDO MDSYS INVALID

Followed this link to drop MDSYS and the invalid synonyms.

https://mikedietrichde.com/2017/07/30/oracle-spatial-sdo-clean-oracle-database-11-2-12-2/

(it is for v11 but applies to 19c)

from the link :

drop user MDSYS cascade;

spool dropsyn.sql;
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;

Once MDSYS is removed ran the following to install MDSYS.

connect / as sysdba
create user MDSYS identified by <password> default tablespace USERS account lock;
spool mdprivs.log
start ?/md/admin/mdprivs.sql
alter session set current_schema="MDSYS";
spool off

Execute scripts to install Oracle Spatial.

SQL> spool /tmp/spatial_install.log;
SQL> @?/md/admin/mdprivs.sql;
SQL> @?/md/admin/mdinst.sql;
SQL> spool off;

Verify Oracle Spatial is installed correctly.

There should not be any invalid objects in MDSYS account.

SQL>
SELECT comp_id, control, schema, version, status, comp_name FROM dba_registry
WHERE comp_id = 'SDO';

COMP_ID CONTROL SCHEMA VERSION STATUS COMP_NAME
-------- ------- ------ ---------- ------ ---------
SDO SYS MDSYS 19.0.0.0.0 VALID Spatial
1 row selected.

Check invalid objects.
SQL>
SELECT object_name, object_type, status FROM dba_objects
WHERE owner = 'MDSYS'
AND status <> 'VALID'
ORDER BY 1;
no rows selected

Check database options.

SQL> l
1* SELECT comp_id, control, schema, version, status, comp_name from dba_registry
SQL> /

COMP_ID CONTROL SCHEMA VERSION STATUS COMP_NAME
---------- ---------- ---------- ------------ ---------- ----------------------------------------
CATALOG SYS SYS 19.0.0.0.0 VALID Oracle Database Catalog Views
CATPROC SYS SYS 19.0.0.0.0 VALID Oracle Database Packages and Types
RAC SYS SYS 19.0.0.0.0 OPTION OFF Oracle Real Application Clusters
JAVAVM SYS SYS 19.0.0.0.0 VALID JServer JAVA Virtual Machine
XML SYS SYS 19.0.0.0.0 VALID Oracle XDK
CATJAVA SYS SYS 19.0.0.0.0 VALID Oracle Database Java Packages
APS SYS SYS 19.0.0.0.0 VALID OLAP Analytic Workspace
XDB SYS XDB 19.0.0.0.0 VALID Oracle XML Database
OWM SYS WMSYS 19.0.0.0.0 VALID Oracle Workspace Manager
CONTEXT SYS CTXSYS 19.0.0.0.0 VALID Oracle Text
ORDIM SYS ORDSYS 19.0.0.0.0 VALID Oracle Multimedia
XOQ SYS OLAPSYS 19.0.0.0.0 VALID Oracle OLAP API
DV SYS DVSYS 19.0.0.0.0 VALID Oracle Database Vault
OLS SYS LBACSYS 19.0.0.0.0 VALID Oracle Label Security
SDO SYS MDSYS 19.0.0.0.0 VALID Spatial

15 rows selected.

This is similar to this but the link is for v11 :

http://oracle-solutions.com/install-oracle-spatial-11-2-0-4/

Seeing this in 19c for a lot of new users that we are building out.

It seems that users are missing the create job priv, needs to be added.

From Oracle Metalink.

In 19c Database "ORA-27486: insufficient privileges" Error Encountered.

(Doc ID 2608325.1)

ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 9387
ORA-06512: at "SYS.DBMS_ISCHED", line 9376
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_ISCHED", line 9302
ORA-06512: at "SYS.DBMS_IJOB", line 196
ORA-06512: at "SYS.DBMS_JOB", line 168
ORA-06512: at line 3

CAUSE

....... user does not have create job privilege.
It is a known issue and an enhancement bug is already there to fix this privilege issue.

SOLUTION

Please grant the following to ...... user and the script works fine after that:

SQL> grant create job to .......;

Durham Oracle

Invalid MDSYS schema

Users missing create job priv

CAUSE

SOLUTION

Oracle Metalink down....