What is an Oracle Schema - Database Tutorial 58 - Oracle DBA Tutorial

Sharing buttons:

hi my name is Sam dhanasekaran and

welcome to this database lessons video

series in this video we will talk about

what is a schema so a schema is again a

article user okay the only difference is

in most of the companies I would say

99.9% instead it's a user who can be

used to store the object for example in

the previous video I said that we've

created three users Amir Amir Amitabh

orogeny and Amir okay they are

accountants okay but then they want to

use the HR data or financial data right

so I can store the financial data under

a different a common schema and then I

can give the access to them so that even

in future if our meetup leaves and

somebody else Tom comes you know all I

need to do is just remove our meetups

account but I won't lose the data okay

so now let me create a use or finance

okay okay

it's just same okay finance

okay okay so it's like creating the user

so up to this point this user finance is

a user okay and I'm going to give some

privileges to this user so grant tonight


finance okay

and also I'm going to grant some space

okay generally we don't give unlimited

table space to a schema user

but then the reason I wanted to give is

I wanted to explain this at this moment

when you give unlimited tables with

space privilege to a new sir so meaning

that the user can create object in any

table spaces right any table spaces okay

but then in the real life what happens

is we create a table space for this user

and then we make that table space as the

default tablespace for this is ER okay

I'll come to that later so but the

important point is I wanted to explain

the difference between the user and the

scheme what is the scheme are as well as

I wanted to explain the difference

between user and schema so up until this

point it's just a new sir finance is

just a new sir okay fi n one one one

okay so now so I've granted this one and

let me see what other roles I can

okay so those roles are enough but then

again I wanted to I wanted this user to

be able to create games so I'm giving

this user the ability to create table

okay so now let me see if I can connect

to the database as that user okay

I'm just giving any finance at demo DB

so username finance password this fi n

one one one I'm gonna say the password

I'm just going to use the TNS so that's

the database okay test the connection is


save it okay connection may not contain

the file system okay that's fine okay

let me see if this if it takes this one

okay so I've connected as user finance

here okay so what I can do is I can

create table table tab1 and then I can

call them column one okay and that yeah

we call it integer okay

okay so before creating I wanted to show

you something here so if you if you go

here okay let me go here so I'm just

gonna go here so here in this connection


this connection I've connected as the

DBA right Sam

here I'm connected as finance okay well

then let me open browse using the DBA so

it will shows it will show what tables

do I have okay

as the user Sam I don't have a table

anything so that's fine now here I can

browse other users tables okay so if I

go here let's see if our meal has any

table no okay oh my top I said a table

no okay so Finance I said a table no

okay so up until this point Finance is

no different from our me Rama table

except the fact that there is actually a

live person who be using this account to

login okay so now here you know I'm

gonna create a table for the finance

user okay my table has been created

okay so no let me refresh this okay

so once it is refreshed so if I see

Finance there is a table right so now

this Finance user has become a schema

okay so that's the difference so this

user when a user starts owning some

objects that user becomes a schema and

technically our mere can worn a table

okay but then the business right it

always business will always want

important data to be stored under

these schema names so that even if to

our army our Amitabh I know they leave

the company these users can be dropped

but the data will still be here okay so

now the question is how do army I'm

aware are Amitabh orogeny can access is

stable so this user I logged in as

Finance right I can grant select on

table one to army

okay so

well that's it

are you know the if if this user wants I

mean if this finance schema wants the

finance schema can grant access to other

user sense for our the DBA can do that

to grant select on who wants that table

finance okay

finance dot app one to right Leslie okay

so now when when Amir orogeny login

right they will be able to select from

this table like they would just do

select star from finance table one if

they do that it will show what do they

have okay here you know I logged in as

Sam and I'm able to do that because Sam

is a DBA meaning that Sam can select or

delete or insert update on anybody's

object okay so now that's the difference

between a user and schema okay and this

video I explain what is a schema and the

difference between user and schema again

let me repeat a schema starts out like

an user only thing is it's not attached

to any like person generally and that

user will be used to store objects which

will be accessed by other users okay

that brings in a question what is an

application account okay I'll explain

that later thank you for watching