Category: ORA- ERRORS

ORA-28040: No matching authentication protocol

After Oracle database server upgraded from 11.2.0.4 to 12.2.0.1, client application connections throw the following errors:

at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:83)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
... 27 more
Caused by: java.sql.SQLException: ORA-28040: No matching authentication protocol

SOLUTION

Add the following line into sqlnet.ora file of ORACLE_HOME:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Then client application connects to new database working fine.

ORA-02421: missing or invalid schema authorization identifier

SQL>  alter session set current_schema='sysman';
 alter session set current_schema='sysman'
                                  *
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

The right syntax should be :

SQL>   alter session set current_schema=sysman;

Session altered.

Oracle Fails to Drop Unused Columns

When trying to drop an Oracle table unused columns, the sql command executes and shows successful, but actually the unused columns are not dropped. 

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

Further investigation shows the table is created with compression :

SQL>select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TRANSACTIONS';

OWNER    TABLE_NAME   COMPRESS  COMPRESS_FOR
-------- ------------ --------- -------------
TESTER    TRANSACTIONS ENABLED    ADVANCED

Change table to uncompressing status without moving data:

SQL>  alter table TRANSACTIONS NOCOMPRESS;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;
ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Have to move table with nocompress option, then drop unused columns successfully.

SQL> alter table TRANSACTIONS move nocompress;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

no rows selected

Oracle Multitenant Database System User ORA-01031: insufficient privileges

DBA is trying to setup a job to purge table “sys.aus$” periodically with following sql and errors:

SQL> show user;

USER is "SYSTEM"

SQL> delete FROM sys.aud$ WHERE  NTIMESTAMP# < sysdate -31;

ERROR at line 1:
ORA-01031: insufficient privileges

Solution

Though SYSTEM user has DBA role privilege, the specific object privilege is still needed in this case:

SQL> grant delete on sys.aud$ to system container=all;

Grant succeeded.

SQL>  delete FROM sys.aud$ WHERE  NTIMESTAMP# < sysdate -31;

2006228 rows deleted.


SQL> commit;

Commit complete.

ORA-04021: timeout occurred while waiting to lock object

When manually compiling a package body, the following error occurs:

SQL> alter package USER.PKGNAME compile body;
alter package USER.PKGNAME compile body
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object USER.PKGNAME

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe