dbms_utility上的Oracle错误

Question

当我尝试创建一个超过30个字符的数据库用户，然后在其中(在一个脚本中)创建触发器时，出现了这个问题。用户没有被创建，因为它有超过30个字符，但不知何故触发器被创建了。现在我不能再登录到数据库了，当我的java应用程序试图连接到这个数据库时，我抛出了下面的异常。

 Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory 
 (ORA-00604: error occurred at recursive SQL level 1
 ORA-00972: identifier is too long
ORA-06512: at "SYS.DBMS_UTILITY", line 833
ORA-06512: at "SYS.DBMS_SESSION", line 230
ORA-06512: at line 2
)
    at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2303)
    at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2043)
    at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1543)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:139)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:380)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:228)
    ... 95 more
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL 
level 1
ORA-00972: identifier is too long
ORA-06512: at "SYS.DBMS_UTILITY", line 833
ORA-06512: at "SYS.DBMS_SESSION", line 230
ORA-06512: at line 2

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
    at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:401)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
    at org.apache.tomcat.dbcp.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.tomcat.dbcp.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:257)
    at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2313)
    at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2299)

如何删除此触发器或任何其他方法来纠正此问题？

我执行的脚本：(但我之前有超过30个字符，而不是TESTDB )

-- USER SQL
CREATE USER TESTDB IDENTIFIED BY nodba ;


-- ROLES

GRANT "CONNECT" TO TESTDB ;

-- SYSTEM PRIVILEGES
GRANT SELECT ANY TABLE TO TESTDB ;
GRANT SELECT ANY SEQUENCE TO TESTDB ;
GRANT SELECT ANY TRANSACTION TO TESTDB ;
GRANT UPDATE ANY TABLE TO TESTDB ;
GRANT INSERT ANY TABLE TO TESTDB ;
GRANT DELETE ANY TABLE TO TESTDB ;

create role TestRole;

grant TestRole to TESTDB;


ALTER SESSION SET CURRENT_SCHEMA = TESTDB;

create or replace trigger readonly_logon_trigger
after logon on database
begin
if (dbms_session.is_role_enabled('TestRole')) then
 execute immediate 'alter session set current_schema = MAINDB';
end if;
end;

Answer 1

触发器是在系统级别添加的，其中超过30个字符导致了此问题。现在已经解决了。