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/
great, thx
ReplyDelete