Make DBA Life Easy

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.

OEM NAMED CREDENTIAL : java.util.HashMap cannot be cast to oracle.sysman.emSDK.sec.targetauth.EMTargetConnectionPool

On OEM, when DBA trying to browse database performance or top activity page,
The following error occurs:

java.util.HashMap cannot be cast to oracle.sysman.emSDK.sec.targetauth.EMTargetConnectionPool

Checked named credential working fine against database target, and preferred credential has been setup against database target accordingly.

Subscribe to get access

Read more of this content when you subscribe today.

ORA-00942: table or view does not exist ORA-01031: insufficient privileges while creating materialized view

ORA-00942 or ORA-01031 error occurs while creating a materialized view from a table of another schema.

Source Table : USERA.TABLE_A
Materialized View : USERB.TABLE_A_MV

SQL> show user
USER is "USERB"
SQL>  create materialized view TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
                           *
ERROR at line 5:
ORA-12018: following error encountered during code generation for
"USERB"."TABLE_A_MV"
ORA-00942: table or view does not exist

But USERB has no problem to access USERA.TABLE_A:

SQL> show user
USER is "USERB"
SQL> select count(*) from  usera.table_a;
  COUNT(*)
----------
   1623583

If creating the materialized view by sys user, then gets another different error:

SQL> show user
USER is "SYS"
SQL> create materialized view USERB.TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
*
ERROR at line 4:
ORA-01031: insufficient privileges

Subscribe to get access

Read more of this content when you subscribe today.

How to Upgrade AWS RDS PostgreSQL With Extensions(5)

In another four posts, we have upgraded from AWS PostgreSQL RDS 9.5.23-R1 to 9.6.20-R1, 9.6.20-R1 to 10.15, 10.15 to 11.10 and 11.10 to 12.5 respectively.

how to Upgrade AWS RDS PostgreSQL with Extensions(1) from 9.5 to 9.6
How to Upgrade AWS RDS PostgreSQL with Extensions(2) from 9.6 to 10.15
How to Upgrade AWS RDS PostgreSQL with Extensions(3) from 10.15 to 11.10
How to Upgrade AWS RDS PostgreSQL with Extensions(4) from 11.10 to 12.5

According to AWS doc about PostGIS extension:

If a database uses the PostGIS extension, you can’t skip major versions for some source to target combinations. For these circumstances, upgrade in steps to the next major version one step at a time until you reach the desired target version.

So in this post, we will show how to upgrade AWS PostgreSQL RDS 12.5 with PostGIS extension to AWS PostgreSQL RDS 13.1.

Subscribe to get access

Read more of this content when you subscribe today.

Tasks of After Major Version Upgrade

Run the ANALYZE operation to refresh the pg_statistic table.
Optionally, use Amazon RDS to view two logs that the pg_upgrade utility produces. These are pg_upgrade_internal.log and pg_upgrade_server.log. Amazon RDS appends a timestamp to the file name for these logs. You can view these logs as you can any other log. For more information, see Amazon RDS database log files.You can also upload the upgrade logs to Amazon CloudWatch Logs. For more information, see Publishing PostgreSQL logs to CloudWatch Logs.
To verify that everything works as expected, test your application on the upgraded database with a similar workload. After the upgrade is verified, you can delete this test instance.

How to Upgrade AWS RDS PostgreSQL With Extensions(4)

In another three posts, we have upgraded from AWS PostgreSQL RDS 9.5 to 9.6, 9.6 to 10.15, and 10.15 to 11.10 respectively.

According to AWS doc about PostGIS extension:

So in this post, we will show how to upgrade AWS PostgreSQL RDS 11.10 with PostGIS extension to AWS PostgreSQL RDS 12.5.

Subscribe to get access

Read more of this content when you subscribe today.