DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Drop user ORA-00942, ORA-00604, ORA-06512

  1. #1
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82

    Drop user ORA-00942, ORA-00604, ORA-06512

    Hello all!

    When I want to drop user i get the following error :

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

    Database version
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production


    Thank you for your help

    Regards Aljaz

  2. #2
    Join Date
    Oct 2001
    Posts
    45
    Run script $ORACLE_HOME/md/admin/catmd.sql as user MDSYS, then drop the user.

  3. #3
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    Thank you for your replay.

    I did that, but the error is stil here.

    Why must I install spatial option if I want to drop user????

    That does not make sence. (as usually by Oracle)

    Help still needed.

    Regards
    Aljaz

  4. #4
    Join Date
    May 2001
    Posts
    736
    The user which u are trying to drop has any objects in his schema.Also check are there any triggers which are preventing u from dropping the user.

  5. #5
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    User a is an account not schema.

    I have enabled sql_trace and from what I can see there is a problem with XDB user.

    When Oracle drop user it checks table xdb.xbd$schema, becouse this table does not exist the drop command fails.

    It seems that I have to create XDB schema.

    I have never manually installed XDB user and this is the production database so there is no room for mistakes.

    Any expirances with install XDB schema.

    Procedure for installing is :
    SQL> CREATE TABLESPACE "XDB"
    2 LOGGING DATAFILE '/u10/app/oradata/MYDB/xdb01.dbf' SIZE 50M REUSE
    3 AUTOEXTEND ON NEXT 50M MAXSIZE 1500M
    4 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

    SQL> @catqm.sql change_on_install XDB TEMP

    SQL> @catxdbj.sql


    Then you adjust dispatchers parameter.

    And after that everything will be OK?
    Right?
    Last edited by Aljaz; 09-08-2004 at 07:16 AM.

  6. #6
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82

    Wink

    I had to create few synonyms for mdsys objects.

    Now it is working.

    Thank you all for your help.

  7. #7
    Join Date
    Oct 2001
    Posts
    45
    Well, we can prove this by turn on the trace.

    alter session set sql_trace=true;

    drop user a;

    In the tracefile, you should be able to see the statement like

    DELETE FROM SDO_GEOM_METADATA_TABLE
    WHERE
    '"'||SDO_OWNER||'"' = '"A"'

    You can check your database whether table SDO_GEOM_METADATA_TABLE exists or not.

    SQL> select owner from dba_tables where table_name='SDO_GEOM_METADATA_TABLE';

    OWNER
    -----------------------
    MDSYS

    In your case, you are supposed to see some error messages like

    ORA-00942: table or view does not exist
    Current SQL statement for this session:
    DELETE FROM SDO_GEOM_METADATA_TABLE WHERE '"'||SDO_OWNER||'"' = '"A"';

    Here the error ORA-00942 refer to table SDO_GEOM_METADATA_TABLE. This is the reason why you need to install Oralce Spatial as the missing table is owned by MDSYS.

    Make sure you install Oralce Spatial correctly and table SDO_GEOM_METADATA_TABLE
    does exist in your database.
    Last edited by xiaomao; 09-08-2004 at 08:08 AM.

  8. #8
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    xiaomao thanks for your help.

    It works fine.

    Regards
    Aljaz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width