Best Answer

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 is a account to connect database(Server). we can create user by using CREATE USER user_name IDENTIFIED BY password.


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 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.

User Avatar

Wiki User

7y ago
This answer is:
User Avatar

Add your answer:

Earn +20 pts
Q: Difference between user and schema oracle?
Write your answer...
Still have questions?
magnify glass
Related questions

Why must create user in oracle todo your work in oracle?

A user in Oracle corresponds to a schema. Objects, such as table and indexes, must exist within a schema. Without creating a user/schema, use of the database will be limited to the built-in schemas and objects, such as the one-row DUAL table and standard PL/SQL packages.

Difference between user and schema for DB2?

In DB2, a user is an individual who is granted permissions to access and interact with the database, while a schema is a named collection of tables, views, procedures, and other database objects owned by a user. Users are associated with schemas to define their default schema for objects they create.

What is the difference between schema and database?

Database schema are the logical structure of entities (tables or object classes) and their relationships (keys, or object associations) in a database. Schema can exist at different levels of abstraction (see: Schema themselves may or may not exist as such in a database. In general schema at the higher levels of abstraction are design models that are captured in printed documents, and authored by business analysts and database designers, rather than created by database programmers and stored in the database itself. But in some types of databases, schema at any level of abstaction can be realized physically. In Oracle databases, schema can be realized as schema objects, which are a part of an Oracle database. These schema objects may represent a human user's conceptual model of the knowledge captured in an enterprise database. Thus defined, there can be many schema that can be associated with one physical database. In other kinds of relational database, an abstract user's schema can be represented by logically linked metadata, views, and stored procedures assocated with a user class. In object-oriented databases, especially those based on the highly self-referential language Smalltalk (e.g. Gemstone/S), schema are realized as "physical" objects in the database as a matter of course, as well as in the logical models captured in design documents. The schema objects in such databases are, in essence, the classes comprising the infrastructure of the database or application. Oracle-style user schema are readily created; these would be realized as user interface orchestrator classes.

Difference between sql and sql plus?

SQL*PLUS is a interface between user and Oracle database. It Provide an environment to use the SQL which is a query language to communicate with oracle database

Which schema is called the view of a manager or other type of user?

external schema

What is the difference between end-user systems and organizational systems?

the difference between end user system and organisational system

What services does active directory schema provide?

An active directory schema allows the user to perform a detailed search for an individual, place or other piece of information. A well designed schema represents software that gives the user access to information within an organization. The structure becomes more detailed as the schema becomes detailed.

What is external schema?

The role of the external schema is to support user views of data and thus to provide programmers with easier data access

What is the difference between an employing and user organisation?

exlain the differnce between an employing and user organisation

What are the 3 views of dbms?

A Database has three views. The External Schema: What the end user sees. The Internal Schema: What the programmers of the program see. The Conceptual Schema: The basic plan of the database. Most of the time this is in paper form as a Conceptual Schema Diagram (CSD)

What is the difference between all user profile and default user profile?


How do you undo a GRANT in Oracle?

Use the Oracle revoke command Example: revoke execute on procedure from user;