3 Replies Latest reply on Sep 6, 2013 12:50 PM by Pradeepcmst

    SQL schemas




      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?




        • 1. Re: SQL schemas
          Bas de Klerk

          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.



          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.

          1 person found this helpful
          • 2. Re: SQL schemas



            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



            Then you can run queries in schema B

            SELECT *
            FROM EMP


            I hope this helps




            1 person found this helpful
            • 3. Re: SQL schemas

              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.




              Pradeep. V