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
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
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.
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.
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).
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.
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.
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.
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.
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
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?
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.
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.
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
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
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 :)