ORA-01031: insufficient privileges while creating a database link

Problem:

While creating a database link, the user is getting insufficient privileges error:

SQL> create database link ASCP_TO_PROD connect to apps_ro identified by apps_ro using 'PROD';

ORA-01031: insufficient privileges while creating a database link


Fix:

SQL> alter session set current_schema = APPS_RO;

Session altered.

SQL> create database link ASCP_TO_PROD connect to apps_ro identified by apps_ro using 'PROD';

Database link created.

User wants to query objects without prefix

Problem:

We have granted user DUMMY with select on all tables and views of APPS schema. But the user wants to access the tables of APPS schema without using prefix.

SQL> select count(*) from fnd_user;

ORA-00942: table or view does not exist

Fix:

Create a trigger for schema to set current_schema at session level to apps and user need not use the prefix anymore.

SQL> conn apps/apps

SQL> CREATE OR REPLACE TRIGGER dummy_logon_trg
AFTER logon ON DUMMY.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

SQL> conn dummy/dummy

SQL> select count(*) from fnd_user;

COUNT(*)
----------
43

Gather Schema Statistics failing after upgrading DB to 11.2.0.2

Problem:

After upgrading the database to 11.2.0.2, Gather Schema Statistics was failing with following errors in the log file:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GME.GME_BATCH_HEADER***ORA-12805: parallel query server died unexpectedly***

Following errors are observed in alert log as well:

ORA-00600: internal error code, arguments: [kgh_heap_sizes:ds], [0x2B5FAA85D510], [], [], [], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [qesaMSR2SorRecMerge()+265] [SIGSEGV] [ADDR:0x30] [PC:0x139B739] [Address not mapped to object] []

ORA-00603: ORACLE server session terminated by fatal error
ORA-24557: error 600 encountered while handling error 600; exiting server process
ORA-00600: internal error code, arguments: [17147], [0x2B4C94C114F8], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17147], [0x2B4C94C114F8], [], [], [], [], [], [], [], [], [], []
ORA-00039: error during periodic action
ORA-00600: internal error code, arguments: [17114], [0x2B4C94C114F8], [], [], [], [], [], [], [], [], [], []
opidrv aborting process P007 ospid (23806) as a result of ORA-603

Fix:
Bug 10013177: FUNCTIONAL INDEX CAUSES MAX DECODE GROUP BY SQL TO TRUNCATE VALUES:

Workaround is:

1) Set _replace_virtual_columns=false

2) Set _disable_function_based_index=true

3) Gathering table stats using degree 1.

Permanent fix is:

Apply Interim patch 10013177