今天在一个新建的测试库上drop user的时候，报错ora-942：
|
1 2 3 4 5 6 7 8 9 10 |
sys@ORALOCAL(10.1.26.26)> drop user HEJIANM cascade; drop user HEJIANM cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-06512: at line 11 Elapsed: 00:00:10.50 |
对于942的报错，我们常用的分析方法是设置一个942的errorstack或者10046的trace，在这里，我用了10046的方式来trace这个问题：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
sys@ORALOCAL(10.1.26.26)> alter session set events '10046 trace name context forever,level 7'; Session altered. Elapsed: 00:00:01.15 sys@ORALOCAL(10.1.26.26)> drop user HEJIANM cascade; drop user HEJIANM cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-06512: at line 11 Elapsed: 00:00:10.50 sys@ORALOCAL(10.1.26.26)> alter session set events '10046 trace name context off'; Session altered. Elapsed: 00:00:00.01 |
将trace文件做tkprof之后，发现如下的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
TKPROF: Release 10.2.0.1.0 - Production on 星期四 11月 27 15:48:17 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: oralocal_ora_632.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** The following statements encountered a error during parse: select count(*) from mdsys.rdf_rulebase$ where owner = : Error encountered: ORA-00942 -------------------------------------------------------------------------------- DELETE FROM SDO_MAPS_TABLE WHERE '"'||SDO_OWNER||'"' = '"HEJIANM"' Error encountered: ORA-00942 ******************************************************************************** alter session set events '10046 trace name context forever,level 7' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.05 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.05 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS …… |
这这边，做drop user之前，有2个语句和ora942相关了：
（1）select count(*) from mdsys.rdf_rulebase$ where owner = :
（2）DELETE FROM SDO_MAPS_TABLE WHERE ‘”‘||SDO_OWNER||'”‘ = ‘”HEJIANM”‘
上述的2个表：mdsys.rdf_rulebase$ 和SDO_MAPS_TABLE，与正常的库对比，发现在正常的库下，没有mdsys.rdf_rulebase$也能顺利的drop user，但是在正常的库下却是有SDO_MAPS_TABLE表的：
正常库：
|
1 2 3 4 5 |
SQL> select owner from dba_objects where object_name='SDO_MAPS_TABLE'; OWNER ------------------------------ MDSYS |
报错942的测试库：
|
1 2 3 |
sys@ORALOCAL(10.1.26.26)> select owner from dba_objects where object_name='SDO_MAPS_TABLE'; no rows selected |
因此，问题定位到测试库中没有mdsys.SDO_MAPS_TABLE表。
根据metalink，可以用以下方式解决：
|
1 2 3 4 5 6 7 |
To check if Objects Options is installed properly - select * from v$options and see if PARAMETER Objects has a VALUE TRUE.To confirm it try creating a sample type as object.If these succeed then there should'nt be any problems with the Objects option. Follow these steps to install Spatial manually: 1. Connect as SYS using sqlplus 2. Create a user mdsys identified by mdsys 3. Execute the following scripts $ORACLE_HOME/md/admin/mdprivs.sql $ORACLE_HOME/md/admin/mdinst.sql |
由于我之前的mdsys用户已经建立，且应该已经授权，那么我只要再次执行$ORACLE_HOME/md/admin/mdinst.sql 脚本应该就能解决该问题了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
sys@ORALOCAL(192.168.0.128)> @?/md/admin/mdprivs.sql Grant succeeded. Elapsed: 00:00:00.26 Grant succeeded. Elapsed: 00:00:00.25 Grant succeeded. Elapsed: 00:00:00.03 Grant succeeded. Elapsed: 00:00:00.03 Grant succeeded. Elapsed: 00:00:00.03 …… |
不过再次执行drop user，还是报同样的错。
难道是要执行mdprivs.sql？于是，执行了mdprivs.sql和mdinst.sql后，再次drop user，依旧报错……
怎么回事？难道是脚本执行有问题？做了spool log，将脚本执行的过程记录了下来，在第七千多行的log中发现了问题：
|
1 2 3 4 5 6 7 8 9 |
…… ERROR at line 1: ORA-01691: unable to extend lob segment MDSYS.SYS_LOB0000034686C00006$$ by 8 in tablespace SYSAUX ORA-06512: at "SYS.DBMS_LOB", line 646 ORA-06512: at line 150 Elapsed: 00:00:02.31 …… |
呵呵，原来是sysaux的空间满了！！估计在一开始建库的时候，就有这个报错，但是由于log一闪而过了，也就没注意到了这个问题。
由于测试库是文件系统，不是裸设备，将sysaux的数据文件设置autoextend on后，再次执行mdinst.sql，再次drop user，成功！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
sys@ORALOCAL(192.168.0.128)>alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORALOCAL\SYSAUX01.DBF' autoextend on; Database altered. Elapsed: 00:00:01.65 sys@ORALOCAL(192.168.0.128)>@?/md/admin/mdinst.sql …… PL/SQL procedure successfully completed. Elapsed: 00:00:00.37 COMP_ID SCHEMA ------------------------------ ------------------------------ VERSION STATUS ------------------------------ ---------------------- COMP_NAME -------------------------------------------------------------------------------- SDO MDSYS 10.2.0.1.0 VALID Spatial 1 row selected. Elapsed: 00:00:00.09 Commit complete. Elapsed: 00:00:00.01 no rows selected Elapsed: 00:00:00.04 Session altered. Elapsed: 00:00:00.01 sys@ORALOCAL(192.168.0.128)> sys@ORALOCAL(192.168.0.128)> sys@ORALOCAL(192.168.0.128)> drop user HEJIANM cascade; User dropped. Elapsed: 00:00:03.93 sys@ORALOCAL(192.168.0.128)> sys@ORALOCAL(192.168.0.128)> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORALOCAL\SYSAUX01.DBF' autoextend off; Database altered. Elapsed: 00:00:01.65 sys@ORALOCAL(192.168.0.128)> |
总结：建库的时候，特别是手工建库的时候一定要注意log是否有报错，一定要让表空间留有足够的剩余空间。