In what order will Oracle use synonym, view, and table objects?What table/view do you query against to select all the table names in a schema in Oracle?Get list of all tables in Oracle?How can I get column names from a table in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Managing Oracle SynonymsOracle stored procedure synonyms and grants not working as expectedHow to grant privileges on trigger and synonyms in oracle 11gPrivileges on views/synonyms vs on underlying tablesCannot create SYNONYM in OracleHow to grant “select” permission on public synonym “SHC.ABC” of table “SHC.ABC”
Very lazy puppy
How can I create folders in folders in terminal
What is the origin of the “clerics can create water” trope?
Can Brexit be undone in an emergency?
Floating Point XOR
Is the name of an interval between two notes unique and absolute?
Why do we need to use transistors when building an OR gate?
What was the deeper meaning of Hermione wanting the cloak?
Strength of Female Chimpanzees vs. Male Chimpanzees?
We suspect colleague is stealing company code - what do we do?
Why are there two bearded faces wearing red hats on my stealth bomber icon?
What exactly is a web font, and what does converting to one involve?
Minimum number of lines to draw 111 squares
If people's daily habits are reliable then why is the stock market so unpredictable?
How do rulers get rich from war?
What is the word for a person who destroys monuments?
Compare FEM mesh with the mesh created within Mathematica
Output Distinct Factor Cuboids
Can I separate garlic into cloves for storage?
Is Yang not precluded from conducting his "UBI experiment" as an electoral candidate?
How could artificial intelligence harm us?
Does battery condition have anything to do with macbook pro performance?
Is a global DNS record a security risk for phpMyAdmin?
removing rows containing NA in every column
In what order will Oracle use synonym, view, and table objects?
What table/view do you query against to select all the table names in a schema in Oracle?Get list of all tables in Oracle?How can I get column names from a table in Oracle?How do I limit the number of rows returned by an Oracle query after ordering?Managing Oracle SynonymsOracle stored procedure synonyms and grants not working as expectedHow to grant privileges on trigger and synonyms in oracle 11gPrivileges on views/synonyms vs on underlying tablesCannot create SYNONYM in OracleHow to grant “select” permission on public synonym “SHC.ABC” of table “SHC.ABC”
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Let's say that my database has the following objects:
- A table on schema "B" named "mlb_players"
- A view on schema "C" named "mlb_players" that is a SELECT against a table on server "DB2"
- A synonym on schema "D" named "mlb_players" that points to a table on server "DB3"
All of these objects are granted to my schema "RedSoxRule"
If I execute this query, from where would the data be retrieved?
SELECT *
FROM mlb_players
In other words, if a given name (in this case "mlb_players") is applicable to different object types, and the GRANTS are equal, in what order will Oracle find the requested object?
oracle oracle11g
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
add a comment
|
Let's say that my database has the following objects:
- A table on schema "B" named "mlb_players"
- A view on schema "C" named "mlb_players" that is a SELECT against a table on server "DB2"
- A synonym on schema "D" named "mlb_players" that points to a table on server "DB3"
All of these objects are granted to my schema "RedSoxRule"
If I execute this query, from where would the data be retrieved?
SELECT *
FROM mlb_players
In other words, if a given name (in this case "mlb_players") is applicable to different object types, and the GRANTS are equal, in what order will Oracle find the requested object?
oracle oracle11g
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
experts-exchange.com/questions/28343488/…
– Robert Kock
Mar 28 at 14:01
3
@RobertKock - the whole and entire purpose of StackOverflow is to save developers from visiting that site and its pernicious paywall.
– APC
Mar 28 at 14:03
add a comment
|
Let's say that my database has the following objects:
- A table on schema "B" named "mlb_players"
- A view on schema "C" named "mlb_players" that is a SELECT against a table on server "DB2"
- A synonym on schema "D" named "mlb_players" that points to a table on server "DB3"
All of these objects are granted to my schema "RedSoxRule"
If I execute this query, from where would the data be retrieved?
SELECT *
FROM mlb_players
In other words, if a given name (in this case "mlb_players") is applicable to different object types, and the GRANTS are equal, in what order will Oracle find the requested object?
oracle oracle11g
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
Let's say that my database has the following objects:
- A table on schema "B" named "mlb_players"
- A view on schema "C" named "mlb_players" that is a SELECT against a table on server "DB2"
- A synonym on schema "D" named "mlb_players" that points to a table on server "DB3"
All of these objects are granted to my schema "RedSoxRule"
If I execute this query, from where would the data be retrieved?
SELECT *
FROM mlb_players
In other words, if a given name (in this case "mlb_players") is applicable to different object types, and the GRANTS are equal, in what order will Oracle find the requested object?
oracle oracle11g
oracle oracle11g
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
asked Mar 28 at 13:52
Jeromy FrenchJeromy French
9,26710 gold badges60 silver badges107 bronze badges
9,26710 gold badges60 silver badges107 bronze badges
experts-exchange.com/questions/28343488/…
– Robert Kock
Mar 28 at 14:01
3
@RobertKock - the whole and entire purpose of StackOverflow is to save developers from visiting that site and its pernicious paywall.
– APC
Mar 28 at 14:03
add a comment
|
experts-exchange.com/questions/28343488/…
– Robert Kock
Mar 28 at 14:01
3
@RobertKock - the whole and entire purpose of StackOverflow is to save developers from visiting that site and its pernicious paywall.
– APC
Mar 28 at 14:03
experts-exchange.com/questions/28343488/…
– Robert Kock
Mar 28 at 14:01
experts-exchange.com/questions/28343488/…
– Robert Kock
Mar 28 at 14:01
3
3
@RobertKock - the whole and entire purpose of StackOverflow is to save developers from visiting that site and its pernicious paywall.
– APC
Mar 28 at 14:03
@RobertKock - the whole and entire purpose of StackOverflow is to save developers from visiting that site and its pernicious paywall.
– APC
Mar 28 at 14:03
add a comment
|
2 Answers
2
active
oldest
votes
Oracle has a nice long explanation of this.
Basically, when resolving a name, it looks:
- For objects in your schema with that name
- For public synonyms with that name
- If the name has multiple parts (e.g.
C.mlb_players, it checks to see if the first part is a qualifying schema that you have access to.
In your example, Oracle won't find any of them. They're all in different schemas from your RedSoxRule schema, none of them are public synonyms, and you didn't qualify mlb_players with a schema name.
It doesn't actually matter what the type of the object is (table, view, synonym, package, etc) - they're all treated the same.
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
add a comment
|
If I execute this query, from where would the data be retrieved
Nowhere. REDSOXRULE has no object called mlb_players so the query would fail with ORA-00942: table or view does not exist.
You would need to prefix the table name with the schema you're prefixing, e.g.
SELECT *
FROM d.mlb_players;
Let's suppose you have a variation on your posted structure.
- REDSOXRULE has a view
mlb_playersfora.mlb_players. The queryselect * from mlb_playerswould select from this view, i.e froma.mlb_players. - Instead of a view REDSOXRULE has a private synonym
mlb_playersforb.mlb_players. The queryselect * from mlb_playerswould select from this synonym, i.e fromb.mlb_players. Note that you can't have a private synonym with the same name as a table or view in your schema. - Instead of a private synonym the database has a public synonym
mlb_playersford.mlb_players. The queryselect * from mlb_playerswould select from this publicsynonym, i.e fromd.mlb_players.
That is, Oracle looks first for objects owned by the schema (tables, views, private synonyms, etc). Then it looks at public synonyms. Then it looks for objects in other schemas
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
add a comment
|
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55399315%2fin-what-order-will-oracle-use-synonym-view-and-table-objects%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Oracle has a nice long explanation of this.
Basically, when resolving a name, it looks:
- For objects in your schema with that name
- For public synonyms with that name
- If the name has multiple parts (e.g.
C.mlb_players, it checks to see if the first part is a qualifying schema that you have access to.
In your example, Oracle won't find any of them. They're all in different schemas from your RedSoxRule schema, none of them are public synonyms, and you didn't qualify mlb_players with a schema name.
It doesn't actually matter what the type of the object is (table, view, synonym, package, etc) - they're all treated the same.
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
add a comment
|
Oracle has a nice long explanation of this.
Basically, when resolving a name, it looks:
- For objects in your schema with that name
- For public synonyms with that name
- If the name has multiple parts (e.g.
C.mlb_players, it checks to see if the first part is a qualifying schema that you have access to.
In your example, Oracle won't find any of them. They're all in different schemas from your RedSoxRule schema, none of them are public synonyms, and you didn't qualify mlb_players with a schema name.
It doesn't actually matter what the type of the object is (table, view, synonym, package, etc) - they're all treated the same.
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
add a comment
|
Oracle has a nice long explanation of this.
Basically, when resolving a name, it looks:
- For objects in your schema with that name
- For public synonyms with that name
- If the name has multiple parts (e.g.
C.mlb_players, it checks to see if the first part is a qualifying schema that you have access to.
In your example, Oracle won't find any of them. They're all in different schemas from your RedSoxRule schema, none of them are public synonyms, and you didn't qualify mlb_players with a schema name.
It doesn't actually matter what the type of the object is (table, view, synonym, package, etc) - they're all treated the same.
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
Oracle has a nice long explanation of this.
Basically, when resolving a name, it looks:
- For objects in your schema with that name
- For public synonyms with that name
- If the name has multiple parts (e.g.
C.mlb_players, it checks to see if the first part is a qualifying schema that you have access to.
In your example, Oracle won't find any of them. They're all in different schemas from your RedSoxRule schema, none of them are public synonyms, and you didn't qualify mlb_players with a schema name.
It doesn't actually matter what the type of the object is (table, view, synonym, package, etc) - they're all treated the same.
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
answered Mar 28 at 14:11
kfinitykfinity
4,2051 gold badge7 silver badges14 bronze badges
4,2051 gold badge7 silver badges14 bronze badges
add a comment
|
add a comment
|
If I execute this query, from where would the data be retrieved
Nowhere. REDSOXRULE has no object called mlb_players so the query would fail with ORA-00942: table or view does not exist.
You would need to prefix the table name with the schema you're prefixing, e.g.
SELECT *
FROM d.mlb_players;
Let's suppose you have a variation on your posted structure.
- REDSOXRULE has a view
mlb_playersfora.mlb_players. The queryselect * from mlb_playerswould select from this view, i.e froma.mlb_players. - Instead of a view REDSOXRULE has a private synonym
mlb_playersforb.mlb_players. The queryselect * from mlb_playerswould select from this synonym, i.e fromb.mlb_players. Note that you can't have a private synonym with the same name as a table or view in your schema. - Instead of a private synonym the database has a public synonym
mlb_playersford.mlb_players. The queryselect * from mlb_playerswould select from this publicsynonym, i.e fromd.mlb_players.
That is, Oracle looks first for objects owned by the schema (tables, views, private synonyms, etc). Then it looks at public synonyms. Then it looks for objects in other schemas
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
add a comment
|
If I execute this query, from where would the data be retrieved
Nowhere. REDSOXRULE has no object called mlb_players so the query would fail with ORA-00942: table or view does not exist.
You would need to prefix the table name with the schema you're prefixing, e.g.
SELECT *
FROM d.mlb_players;
Let's suppose you have a variation on your posted structure.
- REDSOXRULE has a view
mlb_playersfora.mlb_players. The queryselect * from mlb_playerswould select from this view, i.e froma.mlb_players. - Instead of a view REDSOXRULE has a private synonym
mlb_playersforb.mlb_players. The queryselect * from mlb_playerswould select from this synonym, i.e fromb.mlb_players. Note that you can't have a private synonym with the same name as a table or view in your schema. - Instead of a private synonym the database has a public synonym
mlb_playersford.mlb_players. The queryselect * from mlb_playerswould select from this publicsynonym, i.e fromd.mlb_players.
That is, Oracle looks first for objects owned by the schema (tables, views, private synonyms, etc). Then it looks at public synonyms. Then it looks for objects in other schemas
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
add a comment
|
If I execute this query, from where would the data be retrieved
Nowhere. REDSOXRULE has no object called mlb_players so the query would fail with ORA-00942: table or view does not exist.
You would need to prefix the table name with the schema you're prefixing, e.g.
SELECT *
FROM d.mlb_players;
Let's suppose you have a variation on your posted structure.
- REDSOXRULE has a view
mlb_playersfora.mlb_players. The queryselect * from mlb_playerswould select from this view, i.e froma.mlb_players. - Instead of a view REDSOXRULE has a private synonym
mlb_playersforb.mlb_players. The queryselect * from mlb_playerswould select from this synonym, i.e fromb.mlb_players. Note that you can't have a private synonym with the same name as a table or view in your schema. - Instead of a private synonym the database has a public synonym
mlb_playersford.mlb_players. The queryselect * from mlb_playerswould select from this publicsynonym, i.e fromd.mlb_players.
That is, Oracle looks first for objects owned by the schema (tables, views, private synonyms, etc). Then it looks at public synonyms. Then it looks for objects in other schemas
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
If I execute this query, from where would the data be retrieved
Nowhere. REDSOXRULE has no object called mlb_players so the query would fail with ORA-00942: table or view does not exist.
You would need to prefix the table name with the schema you're prefixing, e.g.
SELECT *
FROM d.mlb_players;
Let's suppose you have a variation on your posted structure.
- REDSOXRULE has a view
mlb_playersfora.mlb_players. The queryselect * from mlb_playerswould select from this view, i.e froma.mlb_players. - Instead of a view REDSOXRULE has a private synonym
mlb_playersforb.mlb_players. The queryselect * from mlb_playerswould select from this synonym, i.e fromb.mlb_players. Note that you can't have a private synonym with the same name as a table or view in your schema. - Instead of a private synonym the database has a public synonym
mlb_playersford.mlb_players. The queryselect * from mlb_playerswould select from this publicsynonym, i.e fromd.mlb_players.
That is, Oracle looks first for objects owned by the schema (tables, views, private synonyms, etc). Then it looks at public synonyms. Then it looks for objects in other schemas
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
edited Mar 28 at 14:17
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
answered Mar 28 at 14:10
APCAPC
126k17 gold badges128 silver badges239 bronze badges
126k17 gold badges128 silver badges239 bronze badges
add a comment
|
add a comment
|
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55399315%2fin-what-order-will-oracle-use-synonym-view-and-table-objects%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
experts-exchange.com/questions/28343488/…
– Robert Kock
Mar 28 at 14:01
3
@RobertKock - the whole and entire purpose of StackOverflow is to save developers from visiting that site and its pernicious paywall.
– APC
Mar 28 at 14:03