19c删除spatial组件ORA-28014: cannot drop administrative user or role
SQL> alter session set container=pdb;
Session altered.
SQL> select comp_id, version, status from dba_registry ;
COMP_ID VERSION STATUS
------------------------------ ------------------------------ ----------------------
CATALOG 19.0.0.0.0 VALID
CATPROC 19.0.0.0.0 VALID
RAC 19.0.0.0.0 VALID
JAVAVM 19.0.0.0.0 VALID
XML 19.0.0.0.0 VALID
CATJAVA 19.0.0.0.0 VALID
APS 19.0.0.0.0 VALID
XDB 19.0.0.0.0 VALID
OWM 19.0.0.0.0 VALID
CONTEXT 19.0.0.0.0 VALID
ORDIM 19.0.0.0.0 VALID
COMP_ID VERSION STATUS
------------------------------ ------------------------------ ----------------------
SDO 19.0.0.0.0 INVALID
XOQ 19.0.0.0.0 VALID
OLS 19.0.0.0.0 VALID
DV 19.0.0.0.0 VALID
15 rows selected.
SQL> select owner,index_name from dba_indexes
2 where ityp_name = 'SPATIAL_INDEX';
no rows selected
SQL> /
no rows selected
SQL> set pages 200
SQL> col owner for a20
SQL> col table_name for a30
SQL> col column_name for a25
SQL>
SQL> select owner, table_name, column_name
from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
and owner != 'MDSYS'
order by 1,2,3;
no rows selected
SQL> drop user mdsys cascade;
drop user mdsys cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative user or role
SQL> !oerr ora 28014
28014, 00000, "cannot drop administrative user or role"
// *Cause: An attempt was made to drop an administrative user or role.
// An administrative user or role can be dropped only by SYS during
// migration mode.
// *Action: Drop the administrative user or role during migration mode.
//
SQL> conn /as sysdba
Connected.
SQL> drop user mdsys cascade;
drop user mdsys cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative user or role
SQL> alter session set container=pdb;
Session altered.
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> drop user mdsys cascade;
15:51:04 SQL> conn /as sysdba
Connected.
15:51:14 SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY YES
3 PDB READ WRITE YES
15:51:24 SQL> alter session set "_oracle_script"=true;
Session altered.
Elapsed: 00:00:00.00
15:51:28 SQL> drop user mdsys cascade;
User dropped.
Elapsed: 00:03:18.65
检查已删除sdo组件:
15:54:52 SQL> select comp_id, version, status from dba_registry ;
COMP_ID VERSION STATUS
------------------------------ ------------------------------ ----------------------
CATALOG 19.0.0.0.0 VALID
CATPROC 19.0.0.0.0 VALID
RAC 19.0.0.0.0 VALID
JAVAVM 19.0.0.0.0 VALID
XML 19.0.0.0.0 VALID
CATJAVA 19.0.0.0.0 VALID
APS 19.0.0.0.0 VALID
XDB 19.0.0.0.0 VALID
OWM 19.0.0.0.0 VALID
CONTEXT 19.0.0.0.0 VALID
ORDIM 19.0.0.0.0 VALID
XOQ 19.0.0.0.0 VALID
OLS 19.0.0.0.0 VALID
DV 19.0.0.0.0 VALID
14 rows selected.
Elapsed: 00:00:00.03
删除同义词：
set pagesize 0
set feed off
spool /home/oracle/dropsyn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
@/home/oracle/dropsyn.sql
在cdb和pdb都执行删除。种子PDB$SEED也要删组件，否则报错：
Database option SDO mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL.
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> alter pluggable database pdb$seed close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb$seed open;
Spatial also creates a few user schemas during installation which can be dropped as well:
drop user mddata cascade;
-- Only created as of release 11g:
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade;
select message from PDB_PLUG_IN_VIOLATIONS;