为什么Oracle同义词将不同数量的行返回到基础表? [英] Why would an Oracle synonym return a different number of rows to the underlying table?

问题描述

我有一个非常不寻常的情况，我希望有人能够对此有所了解.我对oracle同义词的理解是，它基本上是另一个模式中表的别名.

I have a very unusual situation that I am hoping someone will be able to shed some light onto. My understanding of an oracle synonym is that it is basically an alias to a table in another schema.

当我根据同义词进行计数时，它将返回零行.当我在基础表中执行相同操作时，它将返回12,000行.

When I do a count from the synonym, it returns zero rows. When I do the same from the underlying table, it returns 12 thousand rows.

我无法解释这种差异.有人可以帮忙吗?

I cannot explain this discrepancy. Can anyone help?

select * from dba_synonyms
where synonym_name = 'CS_INCIDENTS_B_SEC';

OWNER  SYNONYM_NAME        TABLE_OWNER  TABLE_NAME          DB_LINK
------ ------------------- ------------ ------------------- -------
APPS   CS_INCIDENTS_B_SEC  CS           CS_INCIDENTS_ALL_B         



select count(*) from CS.CS_INCIDENTS_ALL_B;

COUNT(*)               
---------------------- 
12549                  

select count(*) from APPS.CS_INCIDENTS_B_SEC;

COUNT(*)               
---------------------- 
0                      

说明计划:

直接在桌子上...

EXPLAIN PLAN FOR
SELECT  *
FROM    CS.CS_INCIDENTS_ALL_B

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows | Bytes| Cost(%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    | 6056 | 1549K|  122   (3)|
|   1 |  TABLE ACCESS FULL| CS_INCIDENTS_ALL_B | 6056 | 1549K|  122   (3)|
--------------------------------------------------------------------------

通过同义词...

EXPLAIN PLAN FOR
SELECT  *
FROM    APPS.CS_INCIDENTS_B_SEC

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows | Bytes| Cost(%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |    1 |  262 |    0   (0)|
|*  1 |  FILTER            |                    |      |      |           |
|   2 |   TABLE ACCESS FULL| CS_INCIDENTS_ALL_B | 6056 | 1549K|  122   (3)|
---------------------------------------------------------------------------

1 - filter(NULL IS NOT NULL)

同义词链...

SQL> SELECT  *
  2  FROM    dba_synonyms
  3  START WITH
  4          owner = 'CS'
  5          AND synonym_name = 'CS_INCIDENTS_ALL_B'
  6  CONNECT BY
  7          owner = PRIOR table_owner
  8          AND synonym_name = PRIOR table_name
  9  /

no rows selected

SQL> SELECT  *
  2  FROM    dba_synonyms
  3  START WITH
  4          owner = 'APPS'
  5          AND synonym_name = 'CS_INCIDENTS_B_SEC'
  6  CONNECT BY
  7          owner = PRIOR table_owner
  8          AND synonym_name = PRIOR table_name
  9  /

正在检查数据库策略...

Checking Policies on database...

SQL> SELECT *
  2  FROM dba_policies
  3  WHERE OBJECT_NAME = 'CS_INCIDENTS_B_SEC'
  4  /

OBJECT_OWNER  OBJECT_NAME         POLICY_GROUP  POLICY_NAME          
------------- ------------------- ------------- -------------------- 
APPS          CS_INCIDENTS_B_SEC  SYS_DEFAULT   CS_SR_SEC_SR_ACCESS  


PF_OWNER  PACKAGE            FUNCTION       SEL INS UPD DEL IDX CHK 
--------- ------------------ -------------- --- --- --- --- --- --- 
APPS      FND_GENERIC_POLICY GET_PREDICATE  YES NO  NO  NO  NO  NO  


ENABLE STATIC_POLICY POLICY_TYPE  LONG_PREDICATE
------ ------------- ------------ --------------
YES    NO            DYNAMIC      YES

推荐答案

更新:

您有 ROW LEVEL SECURITY 已启用.

You have ROW LEVEL SECURITY enabled.

每次访问表时都会调用用户功能FND_GENERIC_POLICY.GET_PREDICATE，并限制对某些行的访问.

The user function FND_GENERIC_POLICY.GET_PREDICATE gets called each time you access the table and limits access to some rows.

根据您如何访问表，它会返回不同的结果:直接还是通过SYNONYM.

It returns different results depending on how do you access the table: directly or through the SYNONYM.

您需要查看该函数并查看发生了什么(或在此处发布函数文本).

You'll need to look into the function and see what's going on (or post the function text here).

这篇关于为什么Oracle同义词将不同数量的行返回到基础表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！