Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Unanswered: Difference between schema and user

    Hi Experts !

    This is a question which has been bugging me for some time (I am a novice to Oracle).
    Please let me know what is the exact difference between schema and user.
    The command
    create user abcd identified by def
    This creates a schema .. does it also create a user .. just a little puzzled.

    Thanx and Rgds,
    Deepa

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Difference between schema and user

    Originally posted by GDEEPA
    Hi Experts !

    This is a question which has been bugging me for some time (I am a novice to Oracle).
    Please let me know what is the exact difference between schema and user.
    The command
    create user abcd identified by def
    This creates a schema .. does it also create a user .. just a little puzzled.

    Thanx and Rgds,
    Deepa
    The CREATE USER command creates a USER (of course!). If that user can create objects, then the collection of objects owned by that user is called a SCHEMA. So every SCHEMA corresponds to a USER, but not every USER corresponds to a SCHEMA.

  3. #3
    Join Date
    Mar 2003
    Location
    Bangalore
    Posts
    14
    Hi deepa,
    you seriously need to read the documentation.It always helps.


    "So every SCHEMA corresponds to a USER, but not every USER corresponds to a SCHEMA."

    Hi andrewst i understand the first part.but how can a user be without a schema.
    can u clarify
    Cheers
    Murali

  4. #4
    Join Date
    Nov 2016
    Posts
    1
    Hai Deepa,
    The both words user and schema are interchangeble,thats why most people get confusion on this words.Below i explained the difference between them

    --User
    User is a account to connect database(Server). we can create user by using CREATE USER user_name IDENTIFIED BY password.

    --Schema

    Actually Oracle Database contain logical and physical strucutre to process the data.The Schema Also Logical Structure to process the data in Database(Memory Component). Its Created automatically by oracle when user created.It Contains All Objects created by the user associated to that schema.For Example if i created a user with name santhosh then oracle createts a schema called santhosh,oracle stores all objects created by user santhosh in santhosh schema.

    We can create schema by CREATE SCHEMA statement ,but Oracle Automatically create a user for that schema.

    We can Drop the schema by using DROP SCHEMA schema_name RESTRICT statement but it can not delete scehema contains objects,so to drop schema it must be empty.here the restrict word forcely specify that schema with out objects.

    If we try to drop a user contain objects in his schema we must specify CASCADE word because oracle does not allow you to delete user contain objects. DROP USER user_name CASCADE so oracle deletes the objects in schema and then it drops the user automatically,Objects refered to this schema objects from other schema like views and private synonyms goes to invalid state.

    I hope now you got the difference between them,if you have any doubts on this topic,please feel free to ask.

    Thank you.
    Last edited by santhoshreddytemp; 11-07-16 at 06:16.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    That's really nice; however, perhaps you'd rather want to participate more recent discussions as this one is 13.5 years old so ... I'm not sure Deepa held his breath that long.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •