topic Re: Problem dropping user cascade !!! in Operating System - HP-UX

Problem dropping user cascade !!!

Henrique Silva_3 — Mon, 03 Mar 2003 14:38:02 GMT

I had some problems when installing oracle on this box, but was able to after playing with some kernel parameters.

did the patch install. Running
oracle 8.1.7.4 ( 64 bit )on hpux 11 64 bit OS

created DB fine, using scripts from $ORACLE_HOME/assistant/dbca that I have been using from other boxes.

logged in to DB, created user, try toload dump and it failed. Fixed the problem (additional system roll back segment was needed ) and when I was trying to drop the user to load the dump again, I get this error :

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

re-run catalog.sql and catproc.sql to make sure it was not missing any data dictionary views.

tried again and same problem.

B4 I get rid of this instance and create a new one, I was wondering if anybody is seen such a problem, and if they know if I need to run something else.

This instance was created on this box, and it was not copied over from some other box.

Oh, I also relinked the binaries this morning, before running the sql scripts.

Thanks,

Henrique Silva

Re: Problem dropping user cascade !!!

Ian Lochray — Mon, 03 Mar 2003 14:46:14 GMT

Is there any additional information in the alert log. I have seen a similar issue reported on Metalink and the answer was that the package 'SYS.DBMS_AQ_SYS_EXP_INTERNAL' was in error. Recreating the package fixed that particular problem.

Re: Problem dropping user cascade !!!

Cheryl Griffin — Mon, 03 Mar 2003 14:49:30 GMT

http://www-rohan.sdsu.edu/doc/oracle/errmsg803/A54625_01/newch2b.htm suggests for both errors says your dictionary tables have a problem. There is also a mention that this may be an Oracle 8.1.7 bug. Try checking otn.oracle.com
Cheryl

Re: Problem dropping user cascade !!!

John Palmer — Mon, 03 Mar 2003 14:50:52 GMT

Metalink suggests that one or more of the AQ tables may have been dropped. Suggests running catnoqueue.sql then catqueue.sql.

Regards,
John

Re: Problem dropping user cascade !!!

Steven E. Protter — Mon, 03 Mar 2003 21:33:07 GMT

It is most likely that some step in the database creation process errored out and you missed the error.

This is not a database migration as that bring other factors into it.

You should also verify all database objects in sql.

Not a likely cause but.
To run this version of Oracle, they recomend the December 2002 patch set plus the patches recommended by OS on metalink.com in document.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=43507.1

SEP

Re: Problem dropping user cascade !!!

Brian Crabtree — Mon, 03 Mar 2003 21:50:35 GMT

You might also want to verify that you are using the correct patchset version as well, and not the 32bit patchset version.

Brian

Re: Problem dropping user cascade !!!

Yogeeraj_1 — Tue, 04 Mar 2003 04:27:10 GMT

hi,

the output of the following can shed some light:

exp someuser/password file=someuser.dmp rows=no

hth
Yogeeraj

Re: Problem dropping user cascade !!!

T G Manikandan — Tue, 04 Mar 2003 07:07:53 GMT

probably you should minimise the problem by dropping the tables (one by one) using the script and finding out the table that is causing the problem.

If those are due to the AQ tables do not run catnoqueue.sql.

Try stopping the queue's and then dropping them and then dropping those tables.

You can do a query on
select * from all_queues
to find any queues that are running.

Revert

Re: Problem dropping user cascade !!!

Henrique Silva_3 — Tue, 04 Mar 2003 11:40:43 GMT

Thanks everyone.

I tried a few suggestions but nothing. So, I bit the bullet, dropped the database and recreated.

everything is fine now. Not sure what went wrong at DB creation B4. It had failed in the middle of the DBNAMErun1.sh script, but I had gone back, fixed the problem and re-run the creation scriptfromthat point on, and no more problems were there.

Now, can you explain to me what is the purpose of these queues ? I have not heard of them B4.

Thanks,

Henrique

Re: Problem dropping user cascade !!!

John Palmer — Tue, 04 Mar 2003 11:49:33 GMT

AQ is Oracle's Advanced Queuing. There's an overview here (you'll have to register if you haven't already): http://otn.oracle.com/products/aq/htdocs/aq9i_overview.html

Regards,
John

Re: Problem dropping user cascade !!!

Yogeeraj_1 — Tue, 04 Mar 2003 11:57:16 GMT

hi henrique,

About Queues:
In queuing systems, producer applications put messages into a queue (enqueue). In the simple case, messages are then retrieved from the same queue by Consumer applications (dequeue). This lets applications can continue with their work after placing a request in the queue because they are not blocked waiting for a reply. It is also allows applications to continue with their work until there is a message to retrieve.

The way queues work is that you would start your process externally (eg: I put
them in my startup scripts so that after the database is started -- the daemons
get started). It is upto you to get them going and keep them going. We do not
manage the "dequeue" processes (which may or may not even be on the same machine
with the database)

HTH
Yogeeraj