Why user can't drop his own table and why this error is shown ORA-01017?
SQL> SELECT * FROM V$VERSION; Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.7.0.25.01 Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
SQL> show user USER is "LAB"
SQL> create table TEST1(test1 varchar2(1)) ;
Table created.
SQL> select DBMS_METADATA.GET_DDL('TABLE','TEST1') from DUAL;
CREATE TABLE "LAB"."TEST1" ( "TEST1" VARCHAR2(1), "TEST2" VARCHAR2(1) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "LAB"
SQL> drop table LAB.TEST1; drop table LAB.TEST1 * ERROR at line 1: ORA-00604: Error occurred at recursive SQL level 1. Check subsequent errors. ORA-01017: invalid credential or not authorized; logon denied
Your error stack states:
ORA-00604: Error occurred at recursive SQL level 1
Note that it was recursive SQL - something that runs under the covers as a side-effect of your command, not your command itself. That's very likely a trigger. Secondly, the original error is ORA-01017: invalid credential or not authorized; logon denied, which is a very unusual error to get on a DDL. Something appears to be trying to make a connection to another Oracle database - again, something that most likely is happening from a trigger. Maybe the DBAs have a trigger that logs DDL in some central repository database and that link is broke.
To see the triggers, query:
SELECT *
FROM dba_triggers
WHERE base_object_Type LIKE 'DATABASE%'
AND (triggering_event LIKE '%DROP%' OR triggering_event LIKE '%DDL%')
AND status = 'ENABLED'
Read through the trigger code and see if any trigger looks like it's trying to communicate with a remote database. If you can't query this view, you'll have to talk to your DBA to look into it for you. They'll probably have to fix whatever's wrong anyway.
If you're unable to find a trigger that explains the issue, then enable 10046 tracing in your session (ALTER SESSION SET EVENTS '10046 trace name context forever, level 8') and run the erroring command again in the same session. This requires the alter session system priv, which you may not have by default. Then ask your DBA to find the trace file on the database server and provide it or examine it to see what the last command is before the ERR is thrown in the trace. By matching up the cursor id # to the last PARSE event of that same #, it'll show the recursive SQL that threw the error.