5 Replies Latest reply: Nov 16, 2010 10:38 AM by HG75 RSS

    Difference between Database, instance and schema

    783107
      Hi
      I don't know the clear difference between database, instance and schema.. In the broad sense, everything seems to be the same. If anyone can differentiate between them with some example, that will be of great help.
      Thank you.
        • 1. Re: Difference between Database, instance and schema
          795160
          If you would have searched in google you would have got the answer by this time. Very first link I got is .

          http://decipherinfosys.wordpress.com/2007/03/08/instance-database-and-schema/

          Regards

          Raj
          • 2. Re: Difference between Database, instance and schema
            jpvybes
            Let's say you had a company of 2 clients "A" and "B" each having a PRODUCTION instance and a DEVELOPMENT instance. If you wanted to keep the clients separate, client A would have it's own schema on the PROD and DEV instances of the same oracle database and client B would have the same. Does this help?
            • 3. Re: Difference between Database, instance and schema
              6363
              Fortunately this is already documented.

              http://tahiti.oracle.com/

              E.g.

              http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/intro.htm#i62345

              http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/intro.htm#CEGJFFFD

              If you have a specific question about anything there that is not clear please feel free to post it.
              • 4. Re: Difference between Database, instance and schema
                Frank Kulash
                Hi,

                In Oracle, people often do use "database" and "instance" interchangeably, or together as one term: "database instance".
                A "schema" is quite different. A schema is a collection of objects (such as tables, indexes or procedures). Every object belongs to exactly one schema, and every schema is a part of exactly one database. (Names may not be unique, however. For example, your database may have one schema named SCOTT, and another schema named USER1. Each of those schemas may have a table named EMP. The EMP table in the SCOTT schema, or SCOTT.EMP, is not the same as the EMP table in the USER1 schema, or USER1.EMP. A different database, my database for example, may also have schemas called SCOTT and USER1, which are completely independent of the SCOTT and USER1 in your database.)
                • 5. Re: Difference between Database, instance and schema
                  HG75
                  Hi,
                  in my understanding,
                  Database is the collection of data stored logically in the tablespace and physically in datafiles.
                  Instance is processes, memories that accessing the database. More than one instances can access the same database, example is in RAC. more than one RAC instances can access the same DB.
                  Schema is a collection of objects owned by a user.

                  thanks