This discussion is archived
2 Replies Latest reply: Feb 15, 2013 7:51 AM by rp0428 RSS

Scheme Name in front of Table Name

991339 Newbie
Currently Being Moderated
Why do I have to always put the Scheme Name in front of the Table name in all sql queries?
  • 1. Re: Scheme Name in front of Table Name
    990618 Newbie
    Currently Being Moderated
    If you are the owner of these tables or views (they are in your schema), you shouldn't need to. If this isn't the case (the tables are in a different schema), then you will need to.

    If they are in a different schema, you could choose to create synonyms in your schema with the same name as the tables you're interested in which point to those tables.


    Steve
  • 2. Re: Scheme Name in front of Table Name
    rp0428 Guru
    Currently Being Moderated
    >
    Why do I have to always put the Scheme Name in front of the Table name in all sql queries?
    >
    So that Oracle can determine which table you are talking about. Any user in the database can create a table named DEPT or EMP.

    See the 'Name Resolution in Schema Scope' section of the Advanced Application Developer's Guide.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_dependencies.htm#i3136

    Have you ever used valet parking at a nightclub or restaurant?

    After dinner when you want the valet to go get your car you can't just say 'please get my black BMW'. There might be many black BMWs. You can only get the one that YOU own. Think about it. Do you really want the valet to give YOUR BMW to someone else? Your BMW might have important 'data' in it (e.g. your laptop and checkbook) and a different BMW might have 'junk' data; empty beer cans and cigarette butts.

    Oracle is the valet. It is only going to give you YOUR table and you have to prove it is yours by being connected as YOU and providing YOUR schema name.
    You ask for some other user's table by giving THEIR schema name but that only works if the valet (Oracle) has a signed permission slip from that user giving you permission to use their table.

    Or you can use a public synonym which tells the valet (Oracle) that anyone is allowed to use your table. That isn't any wiser for tables that it would be for your car!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points