CODEWITHSUNDEEP
databaseoracle-database
Chuntao Fu
Chuntao Fu

Reputation: 35

What are the "Schema" differences?

Can you describe the differences of the term "Schema" in terms of database development perspective or Oracle perspective.

Upvotes: 1

Views: 87

Answers (2)

A B
A B

Reputation: 4148

In ORACLE, schemas and users are effectively the same, even though there is no requirement in the SQL standard (from 1996) for this. The word "schema" is defined in the SQL standard; this may be why ORACLE has references to the word "schema".

However, the ORACLE "CREATE SCHEMA" keyword doesn't even create a schema: I would never use the keyword "SCHEMA" in an ORACLE project. All the objects contained in a schema can be created without the keyword "SCHEMA".

This is the definition of a "schema" in ORACLE 12C: "A named collection of database objects, including logical structures such as tables and indexes. A schema has the name of the database user who owns it."

http://docs.oracle.com/database/121/CNCPT/glossary.htm#CNCPT89131

The above sentence means that one schema can only exist inside one user (schemas cannot span multiple users).

The following URL's state that users cannot have or be associated with more than one schema:

https://community.oracle.com/thread/960972?tstart=0

http://searchoracle.techtarget.com/answer/Can-I-create-multiple-schemas-in-Oracle-for-one-user

After you create an ORACLE user, and create objects owned by that user, all the objects are considered a collection that is called a schema. The word "schema" is specific to the SQL standard, but the content of the ORACLE pages implies that all schema objects are always contained within a user.

The ORACLE "CREATE SCHEMA" statement is misleading. I would not use it at all for 2 reasons:

  • The "CREATE SCHEMA" ORACLE keyword does not even create a schema, according to this URL:

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm

  • The "CREATE SCHEMA" ORACLE keyword supports only standard SQL and not all of the features in the ORACLE database.

    • The above page also states that a schema is automatically created when a user is created.

    "Oracle Database automatically creates a schema when you create a user"

    References:

    http://www.oratable.com/oracle-user-schema-difference/

    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm

    SQL Standard:

    http://pubs.opengroup.org/onlinepubs/9695959099/toc.pdf

    Upvotes: 1

    mmmmmpie
    mmmmmpie

    Reputation: 3029

    A schema is a both a user and the collection of objects inside of it.
    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6162110256950

    Upvotes: 1

    Related Questions