Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem dropping user cascade !!!

Henrique Silva_3
Regular Advisor

Problem dropping user cascade !!!

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
"to be or not to be, what was the question ???? "
10 REPLIES
Ian Lochray
Respected Contributor

Re: Problem dropping user cascade !!!

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.
Cheryl Griffin
Honored Contributor

Re: Problem dropping user cascade !!!

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
"Downtime is a Crime."
John Palmer
Honored Contributor

Re: Problem dropping user cascade !!!

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

Regards,
John
Steven E. Protter
Exalted Contributor

Re: Problem dropping user cascade !!!

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Brian Crabtree
Honored Contributor

Re: Problem dropping user cascade !!!

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

Brian
Yogeeraj_1
Honored Contributor

Re: Problem dropping user cascade !!!

hi,


the output of the following can shed some light:

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

hth
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
T G Manikandan
Honored Contributor

Re: Problem dropping user cascade !!!

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
Henrique Silva_3
Regular Advisor

Re: Problem dropping user cascade !!!

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
"to be or not to be, what was the question ???? "
John Palmer
Honored Contributor

Re: Problem dropping user cascade !!!

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
Yogeeraj_1
Honored Contributor

Re: Problem dropping user cascade !!!

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)