CODEWITHSUNDEEP
sqldatabaseoracle-database
user9892866
user9892866

Reputation:

Dual table issue

Hi We all know DUAL is the table of SYS and other users have synonym of it. but when i fire the below commnad

 create table dual(x varchar2(1));

it worked. An object of the name DUAL was created.

when there is already a synonym with the name DUAL how can we create another object of same name? why oracle is allowing us to do it.

Upvotes: 2

Views: 804

Answers (4)

wolφi
wolφi

Reputation: 8361

I think I found a way to demonstrate what is going on (without using DUAL or any other SYS object ;-):

If two users create a table with the same name, the tables end up in their own schema as expected:

CREATE USER user_a IDENTIFIED BY user_a;
CREATE USER user_b IDENTIFIED BY user_b;

CREATE TABLE user_a.foo (x NUMBER);
CREATE TABLE user_b.foo (x NUMBER);

SELECT object_id, object_type, owner, object_name, namespace 
  FROM dba_objects 
 WHERE object_name='FOO';

OBJECT_ID OBJECT_TYPE OWNER   OBJECT_NAME NAMESPACE
    78225 TABLE       USER_A  FOO                 1
    78226 TABLE       USER_B  FOO                 1

But when one of them creates a public synonym (as SYS did with it's DUAL table), it ends up in a magic schema with the name PUBLIC:

CREATE PUBLIC SYNONYM foo FOR user_a.foo;

SELECT object_id, object_type, owner, object_name, namespace 
  FROM dba_objects 
 WHERE object_name='FOO';

OBJECT_ID OBJECT_TYPE OWNER   OBJECT_NAME NAMESPACE
    78225 TABLE       USER_A  FOO                 1
    78226 TABLE       USER_B  FOO                 1
    78156 SYNONYM     PUBLIC  FOO                 1

So, in other words, public synonyms are just synonyms that live in the schema PUBLIC. And you can have only one table, view, sequence, package, synonym with the same name per schema.

Upvotes: 1

wolφi
wolφi

Reputation: 8361

You can create a table named DUAL because tables and public synonyms have a different namespace. For details, see chapter Database Object Names and Qualifiers of Oracle's SQL Language Reference.

EDIT: To illustrate the mechanism:

If you create your own DUAL table as user scott

CREATE TABLE dual (x VARCHAR2(1));

... then it shows up in the data dictionary: 

SELECT object_id, object_type, owner, object_name, namespace 
  FROM dba_objects 
 WHERE object_name='DUAL';

OBJECT_ID OBJECT_TYPE OWNER  OBJECT_NAME NAMESPACE
      142 TABLE       SYS    DUAL                1
      143 SYNONYM     PUBLIC DUAL                1
    78138 TABLE       SCOTT  DUAL                1

So, the names are unique per owner and namespace. You cannot add yet another table in your schema called DUAL. You cannot create a private synonym named DUAL, but you can create your own synonym for schemas in other objects.

Please make sure to drop the table again. Even simple statements won't work anymore:

SELECT sysdate FROM DUAL;
--


DROP TABLE dual;
SELECT sysdate FROM DUAL;
01.07.2018

Upvotes: 6

William Robertson
William Robertson

Reputation: 16001

I think DUAL is a distraction here. Of course it's a system table and there are side effects if you mess about with it, so don't. But the question is really about why there is no namespace conflict between any object and a public synonym with the same name. For example, I can create a table named ALL_TABLES or DBMS_OUTPUT in my own schema (if I really want to). Or, I can create a table called MYDEMOTABLE and then create a public synonym MYDEMOTABLE for WILLIAM.MYDEMOTABLE.

But then, what restriction are you expecting to exist? There is already both a table (owned by SYS) and a public synonym (owned by PUBLIC) named DUAL. You can create a third object with the same name, as long as it isn't owned by SYS or PUBLIC.

Upvotes: 1

Irfan
Irfan

Reputation: 695

There have various schemas in your single database. When you asked about default DUAL table, which actually belongs to 'sys' Schema which is system schema. But your default Database schema is dbo it means when you have executed your query to create DUAL table is actually created in your default schema named dbo.

If you want to check their actual double existence there are many ways to check.

  • You can apply a query like "select * from sys.DUAL" and "select * from dbo.DUAL". there you will see two different outputs.
  • you can check all the schemas in "select * from sys.schemas".
  • Also, you can check their different schemas in with table name

    "select * from sys.tables where name ='DUAL'"; the result will be two different tables with two different schema Id's.

Hope this will help you to understand schema basics.

Upvotes: 0

Related Questions