happydba

HTML clipboard .style1 { color: #FF0000; } .style2 { background-color: #C0C0C0; }

	文档 ID:	434557.1	类型:	PROBLEM
	Modified Date:	08-NOV-2007	状态:	PUBLISHED

In this Document
Symptoms
Cause
Solution
References

---

Applies to:

Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform.

Symptoms

CREATE DATABASE statement fails with ORA-604 / ORA-942 while executing SQL.BSQ :

ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file ‘%ORACLE_HOME%\RDBMS\ADMIN\SQL.BSQ’ near line ..
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist

Cause

The SYSAUX tablespace is too small.

To capture the failing SQL statements, add the following line to the database init.ora file and re-run the create database command.

EVENT=”604 trace name errorstack level 3: 10046 trace name context forever, level 4″

The trace file generated in udump (or user_dump_dest location) shows the failing SQL statement to be:

PARSE ERROR #6:len=148 dep=2 uid=0 oct=3 lid=0 tim=3388826914006940 err=942
select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and
to_number(bitand(flags, 16)) = 16 order by dropsc
EXEC #5:c=10439,e=12665,p=0,cr=57,cu=67,mis=0,r=0,dep=1,og=4,tim=3388826914007099
ERROR #5:err=604 tim=201164619
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist
Offending statement at line 6377
create table aw_obj$ /* Analytical Workspace Object table */
(awseq# number, /* aw sequence number */
oid number(20), /* object number, up to UB8MAXVAL */
objname varchar2(256), /* object name, ref NAMESIZE in xsobj.c */
gen# number(10), /* generation number */
objtype number(4), /* object type */
partname varchar2(256), /* partition name */
objdef blob, /* object definition */
objvalue blob, /* object value */
compcode blob) /* compiled code body */
lob(objdef) store as (enable storage in row)
lob(objvalue) store as (enable storage in row)
lob(compcode) store as (enable storage in row)
tablespace sysaux
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file ‘//DD:SQLBSQ’ near line 6377
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist

Re-running the CREATE DATABASE, but preceding it with ‘alter system recyclebin=off‘ statement, to disable the recyclebin option gives the following results:

ORA-1652: unable to extend temp segment by 16 in tablespace SYSAUX
12:09:54.66 00030000 Errors in file ORADBTS0.TRACE.D070523.T100855.G0030000:
ORA-00604: error occurred at recursive SQL level 1
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSAUX

The actual CREATE DATABASE statement was:

CREATE DATABASE TS0
MAXDATAFILES 255
MAXLOGFILES 255
CONTROLFILE REUSE
LOGFILE GROUP 1 (‘ORADBTS0.REDO.G1.M1′,
‘ORADBTS0.REDO.G1.M2′) SIZE 25063424 REUSE,
GROUP 2 (‘ORADBTS0.REDO.G2.M1′,’ORADBTS0.REDO.G2.M2′) SIZE 25063424 REUSE
DATAFILE ‘ORADBTS0.DATA.SYSTEM.D01′ SIZE 200156K REUSE,
‘ORADBTS0.DATA.SYSTEM.D02′ SIZE 200156K REUSE
SYSAUX DATAFILE ‘ORADBTS0.DATA.SYSAUX.D01′ SIZE 200156 REUSE
UNDO TABLESPACE “UNDOTBS”
DATAFILE ‘ORADBTS0.DATA.UNDO.D01′ SIZE 200156K,
‘ORADBTS0.DATA.UNDO.D02′ SIZE 200156K
DEFAULT TABLESPACE USERS
DATAFILE ‘ORADBTS0.DATA.USER.D01′ SIZE 40316 K REUSE,
‘ORADBTS0.DATA.USER.D02′ SIZE 40316 K REUSE
DEFAULT TEMPORARY TABLESPACE “TEMP”
TEMPFILE ‘ORADBTS0.DATA.TEMP.D01′ SIZE 236924 K REUSE
CHARACTER SET “EE8EBCDIC870S”

The SYSAUX datafile has a size specification of 200156 bytes.  The intention was that this should have been 200156K to match the SYSTEM and UNDO tablespaces.

Solution

Increase the size of SYSAUX tablespace in the CREATE DATABASE statement.

From Oracle® Database Administrator’s Guide10g Release 2 (10.2)

Chapter 2 – Creating an Oracle Database
Creating the SYSAUX Tablespace

“The SYSAUX tablespace needs to be at least 240 MB at the time of database creation. The space requirements of the SYSAUX tablespace will increase after the database is fully deployed … ”

References

Note 265253.1 – 10g Recyclebin Features And How To Disable it( _recyclebin )

 

	文档 ID:	269469.1	类型:	PROBLEM
	Modified Date:	30-OCT-2008	状态:	PUBLISHED

---

Applies to:

Oracle Server – Enterprise Edition -
This problem can occur on any platform.

Symptoms

The command “DROP USER” fails with error: ORA-00942 and ORA-00604

For example:

SQL> drop user testb;
drop user testb
*
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 7

Cause

Table SDO_GEOM_METADATA_TABLE (part of Oracle Spatial) not present in the
DB.

This can be confirmed the doing the following:

alter session set sql_trace=true;

–OR–

alter session set events’10046 trace name context forever,level 4′;

drop user cascade;

The error tracefile will contain the failing statement.

For example:

ORA-00942: table or view does not exist
Current SQL statement for this session:
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = ‘TESTA’

Solution

Run the script catmd.sql (located in $ORACLE_HOME/md/admin dir).

The catmd.sql script is the script that loads all objects needed by Oracle spatial in the
database. Then drop the user.

References

Note 110217.1 – SDO 8.1.x: Steps for Manual Installation of Oracle 8i Spatial Data Option
Note 303975.1 – Dropping user results in ORA-942 against SDO_GEOM_METADATA_TABLE

================================================================================

(2)

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1.0 to 11.1.0.6.0
This problem can occur on any platform.

Symptoms

When trying to drop a user, the command fails with errors ORA-00604 and ORA-00942

Example
————-

SQL> drop user test ;
drop user test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

SQL trace (10046) trace will show a dml operation performed on system.aq$_internet_agent_privs
table.

———————————————————————————————————-

PARSE ERROR #5:len=78 dep=1 uid=0 oct=7 lid=0 tim=1416609304389 err=942
delete from system.aq$_internet_agent_privs WHERE db_username = NLS_UPPER(:1)
EXEC #1:c=70000,e=552054,p=148,cr=448,cu=0,mis=0,r=0,dep=0,og=4,tim=
1416609309213
ERROR #1:err=604 tim=145060793
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=3490 op=’TABLE ACCESS FULL OBJ#(3490) ‘

———————————————————————————————————-

Cause

The table system.aq$_internet_agent_privs is missing.

Solution

To implement the solution, execute the following steps:

1.Check if system.aq$_internet_agent_privs exist.

SQL> conn / as sysdba
SQL> desc system.aq$_internet_agent_privs

2.Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

SQL> select default_tablespace from dba_users where username=’SYSTEM’;

DEFAULT_TABLESPACE
——————————
SYSTEM

3.If system.aq$_internet_agent_privs does not exist, run $ORACLE_HOME/rdbms/admin/catqueue.sql
script manually logged in as ‘SYS AS SYSDBA’. This will create the system.aq$_internet_agent_privs
table

SQL> conn / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql
SQL> exit

4.Confirm that system.aq$_internet_agent_privs is created properly:

SQL> desc system.aq$_internet_agent_privs

Name Null? Type
———– ——– ————
AGENT_NAME NOT NULL VARCHAR2(30)
DB_USERNAME NOT NULL VARCHAR2(30)

5.Then execute the DROP USER command again.