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.
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 plans:
Directly on the table...
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)|
--------------------------------------------------------------------------
Through the synonym...
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)
Synonym chain...
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
Advantages of Using SynonymsYou can create a synonym for an object in a schema, and use the synonym in your SQL statement to access the object. If you need to access the underlying object in a different schema, modify the definition of the synonym to point to the object in a different schema.
A table resides physically in the database. A view is not a part of the database's physical representation. It is precompiled, so that data retrieval behaves faster and also provides a secure accessibility mechanism. A synonym is an alternate name assigned to a table, view, sequence or program unit.
View is logical and does not occupies space. Synonym can be created for single table, view, sequence or index. Synonym is physical and needs space.
You want to work on your own tables. E. You want to use another schema's tables.
Update:
You have ROW LEVEL SECURITY
enabled.
The user function FND_GENERIC_POLICY.GET_PREDICATE
gets called each time you access the table and limits access to some rows.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With