Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ahmed.

Asked: October 26, 2002 - 4:38 pm UTC

Last updated: May 08, 2016 - 10:57 am UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Hello Mr. Tom
i read so many book for oracle and java and every book speak about schema but did not explain the actual meaning of it.once give the meanning of "user and all objects it own".other give the meaning of
" Structure of table".So this cause me to be confused about the accurate meanning of schema.please help me to understand the meaning of schema spcially i will use it in my next java developer certified exam.
thank you in advanced.

and Tom said...

You should consider a schema to be the user account and collection of all objects therein as a schema for all intents and purposes.

SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.

SYS is a schema that includes tons of tables, views, grants, etc etc etc.

SYSTEM is a schema.....


Technically -- A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.



Rating

  (26 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

confused

Tanmoy, April 04, 2003 - 11:19 am UTC

tom ,
thanks for your reply ..i have still some doubt..
if i create a user will a schema automatically created ...
is schema and user are synonym only ...
can i change the schema ..
can you please tell me more about schema and user

Tom Kyte
April 04, 2003 - 11:35 am UTC

think of then synonomously, the same

schema and User

A reader, April 04, 2003 - 11:59 am UTC

IF Oracle user has objects
then
call it SCHEMA
else
call it USER
END IF;

A USER may be given access to SCHEMA OBJECTS owned by different USERS.

Hope to (DE-)Confuse you more

Oracle DBA

Arash, April 04, 2003 - 2:03 pm UTC

Hi Tom,

First of all thanks for your information. Second of all I still don't understand why Oracle have Schema & User which in my point of view are the same !?

I would appreciate it if you tell us the major reason of having both.

Thanks in advance,
Arash


Tom Kyte
April 04, 2003 - 6:30 pm UTC

schema is a fancy name for user most of the time. A schema is really that collection of objects owned by a user (as someone pointed out above)

oracle didn't do it -- all relational databases have this

Schema Vs. User

josh, April 04, 2003 - 6:47 pm UTC

Can you have a user without a schema? It appears to me you can.

Tom Kyte
April 04, 2003 - 6:59 pm UTC

yes, technically speaking....

lets put it this way -- for all intents and purposes just consider user = schema = user = schema = the same thing.

Please Digest this for once and for all

A reader, April 04, 2003 - 7:41 pm UTC

Why is every body asking the same question in different ways and Thanx Tom is answering.
Tom would you agree
IF Oracle user has objects
then
call it SCHEMA
else
call it USER
END IF;

A USER may be given access to SCHEMA OBJECTS owned by different USERS.


Tom Kyte
April 05, 2003 - 11:41 am UTC

you say potato, i say potatah -- its all the same to me.

a reader, April 05, 2003 - 10:23 am UTC

In Relational Algebra (mathematical theory behind relational databases), a table is called a relation and the schema of the relation is the basic information describing this relation (column names and data types).

Tom Kyte
April 05, 2003 - 12:01 pm UTC

and rows are tuples, and non key attributes are functional dependencies, yadda yadda yadda. Lots of fancy terms to describe users, tables, rows, columns....

Great!!

tanmoydc, April 06, 2003 - 10:54 pm UTC

thanks tom for reply ...it cleared my doubt ..

Difference between User and Schema

Kunjan, December 11, 2003 - 6:34 am UTC

Hi Tom,

this topic is interesting. I would like to share my thoughts.

Hi Ahmed, Tanmoy, 'A Reader', Arash, Josh,

There CAN be a USER can exist without a SCHEMA.
There CAN be a SCHEMA can exist without a USER.

As Tom said, for all practical purposes, eg. design, development etc. you can use them interchangeably. But still there is slight difference between them. (Tom please correct me if wrong!)

(1) User without a Schema -
When a user is created in a database, that user is without any schema. The schema is created (in fact perhaps only conceptualized) when that user creates the FIRST object in the database. The name of this schema is same as that of user. and yes, it is a collection of all the object created/owned by corresponding user.

(If you are using any GUI tool which lists User and Schema e.g. SQL Navigator OR TOAD, you can see the schmea name being added in schema list when u create FIRST object for a user, and being removed from the schema list when u delete LAST object for any user)

(2) Schema without a User -
There is only 1 schema which does not have a user associated with it. 'PUBLIC'. This is a schema, as there are objects in it, but there is no user with name 'PUBLIC'.

again repeating : for all practical purposes and intents, you don't need to know the difference and can use them synonomously.

Tom Kyte
December 11, 2003 - 7:21 am UTC

once you create a user in the database -- there is a schema. it is empty, but there is a schema.

In fact, we have the concept of a schemaless user (user is stored outside the database, in an ldap respository for example). Here, there is no schema, not even the empty one.


and in fact:

[tkyte@localhost tkyte]$ sqlplus "/ as sysoper"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Dec 11 12:22:20 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

public@ORA9IR2> show user
USER is "PUBLIC"
public@ORA9IR2>
public@ORA9IR2> select user from dual;

USER
------------------------------
PUBLIC



There is a public user!

Happened schema has the same name as object in another schema

Alexander Semenov, April 25, 2006 - 11:29 am UTC

Hello,

Say, I have a table 'cassa' in schema 'sag' that was created long time ago and tightly coupled with other objects (like views, packages etc) in the 'sag'.

I have a schema named 'cassa' created independently long time ago too. There is a package 'some_cassa_pkg' in 'Cassa' that has 'GetFunc' and 'SetFunc(some_param int)'

The time has come that I had to use those functions from 'sag'.
When I'm trying to use them somewhere in package like:
CREATE OR REPLACE PACKAGE BODY my_pack is

FUNCTION my_GetFunc return number is
begin
cassa.GetFunc;
end;
...

I get error PLS-00302 component 'GetFunc' must be declared.

When I use it in view like:
create or replase My_view as
select cassa.GetFunc from dual;

and then use this view in 'my_pack' package it works well.
Ok, but how can I use cassa.SetFunc(param)?
Is there any way to use cassa.SetFunc(param) from schema 'sag' where table
'cassa' exists excepting public synonym creation?

Thank you.


Tom Kyte
April 25, 2006 - 2:23 pm UTC

it would be

cassa.some_cassa_pkg.getFunc

there is no such thing as cassa.getFunct in your schemas, there is a package cassa.some_cassa_pkg - and it have a thing "getFunct"

Happened schema has the same name as object in another schema

Alexander Semenov, April 26, 2006 - 3:07 am UTC

Hello.

Excuse me please, of couse cassa.some_cassa_pkg.getFunc I've just missed the package here, but I've meant another problem. My problem is - I have a table 'cassa' in my schema and I have another schema with the same name as my table i.e. 'cassa'. And when I try to use cassa.some_cassa_pkg.getFunc from a package of my schema I get error PLS-00302 component 'some_cassa_pkg' must be declared.
Sorry for my inaccuracy in previous post.

Thank you.

Tom Kyte
April 26, 2006 - 8:02 am UTC

give us the step by steps. "of course" I cannot deal with, I can only take what is typed in.


It is ambigous to me what "of my schema" is above. How about you set up a small test case for us to look at - SMALL, yet 100% complete (eg: create user A, create user B, ..... - simulate your problem with AS FEW LINES OF CODE AS HUMANLY POSSIBLE)

Happened schema has the same name as object in another schema

Alexandr Semenov, May 17, 2006 - 12:02 pm UTC

Hello.

Sorry for delay in my responce...
Here is the script:

create user sag identified by sag

create user cassa identified by cassa

connect sag/sag

create table sag.cassa(fld number);

connect cassa/cassa

create or replace package cassa.some_pkg as
function Get_some_val() return int;
procedure Set_some_val(param int);
end;/

create or replace package body cassa.some_pkg as
function Get_some_val() return int as
begin
return 1;
end;
procedure Set_some_val(param int) as
begin
null; -- do nothing
end;
end some_pkg;/

grant execute cassa.some_pkg to sag

connect sag/sag

create or replace package Use_cassa_schema_pkg as
procedure Set_ is
begin
cassa.some_pkg.Set_some_val(1); --<<<--PLS-00302 error raises here
end;
end;/

Thank you.

Happened schema has the same name as object in another schema

Alexandr Semenov, May 21, 2006 - 5:29 pm UTC

Do help with my problem, please.

Tom Kyte
May 21, 2006 - 8:18 pm UTC

do not do that, you will have problems with this forever. The rules of scoping will be forever getting in your way. It isn't going to work.

Opinion

Nitin, October 14, 2008 - 2:25 am UTC

I think schema is collection of objects while user is a way
to access those objects
Tom Kyte
October 14, 2008 - 6:04 pm UTC

opinion:

in oracle for all intents and purposes they are 100% interchangeable.


schema

A reader, October 14, 2008 - 8:11 pm UTC


What is the usage of custom schema files related to the change management in oracle?

Yuli, May 24, 2012 - 4:22 am UTC

What is the usage of custom schema files related to the change management in oracle?
Tom Kyte
May 24, 2012 - 9:07 am UTC

Not sure what you mean.

A reader, August 29, 2012 - 2:31 am UTC


Clarification

ed, November 14, 2012 - 9:01 pm UTC

I think it's worth mentioning that Oracle uses a different definition of the term "schema" than other databases.

I have been confused by this ever since I started working with Oracle databases.

Today I finally looked it up on Wikipedia, which is quick to point out that Oracle has a different definition:

From this link: http://en.wikipedia.org/wiki/Database_schema

In an Oracle Database system, the term "schema" has a slightly different connotation. For the interpretation used in an Oracle Database, see schema object.
Tom Kyte
November 19, 2012 - 8:46 am UTC

that is what we call a schema too? please explain what you think is different regarding Oracle's 'schema' and the definition of schema you pointed to???

that page states:

In a relational database, the schema defines the tables, fields, relationships, views, indexes, packages, procedures, functions, queues, triggers, types, sequences, materialized views, synonyms, database links, directories, Java, XML schemas, and other elements.

so what is different about Oracle that makes it not conform to that??


vinod, April 03, 2013 - 1:53 pm UTC

I believe the schema can be viewed as user home folder in Operating systems. take example of windows OS- when you create a user, a home folder is created for that user by the same name and all your objects are saved there.

same way whenever a user is created in oracle, a container is created called schema and you save all of your objects there.

let me know if I am right!

Am I right?

Zilvinas Vidmantas, April 11, 2013 - 2:04 pm UTC

Database metadata is stored in dictionary.
Schema is metadata of one user. So schema would be every piece of information about user(grants, user_obects, etc.):

for i in (select Table_Name from dict) loop
select * from t.Table_Name where owner = 'My_User';
end loop;

When user is created at least All_Users will have data about user. So there is no such thing as absolutely empty schema. Login data already is data about user.

And I believe actual data inserted into table is not part of schema.
Schema describes what data, where and how can be stored, accessed, etc.
Data itself is stored in tablespace and is not part of user's schema. Quota on that tablespace is data about user and is stored in dictionary, so it is schema object.
So schema is not a data container. Schema is rules, properties, definitions, but not rows in the table.



Performance tuning

abc, July 03, 2013 - 10:57 am UTC

so If we create a new schema for some tables does it help in performance improvement.I think since it is a colection of objects actual perfomance will not help.
Tom Kyte
July 16, 2013 - 12:28 pm UTC

why would a different name help speed things up?

no, creating a new schema is not a performance tuning tool

Slight Clarification

Sukesh, March 19, 2014 - 12:30 pm UTC

Hi Tom,

The answers you provided are really helpful. But is it possible , for a single user to have many schemas?

What a Schema is Exactly

Abeiku, April 16, 2014 - 9:38 am UTC

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

Clusters

Database links

Database triggers

Dimensions

External procedure libraries

Indexes and index types

Java classes, Java resources, and Java sources

Materialized views and materialized view logs

Object tables, object types, and object views

Operators

Sequences

Stored functions, procedures, and packages

Synonyms

Tables and index-organized tables

Views
Tom Kyte
April 16, 2014 - 4:33 pm UTC

which is sort of - i don't know - exactly what I wrote initially?


A reader, November 14, 2014 - 8:48 pm UTC

If a user owns objects then it becomes schema. It's like a database in SQL Server or sybase. If the user don't own objects; then it's just a user; not schema at all.

hello Mr.tom

vedavathi, May 07, 2016 - 12:07 pm UTC

what is performance tuning
Connor McDonald
May 08, 2016 - 10:57 am UTC

The method via which you improve the response time of applications to users, or reduce the resource consumption on the server, or both.

Good info

Shekhar, May 13, 2019 - 10:51 am UTC

It helped to clear all the doubts one would have about schema .

good information by Tom .

Thanks
SD

Schema vs User

krishnarjun rao, September 25, 2019 - 4:01 am UTC

I got good information here. What I understood is, in simple words SCHEMA is USER + User Objects and USER is simply User Name.
SCHEMA = USER + USER DB Objects
USER = USER Name + USER Privileges

Thank you folks for enlightening me :)