| User cannot be dropped [message #52534] |
Mon, 29 July 2002 01:38  |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
I am trying to drop a user in the Oracle database (Oracle 8.1.6.0.0), but always give me an error message:
VBO5530: User cannot be dropped.ORA-00600:internal error code, arguments:[[12899]],[[217268]],[[305]],[[]],[[]],[[]],[[]],[[]].
Could someone help me, please?
Thanks
|
|
|
|
|
|
|
|
| Re: User cannot be dropped [message #52554 is a reply to message #52537] |
Mon, 29 July 2002 16:57  |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
Hi...
There are no dependencies for that user. I have checked all objects in schema, and there is no objects belong to that user. I have tried also using DROP user ... CASCADE, and it gives me same error message.
Please advise..
|
|
|
|
| Re: User cannot be dropped [message #52571 is a reply to message #52534] |
Tue, 30 July 2002 07:15 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
You should not be getting an ORA-00600 error. Have you checked all your data files for corruptness. Run this script. It will generate a script to run. This is for unix but you can change it to a batch file if running windows. Don't run this online if you are windows. Shut the DB first. If u r running unix it can run online. This runs dbv (DB Verify).
set serveroutput on
set echo off
set head off
set pages 0
set feedback off
set linesize 100
spool dbv.sh
select 'dbv '||' file='||file_name||' feedback=300'||' BLOCKSIZE='||value
from dba_data_files, v$parameter
where v$parameter.name = 'db_block_size';
spool off;
!chmod +x dbv.sh
!./dbv.sh
Here is a script to drop all users objects. Once u run this try to do a "drop user USERNAME cascade;". Also connect as DBA and do a "select object_name,object_type from dba_objects where owner='SCHEMA_TO_DROP'" and see if the schema does have objects. I hope it isn't a bad datafile not letting go of the objects. Good luck.
rem =========================================================================
rem
rem drop_user_objects.sql
rem
rem Copyright (C) Oriole Software, 1999
rem
rem Downloaded from http://www.oriolecorp.com
rem
rem This script for Oracle database administration is free software; you
rem can redistribute it and/or modify it under the terms of the GNU General
rem Public License as published by the Free Software Foundation; either
rem version 2 of the License, or any later version.
rem
rem This script is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
rem GNU General Public License for more details.
rem
rem You should have received a copy of the GNU General Public License
rem along with this program; if not, write to the Free Software
rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem
rem =========================================================================
rem
rem This script, to be run by a DBA, generates a script to drop
rem all of a user's objects - while retaining the Oracle account with
rem all its privileges. This is especially useful before reimporting
rem data on a per-user basis, for instance to update the contents of
rem a test database.
rem
rem Usage : @drop_user_objects <username>
rem
rem It generates drop_<username>_objects.sql
rem
rem Note that it take cares of dropping only objects which do not
rem depend on other objects (indexes and triggers for instance are
rem dropped with the associated tables, as are package bodies with
rem the associated packages) in order to avoid harmless but irritating
rem error messages. However, no in-depth checking of dependencies is
rem performed.
rem This may lead to a number of misleading error messages :
rem - if you are using clusters (God forbid!) the generated script will
rem try to drop the clustered tables after the cluster is gone.
rem - with Oracle8, 'hidden' tables such as overflow tables for index
rem organized tables will be explicitly dropped after the main
rem table has been dropped.
rem - etc.
rem
rem As a rule, do not worry about '... does not exist' messages
rem
rem Note that, when run, the script which actually drops the objects
rem will generate a spool file.
rem
set pause off
set echo off
set scan on
set verify off
set pagesize 0
set feedback off
set recsep off
set termout off
spool drop_&1._objects.sql
select 'spool drop_&1._objects.log'
from dual
/
select 'drop ' || object_type || ' ' || owner || '.' || object_name
|| decode(object_type,
'CLUSTER', ' including tables cascade constraints;',
'TABLE', ' cascade constraints;',
';')
from dba_objects
where owner = upper('&1')
and object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
'FUNCTION',
'PROCEDURE', 'PACKAGE')
/
select 'spool off'
from dual
/
spool off
set feedback on
set pagesize 24
set termout on
prompt Run @drop_&1._objects to drop &1's objects ...
|
|
|
|
| Re: User cannot be dropped [message #52577 is a reply to message #52537] |
Tue, 30 July 2002 08:15 |
Radek
Messages: 33
Registered: April 2002
|
Member |
|
|
Hi,
I locked the user just for security purposes. You can try it again after restart database without locking.For me it happened in staging database that I can restart only during scheduled downtime. By locking I just prevented others to login as ill-fated user.
Hope this helps,
Radek
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
