Can DBA create a public synonym for a table that has the same name as an existing table
0
Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr.
If a user performs a query like the following statement:
select * from hr;
What will be returned by the query?
Whose table values will be shown?
- The records of the public synonym
- The records of the table
oracle query syntax
edited 14 mins ago
hot2use
8,45652157
asked 1 hour ago
RAJRAJ
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr.
If a user performs a query like the following statement:
select * from hr;
What will be returned by the query?
Whose table values will be shown?
- The records of the public synonym
- The records of the table
oracle query syntax
edited 14 mins ago
hot2use
8,45652157
asked 1 hour ago
RAJRAJ
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr.
If a user performs a query like the following statement:
select * from hr;
What will be returned by the query?
Whose table values will be shown?
- The records of the public synonym
- The records of the table
oracle query syntax
edited 14 mins ago
hot2use
8,45652157
asked 1 hour ago
RAJRAJ
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Suppose a DBA created a public synonym for a table and named it hr. The user also created a table with the same name hr.
If a user performs a query like the following statement:
select * from hr;
What will be returned by the query?
Whose table values will be shown?
- The records of the public synonym
- The records of the table
oracle query syntax
oracle query syntax
edited 14 mins ago
hot2use
8,45652157
asked 1 hour ago
RAJRAJ
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 14 mins ago
hot2use
8,45652157
asked 1 hour ago
RAJRAJ
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 14 mins ago
hot2use
8,45652157
edited 14 mins ago
hot2use
8,45652157
edited 14 mins ago
hot2use
8,45652157
8,45652157
asked 1 hour ago
RAJRAJ
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
RAJRAJ
1
asked 1 hour ago
RAJRAJ
1
1
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
RAJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
User table will take precedence over the public synonym.
Data from user table would be shown.
Oracle object name resolution is explained in below document:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general008.htm#ADMIN11561
Below SQL snippet code will explain the use case
SQL> conn user1
Enter password:
Connected.
SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.
SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.
SQL> select count(0) from test; --first table with 0 rows
COUNT(0)
----------
0
SQL> select count(0) from conflicting_name; --second table with 1 row
COUNT(0)
----------
1
SQL> create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.
SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
COUNT(0)
----------
1
SQL> conn user2
Enter password:
Connected.
SQL> -- Connected to different user
SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
COUNT(0)
----------
0
SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
COUNT(0)
----------
1
answered 54 mins ago
Mehul ShroffMehul Shroff
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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/3.0/"u003ecc by-sa 3.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
});
}
});
RAJ is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2fdba.stackexchange.com%2fquestions%2f233207%2fcan-dba-create-a-public-synonym-for-a-table-that-has-the-same-name-as-an-existin%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
User table will take precedence over the public synonym.
Data from user table would be shown.
Oracle object name resolution is explained in below document:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general008.htm#ADMIN11561
Below SQL snippet code will explain the use case
SQL> conn user1
Enter password:
Connected.
SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.
SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.
SQL> select count(0) from test; --first table with 0 rows
COUNT(0)
----------
0
SQL> select count(0) from conflicting_name; --second table with 1 row
COUNT(0)
----------
1
SQL> create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.
SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
COUNT(0)
----------
1
SQL> conn user2
Enter password:
Connected.
SQL> -- Connected to different user
SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
COUNT(0)
----------
0
SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
COUNT(0)
----------
1
answered 54 mins ago
Mehul ShroffMehul Shroff
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
User table will take precedence over the public synonym.
Data from user table would be shown.
Oracle object name resolution is explained in below document:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general008.htm#ADMIN11561
Below SQL snippet code will explain the use case
SQL> conn user1
Enter password:
Connected.
SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.
SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.
SQL> select count(0) from test; --first table with 0 rows
COUNT(0)
----------
0
SQL> select count(0) from conflicting_name; --second table with 1 row
COUNT(0)
----------
1
SQL> create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.
SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
COUNT(0)
----------
1
SQL> conn user2
Enter password:
Connected.
SQL> -- Connected to different user
SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
COUNT(0)
----------
0
SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
COUNT(0)
----------
1
answered 54 mins ago
Mehul ShroffMehul Shroff
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
0
0
User table will take precedence over the public synonym.
Data from user table would be shown.
Oracle object name resolution is explained in below document:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general008.htm#ADMIN11561
Below SQL snippet code will explain the use case
SQL> conn user1
Enter password:
Connected.
SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.
SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.
SQL> select count(0) from test; --first table with 0 rows
COUNT(0)
----------
0
SQL> select count(0) from conflicting_name; --second table with 1 row
COUNT(0)
----------
1
SQL> create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.
SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
COUNT(0)
----------
1
SQL> conn user2
Enter password:
Connected.
SQL> -- Connected to different user
SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
COUNT(0)
----------
0
SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
COUNT(0)
----------
1
answered 54 mins ago
Mehul ShroffMehul Shroff
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
User table will take precedence over the public synonym.
Data from user table would be shown.
Oracle object name resolution is explained in below document:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/general008.htm#ADMIN11561
Below SQL snippet code will explain the use case
SQL> conn user1
Enter password:
Connected.
SQL> create table test as select * from user_objects where 1=2; --Creating first table with 0 rows
Table created.
SQL> create table conflicting_name as select * from user_objects where rownum<2; --Creating second table with 1 row
Table created.
SQL> select count(0) from test; --first table with 0 rows
COUNT(0)
----------
0
SQL> select count(0) from conflicting_name; --second table with 1 row
COUNT(0)
----------
1
SQL> create public synonym conflicting_name for test; --public synonym with second table's name for first table
Synonym created.
SQL> select count(0) from conflicting_name; --second table still points to original table and public synonym not used
COUNT(0)
----------
1
SQL> conn user2
Enter password:
Connected.
SQL> -- Connected to different user
SQL> select count(0) from conflicting_name; --now public synonym pointing to first table kicks in
COUNT(0)
----------
0
SQL> select count(0) from user1.conflicting_name; --can explicitely access original table bypassing public synonym
COUNT(0)
----------
1
answered 54 mins ago
Mehul ShroffMehul Shroff
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 54 mins ago
Mehul ShroffMehul Shroff
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 54 mins ago
Mehul ShroffMehul Shroff
11
answered 54 mins ago
Mehul ShroffMehul Shroff
11
11
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Mehul Shroff is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
RAJ is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
RAJ is a new contributor. Be nice, and check out our Code of Conduct.
RAJ is a new contributor. Be nice, and check out our Code of Conduct.
RAJ is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
draft saved
draft discarded
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%2fdba.stackexchange.com%2fquestions%2f233207%2fcan-dba-create-a-public-synonym-for-a-table-that-has-the-same-name-as-an-existin%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
