This discussion is archived
3 Replies Latest reply: Sep 6, 2013 5:50 AM by Pradeepcmst RSS

SQL schemas

jajaja Newbie
Currently Being Moderated

Goodmorning,

 

I'm new to oracle, so please be patient.

I notice this difference:

if I have a table 'EMPLOYEES'  in schema ' SYSTEM' then I can query that table like this: ' SELECT * FROM EMPLOYEES'.

If I have the same table in another schema 'TEST'  then I have to query like this: ' SELECT * FROM TEST.EMPLOYEES';

 

How can I avoid that? I would like to query without the schema-name in the SQL-statement. And, is it a problem if I add my own tables to the schema 'SYSTEM'. Are there disadvantages?

 

Greetings,

Mieke

  • 1. Re: SQL schemas
    Bas de Klerk Pro
    Currently Being Moderated

    Hi Mieke,

     

    The schema (=user) name in front of a table is only needed when referring to another schema when logged in as another user.

    If you want to be able to refer to an object ( table/view ) in another schema without specifying the schema name you can create synonyms.

    CREATE SYNONYM EMPLOYEES FOR SYSTEM.EMPLOYEES would create a synonym named EMPLOYEES.

     

    Don't create tables in the oracle (administrator) schemas like sys and system. There are many reasons for this.

    Just a few : One reason is that you put your data between the oracle repository ( like storing files in c:\windows\system32 ), another would be that you fragment the space allocation in the system tablespace (not that big of a problem anymore ). If you want to remove all your own objects you can just drop the entire user instead of having to delete all individual objects etc etc.

    Just create a schema(user) and create your own objects as that user.

  • 2. Re: SQL schemas
    jariola Guru
    Currently Being Moderated

    Hi,

     

    Do not ever create any objects to system schemas like SYS or SYSTEM!

     

    Let's say you have schema A and B.

    In schema A you have table EMP witch you like schema B access.

     

    First you need grant select privilege

    GRANT SELECT A.EMP TO B;

     

    Then you can query table in schema B like

    SELECT *
    FROM A.EMP

     

    If you do not like have schema in query, you can create synonym to schema B

    CREATE SYNONYM EMP FOR A.EMP;

     

    Then you can run queries in schema B

    SELECT *
    FROM EMP

     

    I hope this helps

     

    Regards,

    Jari

  • 3. Re: SQL schemas
    Pradeepcmst Journeyer
    Currently Being Moderated

    Hi Mieke,

    The system tablespace is used to store oracle metadata. Please do not create any user objects in default tablespaces like, SYSTEM. SYSAUX. etc. Oracle also recommended not to create any user objects in these tablespaces.

     

    if you want to query like " SELECT * FROM EMPLOYEES' " , u directly login into user account who owns this EMPLOYEES table. Or else if you want to query using sys user, then you need to use " SELECT * FROM <schema.EMPLOYEES> " .

     

    You would like to know the impact if you create user objects in SYSTEM tables right? here is the simple and clean answer.

    Consider you created many objects in SYSTEM schema and some of the objects were corrupted. if you want to recover, you need to restore the whole system tablespaces and recover it, and u need downtime. But if you create all user objects in a separate tablespace, u can simply down the errored tablespace and start recovering without downtime and without affecting any other tablespaces... In real time, it will be very useful if you follow this Oracle recommended method..

     

    Hope its clear.

     

     

    Regards,

    Pradeep. V

Legend

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